At 12:59 PM 7/29/2003 -0800, you wrote:
Is the INLIST ITERATOR unable to use the index unless we specify NOT NULL? I wanted to bounce this off the list before we log a TAR.
We are examining the performance of a query and I am trying to understand why an INLIST ITERATOR is not used if there is not an explicit IS NOT NULL predicate condition.
TableA.column1 is nullable, has null values and has a high number of distinct values. It also has a nonunique index with only column1.
The table and indexes are recently analyzed. The column has a histogram with 2 buckets.
In the query, the predicate for column1 is "where column1 in ('<value1>', '<value2>')". Both of the values are literals and actual values do exist in the table.
If we use just this predicate (along with the other join conditions), the execution plan is a series of hash joins on full table scans (cost of 38756/card of 3). If we add "and column1 is not null", the execution plan is an INLIST ITERATOR with a series of nested loops using index range or unique scans.
In looking at a 10053 trace file, it becomes clear as to why the query is taking a bad plan. The cost of a single table access is radically different.
With Not Null (Note the tb_sel values (which seem to be the density * # of values) are correct in this computation)
SINGLE TABLE ACCESS PATH
TABLE: SERIALS ORIG CDN: 3318658 ROUNDED CDN: 2 CMPTD CDN: 2
Access path: tsc Resc: 7137 Resp: 7137
Access path: index (no sta/stp keys)
Index: SERIALS_MAN_SER
TABLE: SERIALS
RSC_CPU: 0 RSC_IO: 19877
IX_SEL: 1.0000e+00 TB_SEL: 6.0265e-07
Access path: index (scan)
Index: SERIALS_SER
TABLE: SERIALS
RSC_CPU: 0 RSC_IO: 5
IX_SEL: 6.0277e-07 TB_SEL: 6.0265e-07
Access path: index (equal)
Index: SERIALS_SER
TABLE: SERIALS
RSC_CPU: 0 RSC_IO: 3
IX_SEL: 3.0138e-07 TB_SEL: 3.0133e-07
Access path: index (equal)
Index: SERIALS_SER
TABLE: SERIALS
RSC_CPU: 0 RSC_IO: 3
IX_SEL: 3.0138e-07 TB_SEL: 3.0133e-07
BEST_CST: 5.00 PATH: 4 Degree: 1
Without Not Null (Note the tb_sel values are not correct. If I read this correctly, this is telling the CBO that there is a single value for each of the index columns)
SINGLE TABLE ACCESS PATH
TABLE: SERIALS ORIG CDN: 3318658 ROUNDED CDN: 3318658 CMPTD CDN: 3318658
Access path: tsc Resc: 7137 Resp: 7137
Access path: index (no sta/stp keys)
Index: SERIALS_EQ
TABLE: SERIALS
RSC_CPU: 0 RSC_IO: 13265
IX_SEL: 1.0000e+00 TB_SEL: 1.0000e+00
Access path: index (no sta/stp keys)
Index: SERIALS_MAN_SER
TABLE: SERIALS
RSC_CPU: 0 RSC_IO: 19875
IX_SEL: 1.0000e+00 TB_SEL: 1.0000e+00
Access path: index (no sta/stp keys)
Index: SERIALS_SER
TABLE: SERIALS
RSC_CPU: 0 RSC_IO: 12155
IX_SEL: 1.0000e+00 TB_SEL: 1.0000e+00
Access path: index (no sta/stp keys)
Index: SERIALS_UC
TABLE: SERIALS
RSC_CPU: 0 RSC_IO: 7361
IX_SEL: 1.0000e+00 TB_SEL: 1.0000e+00
BEST_CST: 7137.00 PATH: 2 Degree: 1
The interesting thing is if I extract the access of this table to a single (non-joined) statement, it computes the cost and plan like I would expect. It is when we add in other tables and a join condition that it 'loses' its mind.
Thoughts? Need More Detail?
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).
