Re: Slow selections

In article <87u96h$4p1$1_at_nnrp1.deja.com>,   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.
>

Tony,
can you post the explain plan output together with your sql ? have you tried an index with zip &lname or lname & zip ? ted

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Feb 10 2000 - 09:18:12 CST