My question is one of performance and trying to use XML indexes instead of shredding to relational tables.
Due to an application restraint, I am creating views that return data from XML documents using XMLTABLE..
Here is very simplyfied DDL.
CREATE TABLE T1 (KEY SMALLINT NOT NULL, XMLDOC XML);
CREATE UNIQUE INDEX I1 ON T1 (KEY);
CREATE TABLE T2 (KEY SMALLINT NOT NULL, XMLDOC XML);
CRETE UNIQUE INDEX I2 ON T2 (KEY);
CREATE VIEW V1 (KEY, USERNAME, ORDERNUMBER) AS (
WITH R1 AS (Select T1.KEY, X1.* from T1, XMLTABLE('$XMLDOC/Request' COLUMNS UserName VARCHAR(40) PATH 'UserName') X1),
WITH R2 AS (Select T2.KEY, X2.* from T2, XMLTABLE('$XMLDOC/Request' COLUMNS OrderNumber INTEGER PATH 'OrderNumber') X2)
Left Outer Join
Select R1.KEY, R1.Username, R2.OrderNumber from R1 outer join R2 on r1.key = r2.key);
The application now needs to filter the view on UserName and/or OrderNumber e.g.
select from v1 where username = ?
I would like to add XML indexes to service these queries but reading the Cookbook and testing with explain would suggest that these indexes would not be used without the application using the XMLEXISTS predicate.
Is this true? Is the optimizer unable to rewrite the query so that XML Indexes can be used?