10 Full-Text Search Over XML

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".

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.

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.

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.

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.

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.

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

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.

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.

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.

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 (*).

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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).

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.

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

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.

Policy Example: Supplied Stoplist

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

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.

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.

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.)

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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]

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.