REPOST: CBO skip index on like 'text%'

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)

• WAS CANCELLED BY =======: From: ewong_at_mail.com (Ed Wong) Control: cancel <a5ae1554.0201241801.3409aa17_at_posting.google.com> Subject: cmsg cancel <a5ae1554.0201241801.3409aa17_at_posting.google.com> Date: Mon, 28 Jan 2002 01:15:06 GMT Message-ID: <cancel.a5ae1554.0201241801.3409aa17_at_posting.google.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!news-han1.dfn.de!news-nue1.dfn.de!newsfeed.r-kom.de!newsfeed.freenet.de!newsfeed.media.kyoto-u.ac.jp!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:40724252

This message was cancelled from within The Unacanceller's glorious new software, Lotus 1-2-3 For Rogue Cancellers. Received on Thu Jan 24 2002 - 20:01:04 CST