How to Speed Up Row_Number in Oracle

How can I speed up row_number in Oracle?

ROW_NUMBER is quite inefficient in Oracle.

See the article in my blog for performance details:

• Oracle: ROW_NUMBER vs ROWNUM

For your specific query, I'd recommend you to replace it with ROWNUM and make sure that the index is used:

SELECT  *
FROM    (
        SELECT  /*+ INDEX_ASC(t index_on_column) NOPARALLEL_INDEX(t index_on_column) */
                t.*, ROWNUM AS rn
        FROM    table t
        ORDER BY
                column
        )
WHERE rn >= :start
      AND rownum <= :end - :start + 1

This query will use COUNT STOPKEY

Also either make sure you column is not nullable, or add WHERE column IS NOT NULL condition.

Otherwise the index cannot be used to retrieve all values.

Note that you cannot use ROWNUM BETWEEN :start and :end without a subquery.

ROWNUM is always assigned last and checked last, that's way ROWNUM's always come in order without gaps.

If you use ROWNUM BETWEEN 10 and 20, the first row that satisifies all other conditions will become a candidate for returning, temporarily assigned with ROWNUM = 1 and fail the test of ROWNUM BETWEEN 10 AND 20.

Then the next row will be a candidate, assigned with ROWNUM = 1 and fail, etc., so, finally, no rows will be returned at all.

This should be worked around by putting ROWNUM's into the subquery.

ROW_NUMBER() OVER PARTITION optimization

After trying multiple different solutions, I've found the fastest query with CROSS APPLY statement:

SELECT C.* 
FROM (SELECT DISTINCT Code from Offers) A
CROSS APPLY (SELECT TOP 1 * 
             FROM Offers B
             WHERE A.Code  = B.Code 
             ORDER by Price) C

It take ~1 second to run.

Improve the performance with cte ROW_NUMBER()

One core principle when doing SQL queries with mutliple steps needs to be: eliminate as much data as possible as early as possible. Your CTE loads all rows from TransactionSendQueue, when you only want the latest transaction per Tran_ID. The more data that's being handled, the higher risk of data loaded being written to disk, which is extremely detrimental to performance. The more data that's written to disk, the worse the impact is. You can view your execution plan to check if this is the case but I'd say it's likely considering the execution time.

The CTE should only return one row per row that could possibly be updated in your #TempTran table. You can use an additional CTE to retrieve the latest update first, and then use that information in your ctetran to reduce the amount of data (rows) being search ed through in the update statement.

WITH LatestTran AS --the lastest transaction
(
    SELECT 
        Tran_ID, 
        MAX(LastUpdate) AS LastUpdate
    FROM 
        TransactionSendQueue
    WHERE 
        STATUS = '1' --where 1 mean complete
    GROUP BY
        Tran_ID
), ctetran AS
(
    SELECT 
        Tran_ID, 
        Field2, 
        Field3, 
        Field4, 
        Field5, 
        Field6, 
        Field7, 
        Field8, 
        Field9
    FROM 
        TransactionSendQueue TSQ
    INNER JOIN LatestTran LT ON 
        TSQ.Tran_ID = LT.Tran_ID AND 
        TSQ.LastUpdate = LT.LastUpdate
)

UPDATE temp
SET STATUS = CASE
                WHEN temp.f2 = cte.Field2
                     AND temp.f3 = cte.Field3
                     AND temp.f4 = cte.Field4
                     AND temp.f5 = cte.Field5
                     AND temp.f6 = cte.Field6
                     AND temp.f7 = cte.Field7
                     AND temp.f8 = cte.Field8
                     AND temp.f9 = cte.Field9
                   THEN '2' -- where 2 mean skip
                ELSE '3' --where 3 mean ready to execute
             END
FROM #TempTran temp
INNER JOIN ctetran cte ON temp.Tran_ID = cte.Tran_ID

How big performance increase this will be is dependent on how many Batch_ID you have per Tran_ID, the more the bigger perfomance boost.

If the query is still running slow, you could also look into using an index for the LastUpdate column in the TransactionSendQueue table, since the query is now using that in a join statement.

Please let me know how much the query time is reduced, would be interesting to know.

increment row number when value of field changes in Oracle

You can combine the analytic functions SUM (used as a running total) and LAG:

SQL> WITH data AS (
  2            SELECT 'person1' person, 'day1' day, 'Y' flag FROM dual
  3  UNION ALL SELECT 'person1' person, 'day2' day, 'Y' flag FROM dual
  4  UNION ALL SELECT 'person1' person, 'day3' day, 'Y' flag FROM dual
  5  UNION ALL SELECT 'person1' person, 'day4' day, 'N' flag FROM dual
  6  UNION ALL SELECT 'person1' person, 'day5' day, 'N' flag FROM dual
  7  UNION ALL SELECT 'person1' person, 'day6' day, 'Y' flag FROM dual
  8  UNION ALL SELECT 'person1' person, 'day7' day, 'Y' flag FROM dual
  9  UNION ALL SELECT 'person1' person, 'day8' day, 'Y' flag FROM dual
 10  )
 11  SELECT person, DAY, flag, SUM(gap) over (PARTITION BY person
 12                                           ORDER BY DAY) grp
 13    FROM (SELECT person, DAY, flag,
 14                 CASE WHEN flag = lag(flag) over (PARTITION BY person
 15                                                  ORDER BY DAY)
 16                      THEN 0
 17                      ELSE 1
 18                 END gap
 19            FROM DATA);

PERSON  DAY  FLAG        GRP
------- ---- ---- ----------
person1 day1 Y             1
person1 day2 Y             1
person1 day3 Y             1
person1 day4 N             2
person1 day5 N             2
person1 day6 Y             3
person1 day7 Y             3
person1 day8 Y             3

Alternative to using ROW_NUMBER for better performance

You can try one of the following:

declare @Table table(UpdateID int,   LegKey int,  OriginalSourceTableID int,  UpdateReceived datetime)

Here using the MAX Date in subquery.

select * from @Table as a where a.UpdateReceived = (Select MAX(UpdateReceived) from @Table as b Where b.LegKey = a.LegKey)

Here you can use it in cte with group by.

with MaxDate as( Select LegKey, Max(UpdateReceived) as MaxDate from @Table group by LegKey ) 
select * from MaxDate as a   
inner join @Table as b 
     on b.LegKey=a.LegKey 
    and b.UpdateReceived=a.MaxDate

Oracle view performance with rownum

I'd see what the /*+ NOPARALLEL */ hint does as per GuiGi's answer. Another thing to try is look at the plan generated for this:

select /*+ FIRST_ROWS(10)*/ * from VIEW_NAME where ROWNUM < 5;

---

Related Topics