Re: Help with simple query

Haresh,

Do you have any indexes defined on the table in question? My first thought is that Oracle is doing a full table scan. If there are no indexes on zipcode or age, Oracle would have no choice but to scan the table.

Jonathan Gennick

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;
>
>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 Wed Mar 25 1998 - 00:00:00 CST