|Oracle8i interMedia Text Reference
Release 2 (8.1.6)
Part Number A77063-01
CTX_QUERY Package , 4 of 7
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.
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);
Specify the name of the index for the text column to be queried.
Specify the query expression to be used as criteria for selecting rows.
Specify the name of the table used to store representation of the parse tree for text_query.
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.
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.
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.
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);
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');
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