simple but slow query with "not exists"
user1323Sep 22 2005 — edited Sep 22 2005I 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