19.4 CTX_REPORT Package

Use the CTX_REPORT package to produce reports on indexes and queries. These reports can help you fine-tune or troubleshoot your applications.

See Also:

Oracle Text Reference for more information about this package

The CTX_REPORT package contains the following procedures:

CTX_REPORT.DESCRIBE_INDEX and CTX_REPORT.DESCRIBE_POLICY

These procedures create reports that describe an existing index or policy, including the settings of the index metadata, the indexing objects, the settings of the attributes of the objects, and (for CTX_REPORT.DESCRIBE_INDEX) the index partition information, if any. These procedures are especially useful for diagnosing index-related problems.

This is sample output from DESCRIBE_INDEX, run on a simple context index: 

=================================================================
                        INDEX DESCRIPTION
=================================================================
index name:                      "DR_TEST"."TDRBPRX0"
index id:                        1160
index type:                      context
base table:                      "DR_TEST"."TDRBPR"
primary key column:              ID
text column:                     TEXT2
text column type:                VARCHAR2(80)
language column:
format column:
charset column:
=================================================================
                          INDEX OBJECTS
=================================================================
datastore:                       DIRECT_DATASTORE
filter:                          NULL_FILTER
section group:                   NULL_SECTION_GROUP
lexer:                           BASIC_LEXER
wordlist:                        BASIC_WORDLIST
   stemmer:                         ENGLISH
   fuzzy_match:                     GENERIC
stoplist:                        BASIC_STOPLIST
   stopword:                       teststopword
storage:                         BASIC_STORAGE
   r_table_clause:                  lob (data) store as (cache)
   i_index_clause:                  compress 2

CTX_REPORT.CREATE_INDEX_SCRIPT and CTX_REPORT.CREATE_POLICY_SCRIPT

CREATE_INDEX_SCRIPT creates a SQL*Plus script that can create a duplicate of a given Oracle Text index. Use this when you have an index but you do not have the original script (if any) that was used to create this index, and you want to be able to re-create the index. For example, if you accidentally drop a script, CREATE_INDEX_SCRIPT can re-create it. Likewise, CREATE_INDEX_SCRIPT can be useful if you have inherited indexes from another user but not the scripts that created them.

CREATE_POLICY_SCRIPT does the same thing as CREATE_INDEX_SCRIPT, except that it enables you to re-create a policy instead of an index.

This is sample output from CREATE_INDEX_SCRIPT, run on a simple context index (not a complete listing): 

begin
  ctx_ddl.create_preference('"TDRBPRX0_DST"','DIRECT_DATASTORE');
end;
/
...
/
begin
  ctx_ddl.create_section_group('"TDRBPRX0_SGP"','NULL_SECTION_GROUP');
end;
/
...
begin
  ctx_ddl.create_preference('"TDRBPRX0_WDL"','BASIC_WORDLIST');
  ctx_ddl.set_attribute('"TDRBPRX0_WDL"','STEMMER','ENGLISH');
  ctx_ddl.set_attribute('"TDRBPRX0_WDL"','FUZZY_MATCH','GENERIC');
end;
/
begin
  ctx_ddl.create_stoplist('"TDRBPRX0_SPL"','BASIC_STOPLIST');
  ctx_ddl.add_stopword('"TDRBPRX0_SPL"','teststopword');
end;
/
...
/
begin
  ctx_output.start_log('TDRBPRX0_LOG');
end;
/
create index "DR_TEST"."TDRBPRX0"
  on "DR_TEST"."TDRBPR"
      ("TEXT2")
  indextype is ctxsys.context
  parameters('
    datastore       "TDRBPRX0_DST"
    filter          "TDRBPRX0_FIL"
    section group   "TDRBPRX0_SGP"
    lexer           "TDRBPRX0_LEX"
    wordlist        "TDRBPRX0_WDL"
    stoplist        "TDRBPRX0_SPL"
    storage         "TDRBPRX0_STO"
  ')
/

CTX_REPORT.INDEX_SIZE

This procedure creates a report of the names of the internal index objects, along with their tablespaces, allocated sizes, and used sizes. It is useful for DBAs who may need to monitor the size of their indexes (for example, when disk space is at a premium).

Sample output from this procedure looks like this (partial listing):

=================================================================
                INDEX SIZE FOR DR_TEST.TDRBPRX10
=================================================================
TABLE:                          DR_TEST.DR$TDRBPRX10$I
TABLESPACE NAME:                DRSYS
BLOCKS ALLOCATED:                                            4
BLOCKS USED:                                                 1
BYTES ALLOCATED:                               8,192 (8.00 KB)
BYTES USED:                                    2,048 (2.00 KB)

INDEX (LOB):                    DR_TEST.SYS_IL0000023161C00006$$
TABLE NAME:                     DR_TEST.DR$TDRBPRX10$I
TABLESPACE NAME:                DRSYS
BLOCKS ALLOCATED:                                            5
BLOCKS USED:                                                 2
BYTES ALLOCATED:                             10,240 (10.00 KB)
BYTES USED:                                    4,096 (4.00 KB)

INDEX (NORMAL):                 DR_TEST.DR$TDRBPRX10$X
TABLE NAME:                     DR_TEST.DR$TDRBPRX10$I
TABLESPACE NAME:                DRSYS
BLOCKS ALLOCATED:                                            4
BLOCKS USED:                                                 2
BYTES ALLOCATED:                               8,192 (8.00 KB)
BYTES USED:                                    4,096 (4.00 KB)

CTX_REPORT.INDEX_STATS

INDEX_STATS produces a variety of calculated statistics about an index, such as how many documents are indexed, how many unique tokens in the index, average size of its tokens, and fragmentation information for the index. Optimizing stoplists is an example of a use for INDEX_STATS.

CTX_REPORT.QUERY_LOG_SUMMARY

This procedure creates a report of logged queries, which you can use to perform simple analyses. With query analysis, you can find out:

  • Which queries were made

  • Which queries were successful

  • Which queries were unsuccessful

  • How many times each query was made

You can combine these factors in various ways, such as determining the 50 most frequent unsuccessful queries made by your application.

CTX_REPORT.TOKEN_INFO

TOKEN_INFO helps you diagnose query problems. For example, use it to check that index data is not corrupted and to find out which documents are producing unexpected or bad tokens.

CTX_REPORT.TOKEN_TYPE

TOKEN_TYPE is a lookup function that is used mainly as input to other functions (CTX_DDL.OPTIMIZE_INDEX, CTX_REPORT.TOKEN_INFO, and so on).

See Also: