APPENDIX C. ConText Index Tables and Indexes

ConText Index Tables
Oracle Indexes for ConText Index Tables
SQR Table

This appendix contains detailed information about the database tables and Oracle indexes that are created by ConText Option when a text or theme index is created for a column.

The following topics are covered in this appendix:

ConText Index Tables

Oracle Indexes for ConText Index Tables

SQR Table

ConText Index Tables

ConText index tables are created automatically by ConText Option during text and theme indexing.

The five digit number nnnnn is the identifier for the policy that owns the index. Each of the ConText index tables for a policy has the same five digit identifier.

Note: The ConText index tables are internal tables and should not be accessed directly. To perform administrative tasks on a ConText index, use the CTX_DDL package.

DR_nnnnn_I1Tn

This is the main table of a ConText index. It stores each indexed word from the text column, as well as a reference to the documents in which the word occurs and the location of each occurrence.

The n appended to the end of the table name is an internal identifier (value of 1 or 2) which ConText Option uses to prevent table name collisions when two-table compaction or two-table combined real deletion and compaction are used to optimize the ConText index for a table.

Note: The appended n is transparent to users because a synonym called DR_nnnnn_I1T which points to DR_nnnnn_I1Tn is automatically updated after two-table index optimization to point to the appropriate table.

For more information about two-table index optimization, see "Text Index" in "Text Concepts."

Name Type Description
WORD_TEXT VARCHAR2(64) Indexed word
FIRST_DOC NUMBER(38) DOCID of first document in WORD_INFO
DOCLSIZE NUMBER(38) Size, in bytes, of WORD_INFO string
WORD_INFO LONG RAW String identifying DOCIDs for all documents in which the indexed word occurs and location of each occurrence.

DR_nnnnn_KTB

This internal table maps the textkey for each indexed document to a document identifier (DOCID). ConText indexes use DOCIDs internally to identify the documents in which indexed words occur. ConText indexes also use DOCIDs to track documents that have been deleted or modified through a DML action.

Name Type Description
TEXTKEY VARCHAR2(256) ID for document in text table
DOCID NUMBER(38) ID for document in index

DR_nnnnn_LST

This internal table generates the unique document IDs (DOCID) used in a ConText index. It also stores the next available DOCID and the DOCIDs of documents that have been modified or deleted from the text table.

Name Type Description
SID NUMBER(38) Audit session ID of the ConText server which is currently creating an index
IDCOUNT NUMBER(38) Maximum value for DOCIDs
LTYPE VARCHAR2(32) Status of DOCID in index:
F = Free, D = Deleted
CONTIGUOUS NUMBER(2) Indicates the range of DOCIDs is contiguous for the current indexing (ensures that no overlapping DOCIDs are used in index)
DATA VARCHAR2(1024) If LTYPE = F, DOCID for next insert;
If LTYPE = D, DOCID for deleted document

DR_nnnnn_I1W

This internal table stores each word identified by the Soundex function and the groups to which the word belongs. This table is only created when you index a table with a policy that includes Soundex (SOUNDEX_AT_INDEX attribute enabled for the GENERIC WORD LIST Tile).

Name Type Description
WORD VARCHAR2(15) Word identified by Soundex
GROUP1 VARCHAR2(15) ID for 1st Soundex group to which word belongs
GROUP2 VARCHAR2(15) Reserved for future use
GROUP3 VARCHAR2(15) Reserved for future use

Oracle Indexes for ConText Index Tables

The Oracle indexes for a ConText index are created automatically by ConText Option after the index tables have been populated with the ConText index information.

ConText Option creates a total of four Oracle indexes for the three ConText index tables created during indexing. The Oracle indexes follow the naming conventions used to name the index tables, where the five digit number nnnnn is the internal identifier for the policy that owns the ConText index.

CTX_nnnnn_I1In Oracle index for CTX_nnnnn_I1Tn

CTX_nnnnn_KID Oracle index for CTX_nnnnn_KTB

CTX_nnnnn_KIK Oracle index for CTX_nnnnn_KTB

CTX_nnnnn_KSQ Oracle index for CTX_nnnnn_KTB

CTX_nnnnn_LIX Oracle index for CTX_nnnnn_LST

SQR Table

The SQR table is created automatically by ConText Option during text and theme indexing of a text column; however, the table is not populated until a stored query expression (SQE) is created (stored) for the policy of the text column.

The five digit number nnnnn is the identifier for the policy that owns the SQE.

Note: The SQR table is an internal table and should not be accessed directly. To perform administration tasks on an SQE, use the CTX_QUERY package.

For more information about creating SQEs and the CTX_QUERY package, see Oracle ConText Option Application Developer's Guide.

DR_nnnnn_SQR

This internal table stores the results of an SQE. The definition of the SQE is stored in an internal table owned by CTXSYS.

Name Type Description
QUERY_NAME VARCHAR2(32) Name of SQE
SESSION_ID VARCHAR2(32) SQE type (session or system)
FIRST_DOC VARCHAR2(38) Doc ID for the first document retrieved by SQE
QUERY_RESULT LONG RAW Binary string containing results of SQE

Oracle Index for DR_nnnnn_SQR

During creation of the SQR table, an Oracle index, DR_nnnnn_SRI, is created on the table.

Library

Product

Contents

Index

`Name`	`Type`	`Description`
`WORD_TEXT`	`VARCHAR2(64)`	`Indexed word`
`FIRST_DOC`	`NUMBER(38)`	`DOCID of first document in WORD_INFO`
`DOCLSIZE`	`NUMBER(38)`	`Size, in bytes, of WORD_INFO string`
`WORD_INFO`	`LONG RAW`	`String identifying DOCIDs for all documents in which the indexed word occurs and location of each occurrence.`