Partition Pruning Against Path Table doesn't Happen for XMLExists Query with XMLIndex (Doc ID 1572000.1)

Last updated on APRIL 08, 2015

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.2 to 11.2.0.3 [Release 11.2]
Information in this document applies to any platform.

Symptoms

Partition pruning doesn't happen for XMLExists query with XMLIndex.

 

1) Table

CREATE TABLE TEST_RANGE_XML
 (ID NUMBER(15,0), DOC SYS.XMLTYPE)
 XMLTYPE COLUMN DOC STORE AS SECUREFILE BINARY XML
 (NOCOMPRESS  KEEP_DUPLICATES)
 PARTITION BY RANGE(ID)
  (PARTITION RANGE_PART_01 VALUES LESS THAN (100),
   PARTITION RANGE_PART_02 VALUES LESS THAN (200),
   PARTITION RANGE_PART_MAX VALUES LESS THAN (MAXVALUE));
 

2) Index

CREATE INDEX XMLINDEX_SXI
 ON TEST_RANGE_XML (DOC)
 INDEXTYPE IS XDB.XMLINDEX LOCAL;
 

3) Inserts

INSERT INTO TEST_RANGE_XML VALUES (50,XMLTYPE('<xml><doc>abc</doc></xml>'));
INSERT INTO TEST_RANGE_XML VALUES (150,XMLTYPE('<xml><doc>def</doc></xml>'));


4) SQL

-- This query hits partition 1

SELECT * FROM TEST_RANGE_XML
WHERE ID=50;
 

-- This query scans all partitions

SELECT * FROM TEST_RANGE_XML
WHERE ID=50
AND XMLEXISTS('/xml/doc[text()="abc"]' PASSING DOC);

 

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