0

I created a functional index on "sum(coalesce(INDICATED_REVENUE,0) - coalesce(ACTUAL_REVENUE,0)) " in query below query but still its very slow , what else can I try ?

the stats are current

select           
sum(coalesce(INDICATED_REVENUE,0) - coalesce(ACTUAL_REVENUE,0))       
from TAB1 t       
where t.tid = '925914455'             
and t. PM = '7'             
and t.Token is not null
Ahmad S
  • 21
  • 6
  • Which DBMS are you using? What is the execution plan for this query? –  Dec 16 '16 at 14:36
  • Which database you use? How many records in table TAB1? – Andrew Dec 16 '16 at 14:36
  • In general an index only helps for **reducing** the number of rows, not for calculating values in the `select` list. If at all, an index on `(tid, pm)` would help if that condition reduces the number of rows substantially. If `tid` is defined as a number (e.g. `integer` or `number` depending on the DBMS) you should also compare it to number. `'925914455'` is a string, `925914455` is a number. Depending on the DBMS an index on the `tid` column might not be used if you are comparing it with the wrong literal –  Dec 16 '16 at 14:37

0 Answers0