From oracle-l-bounce@freelists.org Sun Sep 19 15:32:15 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i8JKWEV02875 for ; Sun, 19 Sep 2004 15:32:14 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i8JKWEI02870 for ; Sun, 19 Sep 2004 15:32:14 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 10FA672C334; Sun, 19 Sep 2004 15:38:14 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 09675-36; Sun, 19 Sep 2004 15:38:13 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 6CBED72C15B; Sun, 19 Sep 2004 15:38:13 -0500 (EST) Message-ID: <06c001c49e87$fe9a11a0$3c02a8c0@JARAWIN> From: "jaromir nemec" To: Subject: Change in computation of IXSEL between 8.1.6 and 8.1.7 Date: Sun, 19 Sep 2004 22:33:56 +0200 MIME-Version: 1.0 Content-type: text/plain X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 Content-Transfer-Encoding: 8bit X-archive-position: 9753 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: jaromir@db-nemec.com Precedence: normal Reply-To: jaromir@db-nemec.com X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org Hello List, In 8.1.6.3.0, 64 bit (HP-UX) gives the predicate ca.column1 = '40' and ca.column2 like '5.13341%' an IXSEL of 5.8878e-07 (corresponding exactly to density of the column2). See extracts of 10053 event trace: Table stats Table: TABLE1 Alias: CA TOTAL :: CDN: 1698425 NBLKS: 32089 TABLE_SCAN_CST: 7687 AVG_ROW_LEN: 281 -- Index stats INDEX#: 45231 COL#: 3 11 TOTAL :: LVLS: 2 #LB: 3365 #DK: 1573698 LB/K: 1 DB/K: 1 CLUF: 669635 *************************************** SINGLE TABLE ACCESS PATH Column: COLUMN1 Col#: 11 Table: TABLE1 Alias: CA NDV: 5 NULLS: 0 DENS: 2.0000e-01 Column: COLUMN2 Col#: 3 Table: TABLE1 Alias: CA NDV: 1698425 NULLS: 0 DENS: 5.8878e-07 TABLE: TABLE1 ORIG CDN: 1698425 CMPTD CDN: 1 Access path: tsc Resc: 7687 Resp: 7687 Access path: index (scan) INDEX#: 45231 TABLE: TABLE1 CST: 4 IXSEL: 5.8878e-07 TBSEL: 1.1776e-07 BEST_CST: 4.00 PATH: 4 Degree: 1 *************************************** In 8.1.7.4.0 - 64bit gives the same predicate IXSEL of only 9.0000e-03 (4 order of magnitude difference). The data is not exactly the same, but this cannot explain this big difference. Table stats Table: TABLE1 Alias: CA TOTAL :: CDN: 2008642 NBLKS: 37374 TABLE_SCAN_CST: 8952 AVG_ROW_LEN: 279 -- Index stats INDEX#: 45231 COL#: 3 11 TOTAL :: LVLS: 2 #LB: 3979 #DK: 1671180 LB/K: 1 DB/K: 1 CLUF: 687040 *************************************** SINGLE TABLE ACCESS PATH Column: COLUMN1 Col#: 11 Table: TABLE1 Alias: CA NDV: 5 NULLS: 0 DENS: 2.0000e-01 Column: COLUMN2 Col#: 3 Table: TABLE1 Alias: CA NDV: 2008642 NULLS: 0 DENS: 4.9785e-07 TABLE: TABLE1 ORIG CDN: 2008642 CMPTD CDN: 20087 Access path: tsc Resc: 8952 Resp: 8952 Access path: index (scan) INDEX#: 45231 TABLE: TABLE1 CST: 1275 IXSEL: 9.0000e-03 TBSEL: 1.8000e-03 BEST_CST: 1275.00 PATH: 4 Degree: 1 *************************************** Was the algorithm of computing of IXSEL changed in 8.1.7? I found similar bug relevant to 9.2 on metalink 2991526 resp. 3009559 (SELECTIVITY OF LIKE PREDICATE DIFFERENT IN 9203 FROM 8174) Is there a workaround to this bug/feature? Any explanations and hints appreciated! The main problem: The low index selectivity (in 8.1.7) doesn't disable the index as access path but the high value of CMPTD CDN leads in joins to preferring merge joins over nested loops. Btw, I would expect the filter factor of the mentioned predicate to be as follows (based on the description in A Look under the Hood ..): density of column1 (rule for predicate c1 = value) times (rule for predicate AND predicate) density of column2 (rule for predicate c1 LIKE value) This formula describes in 8.1.6 not IXSEL but TBSEL; interestingly though IXSEL in 8.1.7 is complete different the ratio of TBSEL / IXSEL remain the same - 1/5 (Is this the density of column1??) Thanks Jaromir -- http://www.freelists.org/webpage/oracle-l