Skip Headers
Oracle® XML DB Developer's Guide
10g Release 2 (10.2)

Part Number B14259-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

10 Full-Text Search Over XML

This chapter describes full-text search over XML using Oracle. It explains how to use SQL function contains and XPath function ora:contains, the two functions used by Oracle Database to do full-text search over XML data.

See Also:

Oracle Text Reference and Oracle Text Application Developer's Guide for more information on Oracle Text

This chapter contains these topics:

Overview of Full-Text Search for XML

Oracle supports full-text search on documents that are managed by the Oracle Database. If your documents are XML, then you can use the XML structure of the document to restrict the full-text search. For example, you may want to find all purchase orders that contain the word "electric" using full-text search. If the purchase orders are in XML form, then you can restrict the search by finding all purchase orders that contain the word "electric" in a comment, or by finding all purchase orders that contain the word "electric" in a comment under line items. If your XML documents are of type XMLType, then you can project the results of your query using the XML structure of the document. For example, after finding all purchase orders that contain the word "electric" in a comment, you may want to return just the comments, or just the comments that contain the word "electric".

Comparison of Full-Text Search and Other Search Types

Full-text search differs from structured search or substring search in the following ways:

  • A full-text search looks for whole words rather than substrings. A substring search for comments that contain the string "law" might return a comment that contains "my lawn is going wild". A full-text search for the word "law" will not.

  • A full-text search will support some language-based and word-based searches which substring searches cannot. You can use a language-based search, for example, to find all the comments that contain a word with the same linguistic stem as "mouse", and Oracle Text will find "mouse" and "mice". You can use a word-based search, for example, to find all the comments that contain the word "lawn" within 5 words of "wild".

  • A full-text search generally involves some notion of relevance. When you do a full-text search for all the comments that contain the word "lawn", for example, some results are more relevant than others. Relevance is often related to the number of times the search word (or similar words) occur in the document.

XML search

XML search is different from unstructured document search. In unstructured document search you generally search across a set of documents to return the documents that satisfy your text predicate. In XML search you often want to use the structure of the XML document to restrict the search. And you often want to return just the part of the document that satisfies the search.

Search using Full-Text and XML Structure

There are two ways to do a search that includes full-text search and XML structure:

  • Include the structure inside the full-text predicate, using SQL function contains:

    ... WHERE contains(doc, 'electric INPATH (/purchaseOrder/items/item/comment)')
              > 0 ...
    
    

    Function contains is an extension to SQL, and can be used in any query. It requires a CONTEXT full-text index.

  • Include the full-text predicate inside the structure, using the ora:contains XPath function:

    ... '/purchaseOrder/items/item/comment[ora:contains(text(), "electric")>0]' ...
    

    The ora:contains XPath function is an extension to XPath, and can be used in any call to existsNode, extract, or extractValue.

About the Full-Text Search Examples

This section describes details about the examples included in this chapter.

Roles and Privileges

To run the examples you will need the CTXAPP role, as well as CONNECT and RESOURCE. You must also have EXECUTE privilege on the CTXSYS package CTX_DDL.

Schema and Data for Full-Text Search Examples

Examples in this chapter are based on "The Purchase Order Schema", W3C XML Schema Part 0: Primer.

The data in the examples is "Purchase-Order XML Document, po001.xml". Some of the performance examples are based on a larger table (PURCHASE_ORDERS_xmltype_big), which is included in the downloadable version only.

Some examples here use datatype VARCHAR2; others use XMLType. All examples that use VARCHAR2 will also work with XMLType.

Overview of CONTAINS and ora:contains

This section contains these topics:

Overview of SQL Function CONTAINS

SQL function contains returns a positive number for rows where [schema.]column matches text_query, and zero otherwise. It is a user-defined function, a standard extension method in SQL. It requires an index of type CONTEXT. If there is no CONTEXT index on the column being searched, then contains throws an error.

Syntax

contains([schema.]column, text_query VARCHAR2 [,label NUMBER])
RETURN NUMBER

Example 10-1 Simple CONTAINS Query

A typical query looks like this:

SELECT id FROM purchase_orders WHERE contains(doc, 'lawn') > 0;

This query uses table purchase_orders and index po_index. It returns the ID for each row in table purchase_orders where the doc column contains the word "lawn".

Example 10-2 CONTAINS with a Structured Predicate

SQL function contains can be used in any SQL query. Here is an example using table purchase_orders and index po_index:

SELECT id FROM purchase_orders WHERE contains(doc, 'lawn') > 0 AND id < 25;

Example 10-3 CONTAINS Using XML Structure to Restrict the Query

Suppose doc is a column that contains a set of XML documents. You can do full-text search over doc, using its XML structure to restrict the query. This query uses table purchase_orders and index po_index-path-section:

SELECT id FROM purchase_orders WHERE contains(doc, 'lawn WITHIN comment') > 0;

Example 10-4 CONTAINS with Structure Inside Full-Text Predicate

More complex structure restrictions can be applied with the INPATH operator and an XPath expression. This query uses table purchase_orders and index po_index-path-section:

SELECT id FROM purchase_orders
  WHERE contains(doc, 'electric INPATH (/purchaseOrder/items/item/comment)') > 0;

Overview of XPath Function ora:contains

XPath function ora:contains can be used in an XPath expression inside an XQuery expression or in a call to SQL function existsNode, extract, or extractValue. It is used to restrict a structural search with a full-text predicate. Function ora:contains returns a positive integer when the input_text matches text_query (the higher the number, the more relevant the match), and zero otherwise. When used in an XQuery expression, the XQuery return type is xs:integer(); when used in an XPath expression outside of an XQuery expression, the XPath return type is number.

Argument input_text must evaluate to a single text node or an attribute. The syntax and semantics of text_query in ora:contains are the same as text_query in contains, with the following restrictions:

  • Argument text_query cannot include any structure operators (WITHIN, INPATH, or HASPATH).

  • If the weight score-weighting operator is used, the weights will be ignored.

Function ora:contains extends XPath through a standard mechanism: it is a user-defined function in the Oracle XML DB namespace, ora.

Syntax

ora:contains(input_text NODE*, text_query STRING 
             [,policy_name STRING]
             [,policy_owner STRING])

Example 10-5 shows a call to ora:contains in the XPath parameter to existsNode. Note that the third parameter to existsNode (the Oracle XML DB namespace, ora) is required. This example uses table purchase_orders_xmltype.

Example 10-5 ora:contains with an Arbitrarily Complex Text Query

SELECT id
  FROM purchase_orders_xmltype
  WHERE 
    existsNode(doc, 
               '/purchaseOrder/comment
                  [ora:contains(text(), "($lawns AND wild) OR flamingo") > 0]', 
               'xmlns:ora="http://xmlns.oracle.com/xdb"')
    = 1;

See Also:

"ora:contains XPath Function" for more on the ora:contains XPath function

Comparison of CONTAINS and ora:contains

SQL function contains:

  • Needs a CONTEXT index to run. If there is no index, then you get an error.

  • Does an indexed search and is generally very fast

  • Returns a score (through SQL function score)

  • Can restrict a search using both full text and XML structure

  • Restricts a search based on documents (rows in a table) rather than nodes

  • Cannot be used for XML structure-based projection (pulling out parts of an XML document)

XPath function ora:contains:

  • Does not need an index to run, so it is very flexible

  • Separates application logic from storing and indexing considerations

  • Might do an unindexed search, so it might be resource-intensive

  • Does not return a score

  • Can restrict a search using full text in an XPath expression

  • Can be used for XML structure-based projection (pulling out parts of an XML document)

Use contains when you want a fast, index-based, full-text search over XML documents, possibly with simple XML structure constraints. Use ora:contains when you need the flexibility of full-text search in XPath (possibly without an index), or when you need to do projection, and you do not need a score.

CONTAINS SQL Function

This section contains these topics:

Full-Text Search Using Function CONTAINS

The second argument to SQL function contains, text_query, is a string that specifies the full-text search. text_query has its own language, based on the SQL/MM Full-Text standard.

See Also:

  • ISO/IEC 13249-2:2000, Information technology - Database languages - SQL Multimedia and Application Packages - Part 2: Full-Text, International Organization For Standardization, 2000

  • Oracle Text Reference for more information on the operators in the text_query language

The examples in the rest of this section show some of the power of full-text search. They use just a few of the available operators: Booleans (AND, OR, NOT) and stemming. The example queries search over a VARCHAR2 column (PURCHASE_ORDERS.doc) with a text index (indextype CTXSYS.CONTEXT).

Boolean Operators: AND, OR, NOT

The text_query language supports arbitrary combinations of AND, OR, and NOT. Precedence can be controlled using parentheses. The Boolean operators can be written in any of the following ways:

  • AND, OR, NOT

  • and, or, not

  • &, |, ~

