4 Replies Latest reply: May 28, 2013 10:48 AM by user615230-Oracle RSS

    Query on virtual column that is defined in XMLIndex does not use the index

    user615230-Oracle
      Hello,

      I am facing an issue in executing queries on a virtual column that is defined in an XMLIndex: it appears as if the index is not used.

      Database details:
      Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
      PL/SQL Release 11.2.0.3.0 - Production
      CORE 11.2.0.3.0 Production
      TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
      NLSRTL Version 11.2.0.3.0 - Production

      For this use case the XML documents adhere to the following XSD and are stored in an XMLType column in a table:
      <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
          xmlns="http://a_name_space/v1"
          targetNamespace="http://a_name_space/v1"
          elementFormDefault="qualified" attributeFormDefault="unqualified" version="1.0">
          <xsd:element name="fields">
              <xsd:complexType>
                  <xsd:sequence>
                      <xsd:element name="field" maxOccurs="unbounded">
                          <xsd:complexType>
                              <xsd:choice>
                                  <xsd:element name="value" minOccurs="1" maxOccurs="1">
                                      <xsd:complexType>
                                          <xsd:simpleContent>
                                              <xsd:extension base="notEmptyString4000Type"/>
                                          </xsd:simpleContent>
                                      </xsd:complexType>
                                  </xsd:element>
                                  <xsd:element name="values" minOccurs="1" maxOccurs="1">
                                      <xsd:complexType>
                                          <xsd:sequence>
                                              <xsd:element name="value" minOccurs="1" maxOccurs="1">
                                                  <xsd:complexType>
                                                      <xsd:simpleContent>
                                                          <xsd:extension base="notEmptyString4000Type">
                                                              <xsd:attribute name="startDate" type="xsd:date" use="required"/>
                                                              <xsd:attribute name="endDate" type="xsd:date" />
                                                          </xsd:extension>
                                                      </xsd:simpleContent>
                                                  </xsd:complexType>
                                              </xsd:element>
                                          </xsd:sequence>
                                      </xsd:complexType>
                                  </xsd:element>
                              </xsd:choice>
                              <xsd:attribute name="name" type="string30Type" use="required"/>
                              <xsd:attribute name="type" type="dataType" use="required"/>
                          </xsd:complexType>
                      </xsd:element>
                  </xsd:sequence>
              </xsd:complexType>
          </xsd:element>
          <xsd:simpleType name="dataType">
              <xsd:annotation>
                  <xsd:documentation>Char, Date, Number</xsd:documentation>
              </xsd:annotation>
              <xsd:restriction base="xsd:string">
                  <xsd:enumeration value="C"/>
                  <xsd:enumeration value="D"/>
                  <xsd:enumeration value="N"/>
              </xsd:restriction>
          </xsd:simpleType>
          <xsd:simpleType name="string30Type">
              <xsd:restriction base="xsd:string">
                  <xsd:maxLength value="30"/>
              </xsd:restriction>
          </xsd:simpleType>
          <xsd:simpleType name="notEmptyString4000Type">
              <xsd:restriction base="xsd:string">
                  <xsd:maxLength value="4000"/>
                  <xsd:pattern value=".+"/>
              </xsd:restriction>
          </xsd:simpleType>
      </xsd:schema>
      A field can have a single value as well as multiple values.

      The XMLIndex is defined as follows:
      CREATE INDEX test_xmltype_idx ON test_xmltype (additional_fields) INDEXTYPE IS XDB.XMLIndex 
      PARAMETERS
      ('
      XMLTable dt_fld_tab (TABLESPACE "TAB_SPACE" COMPRESS FOR OLTP) ''fields/field''
      COLUMNS
       name varchar2(30 char) PATH ''@name''
      ,dataType varchar2(1 char) PATH ''@type''
      ,val varchar2(4000 char) PATH ''value/text()''
      ,vals XMLType PATH ''values/value'' VIRTUAL
      XMLTable dt_fld_multi_value_tab (TABLESPACE "TAB_SPACE" COMPRESS FOR OLTP) ''value'' passing vals
      COLUMNS
       val varchar2(4000) PATH ''text()''
      ,startDate varchar2(30 char) PATH ''@startDate''
      ,endDate varchar2(30 char) PATH ''@endDate''
      ');
      The following b-tree indexes are defined:
      create index dt_field_name_idx on dt_fld_tab (name);
      create index dt_field_value_idx on dt_fld_tab (val);
      create index dt_field_values_idx on dt_fld_multi_value_tab (val);
      And stats are properly computed before the queries are executed:
      call dbms_stats.gather_table_stats(user, 'test_xmltype', estimate_percent => null);
      Queries for single values are cost efficient and fast. With 600K rows in the table these return with 0.002 seconds.
      Queries for multi-valued fields / elements are not though, these result in a full table scan.

      Sample XML snippet:
      <fields>
        ...
        <field name="multiVal" type="C">
          <values>
            <value startDate="2013-01-01" endDate="2013-01-01">100</value>
            <value startDate="2014-01-01">120</value>
          </values>
        </field>
        ...
      </fields>
      Examples of costly and slow queries:
      select id from test_xmltype
      where xmlexists('/fields/field/@name="multiVal"' passing additional_fields)
      and xmlexists('/fields/field/values/value[@startDate="2013-01-01"]' passing additional_fields)
      and xmlexists('/fields/field/values/value[text()="100"]' passing additional_fields)
      
      select id from test_xmltype
      where xmlexists('/fields/field/@name="multiVal"' passing additional_fields)
      and xmlexists('/fields/field/values/value[@startDate="2013-01-01" and .="100"]' passing additional_fields);
      Whereas the following query on the multi valued field is fast:
      select id from test_xmltype
      where xmlexists('/fields/field/@name="multiVal"' passing additional_fields)
      and xmlexists('/fields/field/values/value[@startDate="2013-01-01"]' passing additional_fields);
      For the XPath /fields/field/values/value[@startDate="2013-01-01"] the index is used.
      Suspected cause: XPath issue for the value of a multi valued field, e.g. /fields/field/values/value[text()="aValue"].

      Any hints are appreciated: what am I overlooking here?

      Thanks in advance,
      -Sjoerd

      Edited by: user615230 on May 27, 2013 7:46 AM