Oracle Text Reference
Release 9.0.1

Part Number A90121-01
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback

Go to previous page Go to beginning of chapter Go to next page

CTX_QUERY Package , 4 of 7


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 a 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:

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.

Limitation

You cannot use EXPLAIN with remote 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,
    part_name      IN VARCHAR2 DEFAULT NULL
);

index_name

Specify the name of the index to be queried.

text_query

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

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.

explain_table

Specify the name of the table used to store representation of the parse tree for text_query. You must have at least INSERT and DELETE privileges on the table used to store the results from EXPLAIN.

See Also:

For more information about the structure of the explain table, see "EXPLAIN Table" in Appendix A

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.

part_name

Specify the name of the index partition to query.

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 3, "CONTAINS Query Operators"

Appendix H, "Stopword Transformations"


Go to previous page Go to beginning of chapter Go to next page
Oracle
Copyright © 1996-2001, Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback