Oracle Live SQL - Script: index xmltype xpath function

Statement 1

drop table t_xpath_index_test

ORA-00942: table or view does not exist

More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 2

create table t_xpath_index_test (idcol number(18), xmlcol xmltype)

Table created.

Statement 3

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 DUAL

10001 row(s) inserted.

Statement 4

explain plan for 
select idcol 
from   T_XPATH_INDEX_TEST 
where xmlexists ('/root/employees/employee/name[text()="Chris"]' passing xmlcol)

Statement processed.

Statement 5

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)

22 rows selected.

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)

Statement 6

create index idx_xpath_emp_name on T_XPATH_INDEX_TEST (xmlcol) INDEXTYPE IS XDB.XMLIndex 
parameters ('PATHS(INCLUDE(/root/employees/employee/name))')

ORA-29958: fatal error occurred in the execution of ODCIINDEXCREATE routine

More Details: https://docs.oracle.com/error-help/db/ora-29958

Statement 7

explain plan for 
select idcol 
from   T_XPATH_INDEX_TEST 
where xmlexists ('/root/employees/employee/name[text()="Chris"]' passing xmlcol)

Statement processed.

Statement 8

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)

22 rows selected.

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)

Statement 9

explain plan for 
select idcol 
from   T_XPATH_INDEX_TEST 
where xmlexists ('/root/employees/employee/name[upper-case(text())="CHRIS"]' passing xmlcol)

Statement processed.

Statement 10

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)

19 rows selected.

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)

Statement 11

drop table t_xpath_index_test

Table dropped.

Statement 12

create table t_xpath_index_test (idcol number(18), xmlcol xmltype)

Table created.

Statement 13

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 DUAL

10001 row(s) inserted.

Statement 14

explain plan for 
select idcol 
from   T_XPATH_INDEX_TEST 
where xmlexists ('/root/employees/employee/name[text()="Chris"]' passing xmlcol)

Statement processed.

Statement 15

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)

22 rows selected.

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)

Statement 16

create index idx_xpath_emp_name on T_XPATH_INDEX_TEST (xmlcol) INDEXTYPE IS XDB.XMLIndex 
parameters ('PATHS(INCLUDE(/root/employees/employee/name))')

ORA-00955: name is already used by an existing object

More Details: https://docs.oracle.com/error-help/db/ora-00955

Statement 17

explain plan for 
select idcol 
from   T_XPATH_INDEX_TEST 
where xmlexists ('/root/employees/employee/name[text()="Chris"]' passing xmlcol)

Statement processed.

Statement 18

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)

22 rows selected.

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)

Statement 19

explain plan for 
select idcol 
from   T_XPATH_INDEX_TEST 
where xmlexists ('/root/employees/employee/name[upper-case(text())="CHRIS"]' passing xmlcol)

Statement processed.

Statement 20

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)

32 rows selected.

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)

Statement 21

drop table t_xpath_index_test

Table dropped.

Statement 22

create table t_xpath_index_test (idcol number(18), xmlcol xmltype)

Table created.

Statement 23

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 DUAL

10001 row(s) inserted.

Statement 24

explain plan for 
select idcol 
from   T_XPATH_INDEX_TEST 
where xmlexists ('/root/employees/employee/name[text()="Chris"]' passing xmlcol)

Statement processed.

Statement 25

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)

22 rows selected.

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)

Statement 26

create index idx_xpath_emp_name on T_XPATH_INDEX_TEST (xmlcol) INDEXTYPE IS XDB.XMLIndex

ORA-29958: fatal error occurred in the execution of ODCIINDEXCREATE routine

More Details: https://docs.oracle.com/error-help/db/ora-29958

Statement 27

explain plan for 
select idcol 
from   T_XPATH_INDEX_TEST 
where xmlexists ('/root/employees/employee/name[text()="Chris"]' passing xmlcol)

Statement processed.

Statement 28

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)

22 rows selected.

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)

Statement 29

explain plan for 
select idcol 
from   T_XPATH_INDEX_TEST 
where xmlexists ('/root/employees/employee/name[upper-case(text())="CHRIS"]' passing xmlcol)

Statement processed.

Statement 30

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)

19 rows selected.

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)

Statement 31

drop table t_xpath_index_test

Table dropped.

Statement 32

create table t_xpath_index_test (idcol number(18), xmlcol xmltype)

Table created.

Statement 33

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 DUAL

10001 row(s) inserted.

Statement 34

explain plan for 
select idcol 
from   T_XPATH_INDEX_TEST 
where xmlexists ('/root/employees/employee/name[text()="Chris"]' passing xmlcol)

Statement processed.

Statement 35

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)

22 rows selected.

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)

Statement 36

create index idx_xpath_emp_name on T_XPATH_INDEX_TEST (xmlcol) INDEXTYPE IS XDB.XMLIndex

ORA-29958: fatal error occurred in the execution of ODCIINDEXCREATE routine

More Details: https://docs.oracle.com/error-help/db/ora-29958

Statement 37

explain plan for 
select idcol 
from   T_XPATH_INDEX_TEST 
where xmlexists ('/root/employees/employee/name[text()="Chris"]' passing xmlcol)

Statement processed.

Statement 38

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)

22 rows selected.

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)

Statement 39

explain plan for 
select idcol 
from   T_XPATH_INDEX_TEST 
where xmlexists ('/root/employees/employee/name[upper-case(text())="CHRIS"]' passing xmlcol)

Statement processed.

Statement 40

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)

19 rows selected.

index xmltype xpath function

Additional Information