I ran into this question several days ago when I was tuning a pagination query.
I received a plan with COUNT STOPKEY operator which was right above VIEW operator.
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 38 (100)| |
| 1 | NESTED LOOPS | | 34 | 70992 | 38 (0)| 00:00:01 |
|* 2 | VIEW | | 34 | 69428 | 4 (0)| 00:00:01 |
|* 3 | COUNT STOPKEY | | | | | |
| 4 | VIEW | | 34 | 68986 | 4 (0)| 00:00:01 |
|* 5 | FILTER | | | | | |
|* 6 | TABLE ACCESS BY INDEX ROWID| APP_USER_RATE | 34 | 1428 | 3 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | IDX_RATE_GETDESC | 34 | | 1 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | SYS_IOT_TOP_805401 | 1 | 20 | 0 (0)| |
|* 9 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE | 1 | 46 | 1 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | PK_EMPLOYEE | 1 | | 0 (0)| |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("RN">=:B4)
3 - filter(ROWNUM<=DECODE(:B3,(-1),10000000000,:B3))
5 - filter( IS NOT NULL)
6 - filter("FLAG"=1)
7 - access("APP_ID"=:B1)
8 - access("APP_ID"=:B1 AND "EMPLOYEE_ID"=:B2)
filter("FLAG"=1)
9 - filter("E"."FLAG"=1)
10 - access("from$_subquery$_002"."EMPLOYEE_ID"="E"."EMPLOYEE_ID")
This fact urged me to doubt if FIRST ROW optimization has taken place.
As stated here
"Where you see the VIEW operator this is Oracle telling you that it has had to “suspend” operations and produce an intermediate result set by running the “sub-plan” under the VIEW".
According this I could not decide exactly would COUNT STOPKEY have any influence on execution of VIEW part. I thought up a way to check what was going on.
I compared A-Rows column for plans which were executed with different pagination range, i. e. first one was execited to receive rows from 1 to 10
and second one was executed to receive rows from 11 to 20. I observed that VIEW part for both plans had strongly correlated values in A-Rows column with pagination range. I believe that this fact can be used to prove FIRST ROW optimization occurrence.
Rows from 1 to 10:
---------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ---------------------------------------------------------------------------------------------------------------------- | 1 | NESTED LOOPS | | 1 | 34 | 10 |00:00:00.01 | 26 | |* 2 | VIEW | | 1 | 34 | 10 |00:00:00.01 | 4 | |* 3 | COUNT STOPKEY | | 1 | | 10 |00:00:00.01 | 4 | | 4 | VIEW | | 1 | 34 | 10 |00:00:00.01 | 4 | |* 5 | FILTER | | 1 | | 10 |00:00:00.01 | 4 | |* 6 | TABLE ACCESS BY INDEX ROWID| APP_USER_RATE | 1 | 34 | 10 |00:00:00.01 | 3 | |* 7 | INDEX RANGE SCAN | IDX_RATE_GETDESC | 1 | 34 | 10 |00:00:00.01 | 1 | |* 8 | INDEX UNIQUE SCAN | SYS_IOT_TOP_805401 | 1 | 1 | 1 |00:00:00.01 | 1 | |* 9 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE | 10 | 1 | 10 |00:00:00.01 | 22 | |* 10 | INDEX UNIQUE SCAN | PK_EMPLOYEE | 10 | 1 | 10 |00:00:00.01 | 12 | ----------------------------------------------------------------------------------------------------------------------Rows from 11 to 20:
---------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ---------------------------------------------------------------------------------------------------------------------- | 1 | NESTED LOOPS | | 1 | 34 | 10 |00:00:00.01 | 26 | |* 2 | VIEW | | 1 | 34 | 10 |00:00:00.01 | 4 | |* 3 | COUNT STOPKEY | | 1 | | 20 |00:00:00.01 | 4 | | 4 | VIEW | | 1 | 34 | 20 |00:00:00.01 | 4 | |* 5 | FILTER | | 1 | | 20 |00:00:00.01 | 4 | |* 6 | TABLE ACCESS BY INDEX ROWID| APP_USER_RATE | 1 | 34 | 20 |00:00:00.01 | 3 | |* 7 | INDEX RANGE SCAN | IDX_RATE_GETDESC | 1 | 34 | 20 |00:00:00.01 | 1 | |* 8 | INDEX UNIQUE SCAN | SYS_IOT_TOP_805401 | 1 | 1 | 1 |00:00:00.01 | 1 | |* 9 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE | 10 | 1 | 10 |00:00:00.01 | 22 | |* 10 | INDEX UNIQUE SCAN | PK_EMPLOYEE | 10 | 1 | 10 |00:00:00.01 | 12 | ----------------------------------------------------------------------------------------------------------------------At this point I decided that I had reached optimization goal for the query.
No comments:
Post a Comment