Inlist Iterator and NULLs

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?

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Daniel Fink
  INET: daniel.fink_at_sun.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).



text/x-vcard attachment: Card for Daniel Fink