CODEWITHSUNDEEP
sqlperformanceoracle-database
Guillaume
Guillaume

Reputation: 5557

SQL (Oracle) : how to improve a select ordered by x desc

I have a huge log table in a db. There is an index on the log_id and the log_date.

When I query logs in reverse order, it takes 'forever' to complete. While I do the same query with no order, the answer is immediate.

How can I tune the table/improve the query to have a quick(er) reverse select ?

Edit: the query (sorry the query was obvious in my mind (and in Florin too !):

select * from logs ordered by log_date desc

Some metrics:

There are around 40 millions of rows in the table

select * from logs where log_id < 500

--> fetched in 0.032 seconds 

select * from logs where log_id < 500 order by log_time desc;

--> fetched in around 20 seconds

$max is a the max log_id and it has been retrieved in another query

select * from logs where log_id > ($max - 500);

--> fetched in around 16 seconds 

select * from logs where log_id > ($max - 500) order by log_time desc;

--> fetched in around 16 seconds

My question is how to improve all the queries that takes too many seconds too execute.

@Florin
With a 'where' clause narrowing the log in time (where log_time >= truncate(sysdate)) I have good performance, but I need to be able to select logs for a long time period or for a range far in the past. In this case the query is still quite slow (like 20s).

Upvotes: 1

Views: 6727

Answers (1)

Florin Ghita
Florin Ghita

Reputation: 17643

select * from log_table 
where log_date >= trunc(sysdate)  --current_day
order by log_id desc, log_date desc

This will do a range scan in the log_date index, and will retrieve a day of rows from log_table, not the entire table. After this the sort will be faster because will have few rows to sort.

UPDATE: Another things you can do:

  • Make the log_date column NOT NULL(this may change the optimiser to use the index);
  • use a /*+parallel(logs 8)*/ hint (if you dont shoot this query too often and just need the results quick in your db tool. And if you have many processors :) )
  • you can partition the table in a monthly manner or even more detailed.

Upvotes: 3

Related Questions