1 Reply Latest reply on Oct 9, 2015 9:55 AM by RajenB

    Query Tuning for date fields


      Hi All,


      I am trying to run a simple query for getting the updates done on a table for the previous day.

      But it is taking too long to run.

      Can someone please help me in tuning this?


      SELECT 'AP_INVOICE_DISTRIBUTIONS_ALL',COUNT( * ) FROM ap_invoice_distributions_all WHERE TRUNC( last_update_date ) = TRUNC(SYSDATE - 1 ) AND TRUNC( last_update_date ) <> TRUNC( creation_date );



        • 1. Re: Query Tuning for date fields



          Even if you transformed the query (removed the TRUNC from last_update_date), it wouldn't hardly make any difference as, as far as I know, Oracle doesn't have indexes on this column.


          We generally don't do such type of queries on the PROD/LIVE instance. Typically, I would be having a cloned or warehouse type of instance (with replicated table) where I can play with indexes (create an additional one on LAST_UPDATE_DATE, for example) and run the optimized query by replacing:

          "TRUNC( last_update_date ) = TRUNC(SYSDATE - 1 )"


          "last_update_date >= trunc(sysdate-1) and last_update_date < trunc(sysdate)"