Re: Help with simple query

On Tue, 24 Mar 1998 19:02:14 -0500, "Haresh Assumal" <assumal_at_sprynet.com> wrote:

>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;

[SNIP]
>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

Hi Haresh,

I can't see why would need a cross join to get your results. Try simply:

SELECT person_id
  FROM data_values
 WHERE ( (age < 30 AND zip = 11111)

         OR (age > 30 AND zip = 22222)
       );

Your query _with_ the cross join is correct from a logical point of view, but physically, in order to process it, Oracle has to create the cartesian product, which is (80000 x 80000) rows and then filter out those record which don't fit your criteria. That is a whole lot of work to do, which basically is not necessary if you just don't use a cross join.

HTH,
Peter

-- 
Peter Schneider
peter.schneider_at_okay.net