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
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);
Running 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 => ''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:
Post a Comment