| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: IN clause doesnt considers INDEXES in CBO mode ALL_ROWS , ( If we use approach suggested in AskTOM column)
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...Received on Thu Oct 23 2003 - 14:50:24 CDT
> 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)
 |
 |