drop table t_xpath_index_testcreate table t_xpath_index_test (idcol number(18), xmlcol xmltype)insert into t_xpath_index_test (idcol, xmlcol)
SELECT ROWNUM
,XMLTYPE('
<root>
<employees>
<employee>
<name>' || DBMS_RANDOM.STRING('A', 10) || '</name>
</employee>
<employee>
<name>' || DBMS_RANDOM.STRING('A', 10) || '</name>
</employee>
<employee>
<name>' || DBMS_RANDOM.STRING('A', 10) || '</name>
</employee>
<employee>
<name>' || DBMS_RANDOM.STRING('A', 10) || '</name>
</employee>
</employees>
</root>')
FROM DUAL
CONNECT BY LEVEL <= 10000
UNION ALL
SELECT 10001
,XMLTYPE('
<root>
<employees>
<employee>
<name>Steven</name>
</employee>
<employee>
<name>Connor</name>
</employee>
<employee>
<name>Maria</name>
</employee>
<employee>
<name>Chris</name>
</employee>
</employees>
</root>')
FROM DUALexplain plan for
select idcol
from T_XPATH_INDEX_TEST
where xmlexists ('/root/employees/employee/name[text()="Chris"]' passing xmlcol)select * from table(dbms_xplan.display())| PLAN_TABLE_OUTPUT | Plan hash value: 814064386 | ------------------------------------------------------------------------------------------ | | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | ------------------------------------------------------------------------------------------ | | 0 | SELECT STATEMENT | | 32 | 64480 | 43353 (1)| 00:00:02 | | |* 1 | FILTER | | | | | | | | 2 | TABLE ACCESS FULL | T_XPATH_INDEX_TEST | 9659 | 18M| 80 (0)| 00:00:01 | | | 3 | NESTED LOOPS SEMI | | 2 | 8 | 5 (0)| 00:00:01 | | | 4 | XPATH EVALUATION | | | | | | | |* 5 | XPATH EVALUATION | | | | | | | ------------------------------------------------------------------------------------------ | Predicate Information (identified by operation id): | --------------------------------------------------- | 1 - filter( EXISTS (SELECT /*+ <not feasible>) | 5 - filter("P1"."C_01$"='Chris') | Note | ----- | - dynamic statistics used: dynamic sampling (level=2) |
|---|
create index idx_xpath_emp_name on T_XPATH_INDEX_TEST (xmlcol) INDEXTYPE IS XDB.XMLIndex
parameters ('PATHS(INCLUDE(/root/employees/employee/name))')explain plan for
select idcol
from T_XPATH_INDEX_TEST
where xmlexists ('/root/employees/employee/name[text()="Chris"]' passing xmlcol)select * from table(dbms_xplan.display())| PLAN_TABLE_OUTPUT | Plan hash value: 814064386 | ------------------------------------------------------------------------------------------ | | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | ------------------------------------------------------------------------------------------ | | 0 | SELECT STATEMENT | | 32 | 64480 | 43353 (1)| 00:00:02 | | |* 1 | FILTER | | | | | | | | 2 | TABLE ACCESS FULL | T_XPATH_INDEX_TEST | 9659 | 18M| 80 (0)| 00:00:01 | | | 3 | NESTED LOOPS SEMI | | 2 | 8 | 5 (0)| 00:00:01 | | | 4 | XPATH EVALUATION | | | | | | | |* 5 | XPATH EVALUATION | | | | | | | ------------------------------------------------------------------------------------------ | Predicate Information (identified by operation id): | --------------------------------------------------- | 1 - filter( EXISTS (SELECT /*+ <not feasible>) | 5 - filter("P1"."C_01$"='Chris') | Note | ----- | - dynamic statistics used: dynamic sampling (level=2) |
|---|
explain plan for
select idcol
from T_XPATH_INDEX_TEST
where xmlexists ('/root/employees/employee/name[upper-case(text())="CHRIS"]' passing xmlcol)select * from table(dbms_xplan.display())| PLAN_TABLE_OUTPUT | Plan hash value: 4270174327 | ----------------------------------------------------------------------------------------- | | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | ----------------------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | 788K| 1517M| 310K (1)| 00:00:13 | | | 1 | NESTED LOOPS SEMI | | 788K| 1517M| 310K (1)| 00:00:13 | | | 2 | TABLE ACCESS FULL| T_XPATH_INDEX_TEST | 9659 | 18M| 80 (0)| 00:00:01 | | |* 3 | XPATH EVALUATION | | | | | | | ----------------------------------------------------------------------------------------- | Predicate Information (identified by operation id): | --------------------------------------------------- | 3 - filter(UPPER("P"."C_01$")='CHRIS') | Note | ----- | - dynamic statistics used: dynamic sampling (level=2) |
|---|
drop table t_xpath_index_testcreate table t_xpath_index_test (idcol number(18), xmlcol xmltype)insert into t_xpath_index_test (idcol, xmlcol)
SELECT ROWNUM
,XMLTYPE('
<root>
<employees>
<employee>
<name>' || DBMS_RANDOM.STRING('A', 10) || '</name>
</employee>
<employee>
<name>' || DBMS_RANDOM.STRING('A', 10) || '</name>
</employee>
<employee>
<name>' || DBMS_RANDOM.STRING('A', 10) || '</name>
</employee>
<employee>
<name>' || DBMS_RANDOM.STRING('A', 10) || '</name>
</employee>
</employees>
</root>')
FROM DUAL
CONNECT BY LEVEL <= 10000
UNION ALL
SELECT 10001
,XMLTYPE('
<root>
<employees>
<employee>
<name>Steven</name>
</employee>
<employee>
<name>Connor</name>
</employee>
<employee>
<name>Maria</name>
</employee>
<employee>
<name>Chris</name>
</employee>
</employees>
</root>')
FROM DUALexplain plan for
select idcol
from T_XPATH_INDEX_TEST
where xmlexists ('/root/employees/employee/name[text()="Chris"]' passing xmlcol)select * from table(dbms_xplan.display())| PLAN_TABLE_OUTPUT | Plan hash value: 814064386 | ------------------------------------------------------------------------------------------ | | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | ------------------------------------------------------------------------------------------ | | 0 | SELECT STATEMENT | | 32 | 64480 | 37731 (1)| 00:00:02 | | |* 1 | FILTER | | | | | | | | 2 | TABLE ACCESS FULL | T_XPATH_INDEX_TEST | 8536 | 16M| 80 (0)| 00:00:01 | | | 3 | NESTED LOOPS SEMI | | 2 | 8 | 5 (0)| 00:00:01 | | | 4 | XPATH EVALUATION | | | | | | | |* 5 | XPATH EVALUATION | | | | | | | ------------------------------------------------------------------------------------------ | Predicate Information (identified by operation id): | --------------------------------------------------- | 1 - filter( EXISTS (SELECT /*+ <not feasible>) | 5 - filter("P1"."C_01$"='Chris') | Note | ----- | - dynamic statistics used: dynamic sampling (level=2) |
|---|
create index idx_xpath_emp_name on T_XPATH_INDEX_TEST (xmlcol) INDEXTYPE IS XDB.XMLIndex
parameters ('PATHS(INCLUDE(/root/employees/employee/name))')explain plan for
select idcol
from T_XPATH_INDEX_TEST
where xmlexists ('/root/employees/employee/name[text()="Chris"]' passing xmlcol)select * from table(dbms_xplan.display())| PLAN_TABLE_OUTPUT | Plan hash value: 2573348203 | -------------------------------------------------------------------------------------------------------------------------- | | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | -------------------------------------------------------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | 1 | 37 | 212 (1)| 00:00:01 | | | 1 | NESTED LOOPS | | 1 | 37 | 212 (1)| 00:00:01 | | | 2 | VIEW | VW_SQ_1 | 1 | 12 | 210 (1)| 00:00:01 | | | 3 | HASH UNIQUE | | 1 | 3524 | | | | |* 4 | TABLE ACCESS FULL | SYS123329685_IDX_XPATH_EMP_NAME_PATH_TABLE | 1 | 3524 | 210 (1)| 00:00:01 | | | 5 | TABLE ACCESS BY USER ROWID| T_XPATH_INDEX_TEST | 1 | 25 | 1 (0)| 00:00:01 | | -------------------------------------------------------------------------------------------------------------------------- | Predicate Information (identified by operation id): | --------------------------------------------------- | 4 - filter("SYS_P4"."VALUE"='Chris' AND "SYS_P4"."PATHID"=HEXTORAW('1095') AND | SYS_XMLI_LOC_ISTEXT("SYS_P4"."LOCATOR","SYS_P4"."PATHID")=1) | Note | ----- | - dynamic statistics used: dynamic sampling (level=2) |
|---|
explain plan for
select idcol
from T_XPATH_INDEX_TEST
where xmlexists ('/root/employees/employee/name[upper-case(text())="CHRIS"]' passing xmlcol)select * from table(dbms_xplan.display())| PLAN_TABLE_OUTPUT | Plan hash value: 2926214754 | -------------------------------------------------------------------------------------------------------------------------- | | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | -------------------------------------------------------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | 1 | 37 | 187K (1)| 00:00:08 | | | 1 | NESTED LOOPS | | 1 | 37 | 187K (1)| 00:00:08 | | | 2 | VIEW | VW_SQ_1 | 918 | 11016 | 187K (1)| 00:00:08 | | | 3 | HASH UNIQUE | | 1 | 1364K| | | | |* 4 | FILTER | | | | | | | |* 5 | TABLE ACCESS FULL | SYS123329685_IDX_XPATH_EMP_NAME_PATH_TABLE | 918 | 1364K| 210 (1)| 00:00:01 | | |* 6 | FILTER | | | | | | | |* 7 | TABLE ACCESS FULL | SYS123329685_IDX_XPATH_EMP_NAME_PATH_TABLE | 1 | 3524 | 210 (1)| 00:00:01 | | | 8 | TABLE ACCESS BY USER ROWID| T_XPATH_INDEX_TEST | 1 | 25 | 1 (0)| 00:00:01 | | -------------------------------------------------------------------------------------------------------------------------- | Predicate Information (identified by operation id): | --------------------------------------------------- | 4 - filter(UPPER( (SELECT "SYS_P3"."VALUE" FROM "SQL_FZMDMEOLSVGMFLOMQGUUMUPDI"."SYS123329685_IDX_XPATH_EMP_NAM | E_PATH_TABLE" "SYS_P3" WHERE SYS_ORDERKEY_MAXCHILD(:B1)>:B2 AND "SYS_P3"."RID"=:B3 AND | "SYS_P3"."PATHID"=HEXTORAW('1095') AND "SYS_P3"."ORDER_KEY">=:B4 AND | "SYS_P3"."ORDER_KEY"<SYS_ORDERKEY_MAXCHILD(:B5) AND SYS_XMLI_LOC_ISTEXT("SYS_P3"."LOCATOR","SYS_P3"."PATHID")=1))= | 'CHRIS') | 5 - filter("SYS_P1"."PATHID"=HEXTORAW('1095') AND SYS_XMLI_LOC_ISNODE("SYS_P1"."LOCATOR")=1) | 6 - filter(SYS_ORDERKEY_MAXCHILD(:B1)>:B2) | 7 - filter("SYS_P3"."RID"=:B1 AND "SYS_P3"."PATHID"=HEXTORAW('1095') AND "SYS_P3"."ORDER_KEY">=:B2 AND | "SYS_P3"."ORDER_KEY"<SYS_ORDERKEY_MAXCHILD(:B3) AND SYS_XMLI_LOC_ISTEXT("SYS_P3"."LOCATOR","SYS_P3"."PATHID")=1) | Note | ----- | - dynamic statistics used: dynamic sampling (level=2) |
|---|
drop table t_xpath_index_testcreate table t_xpath_index_test (idcol number(18), xmlcol xmltype)insert into t_xpath_index_test (idcol, xmlcol)
SELECT ROWNUM
,XMLTYPE('
<root>
<employees>
<employee>
<name>' || DBMS_RANDOM.STRING('A', 10) || '</name>
</employee>
<employee>
<name>' || DBMS_RANDOM.STRING('A', 10) || '</name>
</employee>
<employee>
<name>' || DBMS_RANDOM.STRING('A', 10) || '</name>
</employee>
<employee>
<name>' || DBMS_RANDOM.STRING('A', 10) || '</name>
</employee>
</employees>
</root>')
FROM DUAL
CONNECT BY LEVEL <= 10000
UNION ALL
SELECT 10001
,XMLTYPE('
<root>
<employees>
<employee>
<name>Steven</name>
</employee>
<employee>
<name>Connor</name>
</employee>
<employee>
<name>Maria</name>
</employee>
<employee>
<name>Chris</name>
</employee>
</employees>
</root>')
FROM DUALexplain plan for
select idcol
from T_XPATH_INDEX_TEST
where xmlexists ('/root/employees/employee/name[text()="Chris"]' passing xmlcol)select * from table(dbms_xplan.display())| PLAN_TABLE_OUTPUT | Plan hash value: 814064386 | ------------------------------------------------------------------------------------------ | | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | ------------------------------------------------------------------------------------------ | | 0 | SELECT STATEMENT | | 32 | 64480 | 44943 (1)| 00:00:02 | | |* 1 | FILTER | | | | | | | | 2 | TABLE ACCESS FULL | T_XPATH_INDEX_TEST | 9977 | 19M| 80 (0)| 00:00:01 | | | 3 | NESTED LOOPS SEMI | | 2 | 8 | 5 (0)| 00:00:01 | | | 4 | XPATH EVALUATION | | | | | | | |* 5 | XPATH EVALUATION | | | | | | | ------------------------------------------------------------------------------------------ | Predicate Information (identified by operation id): | --------------------------------------------------- | 1 - filter( EXISTS (SELECT /*+ <not feasible>) | 5 - filter("P1"."C_01$"='Chris') | Note | ----- | - dynamic statistics used: dynamic sampling (level=2) |
|---|
create index idx_xpath_emp_name on T_XPATH_INDEX_TEST (xmlcol) INDEXTYPE IS XDB.XMLIndexexplain plan for
select idcol
from T_XPATH_INDEX_TEST
where xmlexists ('/root/employees/employee/name[text()="Chris"]' passing xmlcol)select * from table(dbms_xplan.display())| PLAN_TABLE_OUTPUT | Plan hash value: 814064386 | ------------------------------------------------------------------------------------------ | | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | ------------------------------------------------------------------------------------------ | | 0 | SELECT STATEMENT | | 32 | 64480 | 44943 (1)| 00:00:02 | | |* 1 | FILTER | | | | | | | | 2 | TABLE ACCESS FULL | T_XPATH_INDEX_TEST | 9977 | 19M| 80 (0)| 00:00:01 | | | 3 | NESTED LOOPS SEMI | | 2 | 8 | 5 (0)| 00:00:01 | | | 4 | XPATH EVALUATION | | | | | | | |* 5 | XPATH EVALUATION | | | | | | | ------------------------------------------------------------------------------------------ | Predicate Information (identified by operation id): | --------------------------------------------------- | 1 - filter( EXISTS (SELECT /*+ <not feasible>) | 5 - filter("P1"."C_01$"='Chris') | Note | ----- | - dynamic statistics used: dynamic sampling (level=2) |
|---|
explain plan for
select idcol
from T_XPATH_INDEX_TEST
where xmlexists ('/root/employees/employee/name[upper-case(text())="CHRIS"]' passing xmlcol)select * from table(dbms_xplan.display())| PLAN_TABLE_OUTPUT | Plan hash value: 4270174327 | ----------------------------------------------------------------------------------------- | | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | ----------------------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | 814K| 1567M| 321K (1)| 00:00:13 | | | 1 | NESTED LOOPS SEMI | | 814K| 1567M| 321K (1)| 00:00:13 | | | 2 | TABLE ACCESS FULL| T_XPATH_INDEX_TEST | 9977 | 19M| 80 (0)| 00:00:01 | | |* 3 | XPATH EVALUATION | | | | | | | ----------------------------------------------------------------------------------------- | Predicate Information (identified by operation id): | --------------------------------------------------- | 3 - filter(UPPER("P"."C_01$")='CHRIS') | Note | ----- | - dynamic statistics used: dynamic sampling (level=2) |
|---|
drop table t_xpath_index_testcreate table t_xpath_index_test (idcol number(18), xmlcol xmltype)insert into t_xpath_index_test (idcol, xmlcol)
SELECT ROWNUM
,XMLTYPE('
<root>
<employees>
<employee>
<name>' || DBMS_RANDOM.STRING('A', 10) || '</name>
</employee>
<employee>
<name>' || DBMS_RANDOM.STRING('A', 10) || '</name>
</employee>
<employee>
<name>' || DBMS_RANDOM.STRING('A', 10) || '</name>
</employee>
<employee>
<name>' || DBMS_RANDOM.STRING('A', 10) || '</name>
</employee>
</employees>
</root>')
FROM DUAL
CONNECT BY LEVEL <= 10000
UNION ALL
SELECT 10001
,XMLTYPE('
<root>
<employees>
<employee>
<name>Steven</name>
</employee>
<employee>
<name>Connor</name>
</employee>
<employee>
<name>Maria</name>
</employee>
<employee>
<name>Chris</name>
</employee>
</employees>
</root>')
FROM DUALexplain plan for
select idcol
from T_XPATH_INDEX_TEST
where xmlexists ('/root/employees/employee/name[text()="Chris"]' passing xmlcol)select * from table(dbms_xplan.display())| PLAN_TABLE_OUTPUT | Plan hash value: 814064386 | ------------------------------------------------------------------------------------------ | | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | ------------------------------------------------------------------------------------------ | | 0 | SELECT STATEMENT | | 32 | 64480 | 37072 (1)| 00:00:02 | | |* 1 | FILTER | | | | | | | | 2 | TABLE ACCESS FULL | T_XPATH_INDEX_TEST | 8404 | 16M| 80 (0)| 00:00:01 | | | 3 | NESTED LOOPS SEMI | | 2 | 8 | 5 (0)| 00:00:01 | | | 4 | XPATH EVALUATION | | | | | | | |* 5 | XPATH EVALUATION | | | | | | | ------------------------------------------------------------------------------------------ | Predicate Information (identified by operation id): | --------------------------------------------------- | 1 - filter( EXISTS (SELECT /*+ <not feasible>) | 5 - filter("P1"."C_01$"='Chris') | Note | ----- | - dynamic statistics used: dynamic sampling (level=2) |
|---|
create index idx_xpath_emp_name on T_XPATH_INDEX_TEST (xmlcol) INDEXTYPE IS XDB.XMLIndexexplain plan for
select idcol
from T_XPATH_INDEX_TEST
where xmlexists ('/root/employees/employee/name[text()="Chris"]' passing xmlcol)select * from table(dbms_xplan.display())| PLAN_TABLE_OUTPUT | Plan hash value: 814064386 | ------------------------------------------------------------------------------------------ | | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | ------------------------------------------------------------------------------------------ | | 0 | SELECT STATEMENT | | 32 | 64480 | 37072 (1)| 00:00:02 | | |* 1 | FILTER | | | | | | | | 2 | TABLE ACCESS FULL | T_XPATH_INDEX_TEST | 8404 | 16M| 80 (0)| 00:00:01 | | | 3 | NESTED LOOPS SEMI | | 2 | 8 | 5 (0)| 00:00:01 | | | 4 | XPATH EVALUATION | | | | | | | |* 5 | XPATH EVALUATION | | | | | | | ------------------------------------------------------------------------------------------ | Predicate Information (identified by operation id): | --------------------------------------------------- | 1 - filter( EXISTS (SELECT /*+ <not feasible>) | 5 - filter("P1"."C_01$"='Chris') | Note | ----- | - dynamic statistics used: dynamic sampling (level=2) |
|---|
explain plan for
select idcol
from T_XPATH_INDEX_TEST
where xmlexists ('/root/employees/employee/name[upper-case(text())="CHRIS"]' passing xmlcol)select * from table(dbms_xplan.display())| PLAN_TABLE_OUTPUT | Plan hash value: 4270174327 | ----------------------------------------------------------------------------------------- | | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | ----------------------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | 686K| 1320M| 270K (1)| 00:00:11 | | | 1 | NESTED LOOPS SEMI | | 686K| 1320M| 270K (1)| 00:00:11 | | | 2 | TABLE ACCESS FULL| T_XPATH_INDEX_TEST | 8404 | 16M| 80 (0)| 00:00:01 | | |* 3 | XPATH EVALUATION | | | | | | | ----------------------------------------------------------------------------------------- | Predicate Information (identified by operation id): | --------------------------------------------------- | 3 - filter(UPPER("P"."C_01$")='CHRIS') | Note | ----- | - dynamic statistics used: dynamic sampling (level=2) |
|---|