IN clause doesnt considers INDEXES in CBO mode ALL_ROWS , ( If we use approach suggested in AskTOM column)

From: N.K <nirmalkannan_at_hotmail.com>
Date: 23 Oct 2003 04:07:08 -0700
Message-ID: <5b04353b.0310230307.61b59829@posting.google.com>

Hi,

Before I start explaining problem here are DB details:

Oracle verion

---

Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production With the Partitioning and Real Application Clusters options JServer Release 9.2.0.3.0 - Production
OS: Red Hat Linux Advanced Server release 2.1AS/i686

All the tables are analyzed.  

I need to pass CSV to a stored proc for using in side 'IN' clause. Client app doesnt supports any other types.

Two simple tables are involved:

(1) Catalog Table with just 1.6 million rows ( few VARCHAR,NUMBER
columns), which has a non-unique index IDX_CAMPAIGN_GUID

(2) Catalog_type is a small table with just 30 - 40 rows ,a look up
table which has PK index

I was using Tom Kyte's approach of parsing CSV string to a Table type and using it inside IN clause
(http://asktom.oracle.com/pls/ask/f?p=4950:8:2023092145920672039::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:210612357425)
 , the query looks like

SELECT COUNT(*) FROM catalogue c INNER JOIN catalogue_type ct ON ct.catalogue_item_type_id = c.catalogue_item_type_id WHERE c.campaign_guid IN ( SELECT temp.COLUMN_VALUE val FROM THE (SELECT CAST( csvToArray('0001545A-A1CF-4524-B8EC-841BDF325E83,0005EC3A-0712-420F-970C-85788C2C8E18,0007A2BD-6866-4 642-80E1-E203491511AF,00089E2E-709B-4B25-8CE0-031E0D528570,0008E385-4D1D-4188-8A4F-BAA38A9CBAE4,

....................') AS ARRAY) FROM dual)temp);

But the strange thing is that this query doesnt considers Index at IDX_CAMPAIGN_GUID!!!!! ( It does if we use /*+ rule */ hint) , Query plan is given below:

Execution Plan

---

   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4292 Card=1 Bytes=
          63)

   1    0   SORT (AGGREGATE)
   2    1     NESTED LOOPS (Cost=4292 Card=18064 Bytes=1138032)
   3    2       HASH JOIN (Cost=4292 Card=18064 Bytes=1083840)
   4    3         VIEW OF 'VW_NSO_1' (Cost=17 Card=8168 Bytes=163360)
   5    4           SORT (UNIQUE)
   6    5             COLLECTION ITERATOR (PICKLER FETCH) OF 'CSVTO
           ARRAY'

   7    6               TABLE ACCESS (FULL) OF 'DUAL' (Cost=2 Card=82)
   8    3         TABLE ACCESS (FULL) OF 'CATALOGUE' (Cost=4146
Card=12968
          30 Bytes=51873200)

   9    2       INDEX (UNIQUE SCAN) OF 'XPK_CATALOGUE_TYPE' (UNIQUE)

Statistics

---

       1891  recursive calls
          0  db block gets
      35293  consistent gets
      20303  physical reads
          0  redo size
        303  bytes sent via SQL*Net to client
        495  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         75  sorts (memory)
          0  sorts (disk)
          1  rows processed

In the same time if we make the query simple and just use a string inside IN clause it considers index!!!!

SELECT COUNT(*) from catalogue INNER JOIN catalogue_type ct ON ct.catalogue_item_type_id = c.catalogue_item_type_id where campaign_guid IN
('0001545A-A1CF-4524-B8EC-841BDF325E83', ....... ') ;
Execution Plan

---

   0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=6 Card=1 Bytes=43)    1 0 SORT (AGGREGATE)

   2    1     NESTED LOOPS (Cost=6 Card=73 Bytes=3139)
   3    2       INLIST ITERATOR
   4    3         TABLE ACCESS (BY INDEX ROWID) OF 'CATALOGUE' (Cost=6
Car
          d=73 Bytes=2920)

   5    4           INDEX (RANGE SCAN) OF 'IDX_CAMPAIGN_GUID' (NON-UNI
          QUE) (Cost=3 Card=73)

   6    2       INDEX (UNIQUE SCAN) OF 'XPK_CATALOGUE_TYPE' (UNIQUE)

Statistics

---

          0  recursive calls
          0  db block gets
        133  consistent gets
          0  physical reads
          0  redo size
        303  bytes sent via SQL*Net to client
        495  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

I am not an Oracle DBA, just an app developer .So no idea why Oracle is behaving this way or what is wrong with the query . IS it a limitation of CBO ?? I happen to read a metalink article which also mentions this problem
(http://www.metalink.oracle.com/metalink/plsql/showdoc?db=Not&id=62153.1
)

Thanks in advance,
Regards
Nirmal

( if you dont mind ,please copy to my personal email id as well:
nkchidambaram_at_yahoo.com) Received on Thu Oct 23 2003 - 06:07:08 CDT