Search code examples
oraclesql-order-bydatabase-performance

Order by causing slower select in Oracle

I have a table that will have transaction records inserted by various program, and i shall process them one by one. The records are selected one by one, and there is a priority field where the lowest number shall be processed first.

My current query:

SELECT * FROM (SELECT CODE,NAME,TYPE,START_DATE, LANGUAGE, PRIORITY, SOURCE 
FROM TBL_INQUEUE ORDER BY PRIORITY) q WHERE ROWNUM = 1

When the data set becomes large, the query will become slower to run. We had added index on the PRIORITY column but it doesn't help. We tried removing the ORDER BY clause and the performance has almost double, but we need the clause to make sure the prioritized records get to be processed first.

Is there a way to optimize this?

UPDATE

With the answers provided, here are the two explain plans:

With Order By using Index

enter image description here

With MAX(PRIORITY)

enter image description here

Solution

  • PRIORITY is set to NOT NULL, is it? The index will not be used otherwise.

    You can workaround that issue by creating a composite function-based index on (PRIORITY, 0), though, or by including "where priority is not null" in the query.