Oracle Text enables you to build text query applications and document classification applications.
This chapter discusses the following topics:
Oracle Text provides indexing, word and theme searching, and viewing capabilities for text in query applications and document classification applications.
To design an Oracle Text application, first determine the type of queries you expect to run. This enables you to choose the most suitable index for the task.
Oracle Text is used for the following categories of applications:
This section contains the following topics.
The collection is typically static with no significant change in content after the initial indexing run. Documents can be of any size and of different formats, such as HTML, PDF, or Microsoft Word. These documents are stored in a document table. Searching is enabled by first indexing the document collection.
Queries usually consist of words or phrases. Application users can specify logical combinations of words and phrases using operators such as
AND. Other query operations can be used to improve the search results, such as stemming, proximity searching, and wildcarding.
An important factor for this type of application is retrieving documents relevant to a query while retrieving as few non-relevant documents as possible. The most relevant documents must be ranked high in the result list.
The queries for this type of application are best served with a
CONTEXT index on your document table. To query this index, the application uses the
CONTAINS operator in the
WHERE clause of a
Figure 1-1 Overview of Text Query Application
A typical text query application on a document collection enables the user to enter a query. The application enters a
CONTAINS query and returns a list, called a hitlist, of documents that satisfy the query. The results are usually ranked by relevance. The application enables the user to view one or more documents in the hitlist.
For example, an application might index URLs (HTML files) on the Web and provide query capabilities across the set of indexed URLs. Hitlists returned by the query application are composed of URLs that the user can visit.
Figure 1-2 illustrates the flowchart of how a user interacts with a simple query application:
The user enters a query.
The application runs a
The application presents a hitlist.
The user selects document from hitlist.
The application presents a document to the user for viewing.
Figure 1-2 Flowchart of a query application
This section contains the following topics.
The stored catalog information consists of text information, such as book titles, and related structured information, such as price. The information is usually updated regularly to keep the online catalog up to date with the inventory.
Queries are usually a combination of a text component and a structured component. Results are almost always sorted by a structured component, such as date or price. Good response time is always an important factor with this type of query application.
Figure 1-3 illustrates the relation of the catalog table, its
CTXCAT index, and the catalog application that uses the
CATSEARCH operator to query the index.
Figure 1-3 A Catalog Query Application
A catalog application enables users to search for specific items in catalogs. For example, an online store application enables users to search for and purchase items in inventory. Typically, the user query consists of a text component that searches across the textual descriptions plus some other ordering criteria, such as price or date.
Figure 1-4 illustrates the flowchart of a catalog query application for an online electronics store.
The user enters the query, consisting of a text component (for example, cd player) and a structured component (for example, order by price).
The application executes the
The application shows the results ordered accordingly.
The user browses the results.
The user then either enters another query or performs an action, such as purchasing the item.
Figure 1-4 Flowchart of a Catalog Query Application
In a document classification application, an incoming stream or a set of documents is compared to a pre-defined set of rules. When a document matches one or more rules, the application performs some action.
For example, assume there is an incoming stream of news articles. You can define a rule to represent the category of Finance. The rule is essentially one or more queries that select document about the subject of Finance. The rule might have the form 'stocks or bonds or earnings'.
When a document arrives about a Wall Street earnings forecast and satisfies the rules for this category, the application takes an action, such as tagging the document as Finance or e-mailing one or more users.
To create a document classification application, create a table of rules and then create a
CTXRULE index. To classify an incoming stream of text, use the
MATCHES operator in the
WHERE clause of a
SELECT statement. See Figure 1-5 for the general flow of a classification application.
Figure 1-5 Overview of a Document Classification Application
An XML search application performs searches over XML documents. A regular document search usually searches across a set of documents to return documents that satisfy a text predicate; an XML search often uses the structure of the XML document to restrict the search. Typically, only that part of the document that satisfies the search is returned. For example, instead of finding all purchase orders that contain the word electric, the user might need only purchase orders in which the comment field contains electric.
Oracle Text enables you to perform XML searching using the following approaches:
CONTAINS operator is well suited to structured searching, enabling you to perform restrictive searches with the
INPATH operators. If you use a
CONTEXT index, then you can also benefit from the following characteristics of Oracle Text searches:
Searches are token-based, whitespace-normalized
Hitlists are ranked by relevance
Linguistic features such as stemming and fuzzy searching
Performance-optimized queries for large document sets
When Oracle Text is manually uninstalled, the DBMS_XDBT package should be dropped. When Oracle Text is manually reinstalled, the DBMS_XDBT package should be created.
Starting with Oracle Database 12c, Oracle XML Database is automatically installed when you install the new Oracle Database software or when you upgrade.
You can combine the features of Oracle Text and Oracle XML DB to create an XML Search Index for applications where you want to do a full-text retrieval, leveraging the XML structure by entering queries such as "find all nodes that contain the word Pentium." Oracle Database 12c extends Oracle's support for the W3C XQuery specification by adding support for the XQuery full-text extension, which enables you to perform XML-aware full-text searches on XML content stored in the database.
The following topics explain how to use Oracle XML DB with Oracle Text applications:
An XML Search Index is an XML-enabled Oracle Text index (CTXSYS.CONTEXT). This index type supports IR-style searching and structured searching in one unified index. XML Search Index also stores a Binary PDOM internally within an Oracle Text table, so that XML operations can be functionally evaluated over the Binary PDOM. This XML Search Index is supported for XMLTYPE data stores. XMLEXISTS is seamlessly rewritten to a CONTAINS in the presence of such an XML Search Index.
When an XML Search Index is created, a Binary PDOM of the XML document is materialized in an internal table of Oracle Text. Post evaluation from the Text index will be redirected to go against the PDOM stored in this internal table.
Oracle Text Reference for information on
xml_enable variable of
SET_SEC_GRP_ATTR to enable XML awareness for XML Search Index
The following example creates an Oracle XML Search Index:
exec CTX_DDL.CREATE_SECTION_GROUP('secgroup','PATH_SECTION_GROUP'); exec CTX_DDL.SET_SEC_GRP_ATTR('secgroup','xml_enable','t'); CREATE INDEX po_ctx_idx on T(X) indextype is ctxsys.context parameters (‘section group SECGROUP');
With Oracle Text, you can create a
CONTEXT index on a column that contains XML data. The column type can be
XMLType, but it can also be any supported type provided you use the correct index preference for XML data.
With the Text-on-XML method, use the standard
CONTAINS query and add a structured constraint to limit the scope of a search to a particular section, field, tag, or attribute. This amounts to specifying the structure inside text operators, such as
For example, set up your
CONTEXT index to create sections with XML documents. Consider the following XML document that defines a purchase order.
<?xml version="1.0"?> <PURCHASEORDER pono="1"> <PNAME>Po_1</PNAME> <CUSTNAME>John</CUSTNAME> <SHIPADDR> <STREET>1033 Main Street</STREET> <CITY>Sunnyvalue</CITY> <STATE>CA</STATE> </SHIPADDR> <ITEMS> <ITEM> <ITEM_NAME> Dell Computer </ITEM_NAME> <DESC> Pentium 2.0 Ghz 500MB RAM </DESC> </ITEM> <ITEM> <ITEM_NAME> Norelco R100 </ITEM_NAME> <DESC>Electric Razor </DESC> </ITEM> </ITEMS> </PURCHASEORDER>
To query all purchase orders that contain Pentium within the item description section, use the
SELECT id from po_tab where CONTAINS( doc, 'Pentium WITHIN desc') > 0;
Specify more complex criteria with
XPATH expressions using the
SELECT id from po_tab where CONTAINS(doc, 'Pentium INPATH (/purchaseOrder/items/item/desc') > 0;
Oracle Text Reference for information about creating a search index on JSON