Slow selections

From: <rrenzo_at_my-deja.com>
Date: Thu, 10 Feb 2000 11:59:15 GMT
Message-ID: <87u96h$4p1$1@nnrp1.deja.com>

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 - 05:59:15 CST