database - Oracle 10g 中的 XMLType

Question

我们必须将 xml 存储在 oracle 数据库中，因为我使用 XMLType 作为其中一个列的数据类型。今天表中有 40000 条记录，我正在使用“extractValue”函数来获取 xml 记录。获取记录查询需要 14 秒。在查询下方找到。

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;

我创建了以下索引，但这也无济于事。

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

我尝试创建 XMLIndex，但 Oracle 11g 支持它。有没有什么方法可以创建索引来提高查询性能或任何其他方法。

谢谢萨克

Answer 1

您提供的信息不足以了解如何提高您的应用性能，但是 this可能有帮助。

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.

还要考虑...

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.