Another explanation
Mikito Harakiri, July 15, 2005 - 6:16 pm UTC
rownum is a hack that precedes row_number(). The later pseudocolumn is much cleaner defined
select ename, row_number() over (order by ename) num from emp
labeled each row with additional column. However, either
select ename, row_number() over (order by ename) num
from emp
where num = 2
and
select ename, row_number() over (order by ename) num from emp
where row_number() over (order by ename) = 2
are illegal. You have to choose what of the two conflicting operations to do first -- projection (maybe "anti-projection" is a better term for adding a pseudocolumn), or selection.
This is, perhaps, the only analytics achievement: clean integer row numbering.
July 15, 2005 - 8:58 pm UTC
(Mikito, I'll never in a billion years understand your point or goal....)
rownum is a very super efficient method to do TOP-N and pagination queries that blows row_number() away
(an analytic function, Mikito -- I'm very surprised at you! You have accepted them finally?)
Your examples, they are the worst way to do a top-n query.
If you have access to Effective Oracle by Design, I explain rownum in some detail and the top-n query optimization that makes rownum "slightly" (as in infinitely) better than row_number().
row_number definitely has a place, just NOT HERE, not for this.
ROWNUM ROCKS! Just use it, once you understand it.
ANALYTICS rock and roll, they are phenomenal, greatest thing to happen to SQL since the keyword select.
Mikito
Jon, July 15, 2005 - 10:10 pm UTC
Mikito, life would be boring with you. Rock on.
You're kinda like the Joey (a la Friends) of the AskTom site...
Mikito reprise
Jon, July 15, 2005 - 10:14 pm UTC
That was suppose to read:
"Mikito, life would be boring without you. Rock on.
You're kinda like the Joey (a la Friends) of the AskTom site... "
Mikito
A reader, July 15, 2005 - 11:34 pm UTC
I think Mikito is a Smart guy.But he does not have the right driving directions. :). He gets lost and If we show him the direction he still insists he is on the right Track.
After all he gave us the connect by trick.
Thanx Mickey Mouse.
Mikito's point or goal
A reader, July 18, 2005 - 3:02 am UTC
"Mikito, I'll never in a billion years understand your point or goal"
why ?
I can understand Mikito very clear, doesn't he write good English ? (And I am not an English native speaker - maybe it's because of that (?))
"
rownum is a hack that precedes row_number(). The later pseudocolumn is much
cleaner defined
"
100% agree.
probably true, that you can optimize some queries via a hack,
but
a hack is a hack is a hack
what's so difficult to understand that ?
"However, either .... are illegal "
Mikito: true.
I agree, that it would be nice, we could do such queries.
"This is, perhaps, the only analytics achievement ..."
Mikito: I disagree
I've seen tons of "rockin' and rollin'" analytics stuff
on this side and elsewhere
July 18, 2005 - 8:00 am UTC
I don't understand his point or goal -- nothing to do with his english. I don't get his point or goal.
row_number is not a pseudo-column, rownum is, and rownum is hardly "a hack". If you read the documentation and gain an understanding of it, rownum is an invaluable tool -- one I would NOT do without. You would not be able to paginate through my articles without.
If you use row_number() when you can use rownum (SHOULD use rownum) the only thing you'll achieve:
lack of performance
increased resource usage
Your choice.
no rownum ?
Matthias Rogel, September 28, 2005 - 9:56 am UTC
Hallo Tom,
try to select rownum and only get NULL
what's wrong or can you explain ?
SQL> set null nada
SQL> select row_number() over (order by rownum), rownum from all_objects where rownum<10;
ROW_NUMBER()OVER(ORDERBYROWNUM) ROWNUM
------------------------------- ----------
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
9 rows selected.
SQL> REM great
SQL> REM but now
SQL> select row_number() over (order by sysdate), rownum from all_objects where rownum<10;
ROW_NUMBER()OVER(ORDERBYSYSDATE) ROWNUM
-------------------------------- ----------
1 nada
2 nada
3 nada
4 nada
5 nada
6 nada
7 nada
8 nada
9 nada
9 rows selected.
SQL> REM why ?
September 28, 2005 - 10:32 am UTC
looks like a 9i issue - suggest you contact support for that.
10g does not reproduce.
Matthias Rogel, September 28, 2005 - 10:42 am UTC
reproduced on
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Prod
PL/SQL Release 10.1.0.3.0 - Production
CORE 10.1.0.3.0 Production
TNS for Linux: Version 10.1.0.3.0 - Production
NLSRTL Version 10.1.0.3.0 - Production
September 28, 2005 - 10:44 am UTC
but not 10.1.0.4 which is what I used - the current production release.
another rownum bug also in 10.2 ?
Matthias Rogel, September 30, 2005 - 10:50 am UTC
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> select row_number() over (order by rownum desc), rownum from all_objects where rownum<10
2 /
ROW_NUMBER()OVER(ORDERBYROWNUMDESC) ROWNUM
----------------------------------- ----------
1 9
2 8
3 7
4 6
5 5
6 4
7 3
8 2
9 1
9 rows selected.
That's false, isn't it ?
according to documentation
[
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/pseudocolumns009.htm#SQLRF00255 ]
"For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on."
Here, the first column has ROWNUM 9, the second has 8, ...
According to tkyte
"If
you read the documentation and gain an understanding of it, rownum is an
invaluable tool -- one I would NOT do without. "
A bug or doesn't even Oracle self understand the heck rownum
?
Do YOU understand what's going on here ?
I agree fully with Mikito when he says rownum is a heck
more: it seems to be a conceptual error
September 30, 2005 - 11:53 am UTC
your order by in the analytic sorted the data
without an order by on the QUERY you have no reasonable expectation of the order the rows are returned in.
I see nothing wrong here.
row_number, the analytic is done *after* the where clause AND before any sorting of the result set.
This looks perfectly right to me? You said
select some data from the table... (it did)
assign rownum (it did)...
keep the first 9 (it did)....
Then assign a row_number() to the data after sorting by rownum desc (it did)
What is Mikito connect by trick ?
Reader, February 12, 2006 - 7:57 pm UTC
What is mikito connect by trick ?
Thanks
February 13, 2006 - 8:06 am UTC
the data generator
with data as (select level l from dual connect by level <= :n)
select * from data;
duplicate rownum
M. Torki, December 07, 2010 - 7:24 am UTC
we move our data on single oracle 11g to oracle RAC 10g. after that when we run this query:
select rownum from mytable where rownum < 5;
result is:
2
4
6
8
please help me to solve this problem.
thanks.
December 07, 2010 - 11:07 am UTC
if that is really happening - please call support - I've never seen that before.
ROWNUM vs. ROW_NUMBER()
James Su, September 15, 2011 - 1:04 pm UTC
Plans in 11g:
select * from (select * from t order by sort_id) where rownum<=5;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 3299198703
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 195 | 4 (25)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 11 | 429 | 4 (25)| 00:00:01 |
|* 3 | SORT ORDER BY STOPKEY| | 11 | 110 | 4 (25)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T | 11 | 110 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=5)
3 - filter(ROWNUM<=5)
select * from (select t.*,row_number() over(order by sort_id) rn from t) where rn<=5;
Execution Plan
----------------------------------------------------------
Plan hash value: 3047187157
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 572 | 4 (25)| 00:00:01 |
|* 1 | VIEW | | 11 | 572 | 4 (25)| 00:00:01 |
|* 2 | WINDOW SORT PUSHED RANK| | 11 | 110 | 4 (25)| 00:00:01 |
| 3 | TABLE ACCESS FULL | T | 11 | 110 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN"<=5)
2 - filter(ROW_NUMBER() OVER ( ORDER BY "SORT_ID")<=5)
You can see "WINDOW SORT PUSHED RANK" with row_number() function here. Does it mean it's now as good as the ROWNUM method?
September 16, 2011 - 1:29 pm UTC
It has the same net effect these days, yes.
More on ROWNUM vs. ROW_NUMBER()
Stew Ashton, September 17, 2011 - 5:31 am UTC
I did some tests on 11.2.0.1 EE and found ROWNUM to be more efficient in some circumstances: when an index is available, ROWNUM uses it whereas ROW_NUMBER() sometimes does a full table scan.
> drop table t_sorted;
table T_SORTED dropped.
> create table t_sorted as select * from all_objects order by object_id;
table T_SORTED created.
> exec DBMS_STATS.GATHER_TABLE_STATS(ownname => user, tabname => 'T_SORTED');
anonymous block completed
> create unique index t_sorted_idx on t_sorted(object_id);
unique index T_SORTED_IDX created.
> drop table t_random;
table T_RANDOM dropped.
> create table t_random as select * from t_sorted order by dbms_random.random;
table T_RANDOM created.
> exec dbms_stats.gather_table_stats(ownname => user, tabname => 'T_RANDOM');
anonymous block completed
> create unique index t_random_idx on t_random(object_id);
unique index T_RANDOM_IDX created.
> alter system set statistics_level='ALL';
system SET altered.
> select * from (
select * from t_sorted order by object_id
) where rownum <= 5;
…
> select * from table(dbms_xplan.display_cursor(null,null,'IOSTATS LAST'))
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | Buffers | Reads |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 5 | 3 | 1 |
|* 1 | COUNT STOPKEY | | 1 | | 5 | 3 | 1 |
| 2 | VIEW | | 1 | 5 | 5 | 3 | 1 |
| 3 | TABLE ACCESS BY INDEX ROWID| T_SORTED | 1 | 71757 | 5 | 3 | 1 |
| 4 | INDEX FULL SCAN | T_SORTED_IDX | 1 | 5 | 5 | 2 | 1 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=5)
> select * from (
select * from t_random order by object_id
) where rownum <= 5
…
> select * from table(dbms_xplan.display_cursor(null,null,'IOSTATS LAST'))
PLAN_TABLE_OUTPUT
…
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | Buffers | Reads |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 5 | 7 | 1 |
|* 1 | COUNT STOPKEY | | 1 | | 5 | 7 | 1 |
| 2 | VIEW | | 1 | 5 | 5 | 7 | 1 |
| 3 | TABLE ACCESS BY INDEX ROWID| T_RANDOM | 1 | 71757 | 5 | 7 | 1 |
| 4 | INDEX FULL SCAN | T_RANDOM_IDX | 1 | 5 | 5 | 2 | 1 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=5)
> select * from (
select t_sorted.*,row_number() over(order by object_id) rn from t_sorted
) where rn<=5
…
> select * from table(dbms_xplan.display_cursor(null,null,'IOSTATS LAST'))
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time| Buffers |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 5 | 00.01 | 3 |
|* 1 | VIEW | | 1 | 71757 | 5 | 00.01 | 3 |
|* 2 | WINDOW NOSORT STOPKEY | | 1 | 71757 | 5 | 00.01 | 3 |
| 3 | TABLE ACCESS BY INDEX ROWID| T_SORTED | 1 | 71757 | 6 | 00.01 | 3 |
| 4 | INDEX FULL SCAN | T_SORTED_IDX | 1 | 71757 | 6 | 00.01 | 2 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN"<=5)
2 - filter(ROW_NUMBER() OVER ( ORDER BY "OBJECT_ID")<=5)
> select * from (
select t_random.*,row_number() over(order by object_id) rn from t_random
) where rn<=5
…
> select * from table(dbms_xplan.display_cursor(null,null,'IOSTATS LAST'))
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 5 |00:00:00.69 | 1027 |
|* 1 | VIEW | | 1 | 71757 | 5 |00:00:00.69 | 1027 |
|* 2 | WINDOW SORT PUSHED RANK| | 1 | 71757 | 6 |00:00:00.69 | 1027 |
| 3 | TABLE ACCESS FULL | T_RANDOM | 1 | 71757 | 71757 |00:00:00.32 | 1027 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN"<=5)
2 - filter(ROW_NUMBER() OVER ( ORDER BY "OBJECT_ID")<=5)
22 rows selected
September 17, 2011 - 11:19 am UTC
that is due to rownum actually changing the estimated cardinality values in this case...
for top-n queries, a first_rows(n) directive (hint) could often be applied
peek a table
a reader, October 05, 2011 - 12:49 pm UTC
Hi Tom,
Is "select 1 from t where rownum = 1" an efficient way to peek if a table has >0 rows or empty?
But seems when this table is huge, it still takes some time to return, why? I expect it returns immediately because it just fetch one row and return, isn't it?
Thanks
October 05, 2011 - 1:11 pm UTC
Let us say there are no indexes on this table OR none of the existing indexes are on a column that is defined as NOT NULL (if an index is on all nullable columns, it might not point to every row in the table - so we cannot use it instead of the table)...
Let us say that once upon a time, the table is full with 1,000,000 rows.
Let us say that you've deleted the "first" 999,999 rows - only the last row remains.
We'll have to full scan over the data blocks that used to hold the first 999,999 rows to find the first row.
Solutions if this is your case:
o create at least one index on a NOT NULL attribute, we can scan that instead.
o alter table t shrink space (reorganize the table)
o use an index access path - instead of just "where rownum = 1" add "where rownum = 1 and some_indexed_column = some_value" (consider the nulls however, if that column allows nulls..... you won't see it in that query!)
ROWNUM vs. ROW_NUMBER() - 15-SEP-2011 -Review
Rajeshwaran, Jeyabal, October 06, 2011 - 11:37 am UTC
Tom:
I was reading your reply two post above.
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:44376372354520#3914041400346764532 And you said
It has the same net effect these days, yes. When using ROWNUM we sorted only 5 rows from 6M rows (this is clearly show in Row Source Operation since pw=0 in all steps, since we have few rows to sort it happened in memory and not at disk)
select * from
(select object_name,rownum from big_table order by object_name)
where rownum <= 5
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.02 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 2.75 25.15 88246 88334 0 5
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 2.75 25.18 88246 88334 0 5
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 91
Rows Row Source Operation
------- ---------------------------------------------------
5 COUNT STOPKEY (cr=88334 pr=88246 pw=0 time=0 us)
5 VIEW (cr=88334 pr=88246 pw=0 time=0 us cost=67639 size=180651240 card=6021708)
5 SORT ORDER BY STOPKEY (cr=88334 pr=88246 pw=0 time=0 us cost=67639 size=150542700 card=6021708)
6021708 COUNT (cr=88334 pr=88246 pw=0 time=40574748 us)
6021708 TABLE ACCESS FULL BIG_TABLE (cr=88334 pr=88246 pw=0 time=22571578 us cost=24111 size=150542700 card=6021708)But, when we use analytics we sort entire 6M rows to get 5 rows (see pw=22670 in Window sort operation below, sorting high volulme of data not sufficient in memory so went to disk)
select * from
( select object_name,row_number() over(order by object_name) as rnum
from big_table
) where rownum <=5
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 6.53 29.05 88816 88334 216 5
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 6.53 29.05 88816 88334 216 5
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 91
Rows Row Source Operation
------- ---------------------------------------------------
5 COUNT STOPKEY (cr=88334 pr=88816 pw=22670 time=0 us)
5 VIEW (cr=88334 pr=88816 pw=22670 time=0 us cost=67639 size=180651240 card=6021708)
5 WINDOW SORT (cr=88334 pr=88816 pw=22670 time=0 us cost=67639 size=150542700 card=6021708)
6021708 TABLE ACCESS FULL BIG_TABLE (cr=88334 pr=88245 pw=0 time=23754542 us cost=24111 size=150542700 card=6021708)
October 06, 2011 - 2:02 pm UTC
select * from
( select object_name,row_number() over(order by object_name) as rnum
from big_table
) where<b> rownum </b> <=5
^^^^^^do you see your mistake now :)
ROWNUM vs. ROW_NUMBER() - 15-SEP-2011 -Review
Rajeshwaran, Jeyabal, October 06, 2011 - 10:13 pm UTC
Thanks Tom. I see the same net effect now!
select * from
(select object_name from big_table order by object_name)
where rownum <= 5
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 2.09 15.96 88245 88334 0 5
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 2.09 15.96 88245 88334 0 5
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 91
Rows Row Source Operation
------- ---------------------------------------------------
5 COUNT STOPKEY (cr=88334 pr=88245 pw=0 time=0 us)
5 VIEW (cr=88334 pr=88245 pw=0 time=0 us cost=67639 size=102369036 card=6021708)
5 SORT ORDER BY STOPKEY (cr=88334 pr=88245 pw=0 time=0 us cost=67639 size=150542700 card=6021708)
6021708 TABLE ACCESS FULL BIG_TABLE (cr=88334 pr=88245 pw=0 time=23010998 us cost=24111 size=150542700 card=6021708)
select * from
( select object_name,row_number() over(order by object_name) as rnum
from big_table
) where rnum <=5
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 2.29 16.12 88245 88334 0 5
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 2.29 16.12 88245 88334 0 5
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 91
Rows Row Source Operation
------- ---------------------------------------------------
5 VIEW (cr=88334 pr=88245 pw=0 time=0 us cost=67639 size=180651240 card=6021708)
6 WINDOW SORT PUSHED RANK (cr=88334 pr=88245 pw=0 time=0 us cost=67639 size=150542700 card=6021708)
6021708 TABLE ACCESS FULL BIG_TABLE (cr=88334 pr=88245 pw=0 time=24981284 us cost=24111 size=150542700 card=6021708)
Why row_number() cannot be put in an expression?
Frank Feng, November 01, 2011 - 12:25 pm UTC
It seems that row_number() cannot be put in an expression. It this a bug? (DB version 11.2.0.1)
SQL> with data as (select mod(level,5) a, mod(level,3) b from dual connect by level < 100)
2 select a, b, row_number() over (order by 1, 2) rn
3 from data
4 group by a, b
5 order by a, b
6 /
A B RN
---------- ---------- ----------
0 0 1
0 1 2
0 2 3
1 0 4
1 1 5
1 2 6
2 0 7
2 1 8
2 2 9
3 0 10
3 1 11
3 2 12
4 0 13
4 1 14
4 2 15
15 rows selected.
SQL> with data as (select mod(level,5) a, mod(level,3) b from dual connect by level < 100)
2 select a, b, (row_number() over (order by 1, 2))+1 rn
3 from data
4 group by a, b
5 order by a, b
6 /
A B RN
---------- ---------- ----------
0 0 2
0 1 2
0 2 2
1 0 2
1 1 2
1 2 2
2 0 2
2 1 2
2 2 2
3 0 2
3 1 2
3 2 2
4 0 2
4 1 2
4 2 2
15 rows selected.
November 02, 2011 - 4:10 am UTC
it was a bug - I cannot reproduce in either 10.2.0.5 or 11.2.0.2
Sorry, I am wrong.
Frank Feng, November 01, 2011 - 1:08 pm UTC
Sorry, I am wrong. The reason is the "over (order by a, b)" cannot be "over (order by 1, 2)".
SQL> with data as (select mod(level,5) a, mod(level,3) b from dual connect by level < 100)
2 select a, b, (row_number() over (order by a, b))+1 rn
3 from data
4 group by a, b
5 order by a, b
6 /
A B RN
---------- ---------- ----------
0 0 2
0 1 3
0 2 4
1 0 5
1 1 6
1 2 7
2 0 8
2 1 9
2 2 10
3 0 11
3 1 12
3 2 13
4 0 14
4 1 15
4 2 16
15 rows selected.
November 02, 2011 - 4:12 am UTC
no, it should have worked with order by 1, 2 - row_number should have been unique.
Minarva, April 09, 2014 - 11:04 am UTC
Hi Tom,
My Oracle version is 11.2.0.2.
I have few doubts regarding pagination query with FIRST_ROWS hint.
My table structure is as below :
Create table cat(CATEGORYID number , CATEGORYTYPE varchar2(10),value varchar2(100));
Create index idx1 on cat( CATEGORYID, CATEGORYTYPE);
Total record count in this table is 60Lakh. For categorytype = 'NATIONALITY', 10Lakh records present.
Query :
select *
from ( select /*+ FIRST_ROWS(50) */
abc.*, ROWNUM rnum
from ( select cat.categoryid, cat.categorytype,
cat.VALUE
FROM cat cat
WHERE
(cat.categorytype = 'NATIONALITY')
ORDER BY cat.categoryid ASC ) abc
where ROWNUM <=
:1 )
where rnum > :2;
For page 1 :
value for :1 = 50
value for :2 = 0
Elapsed: 00:00:00.74
Execution Plan
----------------------------------------------------------
Plan hash value: 3614064812
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 18750 | 1 (0)| 00:00:01 |
|* 1 | VIEW | | 50 | 18750 | 1 (0)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 51 | 18462 | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| CAT | 1001K| 16M| 1 (0)| 00:00:01 |
|* 5 | INDEX FULL SCAN | IX_CATEGORIES_IDS_TYPE_1 | 51 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RNUM">0)
2 - filter(ROWNUM<=50)
5 - access("CAT"."CATEGORYTYPE"=U'NATIONALITY')
filter("CAT"."CATEGORYTYPE"=U'NATIONALITY')
Last page :
value for :1 = 1000000
value for :2 = 999950
select *
from ( select /*+ FIRST_ROWS(50) */
abc.*, ROWNUM rnum
from ( select cat.categoryid, cat.categorytype,
cat.VALUE
FROM cat cat
WHERE
(cat.categorytype = 'NATIONALITY')
ORDER BY cat.categoryid ASC ) abc
where ROWNUM <=
1000000 )
where rnum >999950;
Elapsed: 00:00:02.10
Execution Plan
----------------------------------------------------------
Plan hash value: 3614064812
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 51 | 19125 | 1 (0)| 00:00:01 |
|* 1 | VIEW | | 51 | 19125 | 1 (0)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 51 | 18462 | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| CAT | 1001K| 16M| 1 (0)| 00:00:01 |
|* 5 | INDEX FULL SCAN | IX_CATEGORIES_IDS_TYPE_1 | 51 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RNUM">999950)
2 - filter(ROWNUM<=1000000)
5 - access("CAT"."CATEGORYTYPE"=U'NATIONALITY')
filter("CAT"."CATEGORYTYPE"=U'NATIONALITY')
Observation : access path and cost of the query remain same for all pages. Only time taken increased
1. Even though cost is less, why time taken is more?
2. For first page, my bind variable :1 value is 50, So ROWNUM itself restricting it to 50 records.
Then what is the purpose of adding FIRST_ROW hint?
3. For above query in trace, I can see OPTIMIZER_MODE = FIRST_ROWS, but in status column of sql_monitor table I can see ‘DONE (ALL ROWS)’ for the above query.
Rather than DONE (FIRST N ROWS) why it is showing ‘DONE (ALL ROWS)’?
April 16, 2014 - 5:19 pm UTC
Lakh is a term without meaning outside of India... Just fyi, probably best to stick with "hundreds, thousands, millions" and so on.
so you have about 6,000,000 records. 1,000,000 for "nationality"
that said, first_rows optimizes a query to ..... get to the FIRST ROW as fast as possible.
all_rows on the other hand, optimizes a query to ..... get to the LAST ROW as fast as possible.
To get to row 1,000,000 using an index is "beyond not smart"
ok that said.... (oh how I wish you would have pushed the "code" button, your output is really really really REALLY hard to read....)
1) if you use an index to read the first 10 records, then you use that index to read OVER almost 1,000,000 rows to get the last 10 rows - why wouldn't you think the time would increase? you are obviously doing much more work. you are scanning over the bulk of the index to get to the "end" of it to get the last page.
the cost stays the same because you are asking for 51 records, that is what the first_rows hint does, it artificially lowers the estimated cardinality values to influence the plan. You told us "pretend you are getting the first 50".
2) see above, it is to tell the optimizer to optimize the plan to get the FIRST 50 ROWS.
3) all rows is the database setting (it is the default).
that said, you should go to google and search for "oracle". Note that there are LOTS of results.
Now start going to page 10, 15, 20, ..... notice that EACH PAGE takes a little longer in general than the previous page.
google is optimized to get to the first row as fast as possible.
Now, try to go to page 100 of the result set. I'll wait
.................
oh, you cannot get there! google knows that if you have a result set of thousands of rows - we humans cannot deal with it, it makes no sense to us. So, it stops - it says "don't be silly, do a better search"
In short, use the google logic for pagination. There is no such thing as "go to last page". There is no such thing as "go to page 1,253". there is no going more than say 10 pages further at a time.
If you want to get to the last page of a big result set - you really wanted to getto the FIRST PAGE of a query that orders the result in DESC mode... think about it. DO NOT EVER GO TO THE LAST PAGE - instead, go to the first page of a result set ordered in the opposite direction.
Minarva, April 22, 2014 - 7:16 am UTC
Hi Tom,
1. I understood, with increase in page size the time taken also increased as it will traverse through more data.
But my question is why cost is not changing. If i will remove the FIRST_ROWS hint, then cost is different for page 1 and for last page.
But with hint cost remain same.
Page1 :
SELECT *
FROM (SELECT abc.*, ROWNUM rnum
FROM ( SELECT cat.categoryid, cat.categorytype, cat.VALUE
FROM cat cat
WHERE (cat.categorytype = 'NATIONALITY')
ORDER BY cat.categoryid ASC) abc
WHERE ROWNUM <= 50)
WHERE rnum > 0;
Plan
SELECT STATEMENT ALL_ROWS Cost: 1 Bytes: 18,750 Cardinality: 50
5 VIEW TBAADM. Cost: 1 Bytes: 18,750 Cardinality: 50
4 COUNT STOPKEY
3 VIEW TBAADM. Cost: 1 Bytes: 18,462 Cardinality: 51
2 TABLE ACCESS BY INDEX ROWID TABLE TBAADM.CAT Cost: 1 Bytes: 17,026,231 Cardinality: 1,001,543
1 INDEX FULL SCAN INDEX CRMUSER.IX_CATEGORIES_IDS_TYPE_1 Cost: 1 Cardinality: 51
Last page :
SELECT *
FROM (SELECT abc.*, ROWNUM rnum
FROM ( SELECT cat.categoryid, cat.categorytype, cat.VALUE
FROM cat cat
WHERE (cat.categorytype = 'NATIONALITY')
ORDER BY cat.categoryid ASC) abc
WHERE ROWNUM <= 1000000)
WHERE rnum > 999950;
Plan
SELECT STATEMENT ALL_ROWS Cost: 4,340 Bytes: 375,000,000 Cardinality: 1,000,000
5 VIEW TBAADM. Cost: 4,340 Bytes: 375,000,000 Cardinality: 1,000,000
4 COUNT STOPKEY
3 VIEW TBAADM. Cost: 4,340 Bytes: 362,558,566 Cardinality: 1,001,543
2 TABLE ACCESS BY INDEX ROWID TABLE TBAADM.CAT Cost: 4,340 Bytes: 17,026,231 Cardinality: 1,001,543
1 INDEX FULL SCAN INDEX CRMUSER.IX_CATEGORIES_IDS_TYPE_1 Cost: 2,758 Cardinality: 1,001,543
2. You said FIRST_ROWS hint is to tell optimizer to optimize the plan to get the FIRST 50 ROWS.
If I have m number of records and I need n records, then FIRST_ROWS hint will optimize the plan for first n ROWS.
But if m=n then will FIRST_ROWS hint give better performance?
3. I have executed below queries.
1. select /*+MONITOR FIRST_ROWS (55) */ * from test2
2. select /*+MONITOR FIRST_ROWS (55) */ * from test2 where rownum <= 55
In v$sql_plan , I can see OPTIMIZER = HINT: FIRST_ROWS for both queries but in v$sql_monitor status is different.
select sql_text,status from v$sql_monitor where sql_id in ( '5uvk86gj6w7wa','800zd9ytppycj')
SQL_TEXT STATUS
select /*+MONITOR FIRST_ROWS (55) */ * from test2 DONE (FIRST N ROWS)
select /*+MONITOR FIRST_ROWS (55) */ * from test2 where rownum <= 55 DONE (ALL ROWS)
For 2nd query, why status is DONE(ALL_ROWS).
So I am curious, if optimizer is using hint or not.