Note that NOT is a binary, not a unary operator here. The expression alpha NOT(beta) is equivalent to alpha AND unary-not(beta), where unary-not stands for unary negation.

See Also:

Oracle Text Reference for complete information on the operators you can use in contains and ora:contains

Example 10-6 CONTAINS Query with Simple Boolean

SELECT id FROM purchase_orders WHERE contains(doc, 'lawn AND wild') > 0;

This example uses table purchase_orders and index po_index.

Example 10-7 CONTAINS Query with Complex Boolean

SELECT id FROM purchase_orders
  WHERE contains(doc, '((lawn OR garden) AND (wild OR flooded)) NOT(flamingo)')
        > 0;

This example uses table purchase_orders and index po_index.

Stemming: $

The text_query language supports stemmed search. Example 10-8 returns all documents that contain some word with the same linguistic stem as "lawns", so it will find "lawn" or "lawns". The stem operator is written as a dollar sign ($). There is no operator STEM or stem.

Example 10-8 CONTAINS Query with Stemming

SELECT id FROM purchase_orders WHERE contains(doc, '$(lawns)') > 0;
  

This example uses table purchase_orders and index po_index.

Combining Boolean and Stemming Operators

operators in the text_query language can be arbitrarily combined, as shown in Example 10-9.

Example 10-9 CONTAINS Query with Complex Query Expression

SELECT id FROM purchase_orders
  WHERE contains(doc, '($lawns AND wild) OR flamingo') > 0;

This example uses table purchase_orders and index po_index.

See Also:

Oracle Text Reference for a full list of text_query operators

SCORE SQL Function

SQL function contains has a related function, score, which can be used anywhere in the query. It is a measure of relevance, and it is especially useful when doing full-text searches across large document sets. score is typically returned as part of the query result, used in the ORDER BY clause, or both.

Syntax

score(label NUMBER) RETURN NUMBER

In Example 10-10, score(10) returns the score for each row in the result set. SQL function score returns the relevance of a row in the result set with respect to a particular call to function contains. A call to score is linked to a call to contains by a LABEL (in this case the number 10).

Example 10-10 Simple CONTAINS Query with SCORE

SELECT score(10), id FROM purchase_orders
  WHERE contains(doc, 'lawn', 10) > 0 AND score(10) > 2
  ORDER BY score(10) DESC;

This example uses table purchase_orders and index po_index.

Function score always returns 0 if, for the corresponding contains, the text_query argument does not match the input_text, according to the matching rules dictated by the text index. If the contains text_query does match the input_text, then score will return a number greater than 0 and less than or equal to 100. This number indicates the relevance of the text_query to the input_text. A higher number means a better match.

If the contains text_query consists of only the HASPATH operator and a Text Path, the score will be either 0 or 100, because HASPATH tests for an exact match.

See Also:

Oracle Text Reference for details on how the score is calculated

Structure: Restricting the Scope of a CONTAINS Search

SQL function contains does a full-text search across the whole document by default. In our examples, a search for "lawn" with no structure restriction will find all purchase orders with the word "lawn" anywhere in them.

Oracle offers three ways to restrict contains queries using XML structure:

  • WITHIN

  • INPATH

  • HASPATH

Note:

For the purposes of this discussion, consider section to be the same as an XML node.

WITHIN Structure Operator

The WITHIN operator restricts a query to some section within an XML document. A search for purchase orders that contain the word "lawn" somewhere inside a comment section might use WITHIN. Section names are case-sensitive.

Example 10-11 WITHIN

SELECT id FROM purchase_orders WHERE contains(DOC, 'lawn WITHIN comment') > 0;

This example uses table purchase_orders and index po_index-path-section.

Nested WITHIN

You can restrict the query further by nesting WITHIN. Example 10-12 finds all documents that contain the word "lawn" within a section "comment", where that occurrence of "lawn" is also within a section "item".

Example 10-12 Nested WITHIN

SELECT id FROM purchase_orders
  WHERE contains(doc, '(lawn WITHIN comment) WITHIN item') > 0;
  

This example uses table purchase_orders and index po_index-path-section.

Example 10-12 returns no rows. Our sample purchase order does contain the word "lawn" within a comment. But the only comment within an item is "Confirm this is electric". So the nested WITHIN query will return no rows.

WITHIN Attributes

You can also search within attributes. Example 10-13 finds all purchase orders that contain the word 10 in the orderDate attribute of a purchaseOrder element.

Example 10-13 WITHIN an Attribute

SELECT id FROM purchase_orders
  WHERE contains(doc, '10 WITHIN purchaseOrder@orderDate') > 0;
  

This example uses table purchase_orders and index po_index-path-section.

By default, the minus sign ("-") is treated as a word separator: "1999-10-20" is treated as the three words "1999", "10" and "20". So this query returns one row.

Text in an attribute is not a part of the main searchable document. If you search for 10 without qualifying the text_query with WITHIN purchaseOrder@orderDate, then you will get no rows.

You cannot search attributes in a nested WITHIN.

WITHIN and AND

Suppose you want to find purchase orders that contain two words within a comment section: "lawn" and "electric". There can be more than one comment section in a purchaseOrder. So there are two ways to write this query, with two distinct results.

If you want to find purchase orders that contain both words, where each word occurs in some comment section, you would write a query like Example 10-14.

Example 10-14 WITHIN and AND: Two Words in Some Comment Section

SELECT id FROM purchase_orders
  WHERE contains(doc, '(lawn WITHIN comment) AND (electric WITHIN comment)') > 0;

This example uses table purchase_orders and index po_index-path-section.

If you run this query against the purchaseOrder data, then it returns 1 row. Note that the parentheses are not needed in this example, but they make the query more readable.

If you want to find purchase orders that contain both words, where both words occur in the same comment, you would write a query like Example 10-15.

Example 10-15 WITHIN and AND: Two Words in the Same Comment

SELECT id FROM purchase_orders
  WHERE contains(doc, '(lawn AND electric) WITHIN comment') > 0;

This example uses table purchase_orders and index po_index-path-section.

Example 10-15 will return no rows. Example 10-16, which omits the parentheses around lawn AND electric, on the other hand, will return one row.

Example 10-16 WITHIN and AND: No Parentheses

SELECT id FROM purchase_orders
  WHERE contains(doc, 'lawn AND electric WITHIN comment') > 0;

This example uses table purchase_orders and index po_index-path-section.

Operator WITHIN has a higher precedence than AND, so Example 10-16 is parsed as Example 10-17.

Example 10-17 WITHIN and AND: Parentheses Illustrating Operator Precedence

SELECT id FROM purchase_orders
  WHERE contains(doc, 'lawn AND (electric WITHIN comment)') > 0;

This example uses table purchase_orders and index po_index-path-section.

Definition of Section

The preceding examples have used the WITHIN operator to search within a section. A section can be a:

  • path or zone section

    This is a concatenation, in document order, of all text nodes that are descendants of a node, with whitespace separating the text nodes. To convert from a node to a zone section, you must serialize the node and replace all tags with whitespace. path sections have the same scope and behavior as zone sections, except that path sections support queries with INPATH and HASPATH structure operators.

  • field section

    This is the same as a zone section, except that repeating nodes in a document are concatenated into a single section, with whitespace as a separator.

  • attribute section

  • special section (sentence or paragraph)

    See Also:

    Oracle Text Reference for more information on special sections

INPATH Structure Operator

Operator WITHIN provides an easy and intuitive way to express simple structure restrictions in the text_query. For queries that use abundant XML structure, you can use operator INPATH plus a text path instead of nested WITHIN operators.

Operator INPATH takes a text_query on the left and a Text Path, enclosed in parentheses, on the right. Example 10-18 finds purchaseOrders that contain the word "electric" in the path /purchaseOrder/items/item/comment.

Example 10-18 Structure Inside Full-Text Predicate: INPATH

SELECT id FROM purchase_orders
  WHERE contains(doc, 'electric INPATH (/purchaseOrder/items/item/comment)') > 0;

This example uses table purchase_orders and index po_index-path-section.

The scope of the search is the section indicated by the Text Path. If you choose a broader path, such as /purchaseOrder/items, you will still get 1 row returned, as shown in Example 10-19.

Example 10-19 Structure Inside Full-Text Predicate: INPATH

SELECT id FROM purchase_orders
  WHERE contains(doc, 'electric INPATH (/purchaseOrder/items)') > 0;

This example uses table purchase_orders and index po_index-path-section.

Text Path

The syntax and semantics of the Text Path are based on the w3c XPath 1.0 recommendation. Simple path expressions are supported (abbreviated syntax only), but functions are not. The following examples are meant to give the general flavor.

See Also:

Example 10-20 finds all purchase orders that contain the word "electric" in a "comment" which is the direct child of an "item" with an attribute partNum equal to "872-AA", which in turn is the direct child of an "items", which is any number of levels down from the root node.

