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?begin:vcard
n:Fink;Daniel
tel;cell:303.808.3282
tel;work:303.272.3225
x-mozilla-html:TRUE
adr:;;;;;;
version:2.1
email;internet:[EMAIL PROTECTED]
title:DB Services Lead
x-mozilla-cpt:;-4832
fn:Daniel Fink
end:vcard
