Re: Speeding up query that contains BETWEEN

>
> 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 Received on Fri May 10 2002 - 16:32:51 CDT