Re: CBO skip index on like 'text%'

Ed Wong wrote:
>
> I have a 10 million records tables. After I switch to CBO(for all
> tables in the database), search on LIKE 'text%' does not use index
> anymore and takes 1 minutes to get the result instead of millisecond.
> It used to use index on RBO. The weird thing is only this column on
> this particular table has this strange behavior. Other tables still
> uses index.
>
> SQL> select * from seq where name = 'text';
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=2 Bytes=1276)
> 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'SEQ' (Cost=4 Card=2 Byte
> s=1276)
>
> 2 1 INDEX (RANGE SCAN) OF 'IN_SEQ_NAME' (NON-UNIQUE) (Cost=3
> Card=2)
>
> SQL> select * from seq where name like 'text%';
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=28832 Card=632682 By
> tes=403651116)
>
> 1 0 TABLE ACCESS (FULL) OF 'SEQ' (Cost=28832 Card=632682 Bytes
> =403651116)

The data distribution could be causing this. Adding a FIRST_ROWS hint, or calculating histograms could assist. Alternatively, a stored outline could be used to override certain queries.

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."