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