Document Management with Oracle Text - Page 3

Oracle Text Installation Check

The Oracle interMedia software is installed as a part of the standard installation.

All of the Oracle Text index information is kept in the database repository under user CTXSYS. To install Oracle Text, you need to select Oracle interMedia from the menu.

We can check for the existence of the objects under CTXSYS schema and the version of Oracle Text:

SQL> connect ctxsys/ctxsys
Connected.
SQL> select * from ctx_version;
 
VER_D
-----
8.1.7

SQL> select object_type, count(*)  from user_objects group by object_type order by 1; 
 
OBJECT_TYPE          COUNT(*)
------------------ ----------
FUNCTION                    1
INDEX                      41
INDEXTYPE                   2
LIBRARY                     2
LOB                         1
OPERATOR                    3
PACKAGE                    40
PACKAGE BODY               34
PROCEDURE                   2
SEQUENCE                    3
TABLE                      32
TYPE                        5
TYPE BODY                   4
VIEW                       43
 

Text Index Inventory

Oracle Text Index settings are written in the ctx_user_preference_values view:

SQL> select * from ctx_user_preference_values;
 
PRV_PREFERENCE                 PRV_ATTRIBUTE                  PRV_VALUE
--------------------------------------------------------------------------------------------
DEFAULT_LEXER                  BASE_LETTER                    YES
DEFAULT_LEXER                  COMPOSITE                      GERMAN
DEFAULT_LEXER                  MIXED_CASE                     NO
DEFAULT_LEXER                  INDEX_TEXT                     YES
DEFAULT_LEXER                  INDEX_THEMES                   NO
DEFAULT_LEXER                  ALTERNATE_SPELLING             GERMAN
DEFAULT_STORAGE                R_TABLE_CLAUSE                 lob (data) store as (cache)
DEFAULT_STORAGE                I_INDEX_CLAUSE                 compress 2
DEFAULT_WORDLIST               STEMMER                        ENGLISH
DEFAULT_WORDLIST               FUZZY_MATCH                    GENERIC
MY_LEXER                       COMPOSITE                      GERMAN
MY_LEXER                       MIXED_CASE                     YES
MY_LEXER                       ALTERNATE_SPELLING             GERMAN
URL_DATASTORE                  TIMEOUT                        30
URL_DATASTORE                  MAXTHREADS                     8
URL_DATASTORE                  URLSIZE                        256
URL_DATASTORE                  MAXURLS                        256
URL_DATASTORE                  MAXDOCSIZE                     2097152

These settings are an overview of Oracle Text settings. In this example, the listed settings come from the German language Oracle Text installation. An existence and the status of Text index is checked via user_indexes view:

select index_name name, index_type "TYPE 1", ityp_owner "OWNER",ityp_name "TYPE 2",
domidx_opstatus   status from user_indexes where ityp_name="CONTEXT"

NAME          TYPE 1       OWNER        TYPE 2       STATUS 
------------  ------------ ------------ ------------ -------- 
KB_INDEX      DOMAIN       ARTIST       CONTEXT      VALID
WEB_TEXT      DOMAIN       WEB_ARTIST   CONTEXT      VALID
TEST_IDX      DOMAIN       CTXTEST      CONTEXT      INVALID
WEB_TEXT_GER  DOMAIN       WEB_ARTIST   CONTEXT      VALID

We have four Text indexes defined in the database. One of them is invalid.

The following select will provide information about indexed columns and indexed documents:

SQL> select IDX_OWNER#,IDX_NAME,IDX_TABLE#,IDX_KEY_NAME, IDX_DOCID_COUNT from DR$INDEX;

IDX_OWNER# IDX_NAME              IDX_TABLE#  IDX_KEY_NAME             IDX_DOCID_COUNT
---------------------------------------------------------------------------------------
       107 KB_INDEX            274239       ID,DOCU                   99014
       108 WEB_TEXT            276487       PRODUCT_DETAIL            99332
        81 TEST_IDX            191146       NR                        2
        97 WEB_TEXT_GER        238978       PRODUCT_DETAIL            97368

Listing content for the WEB_TEXT_GER index:

SQL> select token_text from ARTIST.DR$WEB_TEXT_GER$I;

TOKEN_TEXT
----------------
900782
387869
MANTEL
124
221
ANDEREN
EF
26.17
150005
AUFLOESUNGEN 

The Oracle Text index WEB_TEXT_GER belongs to the user WEB_ARTIST and was created on the table WEB_SHOP_GER:

SQL> desc web_artist.web_shop_ger

 Name                          Null?    Type
 ----------------------------- -------- --------------
 ID                            NOT NULL NUMBER(38)
 NAME                          NOT NULL VARCHAR2(100)
 LOCNLS                        NOT NULL VARCHAR2(7)
 GLOBNLS                       NOT NULL VARCHAR2(7)
 DATE_MODIFIED                 NOT NULL DATE
 MODIFIED_BY                   NOT NULL VARCHAR2(30)
 CREATED_BY                    NOT NULL VARCHAR2(30)
 PRODUCT_DETAIL                NOT NULL VARCHAR2(4000)

We can test index functionality using a content-based query:

SQL> select product_detail from web_artist.web_shop_ger 
	where contains (product_detail, 'surestore') > 0;

PRODUCT_DETAIL
--------------------------------------------------------------------------------
Streamer, Hewlett-Packard Surestore Backup, 8GB Kapazit\303\244t, extern, SCSI-Schnittstell

The result set is document rows containing the word surestore.