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

Thanks Jonathan, I tried using TABLE instead of THE .. CAST (thanks Barry for syntax). But still it does a full table scan.

I will try cardinatlity hint, But i am not sure that will work in my case as the number of items in IN clause vary between 10 - 100 .

I am able to solve my problem using a string inside IN clause , But still curious why this doesnt works... (Attached please find PLAN)

SQL> 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 * FROM TABLE ( csvToArray( A5FB0891-09D8-4C2
4-9AB8-C5FFD028E203,A5FB0891-09D8-4C24-9AB8-C5FFD028E203,A5FB0891-09D8-4C24-9AB8-C5FFD028E203,A5FB08 91-09D8-4C24-9AB8-C5FFD028E203,8365197F-603A-491E-A632-FECADB662324,8365197F-603A-491E-A632-FECADB66 2324,A5FB0891-09D8-4C24-9AB8-C5FFD028E203,A5FB0891-09D8-4C24-9AB8-C5FFD028E203,A5FB0891-09D8-4C24-9A B8-C5FFD028E203')));   COUNT(*)

     15012

Execution Plan

   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1892819 Card=1 Byt
          es=46)

   1    0   SORT (AGGREGATE)
   2    1     NESTED LOOPS (SEMI) (Cost=1892819 Card=1113 Bytes=51198)
   3    2       NESTED LOOPS (Cost=209 Card=111330 Bytes=5121180)
   4    3         TABLE ACCESS (FULL) OF 'CATALOGUE' (Cost=209
Card=111330
           Bytes=3673890)

   5    3         INDEX (UNIQUE SCAN) OF 'XPK_CATALOGUE_TYPE' (UNIQUE)
   6    2       COLLECTION ITERATOR (PICKLER FETCH) OF 'CSVTOARRAY'

Statistics

          0  recursive calls
          0  db block gets
       1374  consistent gets
        795  physical reads
          0  redo size
        305  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

Thanks
Nirmal

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:<bn9bc8$7ll$1$8300dec7_at_news.demon.co.uk>...
> Check the CARDINALITY that Oracle has assumed
> for your cast() - it's 8,168, which probably persuades
> Oracle not to use a nested loop. The value seems
> rather high, but I believe Tom also has a note about
> the default value that Oracle uses in circumstances
> like this.
>
> If you know the typical number of items in the
> array, then this would be a good place to use
> the /*+ cardinality (alias, number) */ hint which
> I believe has been documented in the 9.2 Performance
> Reference. In passing the THE() syntax is 8.0, and
> has been superseded by the TABLE() syntax. And I think
> in your case you may even be able to get rid of DUAL
> using something like:
> table(cast(function_name('.....') as array_type))
>
> --
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> The educated person is not the person
> who can answer the questions, but the
> person who can question the answers -- T. Schick Jr
>
>
> One-day tutorials:
> http://www.jlcomp.demon.co.uk/tutorial.html
> ____Belgium__November (EOUG event - "Troubleshooting")
> ____UK_______December (UKOUG conference - "CBO")
>
>
> Three-day seminar:
> see http://www.jlcomp.demon.co.uk/seminar.html
> ____UK___November
>
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
> "N.K" <nirmalkannan_at_hotmail.com> wrote in message
> news:5b04353b.0310230307.61b59829_at_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
> >
> AST( csvToArray('0001545A-A1CF-4524-B8EC-841BDF325E83,0005EC3A-0712-42
> 0F-970C-85788C2C8E18,0007A2BD-6866-4
> >
> 642-80E1-E203491511AF,00089E2E-709B-4B25-8CE0-031E0D528570,0008E385-4D
> 1D-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 Mon Oct 27 2003 - 05:24:36 CST