Indexing XMLType columns
- by Chris
Hello,
I am working with a XMLType and currently experiencing significant performance issues and would like to incorporate indexing to the column type.
Currently I am taking the approach of using the XMLTable() and XQuery functions to create a virtual table. I would like to use this Virtual Table to create a function based index on the table containing the XMLType, but I am receiving this error:
Error report:
SQL Error: ORA-00907: missing right parenthesis
00907. 00000 - "missing right parenthesis"
*Cause:
*Action:
This is the index.. any assistance would be greatly appreciated.
CREATE INDEX indx_medicinalproduct ON d.ProductName
XMLTable('for $i at $a in /safetyreport/patient//drug
for $j in $i/medicinalproduct
return element r { $i/medicinalproduct }'
PASSING s.safetyreport
COLUMNS
ProductName varchar2(70) PATH 'medicinalproduct') d;