oracle 11 optimizer problem : use of analytic functions prevent usage of index
Hi everybody !
can someone help me on this ?
Oracle version : Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
There are a couple of indexes on tables BALANCE and TRANSACTIONS
idx1 : TRANSACTIONS ( CLIENT, CONTO , DATA_VALUTA )
idx2: BALANCES ( CLIENT, CONTO )
If I try the following :
SELECT * FROM
(
SELECT T.CLIENT, S.CONTO, S.DIVISA ,T.DATA_OPERAZIONE, T.DATA_VALUTA, T.DEAL,T.IMPORTO
,CASE WHEN T.IMPORTO<0 THEN 'D' ELSE 'C' END as SEGNO,
T.NARRATIVA,
SUM(T.IMPORTO) OVER (PARTITION BY T.CONTO ORDER BY T.CONTO, T.DATA_OPERAZIONE, T.DEAL) PROGRESSIVO_MOVIMENTI,
S.RT_BALANCE+S.FM_BALANCE SALDO_FINALE,
S.RT_BALANCE+S.FM_BALANCE-SUM(T.IMPORTO) OVER (PARTITION BY T.CONTO ORDER BY T.CONTO) SALDO_INIZIALE,
SUM(T.IMPORTO) OVER (PARTITION BY T.CONTO ORDER BY T.CONTO) TOT_MOVIMENTI,
S.RT_BALANCE+S.FM_BALANCE - SUM(T.IMPORTO) OVER (PARTITION BY T.CONTO ORDER BY T.CONTO) + SUM(T.IMPORTO) OVER (PARTITION BY T.CONTO ORDER BY T.CONTO, T.DATA_OPERAZIONE, T.DEAL) SALDO_PROGRESSIVO
FROM BALANCES S, TRANSACTIONS T
WHERE T.CONTO=S.CONTO
AND S.CLIENT = T.CLIENT
AND T.NARRATIVA <> 'ARCHIVED POSTINGS SUMMARY'
AND T.NARRATIVA <> 'SALDO AL : 31/10/2008'
)
WHERE DATA_VALUTA >= TO_DATE('01012022','ddmmyyyy') AND AND CLIENT=1234
ORDER BY CONTO, DATA_OPERAZIONE, DEAL;
0