Re: Speedup Help

You problem is one of data scatter.
In your sample query, there are
a possible 29,000 entries in the
table that Oracle has to examine.

You need to determine the nature of
ALL the possible queries that should
have quick response times, and index
for them so that every query can be
met from the index without visiting the
table.

For your example you need an index
on (here, distance).

For the 'opposite' example

    select here where there='12345' and distance < '30'; (which by symmetry shouldn't be needed)
you need an index on (there, distance),

Then for

    select distance where here = '1234' and there = '4321' you need an index on (here, there).

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases

Publishers:  Addison-Wesley
Book bound date: 8th Dec 2000
See a first review at:
http://www.ixora.com.au/resources/index.htm#practical_8i
More reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



Gabriel Millerd wrote in message <90a0ek$se2$1_at_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.