Re: Oracle 9.0.1.6 fails to use index with '=' but will with 'like'

steven.he..._at_gmail.com wrote:
> How is this
>
> select * from coneventnp where chcontainer like 'OLSU8888888'
>
> Uses the index on the chcontainer column, but
>
> select * from coneventnp where chcontainer = 'OLSU8888888'
>
> will result in a full table scan.
>
> Anybody ever seen anything like this? We consider this to be beyond
> bizzare.

Not that strange. I get the similar behaviour. I think we underestimate the CBO - it is not (that) stupid. :-)

The LIKE clause's value does not contain a wildcard - thus the LIKE equates to an EQUAL TO. And the CBO seems to know that and treat it as such.

SQL> create table fooscan nologging as select object_type, object_name, object_id from all_objects;

Table created.

SQL> create index idx1_fooscan on fooscan( object_name );

Index created.

SQL> exec dbms_stats.gather_table_stats( USER, 'FOOSCAN' )

PL/SQL procedure successfully completed.

SQL> select count(*) from fooscan where object_name like 'FOOSCAN';

  COUNT(*)

         1

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=23)    1 0 SORT (AGGREGATE)

   2    1     INDEX (RANGE SCAN) OF 'IDX1_FOOSCAN' (NON-UNIQUE) (Cost=
          1 Card=2 Bytes=46)

A LIKE clause always resulting in a full table scan is also not true. Not how it instead does a full scan of index and not the table.. (not that stupid this CBO).

SQL> select count(*) from fooscan where object_name like 'FOOSCAN%';

  COUNT(*)

         1

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=23)    1 0 SORT (AGGREGATE)

   2    1     INDEX (FAST FULL SCAN) OF 'IDX1_FOOSCAN' (NON-UNIQUE) (C
          ost=4 Card=1458 Bytes=33534)

Why you are getting a FTS though on the second SQL statement do seem strange though... This I would put down to either skewed stats or something related to optimiser pfile/spfile settings...

--
Billy