No COUNT STOPKEY in explain plan:- ROWNUM optimization
Product:- Oracle Server (RDBMS)
Range of versions believed to be affected:- 12.1
confirmed affected:- 1. 11.1.0.7
2. 10.2.0.4
Platforms affected:- all
So I was doing explain plan for below SQL on database version 11.2.0.1.0:-
select * from test where rownum = 0;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1829668517
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3219K| 733M| 35580 (2)| 00:07:07 |
| 1 | COUNT | | | | | |
|* 2 | FILTER | | | | | |
| 3 | TABLE ACCESS FULL| TEST | 3219K| 733M| 35580 (2)| 00:07:07 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM=0)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
129363 consistent gets
129358 physical reads
0 redo size
6210 bytes sent via SQL*Net to client
513 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
On Database version 11.2.0.4.0 it was:-
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 2416982823
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 240 | 2 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS FULL| TEST | 1 | 240 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=0)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
6210 bytes sent via SQL*Net to client
513 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
Keep sharing :)
So I was doing explain plan for below SQL on database version 11.2.0.1.0:-
select * from test where rownum = 0;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1829668517
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3219K| 733M| 35580 (2)| 00:07:07 |
| 1 | COUNT | | | | | |
|* 2 | FILTER | | | | | |
| 3 | TABLE ACCESS FULL| TEST | 3219K| 733M| 35580 (2)| 00:07:07 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM=0)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
129363 consistent gets
129358 physical reads
0 redo size
6210 bytes sent via SQL*Net to client
513 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
On Database version 11.2.0.4.0 it was:-
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 2416982823
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 240 | 2 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS FULL| TEST | 1 | 240 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM=0)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
6210 bytes sent via SQL*Net to client
513 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
After studying on oracle support I found this as a bug on 11.2.0.1.0 explained under Oracle
Please check your version for the bug because you can easily see the difference and performance matrix between both explain plans.
Beware! This use of rownum< can cause performance problems. Using rownum may change the all_rows optimizer mode for a query to first_rows, causing unexpected sub-optimal execution plans. One solution is to always include an all_rows hint when using rownum to perform a top-n query.
Keep sharing :)