Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: RE: Index behavior

RE: RE: Index behavior

From: Larry Elkins <elkinsl_at_flash.net>
Date: Thu, 06 Nov 2003 17:04:35 -0800
Message-ID: <F001.005D5DE0.20031106170435@fatcity.com>


But should this be the behavior we expect? I don't know. But there is bug 2991526. Reproducible on 9.2.0.3 on Solaris 64 bit / Win2k sp3. Oracle's test case sounds just like the original problem and your test case, and the transition in their test case was between 2 and 3 characters, index access with 3 characters, and full on 2. So, even though it can be reproduced with a little work, should it really be the way things happen? I don't know, but bug 2991526 seems to imply it isn't.

Regards,

Larry G. Elkins
elkinsl_at_flash.net
214.954.1781

> -----Original Message-----
> From: ml-errors_at_fatcity.com [mailto:ml-errors_at_fatcity.com]On Behalf Of
> Wolfgang Breitling
> Sent: Thursday, November 06, 2003 9:19 AM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: RE: Index behavior
>
>
> Actually, it has nothing to do with any of the table or index statistics.
> OK, almost nothing. I suppose if Jonathan (Lewis) can get the
> optimizer to
> do a FTS on an umpteen billion row table to retrieve a single row by its
> prime key, one can concoct a scenario of statistics values, aided by init
> or session parameters, that would cause the CBO to use a full
> table scan to
> resolve "where name like 'AB%".
> Back to the topic. I did a test and the situation is easily
> reproduceable.
> What happens is that as the like comparison string gets short, the
> selectivity of the predicate decreases ( if you look at the 10053 trace,
> the TBSEL value increases but that is the same paradoxon as with
> performance: if something gets faster, did its performance decrease? ) as
> one would expect. The TBSEL selectivity value and the rate of its
> increase
> depends on the length of the like comparison string and the
> average column
> length. When it gets down to the transition from ABC% to AB%, that trend
> breaks sharply and suddenly the selectivity increases by orders of
> magnitude ( TBSEL decreases by a huge factor ). for "like A%" it
> decreases
> again, but is still lower (depends on avg col length) than the
> selectivity
> of "like ABC%".
> You can see that in the following test. The cardinality reflects the
> changes in the tbsel value (cardinality = tbsel * num_rows, which was
> 10,000 for the test).
>
> select id from sam where name like 'ABCDEFGHI%';
> card operation
> ----- ----------------------------------
> 1 SELECT STATEMENT
> 1 TABLE ACCESS BY INDEX ROWID SAM
> 1 INDEX RANGE SCAN SAM_IX
>
> select id from sam where name like 'ABCDEFGH%';
> card operation
> ----- -----------------------------------
> 1 SELECT STATEMENT
> 1 TABLE ACCESS BY INDEX ROWID SAM
> 1 INDEX RANGE SCAN SAM_IX
>
> select id from sam where name like 'ABCDEFG%';
> card operation
> ----- -----------------------------------
> 57 SELECT STATEMENT
> 57 TABLE ACCESS FULL SAM
>
> select id from sam where name like 'ABCDEF%';
> card operation
> ----- -----------------------------------
> 100 SELECT STATEMENT
> 100 TABLE ACCESS FULL SAM
>
> select id from sam where name like 'ABCDE%';
> card operation
> ----- -----------------------------------
> 178 SELECT STATEMENT
> 178 TABLE ACCESS FULL SAM
>
> select id from sam where name like 'ABCD%';
> card operation
> ----- -----------------------------------
> 317 SELECT STATEMENT
> 317 TABLE ACCESS FULL SAM
>
> select id from sam where name like 'ABC%';
> card operation
> ----- -----------------------------------
> 563 SELECT STATEMENT
> 563 TABLE ACCESS FULL SAM
>
> select id from sam where name like 'AB%';
> card operation
> ----- -----------------------------------
> 2 SELECT STATEMENT
> 2 TABLE ACCESS BY INDEX ROWID SAM
> 2 INDEX RANGE SCAN SAM_IX
>
> select id from sam where name like 'A%';
> card operation
> ----- -----------------------------------
> 297 SELECT STATEMENT
> 297 TABLE ACCESS FULL SAM
>
> At 04:29 PM 11/5/2003, you wrote:
> >Hi Goulet,
> >
> >The clustering factor on the index=37930
> >number of distinct keys=38357
> >number of leaf blocks=1075
>
> Wolfgang Breitling
> Oracle7, 8, 8i, 9i OCP DBA
> Centrex Consulting Corporation
> http://www.centrexcc.com
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Wolfgang Breitling
> INET: breitliw_at_centrexcc.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Larry Elkins
  INET: elkinsl_at_flash.net

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Thu Nov 06 2003 - 19:04:35 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US