CODEWITHSUNDEEP
sqloracle-databaseperformancesql-order-byrownum
Will Graham
Will Graham

Reputation: 350

Oracle query with order by perfomance issue

I have really complicated query:

select * from (
select * from tbl_user ...
where ...
and date_created between :date_from and :today
...
order by date_created desc
) where rownum <=50;

Currently query is fast enough because of where clause (only 3 month before today, date_from = today - 90 days).

I have to remove this clause, but it causes performance degradation. What if first calculate date_from by `

SELECT MIN(date_created) where...

and then insert this value into main query? Set of data will be the same. Will it improve performance? Does it make sense? Could anyone have any assumption about optimization?

Upvotes: 1

Views: 1255

Answers (1)

Jacobm001
Jacobm001

Reputation: 4539

Using an order by operation will of course cause the query to take a little longer to return. That being said, it is almost always faster to sort in the DB than it is to sort in your application logic.

It's hard to really optimize without the full query and schema information, but I'll take a stab at what seems like the most obvious to me.

Converting to Rank()

Your query could be a lot more efficient if you use a windowed rank() function. I've also converted it to use a common table expression (aka CTE). This doesn't improve performance, but does make it easier to read.

with cte as (
  select 
    * 
    , rank() over (
      partition by
        -- insert what fields differentiate your rows here
        -- unlike a group by clause, this doesn't need to be
        -- every field
      order by
        date_created desc
    )
  from 
    tbl_user 
    ...
  where 
    ...
    and date_created between :date_from and :today
)
select 
  * 
from 
  cte
where
  rk <= 50

Indexing

  1. If date_created is not indexed, it probably should be.
  2. Take a look at your autotrace results. Figure out what filters have the highest cost. These are probably unindexed, and maybe should be.

If you post your schema, I'd be happy to make better suggestions.

Upvotes: 1

Related Questions