Re: selectivity of predicates with LIKE - diff between 8i and

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Wed, 11 Jun 2003 13:59:01 -0700
Message-ID: <F001.005AF848.20030611131945@fatcity.com>

I pasted the wrong value. That last line should read

It is an index-only access path, so the cost is LVLS + FF * #LB = 2 + 3.4877e-02 * 1035 = 2 + 36.0977, rounded to 39.

At 12:06 PM 6/11/2003 -0800, you wrote:
So far I have not observed a difference in selectivity of predicates between 8i and 9i.
Is there something special about the table or index? I tried to create a mock table with your statistics and the 9i optimizer came up with the same cardinality and cost estimates as 8i. Can you post (or e-mail me) more of the 10053 trace?

In 9i the optimizer used a FF of 3.4877e-02 (see IXSEL or TBSEL in the 10053 trace) giving the high cardinality estimate : 241286 * 3.4877e-02 = 8414.85, rounded to 8415.
And the explanation of the cost of 39 is as follows:

It is an index-only access path, so the cost is LVLS + FF * #LB = 2 + 8414.85 * 1035 = 2 + 36.0977, rounded to 39. 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: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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 Wed Jun 11 2003 - 15:59:01 CDT