Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

simple but slow query with "not exists"

user1323Sep 22 2005 — edited Sep 22 2005
I have 4 independent tables a, b, c, d and one table associated with a, b, c, d by their
common FK parent_id. a, b, c, and d are indexed (NOLOGGING)
by (parent_id, a_id), (parent_id, b_id), (parent_id, c_id), (parent_id, d_id).

I want to identify all these in table x but not in either one of
a, b, c or d:

select parent_id, unique_id from x where not exists(
select 1 from x x, a a, b b, c c, d d
where ((x.parent_id = a.parent_id and x.unique_id = a.a_id) or
(x.parent_id = b.parent_id and x.unique_id = b.b_id) or
(x.parent_id = c.parent_id and x.unique_id = c.c_id) or
(x.parent_id = d.parent_id and x.unique_id = d.d_id))
);
/


but this is extrmely slow even for x, a, b, c in the hundreds, d in 100K rows.
Sounds like indexes are not working at all.
Any better way to do this?

Thanks for your help!

- Zack
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 20 2005
Added on Sep 22 2005
5 comments
802 views