Sunday, November 18, 2012

How To View Oracle Text Query Exec Plan

Oracle Text Query is not like normal sql which we can get sql plan via "explain plan for" "select * from table(dbms_xplan.display());".....

It has specific API to get them:

Creating the Explain Table
To create an explain table called test_explain for example, use the following SQL
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:
index_name => ''TEST_IDCTEXT2',
text_query => 'comp% OR ?smith',
explain_table => 'test_explain',
sharelevel => 0,
explain_id => 'Test');

exec ctx_query.explain(index_name => 'TEST_IDCTEXT2',text_query => '((((DEFINESCORE((test), RELEVANCE * .1)) )))  and  (((((EMPL%) WITHIN dDocAccount)) or (((CATEST1%) WITHIN dDocAccount)) or (((GIT/GLOBAL#0023IT#0023ONLY%) WITHIN dDocAccount)) or (((GIT/ALL%) WITHIN dDocAccount)) or (((idcnull) WITHIN dDocAccount))))',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 lpad('   ',level-1)||operation||' '||options||' '||object_name "Plan"
 from test_explain
  connect by prior id = parent_id
start with id=1
 order siblings by position

No comments: