Rownum condition and Explain plan

Recently posted an observation on OTN pertaining to Rownum and Explain plan for > and < where condition.
Though functionality scenarios were incorrect, but as was keen to understand oracle internals wanted to know expert feedback\comments on same.

Below are the observations.

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
1 rows processed

For proper Rownum Condition, Oracle internally applies First_Rows Optimization.On current sql expected rows is only 1, so though table might have millions of rows we only want one rows in output.!
Hence in plan oracle applies “COUNT STOPKEY” logic to terminate execution when 1 row is fetched.
Cardinality estimations is also 1.

now let check vice versa scenario.
SQL> select count(*) from tab_rownum where rownum > 2;

In any case above SQL won’t return any row, conceptually rownum doesn’t work such way that > 2 conditions can return some rows.
so actual rows projected will be 0.

for reference and good insight please check Ask Tom Rownum

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

Observations :
If we check cardinality estimations, it is total rows of table hence no First_Rows_K optimization for such SQL.
Though we wont get any rows still oracle scan complete table for Zero rows in output, which seems to be unnecessary.
Also Count Stopkey got changed to Filter + Count.
filter(ROWNUM>2) is applied, that can’t be true.

So my question was below on OTN.
Need to understand.. why not oracle have some logic to imposed output for > rownum conditions as zero! (As it has for Rownum < conditions)
Why filter for seconds run is not terminating SQL executions… it scan complete set/Table.. for zero rows?

Below are some of best reply received on OTN for above as per me!.

“It is using a filter as the plan clearly shows (rownum > 2). Rownum is dynamicaly calculated and tested. The first row gets rownum 1 and is dismissed because 1 2 is not checked for . It makes no sense. Rownum < 2 is specially considered because a top n query makes a lot of sense."

WHERE ROWNUM > 2 is a very common mistake, but it’s a mistake that most people make once, in Development. They learn that it doesn’t work, and they don’t do it again. I don’t expect Oracle to make features so that once-in-a-lifetime Development queries run faster. They’re probably more concerned with the kinds of things people do every day, in Production.

please visit below OTN link for informations convey by expert.
Rownum and explain plan for where condition

About Deepak Mahto

Having 8+ Years of relevant/exciting/fruitful/challenging/learning in Oracle Technology. Currently working as Oracle Performance Consultant in an MNC. Hold Expertise in Performance Engineering, SQL Tuning, Database Tuning, SQL, PLSQL Development/Design Considerations and Oracle Internals. I enjoy reading on Oracle internals, posting some of my finding or observations and playing football.
This entry was posted in Oracle Internal and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s