Re: Inlist Iterator and NULLs

Can you post the sql, or even the 10053 trace up to the "GENERAL PLANS" section. That would also answer the question "which exact version/release of Oracle 9?"

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: breitliw_at_centrexcc.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).