REPOST: 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..."

========= WAS CANCELLED BY =======:
From: Connor McDonald <connor_mcdonald_at_yahoo.com>
Control: cancel <3C51CE66.2137_at_yahoo.com>
Subject: cmsg cancel <3C51CE66.2137_at_yahoo.com>
Date: Mon, 28 Jan 2002 02:15:56 GMT
Message-ID: <cancel.3C51CE66.2137_at_yahoo.com>
X-No-Archive: yes
Newsgroups: microsoft.test,alt.flame.niggers,comp.databases.oracle.server
NNTP-Posting-Host: w088.z064003087.lax-ca.dsl.cnc.net 64.3.87.88
Lines: 1         
Path: news.uni-stuttgart.de!news.fh-hannover.de!fu-berlin.de!feed.ac-versailles.fr!out.nntp.be!propagator-SanJose!in.nntp.be!news-in-sanjose!sjc-feed.news.verio.net!sea-feed.news.verio.net!news.verio.net!msrnewsc1!cppssbbsa01.microsoft.com!tkmsftngp01!tkmsftngp04!u&n&a&c&anceller
Xref: news.uni-stuttgart.de control:40721881

This message was cancelled from within The Unacanceller's glorious new software, Lotus 1-2-3 For Rogue Cancellers.