Hi Folks...
Just wanted to share a observations.... would be oblige to get an conceptual explanation on same!!
SQL> select * from v$version;
BANNER
-----------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
PL/SQL Release 12.1.0.1.0 - Production
CORE 12.1.0.1.0 Production
TNS for 64-bit Windows: Version 12.1.0.1.0 - Production
NLSRTL Version 12.1.0.1.0 - Production
SQL> select count(*) from tab_rownum where rownum < 2;
Execution Plan
----------------------------------------------------------
Plan hash value: 1301825293
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
|* 2 | COUNT STOPKEY | | | | |
| 3 | TABLE ACCESS FULL| TAB_ROWNUM | 1 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM<2)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
5 physical reads
0 redo size
542 bytes sent via SQL*Net to client
543 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
As most of us must be aware of , First_Rows_k Optimization takes place for Rownum Query..!!
hence for above query , estimated cardinality is 1 and count stop key come in plan with filter as ROWNUM<2.
Now let run below SQL.
SQL> select count(*) from tab_rownum where rownum > 2;
In any case.... above SQL won't be any rows for output!!
As rownum expected is greater then 2.....!!
lets check the plan!!
Execution Plan
----------------------------------------------------------
Plan hash value: 141762995
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 425 (1)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | COUNT | | | | |
|* 3 | FILTER | | | | |
| 4 | TABLE ACCESS FULL| TAB_ROWNUM | 91740 | 425 (1)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(ROWNUM>2)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1529 consistent gets
1526 physical reads
If we check cardinality estimations , it is total rows of table..hence no First_Rows_K optimization for such SQL. (All_rows)
Though we wont get any rows.. still oracle scan complete table..
Also... Count Stop key got changed to Filter + Count..!!
Need to understand.. why not oracle have some logic to imposed output for > rownum conditions as zero!
Why filter for seconds run is not terminating SQL executions... it scan complete set.. for zero rows!
Thanks
Deepak M.