Oracle – Improving Paging Query Performance

oracle

I'm new to Oracle and I've noticed that pagination queries take the format.

select * 
from ( select /*+ FIRST_ROWS(n) */ 
         a.*, ROWNUM rnum 
       from
         ( your_query_goes_here with filter and order by ) a 
       where
         ROWNUM <= :MAX_ROW_TO_FETCH ) 
where
  rnum  >= :MIN_ROW_TO_FETCH;

http://www.oracle.com/technetwork/issue-archive/2006/06-sep/o56asktom-086197.html

Why is there an extra outer select to split the min and max rownum where clause? Would this be just as performant and correct?

select /*+ FIRST_ROWS(n) */
  *
from 
  ( your_query_goes_here with filter and order by )
where
  ROWNUM between :MIN_ROW_TO_FETCH and :MAX_ROW_TO_FETCH

Shouldn't ROWNUM be already be correct since the inner query filters and orders the values?

Best Answer

No, the second query won't work. It will always return 0 rows if :MIN_ROW_TO_FETCH is greater than 1.

Conceptually, if you have a query like

select * /*+ FIRST_ROWS(n) */ 
  from ( your_query_goes_here with filter and order by )
  where ROWNUM between :MIN_ROW_TO_FETCH and :MAX_ROW_TO_FETCH

where :MIN_ROW_TO_FETCH is 11 and :MAX_ROW_TO_FETCH is 20, Oracle would fetch the first row, give it a rownum of 1, then discard it because it didn't satisfy the predicate. It would then fetch the second row from the inner query, give it a rownum of 1 (since no rows have been successfully returned yet), then discard it because it didn't satisfy the predicate. That would repeat until every row had been fetched, assigned a rownum of 1, and discarded. So the query would return 0 rows.

The extra layer of nesting ensures that the outer query with rnum >= :MIN_ROW_TO_FETCH sees rows with rnum values between 1 and :MAX_ROW_TO_FETCH and that the full query returns the expected set of rows.

If you're using 12.1 or later, you can use the OFFSET ... FETCH syntax as well

SELECT *
  FROM table_name
 ORDER BY some_column
 OFFSET n ROWS
  FETCH NEXT m ROWS ONLY;

Related Solutions

Oracle aggregate function performance

Use Oracle analytics:

SELECT distinct
  COUNT(userId) over (partition by date_range_start, date_range_end)
, AVG(age)      over (partition by date_range_start, date_range_end)
, STDDEV(age)   over (partition by date_range_start, date_range_end)
, MIN(age)      over (partition by date_range_start, date_range_end)
, MAX(age)      over (partition by date_range_start, date_range_end)
, date_range_start, date_range_end
FROM users
WHERE
(date_range_start >= TO_DATE('01-Dec-2010')) AND (date_range_end <= TO_DATE('30-Nov-2011')) 
/

It does the same but most of the times it is surprizingly faster.

Oracle View Ignoring External WHERE Clause

With a bit of trial and error, I was able to spot the culprit. Turns out, it has nothing to do with the view itself. Attempting to manually push the where clause directly into the query also gave the same bad results.

As it turns out, the problem was cause by the CONNECT BY clause in the inner queries. My suspicion (which may be completely wrong) is that it has to do with the fact that Oracle 10g cannot handle recursive CTE's. If anyone has any more information about why this might have happened, I am all ears.

I was able to work around the issue by moving the

SELECT level AS UNPIVOT_ROW FROM DUAL CONNECT BY level <= 3

subquery to its own CTE block and selecting from that as normal. Below I have included the (condensed) working version of the above broken query.

CREATE OR REPLACE VIEW FSA.FSA_V_DB_TOTALS_2
(...)
AS
WITH
--Queries to generate intermediate result sets

--'NUMBERS' CTE block to work around Oracle 10g limitation wherein WHERE
--  clause is ignored if CONNECY BY is directly written into inner queries.
--  NOTE: the size limitation (currently 10) only needs to be larger than
--        or equal to the largest value needed.
NUMBERS AS (
SELECT level AS UNPIVOT_ROW
  FROM DUAL CONNECT BY level <=10
),

--Distribution TRA
DIST_TRA AS (
SELECT ...
  FROM (
       SELECT ...
         FROM FSA.FSA001_DISTRIBUT T1
              INNER JOIN
              FSA.FSA002_DIST_TRA T2 ON T1.SERIAL_NO = T2.SERIAL_NO
        GROUP BY GROUPING SETS (...)
        ),(
        SELECT UNPIVOT_ROW FROM NUMBERS WHERE UNPIVOT_ROW <= 3      -- <==  THIS
        )
),

--Distribution Fran
DIST_FRAN AS (
SELECT ...
  FROM (
       SELECT ...
         FROM FSA.FSA001_DISTRIBUT T1
              INNER JOIN
              FSA.FSA003_DIST_FRAN T3 ON T1.SERIAL_NO = T3.SERIAL_NO
        GROUP BY GROUPING SETS (...)
        ),(
        SELECT UNPIVOT_ROW FROM NUMBERS WHERE UNPIVOT_ROW <= 2      -- <==  THIS AS WELL
        )
)

--Queries to generate final result set based on CTE intermediate queries
--Subtotals / System totals for Dist-TRA, Dist-Fran, Trans-TRA, Trans-Fran
SELECT ...
  FROM (SELECT * FROM DIST_TRA
        UNION ALL
        SELECT * FROM DIST_FRAN)

Best Answer

Related Solutions

Oracle aggregate function performance

Oracle View Ignoring External WHERE Clause

Related Question