From: caoimhinocrosbai@yahoo.com   
      
   Hi all,   
   (Sorry for the cross-post, there doesn't appear to be much activity on   
   comp.database.oracle)I'm trying to get the last 300 rows from the difference   
   between 2 large   
   tables and my queries are taking at least 10 minutes to do this.   
      
   I'm running on quite a fast server and the tables are not very large,   
   3,000,000 to 30,000,000 rows.   
      
   I've tried the following:   
   (test is the primary key table and test2 and test3 have foreign keys to test   
   also, imagine I have the following unique indexes (test_id, sequence_no) and   
   (parent_id, test_id))   
      
   select test_id   
   from (select test_id from test2 where sequence_no = 0   
             minus   
            select test_id from test3 where parent_id = 581)   
   where rownum < 300   
      
   Explain Plan:   
   SELECT STATEMENT Optimizer=CHOOSE   
     COUNT (STOPKEY)   
       VIEW   
         MINUS   
           SORT (UNIQUE)   
             TABLE ACCESS (FULL) OF TEST2   
           SORT (UNIQUE)   
             INDEX (RANGE SCAN) OF TEST3_PAR_ID_TEST_ID_UNQ (UNIQUE)   
      
   second approach:   
      
   select test_id   
   from test2 t2, (select test_id from test3 where parent_id = 581) t3   
   where t2.test_id = t3.test_id(+)   
   and t2.sequence_no = 0   
   and t3.test_id is null   
   and rownum < 300   
      
   Explain Plan:   
   SELECT STATEMENT Optimizer=CHOOSE   
     COUNT (STOPKEY)   
       FILTER   
         NESTED LOOPS (OUTER)   
           TABLE ACCESS (FULL) OF TEST2   
           INDEX (UNIQUE SCAN) OF TEST3_PAR_ID_TEST_ID_UNQ (UNIQUE)   
      
   The table creates for the above tables are:   
   create table test (   
      test_id number primary key   
   );   
      
   create table test2 (   
      test2_id number primary key,   
      test_id number references test,   
      sequence_no number,   
      test_text varchar2(400)   
   );   
      
   create table test3 (   
      test3_id number primary key,   
      parent_id number,   
      test_id number references test,   
      data number   
   );   
      
   Can anybody think any way I can improve these?   
      
   Thanks,   
      
   Kevin   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)