In the Pagination you are generally interested in taking some complex query, sorting it and then retrieving just the first N rows. ROWNUM has an optimization that facilitates this type of query to be processed efficiently by avoiding a massive sort of result sets.
Suppose you have the query in this form:
select <list of columns>
from <table_name>
where <condition>
order by <column_name>
Assume this query returns a lot of data and you are interested only in subset of data (say 150 to 160), then there are two ways to this approach.
select *
from
(
select a.*,rownum as r
from big_table a
order by object_name,rowid
) where r between 150 and 160
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.02 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 45.79 157.30 379697 186114 721 11
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 45.81 157.33 379697 186114 721 11
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61
Rows Row Source Operation
------- ---------------------------------------------------
11 VIEW (cr=186114 pr=379697 pw=217115 time=124839576 us)
12773888 SORT ORDER BY (cr=186114 pr=379697 pw=217115 time=197569458 us)
12773888 COUNT (cr=186114 pr=162582 pw=0 time=114965056 us)
12773888 TABLE ACCESS FULL BIG_TABLE (cr=186114 pr=162582 pw=0 time=51095606 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 3 0.00 0.00
SQL*Net message from client 3 0.01 0.02
direct path write temp 388 0.08 3.26
db file sequential read 65 0.03 0.07
db file scattered read 10332 0.04 28.01
local write wait 669 0.00 0.34
direct path read temp 8153 0.04 77.10
Oracle database did the following.
1) Run a Full scan on big_table
2) Assign the rownum values to each row it read.
3) Sorted the results by and un-indexed column on the entire result sets.
4) ran out of sort area memory and need to swap the temporary extents to disk ( This is identified by pw=217115 (physical writes) available in step 3 of the Row Source operation )
5) Merge these temporary extents to get the needed results.
6) release these temporary extents as you finish them.
The other approach is
select * from (
select t.*,rownum as r
from
(
select *
from big_table
order by object_name,rowid
)t where rownum <= 160
) where r >= 150
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 8.07 34.20 173388 186114 0 11
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 8.07 34.20 173388 186114 0 11
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61
Rows Row Source Operation
------- ---------------------------------------------------
11 VIEW (cr=186114 pr=173388 pw=0 time=34199867 us)
160 COUNT STOPKEY (cr=186114 pr=173388 pw=0 time=34201314 us)
160 VIEW (cr=186114 pr=173388 pw=0 time=34200668 us)
160 SORT ORDER BY STOPKEY (cr=186114 pr=173388 pw=0 time=34200183 us)
12773888 TABLE ACCESS FULL BIG_TABLE (cr=186114 pr=173388 pw=0 time=51095597 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
db file sequential read 123 0.02 0.08
db file scattered read 11007 0.03 26.63
SQL*Net message from client 2 0.00 0.00
1) Run the full scan on the big_table
2) Sort only the rows needed (say 160 in above scenario) and not the entire result set.
The query read the entire table, but by using the SORT ORDER BY STOPKEY it sorted only the 160 records it was able to limit its use of temporary space for just 160 rows. This result is sorted in sort memory and there is NO disk sort (pw = 0)
This seemingly small detail sorting just N rows can lead to huge gains in performance and resource usage. It takes a lot less RAM to sort 160 rows than it does to sort one million rows (not to mention TEMP space usage).
Suppose you have the query in this form:
select <list of columns>
from <table_name>
where <condition>
order by <column_name>
Assume this query returns a lot of data and you are interested only in subset of data (say 150 to 160), then there are two ways to this approach.
select *
from
(
select a.*,rownum as r
from big_table a
order by object_name,rowid
) where r between 150 and 160
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.02 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 45.79 157.30 379697 186114 721 11
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 45.81 157.33 379697 186114 721 11
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61
Rows Row Source Operation
------- ---------------------------------------------------
11 VIEW (cr=186114 pr=379697 pw=217115 time=124839576 us)
12773888 SORT ORDER BY (cr=186114 pr=379697 pw=217115 time=197569458 us)
12773888 COUNT (cr=186114 pr=162582 pw=0 time=114965056 us)
12773888 TABLE ACCESS FULL BIG_TABLE (cr=186114 pr=162582 pw=0 time=51095606 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 3 0.00 0.00
SQL*Net message from client 3 0.01 0.02
direct path write temp 388 0.08 3.26
db file sequential read 65 0.03 0.07
db file scattered read 10332 0.04 28.01
local write wait 669 0.00 0.34
direct path read temp 8153 0.04 77.10
Oracle database did the following.
1) Run a Full scan on big_table
2) Assign the rownum values to each row it read.
3) Sorted the results by and un-indexed column on the entire result sets.
4) ran out of sort area memory and need to swap the temporary extents to disk ( This is identified by pw=217115 (physical writes) available in step 3 of the Row Source operation )
5) Merge these temporary extents to get the needed results.
6) release these temporary extents as you finish them.
The other approach is
select * from (
select t.*,rownum as r
from
(
select *
from big_table
order by object_name,rowid
)t where rownum <= 160
) where r >= 150
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 8.07 34.20 173388 186114 0 11
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 8.07 34.20 173388 186114 0 11
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 61
Rows Row Source Operation
------- ---------------------------------------------------
11 VIEW (cr=186114 pr=173388 pw=0 time=34199867 us)
160 COUNT STOPKEY (cr=186114 pr=173388 pw=0 time=34201314 us)
160 VIEW (cr=186114 pr=173388 pw=0 time=34200668 us)
160 SORT ORDER BY STOPKEY (cr=186114 pr=173388 pw=0 time=34200183 us)
12773888 TABLE ACCESS FULL BIG_TABLE (cr=186114 pr=173388 pw=0 time=51095597 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
db file sequential read 123 0.02 0.08
db file scattered read 11007 0.03 26.63
SQL*Net message from client 2 0.00 0.00
1) Run the full scan on the big_table
2) Sort only the rows needed (say 160 in above scenario) and not the entire result set.
The query read the entire table, but by using the SORT ORDER BY STOPKEY it sorted only the 160 records it was able to limit its use of temporary space for just 160 rows. This result is sorted in sort memory and there is NO disk sort (pw = 0)
This seemingly small detail sorting just N rows can lead to huge gains in performance and resource usage. It takes a lot less RAM to sort 160 rows than it does to sort one million rows (not to mention TEMP space usage).
