Oracle XMLIndex - Making a Range Search

Question

I'm storing my xml data in Oracle XMLType column, and I want to be able to make a range search over a number inside xml's.

I suppose Oracle supports this kind of operation, but googling didn't help me. How should I create the XMLIndex and the range search query?

Example table:

CREATE TABLE XMLDBTEST.XML_TABLE
(
  CONTENT  SYS.XMLTYPE
)
XMLTYPE CONTENT STORE AS BINARY XML

Example data:

<person>
    <name>feyyaz</name>
    <age>28</age>
</person>

<person>
    <name>ahmet</name>
    <age>26</age>
</person>

<person>
    <name>mehmet</name>
    <age>20</age>
</person>

score 1 · Accepted Answer · answered Jun 16 '14 at 18:34

Create table and sample data

CREATE TABLE XML_TABLE
(
  CONTENT  SYS.XMLTYPE
)
XMLTYPE CONTENT STORE AS BINARY XML;

insert into xml_table values(xmltype('
<persons>
    <person>
        <name>feyyaz</name>
        <age>28</age>
    </person>
    <person>
        <name>ahmet</name>
        <age>26</age>
    </person>
    <person>
        <name>mehmet</name>
        <age>20</age>
    </person>
</persons>'
));

commit;

Create XMLIndex

create index xml_table_ix on xml_table(content)
indextype is xdb.xmlindex
parameters ('PATHS (INCLUDE(/persons/person/age))');

Query age

select name, age
from xml_table
cross join
xmltable
(
    '/persons/person'
    passing xml_table.content
    columns
        name varchar2(100) path 'name',
        age  number        path 'age'
)
where age = 20;

NAME    AGE
----    ---
mehmet   20

Explain plan showing index access

explain plan for
select name, age
from xml_table
cross join
xmltable
(
    '/persons/person'
    passing xml_table.content
    columns
        name varchar2(100) path 'name',
        age  number        path 'age'
)
where age = 20;

select * from table(dbms_xplan.display);

Plan hash value: 2259392803

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                                |     1 |  3536 |     6   (0)| 00:00:01 |
|*  1 |  FILTER                       |                                |       |       |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID | SYS2970790_XML_TABL_PATH_TABLE |     1 |  3524 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN           | SYS2970790_XML_TABL_PIKEY_IX   |     1 |       |     1   (0)| 00:00:01 |
|*  4 |  FILTER                       |                                |       |       |            |          |
|   5 |   NESTED LOOPS                |                                |     1 |  3536 |     4   (0)| 00:00:01 |
|*  6 |    TABLE ACCESS FULL          | SYS2970790_XML_TABL_PATH_TABLE |     1 |  3524 |     3   (0)| 00:00:01 |
|   7 |    TABLE ACCESS BY USER ROWID | XML_TABLE                      |     1 |    12 |     1   (0)| 00:00:01 |
|*  8 |   FILTER                      |                                |       |       |            |          |
|*  9 |    TABLE ACCESS BY INDEX ROWID| SYS2970790_XML_TABL_PATH_TABLE |     1 |  3524 |     2   (0)| 00:00:01 |
|* 10 |     INDEX RANGE SCAN          | SYS2970790_XML_TABL_PIKEY_IX   |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------

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

   1 - filter(:B1<SYS_ORDERKEY_MAXCHILD(:B2))
   2 - filter(SYS_XMLI_LOC_ISNODE("SYS_P3"."LOCATOR")=1)
   3 - access("SYS_P3"."RID"=:B1 AND "SYS_P3"."PATHID"=HEXTORAW('38DE')  AND "SYS_P3"."ORDER_KEY">:B2 
              AND "SYS_P3"."ORDER_KEY"<SYS_ORDERKEY_MAXCHILD(:B3))
       filter(SYS_ORDERKEY_DEPTH("SYS_P3"."ORDER_KEY")=SYS_ORDERKEY_DEPTH(:B1)+1)
   4 - filter(CAST( (SELECT "SYS_P6"."VALUE" FROM "JHELLER_DBA"."SYS2970790_XML_TABL_PATH_TABLE" 
              "SYS_P6" WHERE :B1<SYS_ORDERKEY_MAXCHILD(:B2) AND "SYS_P6"."ORDER_KEY"<SYS_ORDERKEY_MAXCHILD(:B3) AND 
              "SYS_P6"."ORDER_KEY">:B4 AND "SYS_P6"."PATHID"=HEXTORAW('38DE')  AND "SYS_P6"."RID"=:B5 AND 
              SYS_XMLI_LOC_ISNODE("SYS_P6"."LOCATOR")=1 AND SYS_ORDERKEY_DEPTH("SYS_P6"."ORDER_KEY")=SYS_ORDERKEY_DEPT
              H(:B6)+1) AS number        )=20)
   6 - filter("SYS_P1"."PATHID"=HEXTORAW('2BA3')  AND SYS_XMLI_LOC_ISNODE("SYS_P1"."LOCATOR")=1)
   8 - filter(:B1<SYS_ORDERKEY_MAXCHILD(:B2))
   9 - filter(SYS_XMLI_LOC_ISNODE("SYS_P6"."LOCATOR")=1)
  10 - access("SYS_P6"."RID"=:B1 AND "SYS_P6"."PATHID"=HEXTORAW('38DE')  AND "SYS_P6"."ORDER_KEY">:B2 
              AND "SYS_P6"."ORDER_KEY"<SYS_ORDERKEY_MAXCHILD(:B3))
       filter(SYS_ORDERKEY_DEPTH("SYS_P6"."ORDER_KEY")=SYS_ORDERKEY_DEPTH(:B1)+1)

Note
-----
   - dynamic sampling used for this statement (level=2)
   - automatic DOP: skipped because of IO calibrate statistics are missing

Oracle XMLIndex - Making a Range Search

1 Answers1