CODEWITHSUNDEEP
oracle-database
AlexGH
AlexGH

Reputation: 2805

Oracle query really slow when comparing two values of type Date

I'm querying against a view in Oracle, I'm not sure if it's relevant but just in case I'm copying the code that generates the view:

    select distinct cpc.Column1, cpc.Column2, //some other fields.. 
    from Table1 cpc inner join Table2 cpob on cpc.brand = cpob.brand 
    and cpc.ship = cpob.ship inner join Table3 cpvi on cpc.voyage = cpvi.voyage

These queries are fast:

select * from   MyView where rownum <=500 //fast
select * from   MyView where  VOYAGE >= '07-JUL-15'  and rownum <=500 //fast
select * from   MyView where   VOYAGE <= '07-JUL-17' and rownum <=500 //fast

But when I need to retrieve data according to a range of dates it lasts like 40 seconds... no good

select * from MyView where voyage >= '07-JUL-15' 
and voyage <= '07-JUL-17' and rownum <=500  //too slow --- around 40 seconds!

Same thing when using between instead of where ... and ... too slow.. I've created indexes for voyage on the table but it's still to slow. Any ideas to improve the performance of this query? Why is that slow only when I add where voyage >= '07-JUL-15' and voyage <= '07-JUL-17'? and not for the other queries?

Upvotes: 2

Views: 2593

Answers (1)

Dave Costa
Dave Costa

Reputation: 48111

You are not comparing dates, you are comparing strings. Assuming that voyage is of type DATE, it is being coerced to a string (using whatever the default date format is in your database or session) for comparison with the string literals you are providing in the query. This can cause all kinds of madness in the Oracle query optimizer.

I suggest that you explicitly convert the strings to dates, e.g.:

VOYAGE >= TO_DATE('07-JUL-15','DD-MON-YY')

or use date literals, e.g.:

VOYAGE >= date '2015-07-07'

This may or may not improve your query performance, but with the information you've given, it is my best advice. When asking questions about query performance in Oracle, it is more helpful to include the actual table/view definition, any indexes present on the table(s), and the actual query execution plan Oracle is using.

(Also, I just noticed your view includes a DISTINCT operator. This is often not conducive to good query performance. Please consider whether you have used DISTINCT to cover up some logical flaw in the view definition that causes it to produce duplicates.)

Upvotes: 4

Related Questions