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