Pagination

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).