Result Tables, 2 of 4

CTX_QUERY Result Tables

For the CTX_QUERY procedures that return results, tables for storing the results must be created before the procedure is called. The tables can be named anything, but must include columns with specific names and data types.

This section describes the following types of result tables, and their required columns:

EXPLAIN Table

Table B-1 describes the structure of the table to which CTX_QUERY.EXPLAIN writes its results.

Table B-1

Column Name	Datatype	Description
EXPLAIN_ID	VARCHAR2(30)	The value of the explain_id argument specified in the FEEDBACK call.
ID	NUMBER	A number assigned to each node in the query execution tree. The root operation node has ID =1. The nodes are numbered in a top-down, left-first manner as they appear in the parse tree.
PARENT_ID	NUMBER	The ID of the execution step that operates on the output of the ID step. Graphically, this is the parent node in the query execution tree. The root operation node (ID =1) has PARENT_ID = 0.
OPERATION	VARCHAR2(30)	Name of the internal operation performed. Refer to Table B-2 for possible values.
OPTIONS	VARCHAR2(30)	Characters that describe a variation on the operation described in the OPERATION column. When an OPERATION has more than one OPTIONS associated with it, OPTIONS values are concatenated in the order of processing. See Table B-3 for possible values.
OBJECT_NAME	VARCHAR2(80)	Section name, wildcard term, weight, or threshold value or term to lookup in the index.
POSITION	NUMBER	The order of processing for nodes that all have the same PARENT_ID.The positions are numbered in ascending order starting at 1.
CARDINALITY	NUMBER	Reserved for future use. You should create this column for forward compatibility.

Operation Column Values

Table B-2 shows the possible values for the OPERATION column of the explain and hfeedback tables.

Table B-2

Operation Value	Query Operator	Equivalent Symbol
ABOUT	ABOUT	(none)
ACCUMULATE	ACCUM	,
AND	AND	&
COMPOSITE	(none)	(none)
EQUIVALENCE	EQUIV	=
MINUS	MINUS	-
NEAR	NEAR	;
NOT	NOT	~
NO_HITS	(no hits will result from this query)
OR	OR	\|
PHRASE	(a phrase term)
SECTION	(section)
THRESHOLD	>	>
WEIGHT	*	*
WITHIN	within	(none)
WORD	(a single term)

OPTIONS Column Values

The following table list the possible values for the OPTIONS column of the explain table.

Table B-3

Options Value	Description
($)	Stem
(?)	Fuzzy
(!)	Soundex
(T)	Order for ordered Near.
(F)	Order for unordered Near.
(n)	A number associated with the max_span parameter for the Near operator.

HFEEDBACK Table

Table B-4 describes the table to which CTX_QUERY.HFEEDBACK writes its results.

Table B-4

Column Name	Datatype	Description
FEEDBACK_ID	VARCHAR2(30)	The value of the feedback_id argument specified in the HFEEDBACK call.
ID	NUMBER	A number assigned to each node in the query execution tree. The root operation node has ID =1. The nodes are numbered in a top-down, left-first manner as they appear in the parse tree.
PARENT_ID	NUMBER	The ID of the execution step that operates on the output of the ID step. Graphically, this is the parent node in the query execution tree. The root operation node (ID =1) has PARENT_ID = 0.
OPERATION	VARCHAR2(30)	Name of the internal operation performed. Refer to Table B-2 for possible values.
OPTIONS	VARCHAR2(30)	Characters that describe a variation on the operation described in the OPERATION column. When an OPERATION has more than one OPTIONS associated with it, OPTIONS values are concatenated in the order of processing. See Table B-5 for possible values.
OBJECT_NAME	VARCHAR2(80)	Section name, wildcard term, weight, threshold value or term to lookup in the index.
POSITION	NUMBER	The order of processing for nodes that all have the same PARENT_ID.The positions are numbered in ascending order starting at 1.
BT_FEEDBACK	CTX_FEEDBACK_TYPE	Stores broader feedback terms. See Table B-6.
PT_FEEDBACK	CTX_FEEDBACK_TYPE	Stores related feedback terms. See Table B-6.
NT_FEEDBACK	CTX_FEEDBACK_TYPE	Stores narrower feedback terms. See Table B-6.

OPTIONS Column Values

The following table list the values for the OPTIONS column of the feedback table.

Table B-5

Options Value	Description
(T)	Order for ordered Near.
(F)	Order for unordered Near.
(n)	A number associated with the max_span parameter for the Near operator.

CTX_FEEDBACK_TYPE

The CTX_FEEDBACK_TYPE is a nested table of objects. This datatype is pre-defined in the ctxsys schema. Use this type to define the columns BT_FEEDBACK, RT_FEEDBACK, and NT_FEEDBACK.

The nested table CTX_FEEDBACK_TYPE holds objects of type CTX_FEEDBACK_ITEM_TYPE, which is also pre-defined in the ctxsys schema. This object is defined with three members and one method as follows:

Table B-6

CTX_FEEDBACK_ITEM_TYPE Members and Methods	Type	Description
text	member	Feedback term.
cardinality	member	(reserved for future use.)
score	member	(reserved for future use.)
rank	method	(reserved for future use)

The SQL code that defines these objects is as follows:

CREATE OR REPLACE TYPE ctx_feedback_type AS TABLE OF ctx_feedback_item_type;
 
CREATE OR REPLACE TYPE ctx_feedback_item_type AS OBJECT 
(text        VARCHAR2(80), 
 cardinality NUMBER, 
 score       NUMBER, 
 MAP MEMBER FUNCTION rank RETURN REAL, 
 PRAGMA RESTRICT_REFERENCES (rank, RNDS, WNDS, RNPS, WNPS) 
); 
 
CREATE OR REPLACE TYPE BODY ctx_feedback_item_type AS 
   MAP MEMBER FUNCTION rank RETURN REAL IS 
   BEGIN 
      RETURN score; 
   END rank; 
END;

See Also:
For an example of how to select from the hfeedback table and its nested tables, refer to CTX_QUERY.HFEEDBACK in Chapter 10.