Alias on Rownum might degrade performance in oracle.

We use First’s row filtering(using Rownum) for lot of day to day functional implementation, Pagination being one of the best example.
Rownum clause is best way to impose First – N Rows optimization and restrict subset of projected rows as desired.

During one of performance case analysis , come across below format sql performing bad on oracle 11g version.

select *
(select rownum rn ,
from (select from table1, table2
order by column_having_index))
where rn < 10;

Basically we were fetching top 9 rows based on some column orders.

Lets Walk through an example to understand underlying problem.

create table rownum_alias as
select * from dba_objects;

create index idx_object_id on rownum_alias(object_id);

exec dbms_stats.gather_table_stats('DMAHTO','ROWNUM_ALIAS');

Problematic SQL was similar as below,

SELECT /*+ GATHER_PLAN_STATISTICS IDE2 */ *
FROM
(SELECT ROWNUM RN
FROM
(SELECT * FROM ROWNUM_ALIAS WHERE OBJECT_ID IS NOT NULL ORDER BY OBJECT_ID
)
)
WHERE rn < 3;

We are fetching top 2 rows based on object_id order.
Lets walk through underlying execution plan.
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('3P2PKGYKX171G',NULL,'ALLSTATS LAST'));

Plan hash value: 2657346715
----------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | A-Rows | A-Time | Buffers | Reads |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 2 |00:00:00.01 | 201 | 199 |
|* 1 | VIEW | | 90020 | 2 |00:00:00.01 | 201 | 199 |
| 2 | COUNT | | | 90020 |00:00:02.62 | 201 | 199 |
| 3 | VIEW | | 90020 | 90020 |00:00:02.60 | 201 | 199 |
|* 4 | INDEX FULL SCAN| IDX_OBJECT_ID | 90020 | 90020 |00:00:02.58 | 201 | 199 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN"<3)
4 - filter("OBJECT_ID" IS NOT NULL)

Observations :
1. As per SQL condition (rn < 3), we only need top 2 rows in output.
But oracle estimate Cardinality to be "90020", which is far offset then actual rows "2".

2.
filter("RN"<3)
Necessary Rows Filtering is applied after fetching all rows from Index.
Ideally it should had been restricted after fetching first few rows from index.

3.
Though we are applied ROWNUM conditions, but still COUNT STOP KEY does not come into play.
It is mostly because of Alias Assign to Rownum column.

Count Stop Key Example :=

SQL_ID c1tcw8czdznzw, child number 0
-------------------------------------
select * from rownum_alias
WHERE object_id IS NOT NULL and rownum < 2

Plan hash value: 3513997554
----------------------------------------------------------------------
| Id | Operation | Name |E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 1 |00:00:00.01 | 3 |
|* 1 | COUNT STOPKEY | | | 1 |00:00:00.01 | 3 |
|* 2 | TABLE ACCESS FULL| ROWNUM_ALIAS | 1 | 1 |00:00:00.01 | 3 |
----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<2)
2 - filter("OBJECT_ID" IS NOT NULL)

Due to Rownum Conditions, it stop Execution after fetching rownum < 2 rows.
Same is not happening for current case.

Instead of "COUNT STOPKEY" in execution plan as ideal run,optimizer is pushing "COUNT" operations and necessary filter is applied post that.

Assigning alias to rownum, cause it to be treated as normal columns.
It does not exhibit first n rows optimization.

Let rewrite same sql as below, i.e. Replacing Alias rn for rownum with rownum itself.
SELECT *
FROM
(SELECT rownum
FROM
(SELECT * FROM rownum_alias WHERE object_id IS NOT NULL ORDER BY object_id
)
)
WHERE rownum < 3
----------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 |00:00:00.01 | 2 |
|* 1 | COUNT STOPKEY | | 1 | | 2 |00:00:00.01 | 2 |
| 2 | VIEW | | 1 | 2 | 2 |00:00:00.01 | 2 |
| 3 | COUNT | | 1 | | 2 |00:00:00.01 | 2 |
| 4 | VIEW | | 1 | 2 | 2 |00:00:00.01 | 2 |
|* 5 | INDEX FULL SCAN| IDX_OBJECT_ID | 1 | 2 | 2 |00:00:00.01 | 2 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<3)
5 - filter("OBJECT_ID" IS NOT NULL)

Replacing aliase improve cardinality estimation and ultimately rows fetching where restricted after fetching desired number of rows.

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