Hi,
We are trying to use Structured XMLIndexes for a query. But they are not being used. A similar working sample is as follows:
- Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
Sample XML on XMLTYPE COlumn is:
<?xml version="1.0" encoding="UTF-8" standalone='yes'?>
<abc:tagOne preparationTime="2012-10-17T13:01:23.000Z" schemaVersion="1.3.9.0" xsi:schemaLocation="" xmlns:def="DEF" xmlns:abc="ABC" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<def:tagTwo>
<def:tagThree id="Identity1">
<def:Section domain="Domain123">10000</def:Section>
</def:tagThree>
<def:tagThree id="Identity2">
<def:Section domain="Domain123">20000</def:Section>
</def:tagThree>
<def:tagThree id="Identity3">
<def:Section domain="Domain123">30000</def:Section>
</def:tagThree>
<def:tagThree id="Identity4">
<def:Section domain="Domain123">40000</def:Section>
</def:tagThree>
</def:tagTwo>
</abc:tagOne>
Query is like:
select XMLAGG(XML_MESSAGE)
from INT_PART_TABLE
where XMLExists('
declare namespace abc="ABC";
declare namespace def="DEF";
$Message/abc:tagOne/def:tagTwo/def:tagThree[@id="Identity4"][def:Section[@domain="Domain123"] = ("50000", "40000") ]'
passing
xML_mESSAGE as "Message"
)
We are trying to create Structured Index on the xmltype column, as defined in the Sample XML:
CREATE INDEX INT_PART_TABLE_SXI
ON INT_PART_TABLE (XML_MESSAGE)
INDEXTYPE IS XDB.XMLINDEX
PARAMETERS ('XMLTABLE INT_PART_content_table_01
XMLNAMESPACES (''ABC'' AS "abc",
''DEF'' AS "def"),
''/abc:tagOne/def:tagTwo''
COLUMNS
xmltagTwo XMLTYPE PATH ''/abc:tagOne/def:tagTwo'' VIRTUAL
XMLTABLE INT_PART_content_table_02
XMLNAMESPACES (''DEF'' AS "def"),
''/def:tagTwo''
PASSING xmltagTwo
COLUMNS
xmltagThree XMLTYPE PATH ''/def:tagThree'' VIRTUAL
XMLTABLE INT_PART_content_table_03
XMLNAMESPACES (''DEF'' AS "def"),
''/def:tagThree''
PASSING xmltagThree
COLUMNS
section number PATH ''def:Section'',
domain varchar2(20) PATH ''@domain'',
id varchar2(20) PATH ''@id''
')local;
Table structure:
CREATE TABLE INT_PART_TABLE
(
DB_ID VARCHAR2 (10 BYTE),
xML_mESSAGE SYS.XMLTYPE,
LOAD_TIMESTAMP TIMESTAMP (6)
)
XMLTYPE xML_mESSAGE
STORE AS BINARY XML
(ENABLE STORAGE IN ROW
CHUNK 16384
RETENTION
CACHE READS NOLOGGING)
ALLOW NONSCHEMA
DISALLOW ANYSCHEMA
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (BUFFER_POOL DEFAULT)
PARTITION BY RANGE (LOAD_TIMESTAMP)
(PARTITION P_12092012 VALUES LESS THAN (TIMESTAMP ' 2012-09-12 00:00:00')
LOGGING
NOCOMPRESS
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (INITIAL 512 K
NEXT 512 K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT),
PARTITION MAX VALUES LESS THAN (MAXVALUE)
LOGGING
NOCOMPRESS
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (INITIAL 128 K
NEXT 128 K
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT))
PARALLEL (DEGREE 8 INSTANCES 1);
- But the above query doesn't use the STRUCTURED XMLINDEX and instead goes for FTS on INT_PART_TABLE. Please help here.
Is it the wrong way that I am creating Structured Index.
( I tried some other ways where I got -> ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence - got multi-item sequence).
- Please NOTE that I have used two 'VIRTUAL' while creating the Index as otherwise I was getting the Error:
ORA-29958: fatal error occurred in the execution of ODCIINDEXCREATE routine
ORA-30984: The XMLType column has to be declared as virtual
Kindly suggest. Thanks.