Perl Cd Bookshelf [Electronic resources] نسخه متنی

Best Practices

Optimizing the performance of searches can be a trial-and-error process. You should regularly monitor the performance to establish a plan for maintaining the indexes. The following are some tips to maximize the query performance.

When to Use XPath-Based Searches

You should not overuse XPath-based queries on XML documents unless you can ensure that the query rewrite is performed properly. Without query rewrite, the in-memory building of DOM trees or text analysis for ora:contains() can be very expensive and prevent you from searching large numbers of XML documents.

In addition, you should know that without ora:contains(), XPath-based queries are limited to matching substrings and may result in unexpected results.

Finally, the CTXXPATH index and functional indexes are both very useful for XPath-based searches when the query rewrite cannot be used.

When to Use Oracle Text Searches

Compared to XPath-based queries, Oracle Text performs linguistic analysis and provides rich full text search features that go beyond substring matching. For example, it allows you to create a concise abstract, called gist, for the indexed XML documents:

CREATE TABLE ctx_gist (query_id NUMBER,
pov VARCHAR2(80), 
gist CLOB);
BEGIN
FOR item IN (SELECT product_id FROM product) LOOP
CTX_DOC.GIST('product_idx',item.product_id,'CTX_GIST',1,'P', 
pov =>'GENERIC', numParagraphs => 1);
END LOOP;
END;

This example uses the CTX_DOC.GIST() procedure to generate gists for the product descriptions, specifying the size of the gist to be no more than one paragraph and inserting the created abstracts into a gist table called ctx_gist.

Oracle Text provides better scalability for the searches on XML documents that cannot use query rewrite by substantially reducing the processing overhead introduced by XML DOM tree creation and traversal, and the in-memory text analysis for ora:contains().

Additionally, Oracle Text can deal with a variety of documents from various sources in multiple languages. The document formats include Microsoft Word, PDF, Microsoft Excel, and HTML files. These documents can be stored inside the Oracle database as well as outside the database and accessed over HTTP, FTP, or from file servers. This, therefore, allows you to build Oracle Text indexes to optimally search XML documents stored both inside and outside the database.

When to Use Oracle Text Partitioned Indexes

Table partitioning is good to use when dealing with a large number of XML data sets. In Oracle9i, Oracle Text introduced the Local Partitioned Index, which is a partitioned index on a partitioned table with a one-to-one mapping of index partitions to table partitions. This virtually splits up a large table and the CONTEXT index into a set of smaller tables and indexes. It allows you to use local partitioned indexes instead of a global index.

For document systems, in which a large amount of new data is inserted every day, it is not practical to refresh the Oracle Text index for all the data, because that can take a long time. The local partitioned index makes the index more manageable, because after the DML operations you need to rebuild only the partition with the data changes, not the whole index.

On the other hand, with the local partitioning, index partitions can be synchronized in parallel, thus increasing uptime.

Finally, because the local partitioned CONTEXT index takes advantage of partition pruning and partition iteration to filter out the ROWIDs of the other partitions, it can provide faster mixed and sorted queries.