SQL Tuning

[tamilselvan]

Create a concatenated index on I_ID+T_ID on TABLE_X.

And change the SQL to:

select trn1.t_id, csi.i_id
FROM    TABLE_X trn1,
        TABLE_Y csi
WHERE   TRUNC(trn1.modified_date)  < Trunc(Sysdate - 295)
AND     csi.i_id = trn1.i_id
AND     trn1.y_id IN (30, 31)
AND NOT EXISTS (SELECT /*+ FIRST_ROWS */  NULL 
                FROM TABLE_X trn2
                WHERE (trn2.i_id = trn1.i_id
                AND   trn2.t_id <> trn1.t_id));

Tamil