3 Comments

  1. Sid
    12/1/2013

    Hi Marco,

    Thanks for demonstrating the creation and usage of structured XML Indexes.
    If in this example we were to add another XML ‘face’ as:

    INSERT INTO FACE_MASKS(CSXML_DOC) VALUES(XMLTYPE(‘

    223
    001
    002
    003
    224
    005
    006
    002

    224
    011
    022
    033
    224
    055
    066
    022

    ‘));

    Now we need to index :
    – faceid inside the repeating xml face
    – facetypeid inside the repeating xml facetype.

    But we are unable to do that..Tried something like:

    CREATE INDEX FaceTypeId_SXI
    ON FACE_MASKS(CSXML_DOC)
    INDEXTYPE IS XDB.XMLIndex
    PARAMETERS (‘XMLTABLE fm_content_table_01
    ”*”
    COLUMNS
    xmlresult XMLTYPE PATH ”faces/face” VIRTUAL
    XMLTABLE fm_content_table_02
    ”*”
    PASSING xmlresult
    COLUMNS
    FaceId_col number PATH ”faceId/text()”,
    FaceTypeXML XMLTYPE PATH ”facetype” VIRTUAL
    XMLTABLE fm_content_table_03
    ”*”
    PASSING FaceTypeXML
    COLUMNS
    FaceTypeID number PATH ”facetypeid/text()”
    ‘);

    SELECT vt.FaceTypeId_col
    FROM FACE_MASKS fm
    , XMLTABLE(‘*’
    PASSING fm.csxml_doc
    COLUMNS
    xmlresult XMLTYPE PATH ‘faces/face’
    ) xt
    , XMLTABLE(‘*’
    PASSING xt.xmlresult
    COLUMNS
    faceid number PATH ‘faceid/text()’,
    FaceTypeXML XMLTYPE PATH ‘facetype’
    ) xy ,
    XMLTABLE(‘*’
    PASSING xy.FaceTypeXML
    COLUMNS
    FaceTypeId_col NUMBER PATH ‘facetypeid/text()’
    ) vt

    But Select query doesn’t return anything.
    Although the query uses the Content Tables as follows:

    SQL_ID 26xqpy6g7n8nf, child number 0
    ————————————-
    SELECT vt.FaceTypeId_col FROM FACE_MASKS fm , XMLTABLE(‘*’
    PASSING fm.csxml_doc COLUMNS
    xmlresult XMLTYPE PATH ‘faces/face’
    ) xt , XMLTABLE(‘*’ PASSING
    xt.xmlresult COLUMNS faceid
    number PATH ‘faceid/text()’, FaceTypeXML XMLTYPE
    PATH ‘facetype’ ) xy ,
    XMLTABLE(‘*’ PASSING xy.FaceTypeXML
    COLUMNS FaceTypeId_col NUMBER PATH
    ‘facetypeid/text()’ ) vt

    Plan hash value: 2810788700

    ————————————————————————————————————-
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ————————————————————————————————————-
    | 0 | SELECT STATEMENT | | | | 3 (100)| |
    | 1 | NESTED LOOPS | | 1 | 2045 | 3 (34)| 00:00:01 |
    | 2 | NESTED LOOPS | | 1 | 2033 | 2 (50)| 00:00:01 |
    | 3 | NESTED LOOPS | | 1 | 1518 | 2 (50)| 00:00:01 |
    | 4 | VIEW | index$_join$_011 | 1 | 514 | 2 (50)| 00:00:01 |
    |* 5 | HASH JOIN | | | | | |
    | 6 | INDEX FAST FULL SCAN | SYS3327181_3327182_RID_IDX | 1 | 514 | 0 (0)| |
    | 7 | INDEX FAST FULL SCAN | SYS3327181_3327182_KEY_IDX | 1 | 514 | 1 (0)| 00:00:01 |
    | 8 | TABLE ACCESS BY INDEX ROWID| FM_CONTENT_TABLE_02 | 1 | 1004 | 0 (0)| |
    |* 9 | INDEX RANGE SCAN | SYS3327181_3327185_PKY_IDX | 1 | | 0 (0)| |
    | 10 | TABLE ACCESS BY INDEX ROWID | FM_CONTENT_TABLE_03 | 1 | 515 | 0 (0)| |
    |* 11 | INDEX RANGE SCAN | SYS3327181_3327189_PKY_IDX | 1 | | 0 (0)| |
    | 12 | TABLE ACCESS BY USER ROWID | FACE_MASKS | 1 | 12 | 1 (0)| 00:00:01 |
    ————————————————————————————————————-

    Query Block Name / Object Alias (identified by operation id):
    ————————————————————-

    1 – SEL$5BAF7FBA
    4 – SEL$40C3ED9E / SYS_SXI_3@SEL$E20BB5D8
    5 – SEL$40C3ED9E
    6 – SEL$40C3ED9E / indexjoin$_alias$_001@SEL$40C3ED9E
    7 – SEL$40C3ED9E / indexjoin$_alias$_002@SEL$40C3ED9E
    8 – SEL$5BAF7FBA / SYS_SXI_4@SEL$099778A2
    9 – SEL$5BAF7FBA / SYS_SXI_4@SEL$099778A2
    10 – SEL$5BAF7FBA / SYS_SXI_5@SEL$73B70E59
    11 – SEL$5BAF7FBA / SYS_SXI_5@SEL$73B70E59
    12 – SEL$5BAF7FBA / FM@SEL$1

    Outline Data
    ————-

    /*+
    BEGIN_OUTLINE_DATA
    IGNORE_OPTIM_EMBEDDED_HINTS
    OPTIMIZER_FEATURES_ENABLE(‘11.2.0.3′)
    DB_VERSION(‘11.2.0.3′)
    ALL_ROWS
    FORCE_XML_QUERY_REWRITE
    XML_DML_RWT_STMT
    XMLINDEX_REWRITE
    XMLINDEX_REWRITE_IN_SELECT
    NO_COST_XML_QUERY_REWRITE
    OUTLINE_LEAF(@”SEL$40C3ED9E”)
    OUTLINE_LEAF(@”SEL$5BAF7FBA”)
    MERGE(@”SEL$099778A2″)
    MERGE(@”SEL$73B70E59″)
    MERGE(@”SEL$E20BB5D8″)
    OUTLINE(@”SEL$1″)
    OUTLINE(@”SEL$099778A2″)
    OUTLINE(@”SEL$73B70E59″)
    OUTLINE(@”SEL$E20BB5D8″)
    INDEX_JOIN(@”SEL$5BAF7FBA” “SYS_SXI_3″@”SEL$E20BB5D8″ (“FM_CONTENT_TABLE_01″.”RID”)
    (“FM_CONTENT_TABLE_01″.”KEY”))
    INDEX_RS_ASC(@”SEL$5BAF7FBA” “SYS_SXI_4″@”SEL$099778A2″ (“FM_CONTENT_TABLE_02″.”PKEY”))
    INDEX_RS_ASC(@”SEL$5BAF7FBA” “SYS_SXI_5″@”SEL$73B70E59″ (“FM_CONTENT_TABLE_03″.”PKEY”))
    ROWID(@”SEL$5BAF7FBA” “FM”@”SEL$1″)
    LEADING(@”SEL$5BAF7FBA” “SYS_SXI_3″@”SEL$E20BB5D8″ “SYS_SXI_4″@”SEL$099778A2″
    “SYS_SXI_5″@”SEL$73B70E59″ “FM”@”SEL$1″)
    USE_NL(@”SEL$5BAF7FBA” “SYS_SXI_4″@”SEL$099778A2″)
    USE_NL(@”SEL$5BAF7FBA” “SYS_SXI_5″@”SEL$73B70E59″)
    USE_NL(@”SEL$5BAF7FBA” “FM”@”SEL$1″)
    END_OUTLINE_DATA
    */

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

    5 – access(ROWID=ROWID)
    9 – access(“SYS_SXI_3″.”KEY”=”SYS_SXI_4″.”PKEY”)
    11 – access(“SYS_SXI_4″.”KEY”=”SYS_SXI_5″.”PKEY”)

    Column Projection Information (identified by operation id):
    ———————————————————–

    1 – “SYS_SXI_5″.”FACETYPEID”[NUMBER,22]
    2 – “SYS_SXI_3″.”RID”[ROWID,10], “SYS_SXI_5″.”FACETYPEID”[NUMBER,22]
    3 – “SYS_SXI_3″.”RID”[ROWID,10], “SYS_SXI_4″.”KEY”[RAW,1000]
    4 – “SYS_SXI_3″.”KEY”[RAW,1000], “SYS_SXI_3″.”RID”[ROWID,10]
    5 – (#keys=1) “SYS_SXI_3″.”RID”[ROWID,10], “SYS_SXI_3″.”KEY”[RAW,1000]
    6 – ROWID[ROWID,10], “SYS_SXI_3″.”RID”[ROWID,10]
    7 – ROWID[ROWID,10], “SYS_SXI_3″.”KEY”[RAW,1000]
    8 – “SYS_SXI_4″.”KEY”[RAW,1000]
    9 – “SYS_SXI_4″.ROWID[ROWID,10]
    10 – “SYS_SXI_5″.”FACETYPEID”[NUMBER,22]
    11 – “SYS_SXI_5″.ROWID[ROWID,10]

    Note
    —–
    – dynamic sampling used for this statement (level=2)

    Please suggest what’s missing here.

    Thanks,
    Sid

  2. Sid
    12/1/2013

    Hi Marco,

    Sorry but the XMLdidn’t get posted in the previous message.
    It’s something like:

    –Truncate table face_masks

    INSERT INTO FACE_MASKS(CSXML_DOC) VALUES(XMLTYPE(‘

    223
    001007
    002
    003
    224
    005
    006
    002

    223
    001007
    002
    003
    224
    005
    006
    002

    ‘));

  3. 12/2/2013

    Try creating secondary indexes on the specific content table(s) columns and see if the optimizer will pick these up regarding being more “cost” effective.

Comments are closed.