CODEWITHSUNDEEP
sqloracle-databasequery-optimization
schglurps
schglurps

Reputation: 1387

Need some advice to optimize an Oracle query

I have two applications : the first one inserts data in the table MyTable. The second one reads the rows of the table MyTable in chunks : let's say 1000 rows per read. This second app must read the data in chronological order, and use a query similar to :

SELECT
    C1,
    C2
FROM
(
    SELECT
        rownum AS RowNumber, 
        C1, 
        C2
    FROM
        MyTable
    WHERE
        C3 = :C3
        AND IsProcessed = 0
    ORDER BY
        Timestamp
) temp 
WHERE 
    temp.RowNumber <= 1000

The query works, but it is slow (more than one minute, usually it takes only a few seconds to execute) when a lot of not processed rows (for example 10 millions) are waiting in the table MyTable. I suppose this is normal, because Oracle must first sort all the concerned rows in the chronological order... So my question is : is there a better way to write this query ?

enter image description here

Upvotes: 1

Views: 363

Answers (3)

Lukas Eder
Lukas Eder

Reputation: 221325

From your execution plan, I'm guessing that your predicate C3 = :C3 is quite costly. You should try to optimise that by avoiding RAW types. There are several options:

  • Ensure you have an index on SUBCONTRACTID
  • Try adding a function-based index on NVL(BUSINESSTRANSACTIONID, HEXTORAW('00'))
  • Ensure you have an index on BUSINESSTRANSACTIONID and query that using IS NULL, rather than NVL(...)
  • If you can relax the underlying business requirement, it would of course help to remove the ORDER BY timestamp clause and process records in arbitrary order.

Apart from that, your query seems fine.

Also, try applying a /*+FIRST_ROWS(1000)*/ hint, as it seems that this isn't done automatically for some reason in your query, even with ROWNUM filtering

Upvotes: 1

Anjan Biswas
Anjan Biswas

Reputation: 7932

SELECT * FROM
  (    SELECT C1, C2
      FROM MyTable
     WHERE C3 = :C3
       AND IsProcessed = 0
     ORDER BY Timestamp
  ) WHERE rownum <= 1000;

Try this query.

Upvotes: 0

BellevueBob
BellevueBob

Reputation: 9618

You don't really need to define a column for RowNumber:

SELECT C1, C2 
FROM (
   SELECT C1, C2     
   FROM   MyTable
   WHERE  C3 = :C3
      AND IsProcessed = 0
   ORDER BY Timestamp 
   ) temp
WHERE ROWNUM <= 1000

The only way to make it "faster" would be to somehow further restrict the size of the derived table.

Upvotes: 0

Related Questions