Example 10-20 INPATH with Complex Path Expression (1)

SELECT id FROM purchase_orders
  WHERE contains(doc, 'electric INPATH (//items/item[@partNum="872-AA"]/comment)')  
        > 0;

This example uses table purchase_orders and index po_index-path-section.

Example 10-21 finds all purchase orders that contain the word "lawnmower" in a third-level "item" (or any of its descendants) that has a "comment" descendant at any level. This query returns one row. Note that the scope of the query is not a "comment", but the set of "items" that each have a "comment" as a descendant.

Example 10-21 INPATH with Complex Path Expression (2)

SELECT id FROM purchase_orders
  WHERE contains(doc, 'lawnmower INPATH (/*/*/item[.//comment])') > 0;

This example uses table purchase_orders and index po_index-path-section.

Text Path Compared to XPath

The Text Path language differs from the XPath language in the following ways:

  • Not all XPath operators are included in the Text Path language.

  • XPath built-in functions are not included in the Text Path language.

  • Text Path language operators are case-insensitive.

  • If you use "=" inside a filter (inside square brackets), matching follows text-matching rules.

    Rules for case-sensitivity, normalization, stopwords and whitespace depend on the text index definition. To emphasize this difference, this kind of equality is referred to here as text-equals.

  • Namespace support is not included in the Text Path language.

    The name of an element, including a namespace prefix if it exists, is treated as a string. So two namespace prefixes that map to the same namespace URI will not be treated as equivalent in the Text Path language.

  • In a Text Path, the context is always the root node of the document.

    So in the purchase-order data, purchaseOrder/items/item, /purchaseOrder/items/item, and ./purchaseOrder/items/item are all equivalent.

  • If you want to search within an attribute value, then the direct parent of the attribute must be specified (wildcards cannot be used).

  • A Text Path may not end in a wildcard (*).

See Also:

"Text Path BNF Specification" for the Text Path grammar
Nested INPATH

You can nest INPATH expressions. The context for the Text Path is always the root node. It is not changed by a nested INPATH.

Example 10-22 finds purchase orders that contain the word "electric" in a "comment" section at any level, where the occurrence of that word is also in an "items" section that is the direct child of the top-level purchaseOrder.

Example 10-22 Nested INPATH

SELECT id FROM purchase_orders
  WHERE contains(doc, 
                 '(electric INPATH (//comment)) INPATH (/purchaseOrder/items)')
        > 0;

This example uses table purchase_orders and index po_index-path-section.

This nested INPATH query could be written more concisely as shown in Example 10-23.

Example 10-23 Nested INPATH Rewritten

SELECT id FROM purchase_orders
  WHERE contains(doc, 'electric INPATH (/purchaseOrder/items//comment)') > 0;

This example uses table purchase_orders and index po_index-path-section.

HASPATH Structure Operator

Operator HASPATH takes only one operand: a Text Path, enclosed in parentheses, on the right. Use HASPATH when you want to find documents that contain a particular section in a particular path, possibly with an "=" predicate. Note that this is a path search rather than a full-text search. You can check for existence of a section, or you can match the contents of a section, but you cannot do word searches. If your data is of type XMLType, then consider using SQL function existsNode instead of structure operator HASPATH.

Example 10-24 finds purchaseOrders that have some item that has a USPrice.

Example 10-24 Simple HASPATH

SELECT id FROM purchase_orders
  WHERE contains(DOC, 'HASPATH (/purchaseOrder//item/USPrice)') > 0;

This example uses table purchase_orders and index po_index-path-section.

Example 10-25 finds purchaseOrders that have some item that has a USPrice that text-equals "148.95".

See Also:

"Text Path Compared to XPath" for an explanation of text-equals

Example 10-25 HASPATH Equality

SELECT id FROM purchase_orders
  WHERE contains(doc, 'HASPATH (/purchaseOrder//item/USPrice="148.95")') > 0;

This example uses table purchase_orders and index po_index-path-section.

HASPATH can be combined with other contains operators such as INPATH. Example 10-26 finds purchaseOrders that contain the word electric anywhere in the document and have some item that has a USPrice that text-equals 148.95 and contain 10 in the purchaseOrder attribute orderDate.

Example 10-26 HASPATH with Other Operators

