Professional Web Applications Themes

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

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 yzed. 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 ...

  1. #1

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

    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 yzed.

    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_DISPL AYID,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:
    com)
    N.K Guest

  2. #2

    Default 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 cirstances
    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 doented 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" <com> wrote in message
    news:google.com... 
    type 
    (http://asktom.oracle.com/pls/ask/f?p=4950:8:2023092145920672039::NO::
    F4950_P8_DISPLAYID,F4950_P8_CRITERIA:210612357425) 
    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, 
    Bytes= 
    Bytes=163360) 
    Card=82) 
    Bytes=43) 
    (Cost=6 
    (NON-UNI 
    (http://www.metalink.oracle.com/metalink/plsql/showdoc?db=Not&id=62153
    ..1 


    Jonathan Guest

  3. #3

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

    Jonathan mentioned that you can get rid of the THE and DUAL
    references, and you really don't need a CAST in this case. In
    addition, it can often be helpful to add a 'where rownum > 0' clause
    to the select in the IN clause. This is a Tom Kyte tip that will
    often cause the CBO to switch to a nested loop lookup on the large
    table using an index.

    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('0001545A-A1CF-4524-B8EC-841BDF325E83,0005EC3A-0712-420F-970C-85788C2C8E18,...'))
    where rownum > 0);

    -Todd
    Todd Guest

  4. #4

    Default 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" <demon.co.uk> wrote in message news:<bn9bc8$7ll$1$demon.co.uk>... 
    > type 
    > (http://asktom.oracle.com/pls/ask/f?p=4950:8:2023092145920672039::NO::
    > F4950_P8_DISPLAYID,F4950_P8_CRITERIA:210612357425) 
    > 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, 
    > Bytes= 
    > Bytes=163360) 
    > Card=82) 
    > Bytes=43) 
    > (Cost=6 
    > (NON-UNI 
    > (http://www.metalink.oracle.com/metalink/plsql/showdoc?db=Not&id=62153
    > .1 [/ref]
    N.K Guest

Similar Threads

  1. Replies: 0
    Last Post: February 24th, 01:06 PM
  2. Multi-column indexes
    By Edmund Dengler in forum PostgreSQL / PGSQL
    Replies: 3
    Last Post: January 16th, 12:00 AM
  3. changing column from int4 to int8, what happens with indexes?
    By David Teran in forum PostgreSQL / PGSQL
    Replies: 2
    Last Post: January 4th, 04:36 PM
  4. Replies: 0
    Last Post: May 14th, 12:19 AM
  5. Suggested Books
    By Jay in forum ASP.NET General
    Replies: 2
    Last Post: July 22nd, 06:25 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139