Text Query Explain

1
Text Query Explain
2
Objectives

• After completing this lesson, you should be able
  to
  
• do the following
• Describe the purpose of the procedure
  Ctx_Query.Explain
  
• Invoke it from SQLPlus to output the execution
  plan of any Text Query Expression to your
  specified results table
  
• Write a SQL query (using connect by prior) to
  display the Text execution plan from the results
  table in a readable way
  
• Interpret the displayed results

3
Purpose

• Ctx_Query.Explain populates a results table with
  a hierarchy of objects (a parent_id column
  refers back to an id column)
  
• Each object is
• either a Text Query Expression operation (eg AND,
  ACCUM, EQUIV)
  
• or a term (eg dog, quick brown fox)
• Any expansions are fully resolved at the leaves
  of the hierarchy

4
The Results Table
create table ctx_explain ( explain_id
varchar2(30), id number, parent_id
number, operation varchar2(30), options
varchar2(30), object_name varchar2(64),
position number, cardinality number )
5
Ctx_Query.Explain
Ctx_Query.Explain ( index_name
'my_index', text_query p_query,
explain_table 'ctx_explain', sharelevel
0, / truncate the
o/p table / explain_id 'the_id' )
6
Displaying the Output
select lpad ( ' ', 2(level-1), ' ' )
lower ( operation ) operation,
nvl ( options, ' ' ) options,
object_name from explain_op where explain_id
'the_id' start with id 1 connect by prior
id parent_id order by id, position

• level is a pseudocolumn whose value is equal to
  the hierarchical depth given by the self
  reference parent_id to id

7
Examples

• The following slides show the Explain output for
  a selection of text query expressions
  
• think
• go
• ( think go )
• ?oracle
• ( dog , ( cat bird ) ) think
• sqe ( disasters )
• syn ( car, my_thesaurus )
• about ( Bill Clinton prefers Oracle )

8
think
OPERATION OPT OBJECT_NAME ------------ --- ---
-------- equivalence () THINK word
THINK word THINKING word
THINKS word THOUGHT
9
go
OPERATION OPT OBJECT_NAME ------------ --- ---
-------- equivalence () GO word GO
word GOES word GOING
word GONE word WENT
10
( think go )
OPERATION OPT OBJECT_NAME
-------------- --- -----------
and equivalence () THINK word
THINK word THINKING word
THINKS word THOUGHT equival
ence () GO word GO word
GOES word GOING word
GONE word WENT
11
?oracle
OPERATION OPT OBJECT_NAME ------------ --- ---
-------- equivalence (?) ORACLE word
ORACKLE word ORACLE word
ORICLE
12
( dog , ( cat bird ) ) think
OPERATION OPT OBJECT_NAME
-------------- --- -----------
not accumulate word DOG and
word CAT word BIRD
word THINK
13
sqe ( disasters )
Ctx_Query.Store_Sqe ( 'disasters',
'hurricane, earthquake, blizzard' )
OPERATION OPT OBJECT_NAME ------------ --- ---
-------- accumulate word HURRICANE
word EARTHQUAKE word BLIZ
ZARD
14
syn ( car, my_thesaurus )
Ctx_Thes.Create_Thesaurus ( name 'my_thesaur
us', casesens false ) Ctx_Thes.Create_Phrase
( tname 'my_thesaurus', phrase 'automobi
le' ) Ctx_Thes.Create_Phrase ( tname 'my
_thesaurus', phrase 'car', rel 's
yn', relname 'automobile' )
OPERATION OPT OBJECT_NAME ------------ --- ---
-------- or word AUTOMOBILE word
CAR
15
about ( Bill Clinton prefers Oracle )
OPERATION OPT OBJECT_NAME
-------------- --- -------------------------------
---- accumulate about President Wil
liam Jefferson Clinton about Bill Cl
inton about preferentialism about
Oracle Corporation
about Oracle about Bill
Clinton prefers Oracle
16
Practice .1 Overview

• Creating a one row text table with a few words
  that will match a stemming expansion and a few
  that will match a fuzzy expansion
  
• Generating and inspecting the Explain o/p for a
  text query expression with a few levels of
  parentheses to group various binary operators and
  with fuzzy and stem at the leaves

17
Summary

• Ctx_Query.Explain shows the result of parsing the
  Text Query Expression
  
• Its especially useful to investigate the
  behavior of fuzzy expansions in order to set the
  optimal fuzzy parameterization of the Text index
  
• By timing it, you can get an idea of whether or
  not parsing the Text Query expression is a
  bottleneck in your application (typically it
  isnt)
• Ctx_Query.Explain is thus a feature that the
  developer will user during application
  development but will not incorporate into a
  typical application