Geek Answers Handbook

SQL - date calculation optimization for a large table

Can this query be optimized below?

select max(date), sysdate - max(date) from table;

Request execution time ~ 5.7 seconds

I have a different approach

 select date, sysdate - date from (select * from table order by date desc) where rownum = 1;

Request Execution ~ 7.9 seconds

In this particular case, the table contains about 17,000,000 records.

Is there a better way to rewrite this?

Update . Well, I tried the hint suggested by some of you in the development of the database, although with a smaller subset than the original (about 1,000,000 entries). Without an index, queries run slower than with an index.

First query without index: ~ 0.56 s, with index: ~ 0.2 sec. The second query without index: ~ 0.41 s, with index: ~ 0.005 sec. (This surprised me, I thought that the first query would work faster than the second, perhaps it is more suitable for a smaller set of records).

I suggested the DBA this solution, and it will change the structure of the table to take this into account, and then I will spend it with the actual data. Thanks

+4
source share
2 answers

Is there an index in the date column?

+3
source

This query is simple enough that probably nothing can be done to optimize it other than adding an index to the date column. What database? And sysdate another table column?

+1
source

More articles:


All Articles