Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2011
    Posts
    3

    Unanswered: Tuning Select on nested table column

    I defined my table (witch Index on nested table column):
    create or replace type tt_pid as table of number;
    /
    show errors


    create table TB_TEST (
    test_id number(38) not null,
    xyz_id number(38),
    wert number(38),
    wert_erweiterung varchar2(2),
    von_id number(38),
    an_id_liste tt_pid,
    test_typ_id number(38),
    ruecktest_id number(38)
    ) nested table an_id_liste store as tb_pid_nt
    Tablespace TBS_RM
    PctFree 25 PctUsed 60 Initrans 40 Maxtrans 255 LOGGING
    Storage (initial 1M next 1M pctincrease 0 minextents 1 maxextents UNLIMITED)
    /
    create unique index pk_TB_TEST on TB_TEST (test_id)
    reverse
    Pctfree 5 Initrans 40 Maxtrans 255 LOGGING
    Tablespace TBS_IDX_RM
    Storage( initial 500K next 500K pctincrease 0 minextents 1 maxextents UNLIMITED)
    ;
    alter table TB_TEST add
    constraint pk_TB_TEST primary key (test_id)
    USING INDEX ;

    -- Index auf nested Table
    create index UK_TB_TEST4 on tb_pid_nt x(x.column_value)
    reverse
    TABLESPACE TBS_IDX_RM
    Pctfree 10 Initrans 40 Maxtrans 255 LOGGING
    STORAGE( initial 500K next 500K pctincrease 0 minextents 1 maxextents
    UNLIMITED);


    My SELECT-Statement is:

    select 1
    from TB_TEST tb
    where
    (tb.von_id = 3650192 or
    3650192 in (select column_value from Table( cast (tb.an_id_liste as tt_pid ) )) )
    and rownum = 1;

    Ausf³hrungsplan
    ----------------------------------------------------------
    Plan hash value: 1099138203

    --------------------------------------------------------------------------------
    -----------------------

    | Id | Operation | Name | Rows | Bytes |
    Cost (%CPU)| Time |

    --------------------------------------------------------------------------------
    -----------------------

    | 0 | SELECT STATEMENT | | 1 | 19 |
    2 (0)| 00:00:01 |

    |* 1 | COUNT STOPKEY | | | |
    | |

    |* 2 | FILTER | | | |
    | |

    | 3 | TABLE ACCESS FULL | TB_TEST | 1 | 19 |
    2 (0)| 00:00:01 |

    |* 4 | COLLECTION ITERATOR PICKLER FETCH| | | |
    | |

    --------------------------------------------------------------------------------
    -----------------------


    Predicate Information (identified by operation id):
    ---------------------------------------------------

    1 - filter(ROWNUM=1)
    2 - filter("TB"."VON_ID"=3650192 OR EXISTS (SELECT /*+ */ 0
    FROM TABLE()

    "KOKBF$" WHERE VALUE(KOKBF$)=3650192))
    4 - filter(VALUE(KOKBF$)=3650192)


    Statistiken
    ----------------------------------------------------------
    1 recursive calls
    0 db block gets
    2162792 consistent gets
    0 physical reads
    0 redo size
    277 bytes sent via SQL*Net to client
    370 bytes received via SQL*Net from client
    1 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    0 rows processed

    The index was NOT used, 8 Seconds is too long.
    I tried tuning:

    select 1
    from TB_TEST tb
    where
    (tb.von_id = 3650192
    or
    EXISTS(select /*+ INDEX (t UK_TB_TEST4) cardinality (t 1800000) */ distinct column_value from Table( cast (tb.an_id_liste as tt_pid )) t where t.column_value = 3650192) )
    and rownum = 1;

    Ausf³hrungsplan
    ----------------------------------------------------------
    Plan hash value: 1099138203

    --------------------------------------------------------------------------------
    -----------------------

    | Id | Operation | Name | Rows | Bytes |
    Cost (%CPU)| Time |

    --------------------------------------------------------------------------------
    -----------------------

    | 0 | SELECT STATEMENT | | 1 | 19 |
    2 (0)| 00:00:01 |

    |* 1 | COUNT STOPKEY | | | |
    | |

    |* 2 | FILTER | | | |
    | |

    | 3 | TABLE ACCESS FULL | TB_TEST | 1 | 19 |
    2 (0)| 00:00:01 |

    |* 4 | COLLECTION ITERATOR PICKLER FETCH| | | |
    | |

    --------------------------------------------------------------------------------
    -----------------------


    Predicate Information (identified by operation id):
    ---------------------------------------------------

    1 - filter(ROWNUM=1)
    2 - filter("TB"."VON_ID"=3650192 OR EXISTS (SELECT /*+ INDEX
    ("KOKBF$"

    "UK_TB_TEST4") OPT_ESTIMATE (TABLE "KOKBF$" ROWS=1800000.0
    00000 ) */ 0 FROM TABLE()

    "KOKBF$" WHERE VALUE(KOKBF$)=3650192))
    4 - filter(VALUE(KOKBF$)=3650192)


    Statistiken
    ----------------------------------------------------------
    0 recursive calls
    0 db block gets
    2162780 consistent gets
    0 physical reads
    0 redo size
    277 bytes sent via SQL*Net to client
    370 bytes received via SQL*Net from client
    1 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    0 rows processed

    Here you can see a dataset:
    select * from TB_TEST where test_id = 11920192;

    TEST_ID XYZ_ID WERT WE VON_ID
    --------------- ------------------------- ---------- -- ----------------------
    AN_ID_LISTE
    --------------------------------------------------------------------------------
    TEST_TYP_ID RUECKTEST_ID
    ----------- ------------
    11920192 184880192 0 N
    TT_PID(61430192, 61440192, 61450192, 61460192, 61470192, 61480192, 61490192)
    120101


    Could your help me, please?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Post complete results of
    SELECT * from v$version;

    do current statistics exist on all involved tables & indexes?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jan 2011
    Posts
    3
    Version: 10.2.0.4.0

    Yes, statistics exists in tables and indexes

  4. #4
    Join Date
    Jan 2011
    Posts
    3
    the solution is:

    create index UK_TB_TEST5 on tb_pid_nt x(x.nested_table_id)
    reverse
    TABLESPACE TBS_IDX_RM
    Pctfree 10 Initrans 40 Maxtrans 255 LOGGING
    STORAGE( initial 500K next 500K pctincrease 0 minextents 1 maxextents
    UNLIMITED);

Tags for this Thread

Posting Permissions

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