Skip to Main Content
  • Questions
  • Index on XMLTYPE with XPATH Expression including a XPATH Function

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Florian.

Asked: May 12, 2022 - 2:26 pm UTC

Last updated: May 23, 2022 - 10:36 am UTC

Version: 19

Viewed 100+ times

You Asked

Is there a way to create a index for a xpath that is including a xpath function?
Please consider that xmltype index creation fails at oracle livesql.

with LiveSQL Test Case:

and Connor said...

Whilst I'm sure there is a better way than mine :-) is the following sufficent?

SQL> create index idx_xpath_emp_name on T_XPATH_INDEX_TEST x (
  2    upper(xmlcast(xmlquery('$i/root/employees/employee/name' passing x.xmlcol as "i" returning content) as varchar2(20)))
  3    );

Index created.

SQL>
SQL>
SQL> explain plan for
  2  select idcol
  3  from   T_XPATH_INDEX_TEST  x
  4  where upper(xmlcast(xmlquery('$i/root/employees/employee/name' passing x.xmlcol as "i" returning content) as varchar2(20))) = 'CHRIS';

Explained.

SQL>
SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 4035887690

----------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                    |   100 | 11900 |    41   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T_XPATH_INDEX_TEST |   100 | 11900 |    41   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | IDX_XPATH_EMP_NAME |    40 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

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

   2 - access(UPPER(CAST(EXTRACTVALUE(SYS_MAKEXML(0,"SYS_NC00003$" /*+ LOB_BY_VALUE */
              ),'/root/employees/employee/name',null,0,0,524293,1073874944) AS varchar2(20)))='CHRIS')

15 rows selected.

Rating

  (1 rating)

Comments

A reader, May 23, 2022 - 8:18 am UTC

Thanks Connor!
Chris Saxon
May 23, 2022 - 10:36 am UTC

You're welcome

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.