SELECT id FROM purchase_orders
  WHERE contains(doc,
                 'electric
                  AND HASPATH (/purchaseOrder//item/USPrice="148.95")
                  AND 10 INPATH (/purchaseOrder/@orderDate)')
        > 0;

This example uses table purchase_orders and index po_index-path-section.

Structure: Projecting the CONTAINS Result

The result of a SQL query with a contains expression in the WHERE clause is always a set of rows (and possibly score information), or a projection over the rows that match the query. If you want to return only a part of each XML document that satisfies the contains expression, then use SQL functions extract and extractValue. Note that extract and extractValue operate on XMLType, so the following examples use the table purchase_orders_xmltype.

Example 10-27 finds purchaseOrders that contain the word "electric" in a "comment" that is a descendant of the top-level purchaseOrder. Instead of returning the ID of the row for each result, extract is used to return only the "comment".

Example 10-27 Using EXTRACT to Scope the Results of a CONTAINS Query

SELECT extract(doc, 
               '/purchaseOrder//comment', 
               'xmlns:ora="http://xmlns.oracle.com/xdb"') "Item Comment"
  FROM purchase_orders_xmltype
  WHERE contains(doc, 'electric INPATH (/purchaseOrder//comment)') > 0;

This example uses table purchase_orders_xmltype and index po_index_xmltype.

Note that the result of Example 10-27 is two instances of "comment". Function contains indicates which rows contain the word "electric" in a "comment" (the row with ID=1), and extract extracts all the instances of "comment" in the document at that row. There are two instances of "comment" in our purchaseOrder, and the query returns both of them.

This might not be what you want. If you want the query to return only the instances of "comment" that satisfy the contains expression, then you must repeat that predicate in the extract. You do that with ora:contains, which is an XPath function.

Example 10-28 returns only the "comment" that matches the contains expression.

Example 10-28 Using EXTRACT and ora:contains to Project the Result of a CONTAINS Query

SELECT 
  extract(doc, 
          '/purchaseOrder/items/item/comment
            [ora:contains(text(), "electric") > 0]', 
          'xmlns:ora="http://xmlns.oracle.com/xdb"') "Item Comment"
  FROM purchase_orders_xmltype
  WHERE contains(doc, 'electric INPATH (/purchaseOrder/items/item/comment)') > 0;

This example uses table purchase_orders and index po_index-path-section.

Indexing with the CONTEXT Index

This section contains these topics:

Introduction to the CONTEXT Index

The Oracle general purpose full-text indextype is the CONTEXT indextype, owned by the database user CTXSYS. To create a default full-text index, use the regular SQL CREATE INDEX command, and add the clause INDEXTYPE IS CTXSYS.CONTEXT, as shown in Example 10-29.

Example 10-29 Simple CONTEXT Index on Table PURCHASE_ORDERS

CREATE INDEX po_index ON purchase_orders(doc)
  INDEXTYPE IS CTXSYS.CONTEXT ;

This example uses table PURCHASE_ORDERS.

You have many choices available when building a full-text index. These choices are expressed as indexing preferences. To use an indexing preference, add the PARAMETERS clause to CREATE INDEX, as shown in Example 10-30.

Example 10-30 Simple CONTEXT Index on Table PURCHASE_ORDERS with Path Section Group

CREATE INDEX po_index ON purchase_orders(doc)
  INDEXTYPE IS CTXSYS.CONTEXT 
  PARAMETERS ('section group CTXSYS.PATH_SECTION_GROUP');

This example uses table purchase_orders.

Oracle Text provides other index types, such as CTXCAT and CTXRULE, which are outside the scope of this chapter.

See Also:

Oracle Text Reference for more information on CONTEXT indexes
CONTEXT Index on XMLType Table

You can build a CONTEXT index on any data that contains text. Example 10-29 creates a CONTEXT index on a VARCHAR2 column. The syntax to create a CONTEXT index on a column of type CHAR, VARCHAR, VARCHAR2, BLOB, CLOB, BFILE, XMLType, or URIType is the same. Example 10-31 creates a CONTEXT index on a column of type XMLType.

Example 10-31 Simple CONTEXT Index on Table PURCHASE_ORDERS_xmltype

CREATE INDEX po_index_xmltype ON purchase_orders_xmltype(doc)
  INDEXTYPE IS CTXSYS.CONTEXT;

This example uses table purchase_orders_xmltype. The section group defaults to PATH_SECTION_GROUP.

If you have a table of type XMLType, then you need to use object syntax to create the CONTEXT index as shown in Example 10-32.

Example 10-32 Simple CONTEXT Index on XMLType Table

CREATE INDEX po_index_xmltype_table 
  ON purchase_orders_xmltype_table (OBJECT_VALUE)
  INDEXTYPE IS CTXSYS.CONTEXT;

This example uses table purchase_orders_xmltype.

You can query the table using the syntax in Example 10-33.

Example 10-33 CONTAINS Query on XMLType Table

SELECT extract(OBJECT_VALUE, '/purchaseOrder/@orderDate') "Order Date"
  FROM purchase_orders_xmltype_table
  WHERE contains(OBJECT_VALUE, 'electric INPATH (/purchaseOrder//comment)') > 0;

This example uses table purchase_orders_xmltype_table and index po_index_xmltype_table.

Maintaining the CONTEXT Index

The CONTEXT index, like most full-text indexes, is asynchronous. When indexed data is changed, the CONTEXT index might not change until you take some action, such as calling a procedure to synchronize the index. There are a number of ways to manage changes to the CONTEXT index, including some options that are new for this release.

The CONTEXT index can get fragmented over time. A fragmented index uses more space, and it leads to slower queries. There are a number of ways to optimize (defragment) the CONTEXT index, including some options that are new for this release.

See Also:

Oracle Text Reference for more information on CONTEXT index maintenance
Roles and Privileges

You do not need any special privileges to create a CONTEXT index. You need the CTXAPP role to create and delete preferences and to use the Oracle Text PL/SQL packages. You must also have EXECUTE privilege on the CTXSYS package CTX_DDL.

Effect of the CONTEXT Index on CONTAINS

You must create an index of type CONTEXT in order to use SQL function contains. If you call contains, and the column given in the first argument does not have an index of type CONTEXT, then an error is raised.

The syntax and semantics of text_query depend on the choices you make when you build the CONTEXT index. For example:

  • What counts as a word?

  • Are very common words processed?

  • What is a common word?

  • Is the text search case-sensitive?

  • Can the text search include themes (concepts) as well as keywords?

CONTEXT Index: Preferences

A preference can be considered a collection of indexing choices. Preferences include section group, datastore, filter, wordlist, stoplist and storage. This section shows how to set up a lexer preference to make searches case-sensitive.

You can use procedure CTX_DDL.create_preference (or CTX_DDL.create_stoplist) to create a preference. Override default choices in that preference group by setting attributes of the new preference, using procedure CTX_DDL.set_attribute . Then use the preference in a CONTEXT index by including preference type preference_name in the PARAMETERS string of CREATE INDEX.

Once a preference has been created, you can use it to build any number of indexes.

Making Search Case-Sensitive

Full-text searches with contains are case-insensitive by default. That is, when matching words in text_query against words in the document, case is not considered. Section names and attribute names, however, are always case-sensitive.

If you want full-text searches to be case-sensitive, then you need to make that choice when building the CONTEXT index. Example 10-34 returns 1 row, because "HURRY" in text_query matches "Hurry" in the purchaseOrder with the default case-insensitive index.

Example 10-34 CONTAINS: Default Case Matching

SELECT id FROM purchase_orders
  WHERE contains(doc, 'HURRY INPATH (/purchaseOrder/comment)') > 0;

This example uses table purchase_orders and index po_index-path-section.

Example 10-35 creates a new lexer preference my_lexer, with the attribute mixed_case set to TRUE. It also sets printjoin characters to "-" and "!" and ",". You can use the same preferences for building CONTEXT indexes and for building policies.

See Also:

Oracle Text Reference for a full list of lexer attributes

Example 10-35 Create a Preference for Mixed Case

BEGIN
  CTX_DDL.create_preference(PREFERENCE_NAME  =>  'my_lexer',
                            OBJECT_NAME      =>  'BASIC_LEXER');
    
  CTX_DDL.set_attribute(PREFERENCE_NAME  =>  'my_lexer', 
                        ATTRIBUTE_NAME   =>  'mixed_case', 
                        ATTRIBUTE_VALUE  =>  'TRUE');
    
  CTX_DDL.set_attribute(PREFERENCE_NAME  =>  'my_lexer', 
                        ATTRIBUTE_NAME   =>  'printjoins', 
                        ATTRIBUTE_VALUE  =>  '-,!');
END ;
/

Example 10-36 builds a CONTEXT index using the new my_lexer lexer preference.

Example 10-36 CONTEXT Index on PURCHASE_ORDERS Table, Mixed Case

CREATE INDEX po_index ON purchase_orders(doc)
  INDEXTYPE IS CTXSYS.CONTEXT
  PARAMETERS('lexer my_lexer section group CTXSYS.PATH_SECTION_GROUP');

This example uses table purchase_orders and preference preference-case-mixed.

Example 10-34 returns no rows, because "HURRY" in text_query no longer matches "Hurry" in the purchaseOrder. Example 10-37 returns one row, because the text_query term "Hurry" exactly matches the word "Hurry" in the purchaseOrder.

Example 10-37 CONTAINS: Mixed (Exact) Case Matching

SELECT id FROM purchase_orders
  WHERE contains(doc, 'Hurry INPATH (/purchaseOrder/comment)') > 0;

This example uses table purchase_orders and index po_index-case-mixed.

Introduction to Section Groups

One of the choices you make when creating a CONTEXT index is section group. A section group instance is based on a section group type. The section group type specifies the kind of structure in your documents, and how to index (and therefore search) that structure. The section group instance may specify which structure elements are indexed. Most users will either take the default section group or use a pre-defined section group.

Choosing a Section Group Type

The section group types useful in XML searching are:

  • PATH_SECTION_GROUP

    Choose this when you want to use WITHIN, INPATH and HASPATH in queries, and you want to be able to consider all sections to scope the query.

  • XML_SECTION_GROUP

    Choose this when you want to use WITHIN, but not INPATH and HASPATH, in queries, and you want to be able to consider only explicitly-defined sections to scope the query. XML_SECTION_GROUP section group type supports FIELD sections in addition to ZONE sections. In some cases FIELD sections offer significantly better query performance.

  • AUTO_SECTION_GROUP

    Choose this when you want to use WITHIN, but not INPATH and HASPATH, in queries, and you want to be able to consider most sections to scope the query. By default all sections are indexed (available for query restriction). You can specify that some sections are not indexed (by defining STOP sections).

  • NULL_SECTION_GROUP

    Choose this when defining no XML sections.

Other section group types include:

  • BASIC_SECTION_GROUP

  • HTML_SECTION_GROUP

  • NEWS_SECTION_GROUP

Oracle recommends that most users with XML full-text search requirements use PATH_SECTION_GROUP. Some users may prefer XML_SECTION_GROUP with FIELD sections. This choice will generally give better query performance and a smaller index, but it is limited to documents with fielded structure (searchable nodes are all leaf nodes that do not repeat).

See Also:

Oracle Text Reference for a detailed description of the XML_SECTION_GROUP section group type
Choosing a Section Group

When choosing a section group to use with your index, you can choose a supplied section group, take the default, or create a new section group based on the section group type you have chosen.

There are supplied section groups for section group types PATH_SECTION_GROUP, AUTO_SECTION_GROUP, and NULL_SECTION_GROUP. The supplied section groups are owned by CTXSYS and have the same name as their section group types. For example, the supplied section group of section group type PATH_SECTION_GROUP is CTXSYS.PATH_SECTION_GROUP.

There is no supplied section group for section group type XML_SECTION_GROUP, because a default XML_SECTION_GROUP would be empty and therefore meaningless. If you want to use section group type XML_SECTION_GROUP, then you must create a new section group and specify each node that you want to include as a section.

When you create a CONTEXT index on data of type XMLType, the default section group is the supplied section group CTXSYS.PATH_SECTION_GROUP. If the data is VARCHAR or CLOB, then the default section group is CTXSYS.NULL_SECTION_GROUP.

See Also:

Oracle Text Reference for instructions on creating your own section group

To associate a section group with an index, add section group <section group name> to the PARAMETERS string, as in Example 10-38.

Example 10-38 Simple CONTEXT Index on purchase_orders Table with Path Section Group

CREATE INDEX po_index ON purchase_orders(doc)
  INDEXTYPE IS CTXSYS.CONTEXT 
  PARAMETERS ('section group CTXSYS.PATH_SECTION_GROUP');

This example uses table purchase_orders.

ora:contains XPath Function

Function ora:contains is an Oracle-defined XPath function for use in the XPath argument to the SQL/XML functions existsNode, extract, and extractValue.

Note:

These functions are not yet a part of the SQL/XML standard, but these functions or very similar functions are expected to be part of a future version of SQL/XML.

The ora:contains function name consists of a name (contains) plus a namespace prefix (ora:). When you use ora:contains in existsNode, extract or extractValue you must also supply a namespace mapping parameter, xmlns:ora="http://xmlns.oracle.com/xdb".

ora:contains returns a number; it does not return a score. It returns a positive number if the text_query matches the input_text. Otherwise it returns zero.

Full-Text Search Using Function ora:contains

The ora:contains argument text_query is a string that specifies the full-text search. The ora:contains text_query is the same as the contains text_query, with the following restrictions:

  • ora:contains text_query must not include any of the structure operators WITHIN, INPATH, or HASPATH

  • ora:contains text_query may include the score weighting operator weight(*), but weights will be ignored

If you include any of the following in the ora:contains text_query, the query cannot use a CONTEXT index:

  • Score-based operator MINUS (-) or threshold (>)

  • Selective, corpus-based expansion operator FUZZY (?) or soundex (!)

Example 10-39 shows a full-text search using an arbitrary combination of Boolean operators and $ (stemming).

Example 10-39 ora:contains with an Arbitrarily Complex Text Query

SELECT id FROM purchase_orders_xmltype
  WHERE existsNode(doc, 
                   '/purchaseOrder/comment
                     [ora:contains(text(), "($lawns AND wild) OR flamingo") > 0]', 
                   'xmlns:ora="http://xmlns.oracle.com/xdb"')
        = 1;

This example uses table purchase_orders_xmltype.

See Also:

Matching rules are defined by the policy, policy_owner.policy_name. If policy_owner is absent, then the policy owner defaults to the current user. If both policy_name and policy_owner are absent, then the policy defaults to CTXSYS.DEFAULT_POLICY_ORACONTAINS.

Structure: Restricting the Scope of an ora:contains Query

When you use ora:contains in an XPath expression, the scope is defined by argument input_text. This argument is evaluated in the current XPath context. If the result is a single text node or an attribute, then that node is the target of the ora:contains search. If input_text does not evaluate to a single text node or an attribute, an error is raised.

The policy determines the matching rules for ora:contains. The section group associated with the default policy for ora:contains is of type NULL_SECTION_GROUP.

ora:contains can be used anywhere in an XPath expression, and its input_text argument can be any XPath expression that evaluates to a single text node or an attribute.

Structure: Projecting the ora:contains Result

If you want to return only a part of each XML document, then use extract to project a node sequence or extractValue to project the value of a node.

Example 10-40 ora:contains in EXISTSNODE and EXTRACT

This example returns the orderDate for each purchaseOrder that has a comment that contains the word "lawn". It uses table purchase_orders_xmltype.

SELECT extract(doc, '/purchaseOrder/@orderDate') "Order date"
  FROM purchase_orders_xmltype
  WHERE existsNode(doc, 
                   '/purchaseOrder/comment[ora:contains(text(), "lawn") > 0]', 
                   'xmlns:ora="http://xmlns.oracle.com/xdb"')
        = 1;

Function existsNode restricts the result to rows (documents) where the purchaseOrder element includes some comment that contains the word "lawn". Function extract then returns the value of attribute orderDate from those purchaseOrder elements. If //comment had been extracted, then both comments from the sample document would have been returned, not just the comment that matches the WHERE clause.

Policies for ora:contains Queries

The CONTEXT index on a column determines the semantics of contains queries on that column. Because ora:contains does not rely on a supporting index, some other means must be found to provide many of the same choices when doing ora:contains queries. A policy is a collection of preferences that can be associated with an ora:contains query to give the same sort of semantic control as the indexing choices give to the contains user.

Introduction to Policies for ora:contains Queries

When using SQL function contains, indexing preferences affect the semantics of the query. You create a preference using procedure CTX_DDL.create_preference (or CTX_DDL.create_stoplist). You override default choices by setting attributes of the new preference, using procedure CTX_DDL.set_attribute. Then you use the preference in a CONTEXT index by including preference_type preference_name in the PARAMETERS string of CREATE INDEX.

Because ora:contains does not have a supporting index, a different mechanism is needed to apply preferences to a query. That mechanism is a policy, consisting of a collection of preferences, and it is used as a parameter to ora:contains.

Policy Example: Supplied Stoplist

Example 10-41 creates a policy with an empty stopwords list.

Example 10-41 Create a Policy to Use with ora:contains

BEGIN
  CTX_DDL.create_policy(POLICY_NAME  =>  'my_nostopwords_policy',
                        STOPLIST     =>  'CTXSYS.EMPTY_STOPLIST');
END;
/

For simplicity, this policy consists of an empty stoplist, which is owned by user CTXSYS. You could create a new stoplist to include in this policy, or you could reuse a stoplist (or lexer) definition that you created for a CONTEXT index.

Refer to this policy in an ora:contains expression to search for all words, including the most common ones (stopwords). Example 10-42 returns zero comments, because "is" is a stopword by default and cannot be queried.

Example 10-42 Query on a Common Word with ora:contains

SELECT id FROM purchase_orders_xmltype
  WHERE existsNode(doc, 
                   '/purchaseOrder/comment[ora:contains(text(), "is") > 0]', 
                   'xmlns:ora="http://xmlns.oracle.com/xdb"')
        = 1;

This example uses table purchase_orders_xmltype.

Example 10-43 uses the policy created in Example 10-41 to specify an empty stopword list. This query finds "is" and returns 1 comment.

Example 10-43 Query on a Common Word with ora:contains and Policy my_nostopwords_policy

SELECT id FROM purchase_orders_xmltype
  WHERE existsNode(doc, 
                   '/purchaseOrder/comment
                     [ora:contains(text(), "is", "MY_NOSTOPWORDS_POLICY") > 0]', 
                   'xmlns:ora="http://xmlns.oracle.com/xdb"')
        = 1;

This example uses table purchase_orders_xmltype and policy my_nostopwords_policy. (This policy was implicitly named as all uppercase in Example 10-41. Because XPath is case-sensitive, it must be referred to in the XPath predicate using all uppercase: MY_NOSTOPWORDS_POLICY, not my_nostopwords_policy.)

Note:

As always:
  • SQL is case-insensitive, but names in SQL code are implicitly uppercase, unless you enclose them in double-quotes.

  • XML is case-sensitive. You must refer to SQL names in XML code using the correct case: uppercase SQL names must be written as uppercase.

For example, if you create a table named my_table in SQL without using double-quotes, then you must refer to it in XML as "MY_TABLE".

Effect of Policies on ora:contains

The ora:contains policy affects the matching semantics of text_query. The ora:contains policy may include a lexer, stoplist, wordlist preference, or any combination of these. Other preferences that can be used to build a CONTEXT index are not applicable to ora:contains. The effects of the preferences are as follows:

  • The wordlist preference tweaks the semantics of the stem operator.

  • The stoplist preference defines which words are too common to be indexed (searchable).

  • The lexer preference defines how words are tokenized and matched. For example, it defines which characters count as part of a word and whether matching is case-sensitive.

See Also:

Policy Example: User-Defined Lexer

When you search for a document that contains a particular word, you usually want the search to be case-insensitive. If you do a search that is case-sensitive, then you will often miss some expected results. For example, if you search for purchaseOrders that contain the phrase "baby monitor", then you would not expect to miss our example document just because the phrase is written "Baby Monitor".

Full-text searches with ora:contains are case-insensitive by default. Section names and attribute names, however, are always case-sensitive.

If you want full-text searches to be case-sensitive, then you need to make that choice when you create a policy. You can use this procedure:

  1. Create a preference using the procedure CTX_DDL.create_preference (or CTX_DDL.create_stoplist).

  2. Override default choices in that preference object by setting attributes of the new preference, using procedure CTX_DDL.set_attribute.

  3. Use the preference as a parameter to CTX_DDL.create_policy.

  4. Use the policy name as the third argument to ora:contains in a query.

Once you have created a preference, you can reuse it in other policies or in CONTEXT index definitions. You can use any policy with any ora:contains query.

Example 10-44 returns 1 row, because "HURRY" in text_query matches "Hurry" in the purchaseOrder with the default case-insensitive index.

Example 10-44 ora:contains, Default Case-Sensitivity

SELECT id FROM purchase_orders_xmltype
  WHERE existsNode(doc, 
                   '/purchaseOrder/comment[ora:contains(text(), "HURRY") > 0]', 
                   'xmlns:ora="http://xmlns.oracle.com/xdb"')
        = 1;

This example uses table purchase_orders_xmltype.

Example 10-45 creates a new lexer preference my_lexer, with the attribute mixed_case set to TRUE. It also sets printjoin characters to "-" and "!" and ",". You can use the same preferences for building CONTEXT indexes and for building policies.

See Also:

Oracle Text Reference for a full list of lexer attributes

Example 10-45 Create a Preference for Mixed Case

BEGIN
  CTX_DDL.create_preference(PREFERENCE_NAME  =>  'my_lexer',
                            OBJECT_NAME      =>  'BASIC_LEXER');
  CTX_DDL.set_attribute(PREFERENCE_NAME  =>  'MY_LEXER', 
                        ATTRIBUTE_NAME   =>  'MIXED_CASE', 
                        ATTRIBUTE_VALUE  =>  'TRUE');
  CTX_DDL.set_attribute(PREFERENCE_NAME  =>  'my_lexer', 
                        ATTRIBUTE_NAME   =>  'printjoins', 
                        ATTRIBUTE_VALUE  =>  '-,!');
END ;
/

Example 10-46 creates a new policy my_policy and specifies only the lexer. All other preferences are defaulted.

Example 10-46 Create a Policy with Mixed Case (Case-Insensitive)

BEGIN
  CTX_DDL.create_policy(POLICY_NAME  => 'my_policy',
                        LEXER        => 'my_lexer');
END ;
/

This example uses preference-case-mixed.

Example 10-47 uses the new policy in a query. It returns no rows, because "HURRY" in text_query no longer matches "Hurry" in the purchaseOrder.

Example 10-47 ora:contains, Case-Sensitive (1)

SELECT id FROM purchase_orders_xmltype
  WHERE existsNode(doc, 
                   '/purchaseOrder/comment
                     [ora:contains(text(), "HURRY", "my_policy") > 0]', 
                   'xmlns:ora="http://xmlns.oracle.com/xdb"')
        = 1;

This example uses table purchase_orders_xmltype.

Example 10-48 returns one row, because the text_query term "Hurry" exactly matches the word "Hurry" in the purchaseOrder.

Example 10-48 ora:contains, Case-Sensitive (2)

SELECT id FROM purchase_orders_xmltype
  WHERE existsNode(doc, 
                   '/purchaseOrder/comment
                     [ora:contains(text(), "is going wild") > 0]', 
                   'xmlns:ora="http://xmlns.oracle.com/xdb"')
        = 1;

This example uses table purchase_orders_xmltype.

Policy Defaults

The policy argument to ora:contains is optional. If it is omitted, then the query uses the default policy CTXSYS.DEFAULT_POLICY_ORACONTAINS.

When you create a policy for use with ora:contains, you do not need to specify every preference. In Example 10-46, for example, only the lexer preference was specified. For the preferences that are not specified, CREATE_POLICY uses the default preferences:

  • CTXSYS.DEFAULT_LEXER

  • CTXSYS.DEFAULT_STOPLIST

  • CTXSYS.DEFAULT_ WORDLIST

Creating a policy follows copy semantics for preferences and their attributes, just as creating a CONTEXT index follows copy semantics for index metadata.

ora:contains Searches Over a Collection of Elements

It is common to use the schema annotation storeVarrayAsTable="true", and set parameter genTables="true" during schema registration. This automatically creates the tables needed to store the corresponding XML data.

However, if you use these settings, then you will not be able to use Oracle Text indexes for text-based ora:contains searches over a collection of elements – that is, a document with repetitions of the same element type. The storeVarrayAsTable = "true" schema annotation causes element collections to be persisted as rows in an index-organized table (IOT), and Oracle Text does not support IOTs.

To be able to use Oracle Text to search the contents of element collections, you must set parameter genTables="false" during schema registration, then create the necessary tables manually.

Example 10-49 shows how to manually create a table that corresponds to an element definition in an XML schema. It is identical to Example 3-11 (compare), except that it does not use the clause ORGANIZATION INDEX OVERFLOW. The lack of this clause causes the table to be heap-organized instead of index-organized, enabling searching with Oracle Text indexes.

Example 10-49 Creating a Heap-Organized Table that Conforms to an XML Schema

CREATE TABLE purchaseorder OF XMLType
  XMLSCHEMA "http://localhost:8080/home/SCOTT/poSource/xsd/purchaseOrder.xsd"
  ELEMENT "PurchaseOrder"
  VARRAY "XMLDATA"."actions"."action"
    STORE AS TABLE action_table
                   ((PRIMARY KEY (NESTED_TABLE_ID, SYS_NC_ARRAY_INDEX$)))
  VARRAY "XMLDATA"."lineitems"."lineitem"
    STORE AS TABLE lineitem_table 
                   ((PRIMARY KEY (NESTED_TABLE_ID, SYS_NC_ARRAY_INDEX$)));

ora:contains Performance

The ora:contains XPath function does not depend on a supporting index. ora:contains is very flexible. But if you use it to search across large amounts of data without an index, then it can also be resource-intensive. In this section we discuss how to get the best performance from queries that include XPath expressions with ora:contains.

Note:

Function-based indexes can also be very effective in speeding up XML queries, but they are not generally applicable to Text queries.

The examples in this section use table PURCHASE_ORDERS_xmltype_big. This has the same table structure and XML schema as PURCHASE_ORDERS_xmltype, but it has around 1,000 rows. Each row has a unique ID (in the "id" column), and some different text in /purchaseOrder/items/item/comment. Where an execution plan is shown, it was produced using the SQL*Plus command AUTOTRACE. Execution plans can also be produced using SQL commands TRACE and TKPROF. A description of commands AUTOTRACE, trace and tkprof is outside the scope of this chapter.

This section contains these topics:

Use a Primary Filter in the Query

Because ora:contains is relatively expensive to process, Oracle recommends that you write queries that include a primary filter wherever possible. This will minimize the number of rows actually processed by ora:contains.

Example 10-50 examines every row in the table (does a full table scan), as we can see from the Plan in Example 10-51. In this example, ora:contains is evaluated for every row.

Example 10-50 ora:contains in EXISTSNODE, Large Table

SELECT id FROM purchase_orders_xmltype_big
  WHERE existsNode(doc, 
                   '/purchaseOrder/items/item/comment
                     [ora:contains(text(), "constitution") > 0]', 
                   'xmlns:ora="http://xmlns.oracle.com/xdb"')
        = 1;

Example 10-51 EXPLAIN PLAN: EXISTSNODE

Execution Plan
----------------------------------------------------------
0      SELECT STATEMENT Optimizer=CHOOSE
1     0   TABLE ACCESS (FULL) OF 'PURCHASE_ORDERS_XMLTYPE_BIG' (TABLE)

If you create an index on the id column, as shown in Example 10-52, and add a selective id predicate to the query, as shown in Example 10-53, then it is apparent from Example 10-54 that Oracle will drive off the id index. ora:contains will be executed only for the rows where the id predicate is true (where id is less than 5).

Example 10-52 B-Tree Index on ID

CREATE INDEX id_index ON purchase_orders_xmltype_big(id);

This example uses table purchase_orders.

Example 10-53 ora:contains in EXISTSNODE, Mixed Query

SELECT id FROM purchase_orders_xmltype_big
  WHERE existsNode(doc, 
                   '/purchaseOrder/items/item/comment
                     [ora:contains(text(), "constitution") > 0]', 
                   'xmlns:ora="http://xmlns.oracle.com/xdb"')
        = 1
    AND id > 5;

Example 10-54 EXPLAIN PLAN: EXISTSNODE

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0    TABLE ACCESS (BY INDEX ROWSELECT ID) OF 'PURCHASE_ORDERS_XMLTYPE_BIG' (TABLE)
   2    1         INDEX (RANGE SCAN) OF 'SELECT ID_INDEX' (INDEX)

Use a CTXXPATH Index

The CTXXPATH index can be used as a primary filter for existsNode. CTXXPATH is not related to ora:contains. CTXXPATH can be a primary filter for any existsNode query.

The CTXXPATH index stores enough information about a document to produce a superset of the results of an XPath expression. For an existsNode query it is often helpful to interrogate the CTXXPATH index and then apply existsNode to that superset, rather than applying existsNode to each document in turn.

Example 10-55 produces the execution plan shown in Example 10-56.

Example 10-55 ora:contains in EXISTSNODE, Large Table

SELECT id FROM purchase_orders_xmltype_big
  WHERE existsNode(doc, 
                   '/purchaseOrder/items/item/comment
                     [ora:contains(text(), "constitution") > 0]', 
                   'xmlns:ora="http://xmlns.oracle.com/xdb"')
        = 1;

Example 10-56 EXPLAIN PLAN: EXISTSNODE

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0    TABLE ACCESS (FULL) OF 'PURCHASE_ORDERS_XMLTYPE_BIG' (TABLE)

Now create a CTXXPATH index on the DOC column, as shown in Example 10-57. You can create a CTXXPATH index and a CONTEXT index on the same column.

Example 10-57 Create a CTXXPATH Index on purchase_orders_xmltype_big(doc)

CREATE INDEX doc_xpath_index ON purchase_orders_xmltype_big(doc)
  INDEXTYPE IS CTXSYS.CTXXPATH ;

Run Example 10-55 again and you will see from the plan, shown in Example 10-58, that the query now uses the CTXXPATH index.

Example 10-58 EXPLAIN PLAN: EXISTSNODE with CTXXPATH Index

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=2044)
   1    0     TABLE ACCESS (BY INDEX ROWSELECT ID) OF 'PURCHASE_ORDERS_XMLTYPE_BIG' (TABLE) 
                (Cost=2 Card=1 Bytes=2044)

   2    1          DOMAIN INDEX OF 'DOC_XPATH_INDEX' (INDEX (DOMAIN)) 
                     (Cost=0)

When to Use CTXXPATH

CTXXPATH processes only a part of the XPath expression, to give a guaranteed superset (a first-pass estimate) of the results of XPath evaluation.

CTXXPATH does not process:

  • Functions, including ora:contains

  • Range operators: <=, <, >=, >

  • '..', "|"

  • Attribute following '.', '*' or '//'

  • Predicate following '.' or '*'

  • '.' or '*' at the end of a path

  • Any node with unabbreviated XPath syntax

So in Example 10-55, the CTXXPATH index cannot return results for /purchaseOrder/items/item/comment[ora:contains(., "constitution")>0], because it cannot process the function ora:contains. But the CTXXPATH index can act as a primary filter by returning all documents that contain the path /purchaseOrder/items/item/comment. By calculating this superset of results, CTXXPATH can significantly reduce the number of documents considered by existsNode in this case.

There are two situations where a CTXXPATH index will give a significant performance boost:

  • If the document collection is heterogeneous, then knowing which documents contain the path (some purchaseOrder with some items child with some item child with some comment child) is enough to significantly reduce the documents considered by existsNode.

  • If many of the queries include XPath expressions with equality predicates rather than range predicates or functions (such as Example 10-59), then CTXXPATH will process those predicates and therefore will be a useful primary filter. CTXXPATH handles both string and number equality predicates.

Example 10-59 Equality Predicate in XPath, Big Table

SELECT count(*) FROM purchase_orders_xmltype_big
  WHERE existsNode(doc, 
                   '/purchaseOrder/items/item[USPrice=148.9500]', 
                   'xmlns:ora="http://xmlns.oracle.com/xdb"') 
        = 1;

If you are not sure that CTXXPATH will be useful, then create a CTXXPATH index and gather statistics on it, as shown in Example 10-60. With these statistics in place, the Oracle Cost Based Optimizer can make an informed choice about whether to use the CTXXPATH index or to ignore it.

Example 10-60 Gathering Index Statistics

BEGIN
  DBMS_STATS.GATHER_INDEX_STATS(OWNNAME  => 'test',
                                INDNAME  => 'doc_xpath_index');  
END;
/

This example uses index-ctxxpath-1.

Maintaining the CTXXPATH Index

The CTXXPATH index, like the CONTEXT index, is asynchronous. When indexed data is changed, the CTXXPATH index might not change until you take some action, such as calling a procedure to synchronize the index. There are a number of ways to manage changes to the CTXXPATH index, including some options that are new for this release.

If the CTXXPATH index is not kept in synch with the data, then the index gradually becomes less efficient. The CTXXPATH index still calculates a superset of the true result, by adding all unsynchronized (unindexed) rows to the result set. So existsNode must process all the rows identified by the CTXXPATH index plus all unsynchronized (unindexed) rows.

The CTXXPATH index may get fragmented over time. A fragmented index uses more space and leads to slower queries. There are a number of ways to optimize (defragment) the CTXXPATH index, including some options that are new for this release.

See Also:

Oracle Text Reference for information on CTXXPATH index maintenance

XPath Rewrite and the CONTEXT Index

ora:contains does not rely on a supporting index. But under some circumstances an ora:contains may use an existing CONTEXT index for better performance.

Benefits of XPath Rewrite

Oracle will, in some circumstances, rewrite a SQL/XML query into an object-relational query. This is done as part of query optimization and is transparent to the user. Two of the benefits of XPath rewrite are:

  • The rewritten query can directly access the underlying object-relational tables instead of processing the whole XML document.

  • The rewritten query can make use of any available indexes.

XPath rewrite is a performance optimization. XPath rewrite only makes sense if the XML data is stored object-relationally, which in turn requires the XML data to be XML schema-based.

See Also:

From Documents to Nodes

Consider Example 10-61, a simple ora:contains query. To naively process the XPath expression in this query, each cell in the doc column must be considered, and each cell must be tested to see if it matches this XPath expression:

/purchaseOrder/items/item/comment[ora:contains(text(), "electric")>0]

Example 10-61 ora:contains in existsNode

SELECT id FROM purchase_orders_xmltype
  WHERE existsNode(doc, 
                   '/purchaseOrder/items/item/comment
                     [ora:contains(text(), "electric") > 0]', 
                   'xmlns:ora="http://xmlns.oracle.com/xdb"')
        = 1 ;

This example uses table purchase_orders_xmltype.

But if doc is schema-based, and the purchaseOrder documents are physically stored in object-relational tables, then it makes sense to go straight to column /purchaseOrder/items/item/comment (if such a column exists) and test each cell there to see if it matches "electric".

This is the first XPath-rewrite step. If the first argument to ora:contains (text_input) maps to a single relational column, then ora:contains executes against that column. Even if there are no indexes involved, this can significantly improve query performance.

From ora:contains to contains

As noted in "From Documents to Nodes", Oracle may rewrite a query so that an XPath expression in existsNode may be resolved by applying ora:contains to some underlying column instead of applying the whole XPath to the whole XML document. In this section it will be shown how that query might make use of a CONTEXT index on the underlying column.

If you are running ora:contains against a text node or attribute that maps to a column with a CONTEXT index on it, why would you not use that index? One powerful reason is that a rewritten query should give the same results as the original query. To ensure consistent results, the following conditions must be true before a CONTEXT index can be used.

First, the ora:contains target (input_text) must be either a single text node whose parent node maps to a column or an attribute that maps to a column. The column must be a single relational column (possibly in a nested table).

Second, as noted in "Policies for ora:contains Queries", the indexing choices (for contains) and policy choices (for ora:contains) affect the semantics of queries. A simple mismatch might be that the index-based contains would do a case-sensitive search, while ora:contains specifies a case-insensitive search. To ensure that the ora:contains and the rewritten contains have the same semantics, the ora:contains policy must exactly match the index choices of the CONTEXT index.

Both the ora:contains policy and the CONTEXT index must also use the NULL_SECTION_GROUP section group type. The default section group for an ora:contains policy is ctxsys.NULL_SECTION_GROUP.

Third, the CONTEXT index is generally asynchronous. If you add a new document that contains the word "dog", but do not synchronize the CONTEXT index, then a contains query for "dog" will not return that document. But an ora:contains query against the same data will. To ensure that the ora:contains and the rewritten contains will always return the same results, the CONTEXT index must be built with the TRANSACTIONAL keyword in the PARAMETERS string.

XPath Rewrite: Summary

A query with existsNode, extract or extractValue, where the XPath includes ora:contains, may be considered for XPath rewrite if:

  • The XML is schema-based

  • The first argument to ora:contains (text_input) is either a single text node whose parent node maps to a column, or an attribute that maps to a column. The column must be a single relational column (possibly in a nested table).

The rewritten query will use a CONTEXT index if:

  • There is a CONTEXT index on the column that the parent node (or attribute node) of text_input maps to.

  • The ora:contains policy exactly matches the index choices of the CONTEXT index.

  • The CONTEXT index was built with the TRANSACTIONAL keyword in the PARAMETERS string.

XPath rewrite can speed up queries significantly, especially if there is a suitable CONTEXT index.

Text Path BNF Specification

HasPathArg           ::=    LocationPath
                         |  EqualityExpr  
InPathArg            ::=    LocationPath 
LocationPath         ::=    RelativeLocationPath
                         |  AbsoluteLocationPath 
AbsoluteLocationPath ::=    ("/" RelativeLocationPath)
                         |  ("//" RelativeLocationPath) 
RelativeLocationPath ::=    Step
                         |  (RelativeLocationPath "/" Step)
                         |  (RelativeLocationPath "//" Step) 
Step                 ::=    ("@" NCName)
                         |  NCName
                         |  (NCName Predicate)
                         |  Dot
                         |  "*" 
Predicate            ::=    ("[" OrExp "]")
                         |  ("[" Digit+ "]") 
OrExpr               ::=    AndExpr
                         |  (OrExpr "or" AndExpr) 
AndExpr              ::=    BooleanExpr
                         |  (AndExpr "and" BooleanExpr) 
BooleanExpr          ::=    RelativeLocationPath
                         |  EqualityExpr
                         |  ("(" OrExpr ")")
                         |  ("not" "(" OrExpr ")") 
EqualityExpr         ::=    (RelativeLocationPath "=" Literal)
                         |  (Literal "=" RelativeLocationPath)
                         |  (RelativeLocationPath "=" Literal)
                         |  (Literal "!=" RelativeLocationPath)
                         |  (RelativeLocationPath "=" Literal)
                         |  (Literal "!=" RelativeLocationPath) 
Literal              ::=    (DoubleQuote [~"]* DoubleQuote)
                         |  (SingleQuote [~']* SingleQuote) 
NCName               ::=    (Letter |  Underscore) NCNameChar* 
NCNameChar           ::=    Letter
                         |  Digit
                         |  Dot
                         |  Dash
                         |  Underscore 
Letter               ::=    ([a-z] | [A-Z]) 
Digit                ::=    [0-9] 
Dot                  ::=    "." 
Dash                 ::=    "-" 
Underscore           ::=    "_" 

Support for Full-Text XML Examples

This section contains these topics:

Purchase-Order XML Document, po001.xml

Example 10-62 Purchase Order XML Document, po001.xml

<?xml version="1.0" encoding="UTF-8"?>
<purchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
               xsi:noNamespaceSchemaLocation="xmlschema/po.xsd" 
               orderDate="1999-10-20">
  <shipTo country="US">
    <name>Alice Smith</name>
    <street>123 Maple Street</street>
    <city>Mill Valley</city>
    <state>CA</state>
    <zip>90952</zip>
  </shipTo>
  <billTo country="US">
    <name>Robert Smith</name>
    <street>8 Oak Avenue</street>
    <city>Old Town</city>
    <state>PA</state>
    <zip>95819</zip>
  </billTo>
  <comment>Hurry, my lawn is going wild!</comment>
  <items>
    <item partNum="872-AA">
      <productName>Lawnmower</productName>
      <quantity>1</quantity>
      <USPrice>148.95</USPrice>
      <comment>Confirm this is electric</comment>
    </item>
    <item partNum="926-AA">
      <productName>Baby Monitor</productName>
      <quantity>1</quantity>
      <USPrice>39.98</USPrice>
      <shipDate>1999-05-21</shipDate>
    </item>
  </items>
</purchaseOrder>

CREATE TABLE Statements

Example 10-63 CREATE TABLE purchase_orders

CREATE TABLE purchase_orders (id   NUMBER,
                              doc  VARCHAR2(4000));
INSERT INTO purchase_orders (id, doc)
  VALUES (1,
          '<?xml version="1.0" encoding="UTF-8"?>
           <purchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                          xsi:noNamespaceSchemaLocation="xmlschema/po.xsd"
                          orderDate="1999-10-20">
             <shipTo country="US">
               <name>Alice Smith</name>
               <street>123 Maple Street</street>
               <city>Mill Valley</city>
               <state>CA</state>
               <zip>90952</zip>
             </shipTo>
             <billTo country="US">
               <name>Robert Smith</name>
               <street>8 Oak Avenue</street>
               <city>Old Town</city>
               <state>PA</state>
               <zip>95819</zip>
             </billTo>
             <comment>Hurry, my lawn is going wild!</comment>
             <items>
               <item partNum="872-AA">
                 <productName>Lawnmower</productName>
                 <quantity>1</quantity>
                 <USPrice>148.95</USPrice>
                 <comment>Confirm this is electric</comment>
               </item>
               <item partNum="926-AA">
                 <productName>Baby Monitor</productName>
                 <quantity>1</quantity>
                 <USPrice>39.98</USPrice>
                 <shipDate>1999-05-21</shipDate>
               </item>
             </items>           </purchaseOrder>');COMMIT;

Example 10-64 CREATE TABLE purchase_orders_xmltype

CREATE TABLE purchase_orders_xmltype (id  NUMBER ,
                                      doc XMLType);
INSERT INTO purchase_orders_xmltype (id, doc)
  VALUES (1,
          XMLTYPE ('<?xml version="1.0" encoding="UTF-8"?>
                     <purchaseOrder
                       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                       xsi:noNamespaceSchemaLocation="po.xsd"
                       orderDate="1999-10-20">
                       <shipTo country="US">
                         <name>Alice Smith</name>
                         <street>123 Maple Street</street>
                         <city>Mill Valley</city>
                         <state>CA</state>
                         <zip>90952</zip>
                       </shipTo>
                       <billTo country="US">
                         <name>Robert Smith</name>
                         <street>8 Oak Avenue</street>
                         <city>Old Town</city>
                         <state>PA</state>
                         <zip>95819</zip>
                       </billTo>
                       <comment>Hurry, my lawn is going wild!</comment>
                       <items>
                         <item partNum="872-AA">
                           <productName>Lawnmower</productName>
                           <quantity>1</quantity>
                           <USPrice>148.95</USPrice>
                           <comment>Confirm this is electric</comment>
                         </item>
                         <item partNum="926-AA">
                           <productName>Baby Monitor</productName>
                           <quantity>1</quantity>
                           <USPrice>39.98</USPrice>
                           <shipDate>1999-05-21</shipDate>
                         </item>
                       </items>
                   </purchaseOrder>'));
COMMIT;

Example 10-65 CREATE TABLE purchase_orders_xmltype_table

CREATE TABLE purchase_orders_xmltype_table OF XMLType;

INSERT INTO purchase_orders_xmltype_table
  VALUES (
    XMLType ('<?xml version="1.0" encoding="UTF-8"?>              <purchaseOrder 
                xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                xsi:noNamespaceSchemaLocation="xmlschema/po.xsd"
                orderDate="1999-10-20">
                <shipTo country="US">
                  <name>Alice Smith</name>
                  <street>123 Maple Street</street>
                  <city>Mill Valley</city>
                  <state>CA</state>
                  <zip>90952</zip>
                </shipTo>
                <billTo country="US">
                  <name>Robert Smith</name>
                  <street>8 Oak Avenue</street>
                  <city>Old Town</city>
                  <state>PA</state>
                  <zip>95819</zip>
                </billTo>
                <comment>Hurry, my lawn is going wild!</comment>
                <items>
                  <item partNum="872-AA">
                    <productName>Lawnmower</productName>
                    <quantity>1</quantity>
                    <USPrice>148.95</USPrice>
                    <comment>Confirm this is electric</comment>
                  </item>
                  <item partNum="926-AA">
                    <productName>Baby Monitor</productName>
                    <quantity>1</quantity>
                    <USPrice>39.98</USPrice>
                    <shipDate>1999-05-21</shipDate>
                  </item>
                </items>
              </purchaseOrder>'));
COMMIT;

Purchase-Order XML Schema for Full-Text Search Examples

Example 10-66 Purchase-Order XML Schema for Full-Text Search Examples

<?xml version="1.0" encoding="UTF-8"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <xsd:annotation>
    <xsd:documentation xml:lang="en">
      Purchase order schema for Example.com.
      Copyright 2000 Example.com. All rights reserved.
    </xsd:documentation>
  </xsd:annotation>
  <xsd:element name="purchaseOrder" type="PurchaseOrderType"/>
  <xsd:element name="comment" type="xsd:string"/>
  <xsd:complexType name="PurchaseOrderType">
    <xsd:sequence>
      <xsd:element name="shipTo" type="USAddress"/>
      <xsd:element name="billTo" type="USAddress"/>
      <xsd:element ref="comment" minOccurs="0"/>
      <xsd:element name="items" type="Items"/>
    </xsd:sequence>
    <xsd:attribute name="orderDate" type="xsd:date"/>
  </xsd:complexType>
  <xsd:complexType name="USAddress">
    <xsd:sequence>
      <xsd:element name="name" type="xsd:string"/>
      <xsd:element name="street" type="xsd:string"/>
      <xsd:element name="city" type="xsd:string"/>
      <xsd:element name="state" type="xsd:string"/>
      <xsd:element name="zip" type="xsd:decimal"/>
    </xsd:sequence>
    <xsd:attribute name="country" type="xsd:NMTOKEN" fixed="US"/>
  </xsd:complexType>
  <xsd:complexType name="Items">
    <xsd:sequence>
      <xsd:element name="item" minOccurs="0" maxOccurs="unbounded">
        <xsd:complexType>
          <xsd:sequence>
            <xsd:element name="productName" type="xsd:string"/>
            <xsd:element name="quantity">
              <xsd:simpleType>
                <xsd:restriction base="xsd:positiveInteger">
                  <xsd:maxExclusive value="100"/>
                </xsd:restriction>
              </xsd:simpleType>
            </xsd:element>
            <xsd:element name="USPrice" type="xsd:decimal"/>
            <xsd:element ref="comment" minOccurs="0"/>
            <xsd:element name="shipDate" type="xsd:date" minOccurs="0"/>
          </xsd:sequence>
          <xsd:attribute name="partNum" type="SKU" use="required"/>
        </xsd:complexType>
      </xsd:element>
    </xsd:sequence>
  </xsd:complexType>
  <!-- Stock Keeping Unit, a code for identifying products -->
  <xsd:simpleType name="SKU">
    <xsd:restriction base="xsd:string">
      <xsd:pattern value="\d{3}-[A-Z]{2}"/>
    </xsd:restriction>
  </xsd:simpleType>
</xsd:schema>