|Oracle Text Reference
Part Number A90121-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.
You cannot use EXPLAIN with remote 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, part_name IN VARCHAR2 DEFAULT NULL);
Specify the name of the index to be queried.
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.
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.
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.
Specify the name of the index partition to query.
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