9
CTX_QUERY Package

This chapter describes the CTX_QUERY PL/SQL packages for generating query feedback, counting hits, and creating stored query expressions.

The CTX_QUERY package includes the following procedures and functions:

Name	Description
COUNT_HITS	Returns the number hits to a query.
EXPLAIN	Generates query expression parse and expansion information.
HFEEDBACK	Generates hierarchical query feedback information (broader term, narrower term, and related term).
REMOVE_SQE	Removes a specified SQE from the SQL tables.
STORE_SQE	Executes a query and stores the results in stored query expression tables.

---

COUNT_HITS

Returns the number of hits for the specified query. You can call COUNT_HITS in exact or estimate mode. Exact mode returns the exact number of hits for the query. Estimate mode returns an estimate but runs faster than exact mode.

Syntax

COUNT_HITS ( 
    index_name  IN VARCHAR2, 
    text_query  IN VARCHAR2, 
    exact       IN BOOLEAN  DEFAULT TRUE 
) RETURN NUMBER; 

index_name

Specify the index name.

text_query

Specify the query.

exact

Specify TRUE for an exact count. Specify FALSE for an upper-bound estimate.

Notes

Specifying FALSE returns a less accurate number but runs faster.

If the query contains structured criteria, you should use SELECT COUNT(*).

---

EXPLAIN

Use CTX_QUERY.EXPLAIN to generate explain plan information for a query expression. The EXPLAIN plan provides a graphical representation of the parse tree for a Text query expression. This information is stored in result table.

This procedure does not execute the query. Instead, this procedure can tell you how a query is expanded and parsed before you issue the query. This is especially useful for stem, wildcard, thesaurus, fuzzy, soundex, or about queries. Parse trees also show the following information:

• order of execution (precedence of operators)

• ABOUT query normalization

• query expression optimization

• stop-word transformations

• breakdown of composite-word tokens

Knowing how Oracle evaluates a query is useful for refining and debugging queries. You can also design your application so that it uses the explain plan information to help users write better queries.

Syntax

CTX_QUERY.EXPLAIN(
           index_name     IN VARCHAR2,
           text_query     IN VARCHAR2,
           explain_table  IN VARCHAR2,
           sharelevel     IN NUMBER DEFAULT 0,
           explain_id     IN VARCHAR2 DEFAULT NULL);

index_name

Specify the name of the index for the text column to be queried.

text_query

Specify the query expression to be used as criteria for selecting rows.

explain_table

Specify the name of the table used to store representation of the parse tree for text_query.

See Also: For more information about the structure of the explain table, see "EXPLAIN Table" in Appendix B.

sharelevel

Specify whether explain_table is shared by multiple EXPLAIN calls. Specify 0 for exclusive use and 1 for shared use. This parameter defaults to 0 (single-use).

When you specify 0, the system automatically truncates the result table before the next call to EXPLAIN.

When you specify 1 for shared use, this procedure does not truncate the result table. Only results with the same explain_id are updated. When no results with the same explain_id exist, new results are added to the EXPLAIN table.

explain_id

Specify a name that identifies the explain results returned by an EXPLAIN procedure when more than one EXPLAIN call uses the same shared EXPLAIN table. This parameter defaults to NULL.

Notes

You must have at least INSERT and DELETE privileges on the table used to store the results from EXPlAIN.

When you include a wildcard, fuzzy, or soundex operator in text_query, this procedure looks at the index tables to determine the expansion.

Wildcard, fuzzy (?), and soundex (!) expression feedback does not account for lazy deletes as in regular queries.

You cannot use EXPLAIN with remote queries.

Example

Creating the Explain Table

To create an explain table called test_explain for example, use the following SQL statement:

create table test_explain(
         explain_id varchar2(30)
         id number,
         parent_id number,
         operation varchar2(30),
         options varchar2(30),
         object_name varchar2(64),
         position number,
         cardinality number);

Executing CTX_QUERY.EXPLAIN

To obtain the expansion of a query expression such as comp% OR ?smith, use CTX_QUERY.EXPLAIN as follows:

ctx_query.explain(
         index_name => 'newindex',
         text_query => 'comp% OR ?smith',
         explain_table => 'test_explain',
         sharelevel => 0,
         explain_id => 'Test');

Retrieving Data from Explain Table

To read the explain table, you can select the columns as follows:

select explain_id, id, parent_id, operation, options, object_name, position
from test_explain order by id;

The output is ordered by ID to simulate a hierarchical query:

EXPLAIN_ID    ID PARENT_ID OPERATION    OPTIONS OBJECT_NAME POSITION 
----------- ---- --------- ------------ ------- ----------- -------- 
Test           1         0 OR           NULL    NULL          1 
Test           2         1 EQUIVALENCE  NULL    COMP%         1
Test           3         2 WORD         NULL    COMPTROLLER   1 
Test           4         2 WORD         NULL    COMPUTER      2 
Test           5         1 EQUIVALENCE  (?)     SMITH         2 
Test           6         5 WORD         NULL    SMITH         1 
Test           7         5 WORD         NULL    SMYTHE        2 

Related Topics

Chapter 4, "Query Operators"

Appendix I, "Stopword Transformations"

---

HFEEDBACK

Generates hierarchical query feedback information (broader term, narrower term, and related term) for the specified query.

Broader term, narrower term, and related term information is obtained from the knowledge base. However, only knowledge base terms that are also in the index are returned as query feedback information. This increases the chances that terms returned from HFEEDBACK produce hits over the currently indexed document set.

Hierarchical query feedback information is useful for suggesting other query terms to the user.

Syntax

