Hi all,

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