By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,502 Members | 1,878 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,502 IT Pros & Developers. It's quick & easy.

selecting the differences between 2 large tables

P: n/a
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
Jul 19 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
"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
Jul 19 '05 #2

P: n/a
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

Jul 19 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.