Forum Stats

  • 3,875,456 Users
  • 2,266,916 Discussions
  • 7,912,219 Comments

Discussions

Function based index using SYS_XQSUBSTRAFT

pl_sequel
pl_sequel Member Posts: 195
edited Nov 15, 2013 9:47AM in XQuery

running on 11.2.0.2

Wondering if this could be a recommended approach to use SYS_XQSUBSTRAFT in an FBI to satisfy an xmlquery using the substring-after function on a column in an xmlquery? I did test it out, and the optimizer is using the index, however not sure if i'll run into problems in later releases of Oracle, if/when xmlparser and rewrite engine is updated?

I am selecting from relational tables using fn:collection, (along with xmltype binary xml column), and need to instr one of the relational columns in the xmlquery context... running an explain plan on my query revealed that substring-after was rewritten to call SYS_XQSUBSTRAFT function.

Thanks for the advice

Best Answer

  • odie_63
    odie_63 Member Posts: 8,493 Silver Trophy
    edited Nov 14, 2013 5:48PM Answer ✓
    Wondering if this could be a recommended approach to use SYS_XQSUBSTRAFT in an FBI to satisfy an xmlquery using the substring-after function on a column in an xmlquery?
    
    
    

    Well, at first I would have said it's a bad idea, as Oracle has always put stress on not relying on the underlying implementation.

    However, it's OK to use XMLQuery in a FBI, like this :

    SQL> create table sample_data ( str varchar2(7) );
    
    Table created.
    
    SQL> begin
      2
      3  insert into sample_data values ('ABC-123');
      4  insert into sample_data values ('XYZ-123');
      5  insert into sample_data values ('ABC-456');
      6  insert into sample_data values ('ABC-789');
      7
      8  end;
      9  /
    
    PL/SQL procedure successfully completed.
    
    SQL> commit;
    
    Commit complete.
    
    SQL> create index sample_data_ix1 on sample_data (
      2    xmlcast(
      3      xmlquery('substring-after($str, "-")' passing str as "str" returning content)
      4      as varchar2(4000)
      5    )
      6  );
    
    Index created.
    
    SQL>
    SQL>
    SQL> set lines 200
    SQL> set pages 100
    SQL> set autotrace on explain
    SQL> select xmlquery(
      2         'for $i in fn:collection("oradb:/DEV/SAMPLE_DATA")/ROW
      3          where substring-after($i/STR, "-") = "123"
      4          return $i'
      5         returning content
      6         ) as result
      7  from dual ;
    
    RESULT
    ------------------------------------------------------------------------------------------------
    <ROW><STR>ABC-123</STR></ROW><ROW><STR>XYZ-123</STR></ROW>
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2484218877
    
    ------------------------------------------------------------------------------------------------
    | Id  | Operation                    | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
    ------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |                 |     1 |       |     2   (0)| 00:00:01 |
    |   1 |  SORT AGGREGATE              |                 |     1 |  2007 |            |          |
    |   2 |   TABLE ACCESS BY INDEX ROWID| SAMPLE_DATA     |     1 |  2007 |     2   (0)| 00:00:01 |
    |*  3 |    INDEX RANGE SCAN          | SAMPLE_DATA_IX1 |     1 |       |     1   (0)| 00:00:01 |
    |   4 |  FAST DUAL                   |                 |     1 |       |     2   (0)| 00:00:01 |
    ------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       3 - access(SYS_XQSUBSTRAFT("STR",'-')='123')
    
    Note
    -----
       - dynamic sampling used for this statement (level=2)
    
    
    

    And the funny thing is that Oracle has actually already rewritten the function in the index :

    SQL> select dbms_metadata.get_ddl('INDEX','SAMPLE_DATA_IX1') from dual;
    
    DBMS_METADATA.GET_DDL('INDEX','SAMPLE_DATA_IX1')
    --------------------------------------------------------------------------------
    
      CREATE INDEX "DEV"."SAMPLE_DATA_IX1" ON "DEV"."SAMPLE_DATA" (SYS_XQSUBSTRAFT("
    STR",'-'))
      PCTFREE 10 INITRANS 2 MAXTRANS 167 COMPUTE STATISTICS
      STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
    FAULT CELL_FLASH_CACHE DEFAULT)
      TABLESPACE "USERS"
    
    

    so I guess your approach is OK, in principle.

    Now I'd probably question the fact of creating an XML-related index on a pure relational table.

    Have you thought of using SQL/XML functions XMLElement, XMLAgg etc. instead of fn:collection() and pass it to your main XQuery ?

Answers

  • odie_63
    odie_63 Member Posts: 8,493 Silver Trophy
    edited Nov 14, 2013 5:48PM Answer ✓
    Wondering if this could be a recommended approach to use SYS_XQSUBSTRAFT in an FBI to satisfy an xmlquery using the substring-after function on a column in an xmlquery?
    
    
    

    Well, at first I would have said it's a bad idea, as Oracle has always put stress on not relying on the underlying implementation.

    However, it's OK to use XMLQuery in a FBI, like this :

    SQL> create table sample_data ( str varchar2(7) );
    
    Table created.
    
    SQL> begin
      2
      3  insert into sample_data values ('ABC-123');
      4  insert into sample_data values ('XYZ-123');
      5  insert into sample_data values ('ABC-456');
      6  insert into sample_data values ('ABC-789');
      7
      8  end;
      9  /
    
    PL/SQL procedure successfully completed.
    
    SQL> commit;
    
    Commit complete.
    
    SQL> create index sample_data_ix1 on sample_data (
      2    xmlcast(
      3      xmlquery('substring-after($str, "-")' passing str as "str" returning content)
      4      as varchar2(4000)
      5    )
      6  );
    
    Index created.
    
    SQL>
    SQL>
    SQL> set lines 200
    SQL> set pages 100
    SQL> set autotrace on explain
    SQL> select xmlquery(
      2         'for $i in fn:collection("oradb:/DEV/SAMPLE_DATA")/ROW
      3          where substring-after($i/STR, "-") = "123"
      4          return $i'
      5         returning content
      6         ) as result
      7  from dual ;
    
    RESULT
    ------------------------------------------------------------------------------------------------
    <ROW><STR>ABC-123</STR></ROW><ROW><STR>XYZ-123</STR></ROW>
    
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 2484218877
    
    ------------------------------------------------------------------------------------------------
    | Id  | Operation                    | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
    ------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT             |                 |     1 |       |     2   (0)| 00:00:01 |
    |   1 |  SORT AGGREGATE              |                 |     1 |  2007 |            |          |
    |   2 |   TABLE ACCESS BY INDEX ROWID| SAMPLE_DATA     |     1 |  2007 |     2   (0)| 00:00:01 |
    |*  3 |    INDEX RANGE SCAN          | SAMPLE_DATA_IX1 |     1 |       |     1   (0)| 00:00:01 |
    |   4 |  FAST DUAL                   |                 |     1 |       |     2   (0)| 00:00:01 |
    ------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       3 - access(SYS_XQSUBSTRAFT("STR",'-')='123')
    
    Note
    -----
       - dynamic sampling used for this statement (level=2)
    
    
    

    And the funny thing is that Oracle has actually already rewritten the function in the index :

    SQL> select dbms_metadata.get_ddl('INDEX','SAMPLE_DATA_IX1') from dual;
    
    DBMS_METADATA.GET_DDL('INDEX','SAMPLE_DATA_IX1')
    --------------------------------------------------------------------------------
    
      CREATE INDEX "DEV"."SAMPLE_DATA_IX1" ON "DEV"."SAMPLE_DATA" (SYS_XQSUBSTRAFT("
    STR",'-'))
      PCTFREE 10 INITRANS 2 MAXTRANS 167 COMPUTE STATISTICS
      STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE
    FAULT CELL_FLASH_CACHE DEFAULT)
      TABLESPACE "USERS"
    
    

    so I guess your approach is OK, in principle.

    Now I'd probably question the fact of creating an XML-related index on a pure relational table.

    Have you thought of using SQL/XML functions XMLElement, XMLAgg etc. instead of fn:collection() and pass it to your main XQuery ?

  • pl_sequel
    pl_sequel Member Posts: 195

    Thanks Odie! I like that solution better...didn't feel right to rely on underlying implementation as you pointed out.

    As for my chosen implementation... the table is relational, but I do have an XMLTYPE column which i have to drill down into and search for particular xml values in my query predicate and return back some transformed XML... in some other cases i have used xmltable, with SQL/XML...and saw a dramatic increase in performance over a pure xmlquery using fn:collection....

    Not sure i follow you when you say "...and pass it to your main XQuery.." 

  • pl_sequel
    pl_sequel Member Posts: 195
    edited Nov 15, 2013 9:51AM

    Hmm... for some reason, my query isn't using my FBI.... but your sample works, FBI is used... have to look into that one a little further. :-)

    Interesting...i had modified the FBI to use varchar2(1000)... changed it to use varchar2(4000)..and now my FBI is being used.

This discussion has been closed.