selecting the differences between 2 large tables
Date: Mon, 10 May 2004 09:09:37 GMT
Message-ID: <483a35a26fdbecb0a8af95c8056e2ed9_at_news.teranews.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