DB = 9.2.0.6
OS = Solaris 8
4CPU 8GBRAM

select count(*) from TABLE_X;

31968842

select count(*) from TABLE_Y;

31413058

I have a cursor in a procedure that is built on these two tables which returns results in around 25 minutes. Code and explain plan below:

Code:
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 trn2.t_id
                FROM TABLE_X trn2
                WHERE (trn2.i_id = trn1.i_id
                AND   trn2.t_id <> trn1.t_id));  

2588 rows selected.

Elapsed: 00:24:46.21

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=101391 Card=92419 Bytes=2680151)
   1    0   FILTER
   2    1     NESTED LOOPS (Cost=8972 Card=92419 Bytes=2680151)
   3    2       TABLE ACCESS (BY INDEX ROWID) OF 'TABLE_X' (Cost=8970 Card=92230 Bytes=2121290)
   4    3         BITMAP CONVERSION (TO ROWIDS)
   5    4           BITMAP AND
   6    5             BITMAP OR
   7    6               BITMAP CONVERSION (FROM ROWIDS)
   8    7                 INDEX (RANGE SCAN) OF 'y_t_FK6' (NON-UNIQUE) (Cost=6057 Card=3541359)
   9    6               BITMAP CONVERSION (FROM ROWIDS)
  10    9                 INDEX (RANGE SCAN) OF 'y_t_FK6' (NON-UNIQUE) (Cost=653 Card=3541359)
  11    5             BITMAP CONVERSION (FROM ROWIDS)
  12   11               SORT (ORDER BY)
  13   12                 INDEX (RANGE SCAN) OF 't_FI1' (NON-UNIQUE) (Cost=458 Card=3541359)
  14    2       INDEX (UNIQUE SCAN) OF 'i_PK' (UNIQUE)
  15    1     TABLE ACCESS (BY INDEX ROWID) OF 'TABLE_X' (Cost=1 Card=1 Bytes=12)
  16   15       INDEX (RANGE SCAN) OF 'i_t_FK4' (NON-UNIQUE) (Cost=3 Card=1)





Statistics
----------------------------------------------------------
          3  recursive calls
        257  db block gets
   13589635  consistent gets
     562588  physical reads
          0  redo size
      55454  bytes sent via SQL*Net to client
       2391  bytes received via SQL*Net from client
        174  SQL*Net roundtrips to/from client
          0  sorts (memory)
          1  sorts (disk)
       2588  rows processed
I have tried the following:

y_ID has 80 distinct values so I created a histogram on that column with 80 buckets, but it didn't alter the plan in any way.

ALTER SESSION SET "_b_tree_bitmap_plans" = FALSE; The plan then looks like this:

Code:
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 trn2.t_id
                FROM TABLE_X trn2
                WHERE (trn2.i_id = trn1.i_id
                AND   trn2.t_id <> trn1.t_id));

2588 rows selected.

Elapsed: 00:15:18.40

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=121716 Card=92419 Bytes=2680151)
   1    0   FILTER
   2    1     NESTED LOOPS (Cost=29297 Card=92419 Bytes=2680151)
   3    2       INLIST ITERATOR
   4    3         TABLE ACCESS (BY INDEX ROWID) OF 'TABLE_X' (Cost=29296 Card=92230 Bytes=2121290)
   5    4           INDEX (RANGE SCAN) OF 'y_t_FK6' (NON-UNIQUE) (Cost=1409 Card=3541359)
   6    2       INDEX (UNIQUE SCAN) OF 'i_PK' (UNIQUE)
   7    1     TABLE ACCESS (BY INDEX ROWID) OF 'TABLE_X' (Cost=1 Card=1 Bytes=12)
   8    7       INDEX (RANGE SCAN) OF 'i_t_FK4' (NON-UNIQUE) (Cost=3 Card=1)





Statistics
----------------------------------------------------------
          3  recursive calls
          0  db block gets
   13603236  consistent gets
     430254  physical reads
          0  redo size
      55454  bytes sent via SQL*Net to client
       2391  bytes received via SQL*Net from client
        174  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       2588  rows processed
That takes 15 minutes. I'd like to try to re-write the query to take a few more minutes off the execution time, however, my attempts haven't been very sucessful so far. I thought getting rid of the NOT EXISTS part might help by doing:

select
from (select blah) trn1,
(select blah) trn2,
csi
joins;

But adding that additional join made it even slower. The real killer seems to be y_ID in(30,31) which returns 10% or so of the total rows from TABLE_X, but i'm not sure how else it can be written.

Does anyone have any ideas on how to tune this statement that they'd like to share with the class?

NOTES:
t_ID is the PK on TABLE_X, and i_ID is the PK on TABLE_Y.
y_t_FK6 Index is on column y_ID
t_FI1 is a Functional Based Index on TRUNC(MODIFIED_DATE)

Muchas Gratias.