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

    Query Tuning for date fields

    User223410

      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 );

       

      Thanks..

        • 1. Re: Query Tuning for date fields
          RajenB

          Hi,

           

          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 )"

          with

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

          Regards,

          Rajen