selecting the differences between 2 large tables

[Kevin Crosbie]

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

[Ed prochak]

"Kevin Crosbie" <ca**************@yahoo.com> wrote in message news:<48******************************@news.terane ws.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. []
Can anybody think any way I can improve these?

Thanks,

Kevin

you picked another defunct group. Post one last time to
comp.databases.oracle.misc
and you'll get some suggestions.

I'll give you one quick comment here: the phrase "the last 300 rows"
is meaningless in a relational database. This isn't a file system,
rows have no location relative to each other. You'll do better to
eliminate such phrases from your vocabulary and describle what you
really mean or need.

See you in .misc
Ed

[Kevin Crosbie]

Thanks for the advice Ed,

I've decided to go a different route anyway, I'll do asynchronous calls
between my applications rather than send to databases and read again.

Thanks.
"Ed prochak" <ed********@magicinterface.com> wrote in message
news:4b**************************@posting.google.c om...

"Kevin Crosbie" <ca**************@yahoo.com> wrote in message

news:<48******************************@news.terane ws.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.

[]

Can anybody think any way I can improve these?

Thanks,

Kevin

you picked another defunct group. Post one last time to
comp.databases.oracle.misc
and you'll get some suggestions.

I'll give you one quick comment here: the phrase "the last 300 rows"
is meaningless in a relational database. This isn't a file system,
rows have no location relative to each other. You'll do better to
eliminate such phrases from your vocabulary and describle what you
really mean or need.

See you in .misc
Ed