Speedup Help

From: Gabriel Millerd <millerd_at_ns1.rli-net.net>
Date: 2 Dec 2000 05:13:56 GMT
Message-ID: <90a0ek$se2$1@news.powerisp.com>

i have a simple table:

CREATE TABLE ZIP2ZIP (

  ID        NUMBER PRIMARY KEY, 
  HERE      VARCHAR2(5), 
  THERE     VARCHAR2(5), 

  DISTANCE FLOAT
);

ID is a sequenced trigger.
HERE is a complete set of zipcodes
THERE is a complete set of zipcodes for each zipcode DISTANCE is the number of miles between zipcodes.

so if only two HERE zipcodes exists the table looks like this:

 1, 12345, 12345,  0
 2, 12345, 67890, 10
 3, 67890, 12345, 10
 4, 67890, 67890,  0

obviously when i have several thousand (or the complete 29,000) entries this table is quite large.

and there is my problem, i need to do lookups such as

 select there where here='12345' and distance < '30';

but this is quite slow. even to "select count(id)" tables a "85 seconds" which is too slow i have added some indexing (but i am quite uneducated on Oracle tuning) and they dont seem to help.

 create index HTD on zip2zip (HERE, THERE, DISTANCE);  create index HT on zip2zip (HERE, THERE);

i am running Oracle v8.1.5 and want to drop the latency down to 8 seconds if possible.

thanks go very much for any help you can give me. Received on Fri Dec 01 2000 - 23:13:56 CST