Querying

You issue Text queries using the CONTAINS operator in a SELECT statement. With CONTAINS, you can issue two types of queries:

• word query

• ABOUT query

You can also optimize queries for better response time. The following sections give an overview of these query scenarios.

Word Query Example

A word query is a query on the exact word or phrase you enter between the single quotes in the CONTAINS operator.

The following example finds all the documents in the text column that contain the word oracle. The score for each row is selected with the SCORE operator using a label of 1:

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

In your query expression, you can use text operators such as AND and OR to achieve different results. You can also add structured predicates to the WHERE clause.

See Also: For more information about the different operators you can use in queries, see Chapter 4, "Query Operators".

You can count the hits to a query using count(*), or CTX_QUERY.COUNT_HITS.

ABOUT Query Example

In all languages, ABOUT queries increases the number of relevant documents returned by a query.

In English, ABOUT queries can use the theme component of the index, which is created by default. As such, this operator returns documents based on the concepts of your query, not only the exact word or phrase you specify.

For example, the following query finds all the documents in the text column that are about the subject politics, not just the documents that contain the word politics:

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

See Also: For more information about the ABOUT operator, see ABOUT in Chapter 4, "Query Operators".

Optimizing Query for Response Time

You can optimize any CONTAINS query (word or ABOUT) for response time in order to retrieve the highest ranking hits in a result set in the shortest time possible. Optimizing for response time is useful in a web-based search application.

To optimize for response time, use the FIRST_ROWS hint. For example, the following PL/SQL block uses a cursor to retrieve the first 20 hits of a query and uses the FIRST_ROWS hint to optimize the response time:

declare
cursor c is 
select /*+ FIRST_ROWS */ pk, score(1), col from ctx_tab 
            where contains(txt_col, 'oracle', 1) > 0 order by score(1) desc;
begin
for i in c
loop
insert into t_s values(i.pk, i.col);
exit when c%rowcount > 21;
end loop;
end;

See Also: For more information on optimizing queries, see Appendix A, "Query Tuning"

Other Query Features

In your query application, you can use other query features such as section searching. The following table lists some of these features and shows where to look in this book for more information.

Feature	Where to Find More Information
Section Searching	Chapter 7, "CTX_DDL Package" for defining sections. WITHIN Operator in Chapter 4 for searching within sections.
Proximity Searching	NEAR (;) Operator in Chapter 4.
Stem and Fuzzy Searching	stem ($) and fuzzy (?) operators in Chapter 4 for issuing queries. "Wordlist Object" in Chapter 3 for setting the options for your language.
Thesaural Queries	Chapter 4, "Query Operators" for using thesaurus operators in queries. Thesaurus operators include SYN, BT, and NT. Chapter 11, "CTX_THES Package" for browsing and altering a thesaurus. "ctxload" in Chapter 11 for loading a thesaurus.
Case Sensitive Searching Base Letter Conversion Word Decompounding (German and Dutch) Alternate Spelling (German, Dutch, and Swedish)	"Lexer Objects" in Chapter 3 for enabling these features.
Optimizing Queries	Appendix A, "Query Tuning"
Query Explain Plan	CTX_QUERY.EXPLAIN procedure in Chapter 10.
Hierarchical Query Feedback	CTX_QUERY.HFEEDBACK procedure in Chapter 10.