Re: Speeding up query that contains BETWEEN

On 10 May 2002 14:32:51 -0700, dsaracini_at_yahoo.com (David Saracini) wrote:

>>
>> The first query will use a simple index lookup.
>> The second query, however, will either use an index range scan with the
>> first column, street, only, or it will use the street_no, street index.
>> I would drop the second index, it's pretty useless as you never want to know
>> all the streets with the street no 2141, and you are bound to become a
>> victim of the optimizer choosing the index.
>> If you want further feedback please post the explain plan results of the two
>> scenario's
>>
>> Hth
>
>
>Thank you for your reply! Here are the plans:
>
>
>First query's plan...
>
>SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=1 Bytes=93)
> SORT (ORDER BY) (Cost=6 Card=1 Bytes=93)
> NESTED LOOPS (Cost=4 Card=1 Bytes=93)
> TABLE ACCESS (BY INDEX ROWID) OF APD_ADR (Cost=2 Card=1 Bytes=71)
> INDEX (RANGE SCAN) OF IDX_ADDR_COMPOSITE (NON-UNIQUE) (Cost=1 Card=1)
> TABLE ACCESS (BY INDEX ROWID) OF BASE (Cost=2 Card=266092 Bytes=5854024)
> INDEX (UNIQUE SCAN) OF A0_BASE_PRIMARYKEY (UNIQUE) (Cost=1 Card=266092)
>
>
>Where IDX_ADDR_COMPOSITE is defined as:
>
>CREATE INDEX IDX_ADDR_COMPOSITE ON
> ADDRESS(STREET_NO, STREET_NAME)
>
>
>Note: I think you're correct about the optimizer choosing the wrong index here.
>
>
>
>Second query's plan...
>
>SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=1 Bytes=93)
> SORT (ORDER BY) (Cost=6 Card=1 Bytes=93)
> NESTED LOOPS (Cost=4 Card=1 Bytes=93)
> TABLE ACCESS (BY INDEX ROWID) OF ADDRESS (Cost=2 Card=1 Bytes=71)
> INDEX (RANGE SCAN) OF IDX_STREET_NAME (NON-UNIQUE) (Cost=1 Card=1)
> TABLE ACCESS (BY INDEX ROWID) OF BASE (Cost=2 Card=266092 Bytes=5854024)
> INDEX (UNIQUE SCAN) OF A0_BASE_PRIMARYKEY (UNIQUE) (Cost=1 Card=266092)
>
>
>Where IDX_STREET_NAME is defined as:
>
>CREATE INDEX IDX_STREET_NAME ON
> ADDRESS(STREET_NAME)
>
>
>Any help will be greatly appreciated!
>
>
>Best Regards,
>
>David Saracini

Aren't we comparing apples and pears here. The first scenario refers to apd_adr and the second to address!

You aren't trying me to keep me busy aren't you. Why do you post not the complete story? Please leave generalizations for us to decide.

Regards

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Fri May 10 2002 - 17:32:25 CDT