XMLCast XQuery Not Using Index When Mixed Is True (Doc ID 1341617.1)

Last updated on FEBRUARY 08, 2017

Applies to:

Oracle Server - Enterprise Edition - Version: 11.2.0.1 and later   [Release: 11.2 and later ]
Information in this document applies to any platform.

Symptoms


SELECT and DELETE do not use Index When the element has mixed="true":

Scenario 1: Using FirstName in the query:

select XMLCast(XMLQUERY('$m/Person/FirstName' PASSING OBJECT_VALUE AS "m" RETURNING CONTENT) as varchar2(100))
from person
where (XMLCast(XMLQUERY('$m/Person/FirstName' PASSING OBJECT_VALUE AS "m" RETURNING CONTENT) as varchar2(100))) = 'yyy';

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7654 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| PERSON | 1 | 7654 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------

delete
from person
where (XMLCast(XMLQUERY('$m/Person/FirstName' PASSING OBJECT_VALUE AS "m" RETURNING CONTENT) as varchar2(100))) = 'yyy';

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1 | 11542 | 2 (0)| 00:00:01 |
| 1 | DELETE | PERSON | | | | |
|* 2 | TABLE ACCESS FULL| PERSON | 1 | 11542 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------

Scenario 2: Using LastName in the query:

select XMLCast(XMLQUERY('$m/Person/LastName' PASSING OBJECT_VALUE AS "m" RETURNING CONTENT) as varchar2(100))
from person
where (XMLCast(XMLQUERY('$m/Person/LastName' PASSING OBJECT_VALUE AS "m" RETURNING CONTENT) as varchar2(100))) = 'yyy';

----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 72 | 0 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| PERSON | 1 | 72 | 0 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | LASTNAME_INDEX | 1 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

delete
from person
where (XMLCast(XMLQUERY('$m/Person/LastName' PASSING OBJECT_VALUE AS "m" RETURNING CONTENT) as varchar2(100))) = 'yyy';

----------------------------------------------------------------------------------------------
| 0 | DELETE STATEMENT | | 1 | 11594 | 0(0)| 00:00:01 |
| 1 | DELETE | PERSON | | | | |
|* 2 | TABLE ACCESS BY INDEX ROWID| PERSON | 1 | 11594 | 0(0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | LASTNAME_INDEX | 1 | | 0(0)| 00:00:01 |
----------------------------------------------------------------------------------------------

1. Here is how the Schema is registered:


exec dbms_xmlschema.deleteSchema('http://www.testxml.com/indexTest.xsd',4);
DECLARE
doc varchar2(2000) :=
'<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xmlns.oracle.com/xdb" elementFormDefault="qualified">
<xs:element name="Person" xdb:defaultTable="PERSON">
<xs:complexType xdb:SQLType="Person_T">
<xs:sequence>
<xs:element ref="FirstName"/>
<xs:element ref="LastName"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="FirstName" xdb:defaultTable="">
<xs:complexType mixed="true" xdb:SQLType="Name_T">
<xs:attribute name="type">
<xs:simpleType>
<xs:restriction base="xs:NMTOKEN"/>
</xs:simpleType>
</xs:attribute>
</xs:complexType>
</xs:element>
<xs:element name="LastName" type="xs:string" xdb:defaultTable=""/>
</xs:schema>';
BEGIN
dbms_xmlschema.registerSchema('http://www.testxml.com/indexTest.xsd',doc,true,true,false,true);
END;
/

2. Here is the creation of the indexes:


create unique index firstName_index on person (XMLCast(XMLQUERY('$m/Person/FirstName' PASSING OBJECT_VALUE AS "m" RETURNING CONTENT) as varchar2(100)));
create unique index lastName_index on person (XMLCast(XMLQUERY('$m/Person/LastName' PASSING OBJECT_VALUE AS "m" RETURNING CONTENT) as varchar2(100)));


3. Here is the description of the tables:

SQL> desc person
Name Null? Type
----------------------------------------- -------- ----------------------------
TABLE of SYS.XMLTYPE(XMLSchema "http://www.testxml.com/indexTest.xsd" Element "P
erson") STORAGE Object-relational TYPE "Person_T"

SQL> desc "Person_T"
Name Null? Type
----------------------------------------- -------- ----------------------------
SYS_XDBPD$ XDB.XDB$RAW_LIST_T
FirstName Name_T
LastName VARCHAR2(4000 CHAR)

SQL> desc "Name_T"
Name Null? Type
----------------------------------------- -------- ----------------------------
SYS_XDBPD$ XDB.XDB$RAW_LIST_T
type VARCHAR2(4000 CHAR)


FirstName has an extra level of complexity due to the attribute "type" but the cause of the problem is not the attribute but the mixed="true"

How can we make the SELECT and DELETE to use the INDEX instead of FTS with ELEMENT FirstName

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms