4 Querying with Oracle Text

4.1.1 Querying with CONTAINS

When you create an index of type CONTEXT, you must use the CONTAINS operator to enter your query. An index of type CONTEXT is suited for indexing collections of large coherent documents.

With the CONTAINS operator, you can use a number of operators to define your search criteria. These operators enable you to enter logical, proximity, fuzzy, stemming, thesaurus and wildcard searches. With a correctly configured index, you can also enter section searches on documents that have internal structure such as HTML and XML.

With CONTAINS, you can also use the ABOUT operator to search on document themes.

SELECT SCORE(1), title from news WHERE CONTAINS(text, 'oracle', 1) > 0;

SELECT SCORE(1), title from news 
           WHERE CONTAINS(text, 'oracle', 1) > 0
           ORDER BY SCORE(1) DESC;

declare 
  rowno number := 0; 
begin 
  for c1 in (SELECT SCORE(1) score, title FROM news 
              WHERE CONTAINS(text, 'oracle', 1) > 0
              ORDER BY SCORE(1) DESC) 
  loop 
    rowno := rowno + 1; 
    dbms_output.put_line(c1.title||': '||c1.score); 
    exit when rowno = 10; 
  end loop; 
end; 

SELECT SCORE(1), title, issue_date from news 
           WHERE CONTAINS(text, 'oracle', 1) > 0
           AND issue_date >= ('01-OCT-97') 
           ORDER BY SCORE(1) DESC;

4.1.2 Querying with CATSEARCH

When you create an index of type CTXCAT, you must use the CATSEARCH operator to enter your query. An index of type CTXCAT is best suited when your application stores short text fragments in the text column and other associated information in related columns.

For example, an application serving an online auction site might have a table that stores item description in a text column and associated information such as date and price in other columns. With a CTXCAT index, you can create b-tree indexes on one or more of these columns. The result is that when you use the CATSEARCH operator to search a CTXCAT index, query performance is generally faster for mixed queries.

The operators available for CATSEARCH queries are limited to logical operations such as AND or OR. The operators you can use to define your structured criteria are greater than, less than, equality, BETWEEN, and IN.

SELECT FROM auction WHERE CATSEARCH(title, 'camera', 'order by bid_close desc')> 0;

SELECT FROM auction WHERE CATSEARCH(title, 'camera', 'category_id=99 order by bid_close desc')> 0;

-- Create and populate table
create table BOOKS (ID number, INFO varchar2(200), PUBDATE DATE);
 
insert into BOOKS values(1, '<author>NOAM CHOMSKY</author><subject>CIVIL
   RIGHTS</subject><language>ENGLISH</language><publisher>MIT
   PRESS</publisher>', '01-NOV-2003');
 
insert into BOOKS values(2, '<author>NICANOR PARRA</author><subject>POEMS 
  AND ANTIPOEMS</subject><language>SPANISH</language>
  <publisher>VASQUEZ</publisher>', '01-JAN-2001');
 
insert into BOOKS values(1, '<author>LUC SANTE</author><subject>XML
  DATABASE</subject><language>FRENCH</language><publisher>FREE
  PRESS</publisher>', '15-MAY-2002');
 
commit;
 
-- Create index set and section group
exec ctx_ddl.create_index_set('BOOK_INDEX_SET');
exec ctx_ddl.add_index('BOOKSET','PUBDATE');
 
exec ctx_ddl.create_section_group('BOOK_SECTION_GROUP',
      'BASIC_SECTION_GROUP');
exec ctx_ddl.add_field_section('BOOK_SECTION_GROUP','AUTHOR','AUTHOR');
exec ctx_ddl.add_field_section('BOOK_SECTION_GROUP','SUBJECT','SUBJECT');
exec ctx_ddl.add_field_section('BOOK_SECTION_GROUP','LANGUAGE','LANGUAGE');
exec ctx_ddl.add_field_section('BOOK_SECTION_GROUP','PUBLISHER','PUBLISHER'); 
 
-- Create index
create index books_index on books(info) indextype is ctxsys.ctxcat
  parameters('index set book_index_set section group book_section_group');
 
-- Use the index
-- Note that: even though CTXCAT index can be created with field sections, it
-- cannot be accessed using CTXCAT grammar (default for CATSEARCH).
-- We need to use query template with CONTEXT grammar to access field 
-- sections with CATSEARCH
 
select  id, info from books
where catsearch(info,
'<query>
      <textquery grammar="context">
              NOAM within author and english within language
      </textquery>
 </query>',
'order by pubdate')>0; 

4.1.3 Querying with MATCHES

When you create an index of type CTXRULE, you must use the MATCHES operator to classify your documents. The CTXRULE index is essentially an index on the set of queries that define your classifications.

For example, if you have an incoming stream of documents that need to be routed according to content, you can create a set of queries that define your categories. You create the queries as rows in a text column. It is possible to create this type of table with the CTX_CLS.TRAIN procedure.

You then index the table to create a CTXRULE index. When documents arrive, you use the MATCHES operator to classify each document

SELECT classification FROM querytable WHERE MATCHES(query_string,:doc_text) > 0;

create table queries (
      query_id      number,
      query_string  varchar2(80)
    );

    insert into queries values (1, 'oracle');
    insert into queries values (2, 'larry or ellison');
    insert into queries values (3, 'oracle and text');
    insert into queries values (4, 'market share');

    create index queryx on queries(query_string)
      indextype is ctxsys.ctxrule;

    select query_id from queries
     where matches(query_string, 
                   'Oracle announced that its market share in databases 
                    increased over the last year.')>0

declare
    doc_text clob;
  begin
    -- create a temporary CLOB to hold the document text
    doc_text := dbms_lob.createtemporary(doc_text, TRUE, DBMS_LOB.SESSION);
 
    -- create a simple policy for this example
    ctx_ddl.create_preference(preference_name => 'fast_filter',
                        object_name       => 'AUTO_FILTER');
    ctx_ddl.set_attribute(preference_name => 'fast_filter',
                        attribute_name    => 'OUTPUT_FORMATTING',
                        attribute_value   => 'FALSE');
    ctx_ddl.create_policy(policy_name     => 'my_policy',
                        filter            => 'fast_filter);

    -- call ctx_doc.policy_filter to filter the BLOB to CLOB data
    ctx_doc.policy_filter('my_policy', :doc_blob, doc_text, FALSE);

    -- now do the matches query using the CLOB version
    for c1 in (select * from queries where matches(query_string, doc_text)>0)
    loop
      -- do what you need to do here
    end loop;

    dbms_lob.freetemporary(doc_text);
  end;

PROMPT  Populate the category table based on newsfeed articles
PROMPT
set serveroutput on;
declare
  mypk   number;
  mytitle varchar2(1000);
  myarticles clob;
  mycategory varchar2(100);
  cursor doccur is select pk,title,articles from newsfeed;
  cursor mycur is  select category from profiles where matches(rule, myarticles)>0;  
  cursor rescur is select category, pk, title from results order by category,pk;

begin
  dbms_output.enable(1000000);
  open doccur;
  loop
    fetch doccur into mypk, mytitle, myarticles;
    exit when doccur%notfound;
    open mycur;
    loop
      fetch mycur into mycategory;
      exit when mycur%notfound;
      insert into results values(mycategory, mypk, mytitle);
    end loop;
    close mycur;
    commit;
  end loop;
  close doccur;
  commit;

end;
/

PROMPT  display the list of articles for every category
PROMPT
set serveroutput on;

declare
  mypk   number;
  mytitle varchar2(1000);
  mycategory varchar2(100);
  cursor catcur is select category from profiles order by category;
  cursor rescur is select pk, title from results where category=mycategory order by pk;

begin
  dbms_output.enable(1000000);
  open catcur;
  loop
    fetch catcur into mycategory;
    exit when catcur%notfound;
    dbms_output.put_line('********** CATEGORY: '||mycategory||' *************');
open rescur;
    loop
      fetch rescur into mypk, mytitle;
      exit when rescur%notfound;
dbms_output.put_line('**  ('||mypk||'). '||mytitle);
    end loop;
    close rescur;
    dbms_output.put_line('**');
dbms_output.put_line('*******************************************************');
  end loop;
  close catcur; 
end;
/

4.1.4 Word and Phrase Queries

A word query is a query on a word or phrase. For example, to find all the rows in your text table that contain the word dog, enter a query specifying dog as your query term.

You can enter word queries with both CONTAINS and CATSEARCH SQL operators. However, phrase queries are interpreted differently.

4.1.5 Querying Stopwords

Stopwords are words for which Oracle Text does not create an index entry. They are usually common words in your language that are unlikely to be searched on by themselves.

Oracle Text includes a default list of stopwords for your language. This list is called a stoplist. For example, in English, the words this and that are defined as stopwords in the default stoplist. You can modify the default stoplist or create new stoplists with the CTX_DDL package. You can also add stopwords after indexing with the ALTER INDEX statement.

You cannot query on a stopword by itself or on a phrase composed of only stopwords. For example, a query on the word this returns no hits when this is defined as a stopword.

You can query on phrases that contain stopwords as well as non-stopwords such as this boy talks to that girl. This is possible because the Oracle Text index records the position of stopwords even though it does not create an index entry for them.

When you include a stopword within your query phrase, the stopword matches any word. For example, the query:

'Jack was big'

matches phrases such as Jack is big and Jack grew big assuming was is a stopword. Note that this query matches grew, even though it is not a stopword.

4.1.6 ABOUT Queries and Themes

An ABOUT query is a query on a document theme. A document theme is a concept that is sufficiently developed in the text. For example, an ABOUT query on US politics might return documents containing information about US presidential elections and US foreign policy. Documents need not contain the exact phrase US politics to be returned.

During indexing, document themes are derived from the knowledge base, which is a hierarchical list of categories and concepts that represents a view of the world. Some examples of themes in the knowledge catalog are concrete concepts such as jazz music, football, or Nelson Mandela. Themes can also be abstract concepts such as happiness or honesty.

During indexing, the system can also identify and index document themes that are sufficiently developed in the document, but do not exist in the knowledge base.

You can augment the knowledge base to define concepts and terms specific to your industry or query application. When you do so, ABOUT queries are more precise for the added concepts.

ABOUT queries perform best when you create a theme component in your index. Theme components are created by default for English and French.

4.1.7 Query Expressions

A query expression is everything in between the single quotes in the text_query argument of the CONTAINS or CATSEARCH operator. What you can include in a query expression in a CONTAINS query is different from what you can include in a CATSEARCH operator.

4.1.8 Case-Sensitive Searching

Oracle Text supports case-sensitivity for word and ABOUT queries.

4.1.9 Query Feedback

Feedback information provides broader term, narrower term, and related term information for a specified query with a context index. You obtain this information programatically with the CTX_QUERY.HFEEDBACK procedure.

Broader term, narrower term, and related term information is useful for suggesting other query terms to the user in your query application.

The feedback information returned is obtained from the knowledge base and contains only those terms that are also in the index. This increases the chances that terms returned from HFEEDBACK produce hits over the currently indexed document set.

4.1.10 Query Explain Plan

Explain plan information provides a graphical representation of the parse tree for a CONTAINS query expression. You can obtain this information programatically with the CTX_QUERY.EXPLAIN procedure.

Explain plan information tells you how a query is expanded and parsed without having the system execute the query. Obtaining explain information is useful for knowing the expansion for a particular stem, wildcard, thesaurus, fuzzy, soundex, or ABOUT query. Parse trees also show the following information:

• Order of execution

• ABOUT query normalization

• Query expression optimization

• Stop-word transformations

• Breakdown of composite-word tokens for supported languages

  See Also:

  Oracle Text Reference for more information about using CTX_QUERY.EXPLAIN

4.1.11 Using a Thesaurus in Queries

Oracle Text enables you to define a thesaurus for your query application.

Defining a custom thesaurus enables you to process queries more intelligently. Because users of your application might not know which words represent a topic, you can define synonyms or narrower terms for likely query terms. You can use the thesaurus operators to expand your query to include thesaurus terms.

4.1.12 Document Section Searching

Section searching enables you to narrow text queries down to sections within documents.

Section searching can be implemented when your documents have internal structure, such as HTML and XML documents. For example, you can define a section for the <H1> tag that enables you to query within this section using the WITHIN operator.

You can set the system to automatically create sections from XML documents.

You can also define attribute sections to search attribute text in XML documents.

4.1.13 Using Query Templates

Query templates are an alternative to the existing query languages. Rather than passing a query string to CONTAINS or CATSEARCH, you pass a structured document that contains the query string in a tagged element. Within this structured document, or query template, you can enable additional query features:

• Query Rewrite

• Query Relaxation

• Query Language

• Alternative and User-defined Scoring

• Alternative Grammar

4.1.14 Query Rewrite

Query applications sometimes parse end user queries, interpreting a query string in one or more ways using different operator combinations. For example, if a user enters a query of kukui nut, your application might enter the queries {kukui nut} and {kukui or nut} to increase recall.

The query rewrite feature enables you to submit a single query that expands the original query into the rewritten versions. The results are returned with no duplication.

You specify your rewrite sequences with the query template feature. The rewritten versions of the query are executed efficiently with a single call to CONTAINS or CATSEARCH.

The following template defines a query rewrite sequence. The query of {kukui nut} is rewritten as follows:

{kukui} {nut}

{kukui} ; {nut}

{kukui} AND {nut}

{kukui} ACCUM {nut}

The query rewrite template for these transformations is as follows:

select id from docs where CONTAINS (text,
 '<query>
   <textquery lang="ENGLISH" grammar="CONTEXT"> kukui nut
     <progression>
       <seq><rewrite>transform((TOKENS, "{", "}", " "))</rewrite></seq>
       <seq><rewrite>transform((TOKENS, "{", "}", " ; "))</rewrite></seq>
       <seq><rewrite>transform((TOKENS, "{", "}", "AND"))</rewrite></seq>
       <seq><rewrite>transform((TOKENS, "{", "}", "ACCUM"))</rewrite></seq>
     </progression>
   </textquery>
  <score datatype="INTEGER" algorithm="COUNT"/>
</query>')>0;

4.1.15 Query Relaxation

Query relaxation enables your application to execute the most restrictive version of a query first, progressively relaxing the query until the required number of hits are obtained.

For example, your application might search first on black pen and then the query is relaxed to black NEAR pen to obtain more hits.

The following query template defines a query relaxation sequence. The query of black pen is entered in sequence as the following:

{black} {pen}

{black} NEAR {pen}

{black} AND {pen}

{black} ACCUM {pen}

The query relaxation template for these transformations is as follows:

select id from docs where CONTAINS (text,
 '<query>
   <textquery lang="ENGLISH" grammar="CONTEXT">
     <progression>
       <seq>{black} {pen}</seq>
       <seq>{black} NEAR {pen}</seq>
       <seq>{black} AND {pen}</seq>
       <seq>{black} ACCUM {pen}</seq>
     </progression>
   </textquery>
   <score datatype="INTEGER" algorithm="COUNT"/>
</query>')>0;

Query hits are returned in this sequence with no duplication as long as the application needs results.

Query relaxation is most effective when your application needs the top n hits to a query, which you can obtain with the FIRST_ROWS hint or in a PL/SQL cursor.

Using query templating to relax a query as such is more efficient than re-executing a query.

4.1.16 Query Language

When you use the multi-lexer to index a column containing documents in different languages, you can specify which language lexer to use during querying. You do so using the lang parameter in the query template.

With the MULTI_LEXER in previous releases, you could only change the query language by altering the session language before executing the query.

select id from docs where CONTAINS (text,
'<query><textquery lang="french">bon soir</textquery></query>')>0;

4.1.17 Alternative and User-defined Scoring

You can use query templating to specify alternative scoring algorithms to use, other than the default, to customize how CONTAINS is scored, and to enable SDATA to be used as part of the scoring expressions. In this way, you can mathematically define the scoring expression using not only pre-defined scoring components, but also SDATA components.

With alternative user-defined scoring, you can:

• Specify the scoring expressions of terms by defining arithmetic expressions that define how the query should be scored, using

  • predefined scoring algorithms: DISCRETE, OCCURRENCE, RELEVANCE, and COMPLETION;

  • arithmetic operations: plus, minus, multiply, divide;

  • arithmetic functions: ABS(n), finding the absolute value of n ; LOG(n), finding the base-10 logarithmic value of n;

  • Numeric literals.

• Specify the scoring expressions at the term level.

• Specify terms that should not be taken into account when calculating the score.

• Specify how the score from child elements of OR and AND operators should be merged.

• Use SDATA that stores numeric or DATETIME values to affect the final score of the document.

The following example specifies an alternative scoring algorithm:

select id from docs where CONTAINS (text,
'<query>        
 <textquery grammar="CONTEXT" lang="english"> mustang  </textquery>     
 <score datatype="float" algorithm="DEFAULT"/>     
</query>')>0

The following query templating example includes SDATA values as part of the final score:

select id from docs where CONTAINS (text,
'<query>
<textquery grammar="CONTEXT" lang="english"> mustang </textquery>
<score datatype="float" algorithm="DEFAULT" normalization_expr =”doc_score+SDATA(price)”/>
</query>')>0"

4.1.18 Alternative Grammar

Query templating enables you to use the CONTEXT grammar with CATSEARCH queries and vice-versa.

select id from docs where CONTAINS (text,
'<query> 
  <textquery grammar="CTXCAT">San Diego</textquery>
  <score datatype="integer"/>
</query>')>0;

4.1.19 Query Analysis

Oracle Text enables you to create a log of queries and to analyze the queries it contains. For example, suppose you have an application that searches a database of large animals, and your analysis of its queries shows that users are continually searching for the word mouse; this analysis might induce you to rewrite your application so that a search for mouse redirects the user to a database of small animals instead of simply returning an unsuccessful search.

With query analysis, you can find out

• Which queries were made

• Which queries were successful

• Which queries were unsuccessful

• How many times each query was made

You can combine these factors in various ways, such as determining the 50 most frequent unsuccessful queries made by your application.

You start query logging with CTX_OUTPUT.START_QUERY_LOG. The query log will contain all queries made to all context indexes that the program is using until a CTX_OUTPUT.END_QUERY_LOG procedure is entered. Use CTX_REPORT.QUERY_LOG_SUMMARY to get a report of queries made.

4.1.20 Other Query Features

In your query application, you can use other query features such as proximity searching. Table 4-1 lists some of these features.

4.2.1 ABOUT Query

Use the ABOUT operator in English or French to query on a concept. The query string is usually a concept or theme that represents the idea to be searched on. Oracle Text returns the documents that contain the theme.

Word information and theme information are combined into a single index. To enter a theme query, your index must have a theme component which is created by default in English and French.

Enter a theme query using the ABOUT operator inside the query expression. For example, to retrieve all documents that are about politics, write your query as follows:

SELECT SCORE(1), title FROM news 
           WHERE CONTAINS(text, 'about(politics)', 1) > 0
           ORDER BY SCORE(1) DESC;

4.2.2 Logical Operators

Logical operators such as AND or OR allow you to limit your search criteria in a number of ways. Table 4-2 describes some of these operators.

'cats AND dogs'
'cats & dogs'

'cats | dogs'
'cats OR dogs'

'animals ~ dogs'

'dogs, cats, puppies'

'German shepherds=alsatians are big dogs'

4.2.3 Section Searching

Section searching is useful for when your document set is HTML or XML. For HTML, you can define sections using embedded tags and then use the WITHIN operator to search these sections.

For XML, you can have the system automatically create sections for you. You can query with the WITHIN operator or with the INPATH operator for path searching.

4.2.4 Proximity Queries with NEAR and NEAR_ACCUM Operators

You can search for terms that are near to one another in a document with the NEAR operator.

For example, to find all documents where dog is within 6 words of cat, enter the following query:

'near((dog, cat), 6)'

The NEAR_ACCUM operator combines the functionality of the NEAR operator with that of the ACCUM operator. Like NEAR, it returns terms that are within a given proximity of each other; however, if one term is not found, it ranks documents according to the frequency of the occurrence of the term that is found.

4.2.5 Fuzzy, Stem, Soundex, Wildcard and Thesaurus Expansion Operators

You can expand your queries into longer word lists with operators such as wildcard, fuzzy, stem, soundex, and thesaurus.

4.2.6 Using CTXCAT Grammar

You can use the CTXCAT grammar in CONTAINS queries. To do so, use a query template specification in the text_query parameter of CONTAINS.

You might take advantage of the CTXCAT grammar when you need an alternative and simpler query grammar.

4.2.7 Stored Query Expressions

You can use the procedure CTX_QUERY.STORE_SQE to store the definition of a query without storing any results. Referencing the query with the CONTAINS SQL operator references the definition of the query. In this way, stored query expressions make it easy for defining long or frequently used query expressions.

Stored query expressions are not attached to an index. When you call CTX_QUERY.STORE_SQE, you specify only the name of the stored query expression and the query expression.

The query definitions are stored in the Text data dictionary. Any user can reference a stored query expression.

begin
ctx_query.store_sqe('disaster', 'tornado | hurricane | earthquake');
end;

SELECT SCORE(1), title from news 
   WHERE CONTAINS(text, 'SQE(disaster)', 1) > 0
   ORDER BY SCORE(1);

4.2.8 Calling PL/SQL Functions in CONTAINS

You can call user-defined functions directly in the CONTAINS clause as long as the function satisfies the requirements for being named in a SQL statement. The caller must also have EXECUTE privilege on the function.

For example, assuming the function french returns the French equivalent of an English word, you can search on the French word for cat by writing:

SELECT SCORE(1), title from news 
   WHERE CONTAINS(text, french('cat'), 1) > 0
   ORDER BY SCORE(1);

4.2.9 Optimizing for Response Time

A CONTAINS query optimized for response time provides a fast solution for when you need the highest scoring documents from a hitlist.

The following example returns the first twenty hits to standard out. This example uses the FIRST_ROWS(n) hint and a cursor.

declare 
cursor c is  
  select /*+ FIRST_ROWS(20) */ title, score(1) score 
    from news where contains(txt_col, 'dog', 1) > 0 order by score(1) desc; 
begin 
  for c1 in c 
  loop 
    dbms_output.put_line(c1.score||':'||substr(c1.title,1,50)); 
    exit when c%rowcount = 21; 
  end loop; 
end; 
/

4.2.10 Counting Hits

To count the number of hits returned from a query with only a CONTAINS predicate, you can use CTX_QUERY.COUNT_HITS in PL/SQL or COUNT(*) in a SQL SELECT statement.

If you want a rough hit count, you can use CTX_QUERY.COUNT_HITS in estimate mode (EXACT parameter set to FALSE). With respect to response time, this is the fastest count you can get.

To count the number of hits returned from a query that contains a structured predicate, use the COUNT(*) function in a SELECT statement.

SELECT count(*) FROM news WHERE CONTAINS(text, 'oracle', 1) > 0;

SELECT COUNT(*) FROM news WHERE CONTAINS(text, 'oracle', 1) > 0 and author = 'jones';

declare count number;
begin
  count := ctx_query.count_hits(index_name => my_index, text_query => 'oracle', exact => TRUE);
 dbms_output.put_line('Number of docs with oracle:');
 dbms_output.put_line(count);
end;

4.2.11 Using DEFINESCORE and DEFINEMERGE for User-defined Scoring

The DEFINESCORE operator enables you to define how the score for a term or phrase is to be calculated. The DEFINEMERGE operator defines how to merge scores of child elements of AND and OR operators. You can also use the alternative scoring template with SDATA to affect the final scoring of the document.

4.3.1 Using CONTEXT Grammar with CATSEARCH

In addition, you can use the CONTEXT grammar in CATSEARCH queries. To do so, use a query template specification in the text_query parameter.

You might use the CONTAINS grammar as such when you need to enter proximity, thesaurus, or ABOUT queries with a CTXCAT index.