Re: Slow selections

HI
Oracle won't split the where up and use an index on the partitial where clause. If you want this speeded up you'll need an index with zip and lname in it.

Or you could try

select count(*) from person where zip like '55%' and exists (select 'x' from person pn where pn.lname like 'Card%')

to split up the query into two and use the existing index

martin  

rrenzo_at_my-deja.com wrote:
>
> I've some troubles with an Oracle 8.0.5 on a NT 4 server with sp 5.
> Consider the following table
>
> create table person
> (
> id number not null,
> lastname varchar(30) not null,
> zipcode varchar(4) not null,
> ...
> );
>
> alter table person add constraint pk_person primary key(id);
> create index ix_person_lastname on person(lastname);
> create index ix_person_zipcode on person(zipcode);
>
> There are about 1.000.000 records and the zipcodes are evenly
> distributed.
>
> With this query:
>
> select count(*) from person where lname like 'Card%'
>
> I get 64 records within a split second.
>
> with this query:
>
> select count(*) from person where zip like '55%'
>
> I get 14000 records within 5 seconds
>
> When I combine the two criteria, ie.
>
> select count(*) from person where zip like '55%' and lname like 'Card%'
>
> I get 1 record taking up 3 minutes!
> Even if it takes the long road (the zip index) then scanning through
> 14.000 records shouldn't take that long!
>
> Who can help me speed this query up?
> Thanks,
> Tony Cardalda.
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Thu Feb 10 2000 - 06:41:50 CST