Search code examples
sqlperformanceoracledatestamp

Two sql for Sorted timestamp date

I have 98w rows data. When I want sort my data with pub_time, I found an interest thing.

Here is the SQL:

select * 
from t_p_blog_article_info t  
order by t.pub_time desc

It cost 19s.

select * 
from t_p_blog_article_info t 
where t.pub_time > to_date( '1900-01-01 01:00:00', 'yyyy-mm-dd   hh24:mi:ss ')  
order by t.pub_time desc

It cost 0.2s.

I want to know, why?

Solution

  • You probably have an index on pub_time on your table.

    Therefore, the second query can make use of this index to return only those records with non-null dates after the specified date, whereas the first query has to query the whole table.