Oracle Views in Oracle Forms: optimize data retrieval from a view avoiding the upstream filter in Oracle Forms

Oracle Views are useful tools for query multiple tables or get aggregate result; Oracle Forms can use views and display view data like a normal data table. In high cardinality data tables this artifact produce a lack in query time result, if we needs to add “ default_where “ condition to our block. We can find the cause of this lack in a underhand splitting of sql execution, which makes useless any kind of sql optimization:
We can imagine the various levels of macro processing as two layers
First layer:
Create view example_vw as
select field1, field2, filed3
from table1, table2, table3
where … (join condition..)
Second layer(on Oracle Forms):
Sqltext := ‘field4  =’||:NONBASETABLE.FILTER1;
If :NONBASETABLE.FILTER2 is not null then
                Sqltext := Sqltext ||‘ and field5  =’||:NONBASETABLE.FILTER2;
End if;
Set_block_property (‘EXAMPLE_VW’, default_where, sqltext);
Execute_query;
Output time increases dramatically because Forms first wait for view recordset; obtained the recordset from the first layer, filter block is applied. If the view returns thousands of records, they can lock the window of Forms for  few minutes, waiting for the Forms sql engine finish applying the filter block on the entire recordset.
However, we can work around  this situation by querying and filtering the view outside Oracle Forms. In this case, the new statement will be
Create view example_vw as
select field1, field2, filed3
from table1, table2, table3
where … (join condition..)
and field4 =:NONBASETABLE.FILTER1
and field5=nvl(:NONBASETABLE.FILTER2, field5)
Mmm.. ok… but it’s clearly noticeable that parameters can’t be passed through Forms to an Oracle view in this way. So we can create a table example_vw_params
Create table example_vw_params(
V_filter1 number,
V_filter2 varchar2(5));
Redefining the view:
Create view example_vw as
select field1, field2, filed3
from table1, table2, table3, example_vw_params
where … (join condition..)
and field4  = V_FILTER1
and field5=nvl(V_FILTER2, field5);
And finally we can change Forms trigger code
begin
Delete from example_vw_params;
Insert into example_vw_params
Select :NONBASETABLE.FILTER1,: NONBASETABLE.FILTER2 from dual;
Go_block(‘EXAMPLE_VW’);
Execute_query;
                :system.message_level:=5;
                commit;
                :system.message_level:=25;
End;
That’s all! View query joins parameters in own session –this is the meaning of post “execute query” commit – and query result appears in Forms in a hundredth of the time of the “classic” method.