My Oracle Support Banner

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

Last updated on AUGUST 04, 2018

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

To view full details, sign in with your My Oracle Support account.

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


In this Document
  Symptoms
  Cause
  Solution
  References

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.