sql - Optimizing a SELECT with sub SELECT query in Oracle -
select id, (select sum(totalpay) table2 t t.id = a.id , t.transamt > 0 , t.paydt between trunc(sysdate-0-7) , trunc(sysdate-0-1)) pay table1
in spite of having indexes on transamt, paydt , id, cost of sub-query on table2 expensive , requires full table scan.
can sub-query optimized in other way? please help.
try this:
select a.id, pay.totalpay table1 (select t.id, sum(totalpay) totalpay table2 t t.transamt > 0 , t.paydt between trunc(sysdate-0-7) , trunc(sysdate-0-1) group t.id ) pay a.id = pay.id
push group joining columns (id column in example) subquery calculate results values in table2 , join table1 table. in original query calculate result every crow table1 table reading full table2 table.
Comments
Post a Comment