11 Full-Text Search Over XML Data

Nested WITHIN

You can restrict the query further by nesting WITHIN. Example 11-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 11-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 11-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 11-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 11-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 11-15 will return no rows. Example 11-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 11-16 is parsed as Example 11-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 about 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 11-20 finds all purchase orders that contain the word "electric" in a comment element that is the child of an item element with a partNum attribute whose value is "872-AA", which in turn is the child of an items element that is any number of levels under 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 11-21 finds all purchase orders that contain the word "lawnmower" in a third-level item element (or any of its descendants) that has a comment element descendant at any level. This query returns one row. The scope of the query is not a comment element, but the set of items elements that each have a comment element 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 (brackets), then 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 11-22 finds purchase orders that contain the word "electric" inside a comment element at any level, where the occurrence of that word is also in an items element that is a child of the top-level purchaseOrder element.

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 11-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 11-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 11-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 11-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 11-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 a 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 11-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 11-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 11-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 11-34 returns no rows, because "HURRY" in text_query no longer matches "Hurry" in the purchaseOrder. Example 11-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 11-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 11-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 11-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 11-43 uses the policy created in Example 11-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 11-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 11-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 11-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 11-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 11-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 11-48 returns one row, because the text_query term "Hurry" exactly matches the text "Hurry" in the comment element.

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.

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 is performed only when XML data is stored object-relationally, which in turn means that the data must be XML schema-based.

From Documents to Nodes

Consider Example 11-54, 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 XML DB might rewrite a query so that an XPath expression passed to function existsNode can be resolved by applying ora:contains to an underlying column, instead of applying the complete XPath expression to the entire XML document. This section shows how that such query can make use of a CONTEXT index on the underlying column.

If you are running ora:contains against a text node or an attribute that maps to a column that has a CONTEXT index, why would you not use that index? One 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, in order for a CONTEXT index to be used.

• 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 an ordered collection table).

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

Summary of Using XPath Rewrite With ora:contains

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 an ordered collection 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.