CODEWITHSUNDEEP
sqloracle-databasedate
MaSyn
MaSyn

Reputation: 21

SQL date conversion adding significant time to Query - Oracle

I have developed a large piece of coding, to get order information from our database. This has previously been working fine, capturing 1000s of lines of data in less than a second.

However, recently the specification has changed. We have moved from being Monday to Monday to the intention of running our orders from Saturday through Friday.

This has meant rewriting the code including case statements the following is an example: 

*(Previous)*
,Case When To_Char(obd.due_date, 'YYYYIW') = To_Char(sysdate, 'YYYYIW') Then 'Current Week'

*(Now)*
,Case When TO_DATE(obd.due_date, 'DD-MM-YYYY') BETWEEN (TO_DATE(TO_CHAR(TRUNC(TO_DATE(SYSDATE,'DD-MM-YYYY'), 'WW')-2,'DD-MON-YYYY'))) AND (TO_DATE(TO_CHAR(TRUNC(TO_DATE(SYSDATE,'DD-MM-YYYY'), 'WW')+5,'DD-MON-YYYY'))) Then 'Current Week'

However I now lose the index on the fields due to conversion and thus the time for the data to be accessed has increased dramatically.

Any help would be greatly appreciated.

Upvotes: 2

Views: 51

Answers (1)

dnoeth
dnoeth

Reputation: 60472

I doubt that your "previous" calculation used an index (and it's used in a CASE, not a WHERE).

You should always try to get a searchable argument, i.e. calculation only on one side of the comparison:

WHEN obd.due_date >= next_day(trunc(current_date) , 'sat')-7
 AND obd.due_date < next_day(trunc(current_date) , 'sat')

should be what you want

Upvotes: 1

Related Questions