Help with simple query

I have a very simple query that performs poorly in Oracle and I cannot figure out why. I need to query a single table and use a cross join. The tables contians 80,000 records.

I have a table called data_values and is defined as:

person_id number,
zip number,
age number.

What I'm looking for is to get all person_ids who's zip code = 11111 and who's age is below 30 OR
all person who's zip code = 22222 and who's age is greater than 30

Here is a query that brings the server to its knees.

select d1.preson_id
from data_values d1, data_values d2
where
((d1.age < 30 and d1.zip = 11111) OR (d2.age > 30 and d2.zip = 22222)) and d1.person_id = d2.person_id;

If for example the data_value looked like:

person_id   zip         age


----               ----        -------

1                 21111        35
2                 22222        40
3                 11111        25
4                 22222        22

Given the above example I should get back person_id's 2 and 3. Note there are 80,000 rows and the query over 10 mins to run!  Without the cross joins i.e. if use two separate queries to get result it comes back in a second.

Is there a better way to do this? Is this an Orcale optimization problem? Any pointers on this would be very helpful.

-Haresh

e-mail: assumal_at_sprynet.com Received on Tue Mar 24 1998 - 00:00:00 CST