XMLType in Oracle 10g

Question

We have to store xml in oracle database for that I am using XMLType as a data type for one of the column. There are today 40000 records in the table and I am using "extractValue" function to get the xml record. To get the record query is taking 14 seconds. Find below the query.

SELECT
    extractValue(
        req.gim_data,
        '/Envelope/Body/ref/instr/name') NAME
FROM 
    TESTDATA req
WHERE
    req.gim_data.existsNode('/Envelope/Body/ref/instr[instrId="AAA44444"]') = 1;

I have created below index but that also doesn't help.

CREATE INDEX gim_data_ix ON TESTDATA
  (extractValue(gim_data, '/Envelope/Body/ref/instr/instrId));

I tried to create XMLIndex but it is supported from Oracle 11g. Is there any way to create index that will improve the query performance or any other way.

Thanks sach

miracle_the_V miracle_the_V · Accepted Answer · 2015-04-20T12:21:16

You gave not enough information to understand how to increase your app performance, but this might help.

Creating Function-Based Indexes on XMLType Tables and Columns

The index that is created in Example 4–26 is an example of a function-based index. A function-based index is created by evaluating the specified functions for each row in the table. In that particular case, t he results of the functions were not useful and consequently the index itself was not useful. However, there are many cases were function-based indexes are useful.

One example of when a function-based index is useful is when the XML content is not being managed using structured storage. In this case, instead of the CREATE INDEX statement being re-written, the index will be created by invoking the function on the XML content and indexing the result.

Given the table created in Example 4–28, which uses CLOB storage rather than structured storage to persist the XML, the following CREATE INDEX statement will result in a function-based index being created on the value of the text node belonging to the Reference element. As the example shows, this index will enforce the unique constraint on the value of the text node associated with the Reference element.

Example 4-28 Creating a Function-Based Index on a CLOB-based XMLType()
 create table PURCHASEORDER_CLOB of XMLTYPE
    XMLType store as CLOB
    ELEMENT "http://localhost:8080/home/SCOTT/poSource/xsd/purchaseOrder.xsd#PurchaseOrder";

Table created.

--
insert into PURCHASEORDER_CLOB
select object_value from PURCHASEORDER;

134 rows created.


create unique index iPURCHASEORDER_REFERENCE
on PURCHASEORDER_CLOB
(extractValue(object_value,'/PurchaseOrder/Reference'));

Index created.

insert into PURCHASEORDER_CLOB
       VALUES
      (
         xmltype
         (
            bfilename('XMLDIR','EABEL-20021009123335791PDT.xml'),
            nls_charset_id('AL32UTF8')
         )
       );
insert into PURCHASEORDER_CLOB*

ERROR at line 1:
ORA-00001: unique constraint (SCOTT.IPURCHASEORDER_REFERENCE) violated
One thing to bear in mind when creating and using function-based indexes is that the optimizer will only consider using the index when the function included in the WHERE clause is identical to the function used to create the index.

Consider the queries in Example 4–29 which both find a PurchaseOrder-based value of the t ext node associated with the Reference element. Note that the first query, which uses existsNode() to locate the document, does not use the index, while the second query, which uses extractValue(), does use the index.

Also consider...

Creating B-Tree Indexes on the Contents of a Collection

You might often need to create an index over nodes that occur more than once in the target document. For instance, assume you wanted to create an index on the Id attribute of the LineItem element. A logical first attempt would be to create an index using the syntax shown in Example 4–25.

Example 4-25 Using extractValue() to Create an Index on a repeating Element or Attributes
CREATE INDEX iLINEITEM_UPCCODE
    ON PURCHASEORDER
    (extractValue(object_value,'/PurchaseOrder/LineItems/LineItemPart/@Id'));
    (extractValue(object_value,'/PurchaseOrder/LineItems/LineItem/Part/@Id'))
                                                         *
    ERROR at line 3:
    ORA-19025: EXTRACTVALUE returns value of only one node
As can be seen, when the Element or Attribute being indexed occurs multiple time s in the document, the create index fails because extractValue() is only allowed to return a single value for each row i t processes. It is possible to create an Index replacing extractValue() with extract().getStringVal() as shown in Example 4–26.

Example 4-26 Using extract().getStringVal() to Create a Function-Based Index on an extract()
CREATE INDEX iLINEITEM_UPCCODE
    ON PURCHASEORDER
    ( extract(object_value,'PurchaseOrder/LineItems/LineItem/Part/@Id').getStringVal());

    Index created.
This allows the Create Index statement to succeed. However, the index that is created is not what is expected. The index is created by invoking the extract() and getStringVal() functions for each row in the table and then indexing the result of the function against the rowid of the row.

The problem with this technique, is that when the XPath expression supplied to the extract() function, the extract() function can only returns multiple nodes. The result of the extract() function is a single XMLType consisting of a fragment containing the matching nodes. The result of invoking getStringVal() on an XMLType that contains a fragment is a concatenation of the nodes in question as shown in Example 4–27.

XMLType in Oracle 10g

1 Answers