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