There is a caveat i wanted to highlight with respect to view criteria that has a performance impact.
if you use a view criteria it is applied after the query block as shown below.
This example is based on the scott schema.
But the fact that ADF view criteria where clause is applied after the main query block will have a huge performance impact in case you end up using a analytical function inside your query block as shown below.
The query plan is shown below
Without view criteria
The query is shown below :-
The execution plan :-
Now this plan is far better as it will firstly filter the rows and then the analytical function row_number() will be applied. You can see that in the plan output that the rows that need to be accessed are 14 (all the rows) and a full index scan (all the index blocks are going to be visited) is going to take place.
The plan output differs due to the fact that whenever you are using analytical function or a rownum clause inside your query. The query optimizer will not push predicates to the inner query block because it can effect the result set returned by the query. While in the former case the query optimizer will push the predicates inside hence execution plans are same.
Hence if you are using analytical functions or a rownum clause in your query be aware of the performance impact and always filter the rows inside.
Note:Also don't choose to ignore null values for predicates that are required and create proper indexes on the table structure depending upon how you filter results. For ex: If predicate is upper(ename)=upper(:bvar) then create a index on upper(ename) .
if you use a view criteria it is applied after the query block as shown below.
This example is based on the scott schema.
select * from (select empno from emp)QRSLT where empno=2;Now the query execution plan will be the same to the output of the following query where you filter the records inside rather than using a viewcriteria.
select empno from emp where empno=:bvar;The plan is shown below :-
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 56244932
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 0 (0)| 00:00:01 |
|* 1 | INDEX UNIQUE SCAN| PK_EMP | 1 | 4 | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("EMPNO"=TO_NUMBER(:BVAR))
But the fact that ADF view criteria where clause is applied after the main query block will have a huge performance impact in case you end up using a analytical function inside your query block as shown below.
select * from (select empno,row_number() over (order by 1) from emp) QRSLT where empno=2;
The query plan is shown below
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1674134785
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 364 | 1 (0)| 00:00:01 |
|* 1 | VIEW | | 14 | 364 | 1 (0)| 00:00:01 |
| 2 | WINDOW NOSORT | | 14 | 56 | 1 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN| PK_EMP | 14 | 56 | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("Q1"."EMPNO"=2)
Without view criteria
The query is shown below :-
select empno,row_number() over (order by 1) from emp where empno=:bvar
The execution plan :-
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
Plan hash value: 1807209526
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 0 (0)| 00:00:01 |
| 1 | WINDOW NOSORT | | 1 | 4 | 0 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN| PK_EMP | 1 | 4 | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=TO_NUMBER(:BVAR))
Now this plan is far better as it will firstly filter the rows and then the analytical function row_number() will be applied. You can see that in the plan output that the rows that need to be accessed are 14 (all the rows) and a full index scan (all the index blocks are going to be visited) is going to take place.
The plan output differs due to the fact that whenever you are using analytical function or a rownum clause inside your query. The query optimizer will not push predicates to the inner query block because it can effect the result set returned by the query. While in the former case the query optimizer will push the predicates inside hence execution plans are same.
Hence if you are using analytical functions or a rownum clause in your query be aware of the performance impact and always filter the rows inside.
Note:Also don't choose to ignore null values for predicates that are required and create proper indexes on the table structure depending upon how you filter results. For ex: If predicate is upper(ename)=upper(:bvar) then create a index on upper(ename) .