CTX_QUERY.HFEEDBACK(
           index_name     IN VARCHAR2,
           text_query     IN VARCHAR2,
           feedback_table IN VARCHAR2,
           sharelevel     IN NUMBER DEFAULT 0,
           feedback_id    IN VARCHAR2 DEFAULT NULL,
           );

index_name

Specify the name of the index for the text column to be queried.

text_query

Specify the query expression to be used as criteria for selecting rows.

feedback_table

Specify the name of the table used to store the feedback terms.

See Also: For more information about the structure of the explain table, see "HFEEDBACK Table" in Appendix B.

sharelevel

Specify whether feedback_table is shared by multiple HFEEDBACK calls. Specify 0 for exclusive use and 1 for shared use. This parameter defaults to 0 (single-use).

When you specify 0, the system automatically truncates the feedback table before the next call to HFEEDBACK.

When you specify 1 for shared use, this procedure does not truncate the feedback table. Only results with the same feedback_id are updated. When no results with the same feedback_id exist, new results are added to the feedback table.

feedback_id

Specify a value that identifies the feedback results returned by a call to HFEEDBACK when more than one HFEEDBACK call uses the same shared feedback table. This parameter defaults to NULL.

Example

Create HFEEDBACK Result Table

Create a result table to use with CTX_QUERY.HFEEDBACK as follows:

 
  CREATE TABLE restab ( 
    feedback_id VARCHAR2(30), 
    id          NUMBER, 
    parent_id   NUMBER, 
    operation   VARCHAR2(30), 
    options     VARCHAR2(30), 
    object_name VARCHAR2(80), 
    position    NUMBER, 
    bt_feedback ctx_feedback_type, 
    rt_feedback ctx_feedback_type, 
    nt_feedback ctx_feedback_type 
  ) NESTED TABLE bt_feedback STORE AS res_bt 
    NESTED TABLE rt_feedback STORE AS res_rt 
    NESTED TABLE nt_feedback STORE AS res_nt; 
 

CTX_FEEDBACK_TYPE is a system-defined type in the CTXSYS schema.

.

Call CTX_QUERY.HFEEDBACK

The following code calls the hfeedback procedure with the query computer industry.

BEGIN 
ctx_query.hfeedback (index_name     => 'my_index', 
                     text_query     => 'computer industry', 
                     feedback_table => 'restab', 
                     sharelevel     => 0, 
                     feedback_id    => 'query10' 
                    ); 
END; 

Select From the Result Table

The following code extracts the feedback data from the result table. It extracts broader term, narrower term, and related term feedback separately from the nested tables.

DECLARE 
  i NUMBER; 
BEGIN 
  FOR frec IN ( 
    SELECT object_name, bt_feedback, rt_feedback, nt_feedback  
    FROM restab 
    WHERE feedback_id = 'query10' AND object_name IS NOT NULL 
  ) LOOP 
 
    dbms_output.put_line('Broader term feedback for ' || frec.object_name || 
':'); 
    i := frec.bt_feedback.FIRST; 
    WHILE i IS NOT NULL LOOP 
      dbms_output.put_line(frec.bt_feedback(i).text); 
      i := frec.bt_feedback.NEXT(i); 
    END LOOP; 
 
    dbms_output.put_line('Related term feedback for ' || frec.object_name || 
':'); 
    i := frec.rt_feedback.FIRST; 
    WHILE i IS NOT NULL LOOP 
      dbms_output.put_line(frec.rt_feedback(i).text); 
      i := frec.rt_feedback.NEXT(i); 
    END LOOP; 
 
    dbms_output.put_line('Narrower term feedback for ' || frec.object_name || 
':'); 
    i := frec.nt_feedback.FIRST; 
    WHILE i IS NOT NULL LOOP 
      dbms_output.put_line(frec.nt_feedback(i).text); 
      i := frec.nt_feedback.NEXT(i); 
    END LOOP; 
 
  END LOOP; 
END;

Sample Output

The following output is for the example above, which queries on computer industry:

Broader term feedback for computer industry: 
hard sciences 
Related term feedback for computer industry: 
computer networking 
electronics 
knowledge 
library science 
mathematics 
optical technology 
robotics 
satellite technology 
semiconductors and superconductors 
symbolic logic 
telecommunications industry 
Narrower term feedback for computer industry: 
ABEND - abnormal end of task 
AT&T Starlans 
ATI Technologies, Incorporated 
ActivCard 
Actrade International Ltd. 
Alta Technology 
Amiga Format 
Amiga Library Services 
Amiga Shopper 
Amstrat Action 
Apple Computer, Incorporated
.....





Note:



The HFEEDBACK information you obtain depends on the contents of your index and knowledge base and as such might differ from above.  

---

REMOVE_SQE

The CTX_QUERY.REMOVE_SQE procedure removes the specified stored query expression.

Syntax

CTX_QUERY.REMOVE_SQE(query_name IN VARCHAR2);

query_name

Specify the name of the SQE to be removed.

Examples

begin
ctx_query.remove_sqe('disasters');
end;

---

STORE_SQE

This procedure creates a stored query expression (SQE). Only the query definition is stored.

Syntax

CTX_QUERY.STORE_SQE(query_name      IN VARCHAR2,
                    text_query      IN VARCHAR2);

query_name

Specify the name of the SQE to be created. If you are CTXSYS, you can specify this as user.name.

text_query

Specify the query expression to be associated with sqe_name.

Examples

begin
ctx_query.store_sqe('disasters', 'hurricanes | earthquakes');
end;

Notes

SQEs support all of the Text query expression operators. SQEs also support all of the special characters and other components that can be used in a query expression, including other SQEs.

Users are allowed to create and remove SQEs owned by them. Users are allowed to use SQEs owned by anyone. The CTXSYS user can create or remove SQEs for any user.

---