Creating and Dropping Indexes

About Creating and Dropping Indexes

Text Index creation is currently supported for regular class properties that are defined as STextnnn (VARCHAR2, CHAR and LONG) logical data types. If the text string length is smaller than say 10 characters and if it contains mostly a single word, then a regular B-tree index is much better than a Text Index. A Text Index is good for a text property which contains multiple text words. The index name is created using the  CTX_<obj_id of the class property> format. The topics covered in this section are:

Creating Text Index

The creating a Text Index allows you to create a text index for a STextnnn type class property. Before creating a Text Index, you should create the Text Index tablespace CISCTX_INDEX_SPACE. See Installing Oracle Text Search Option for details.

This task explains how to create a text index:

  1. From the navigation pad, select Tools >Text Management > Text Index. The Text Index page appears.

  2. Select a language and its territory from the respective lists.

  3. Select the Class Label or Class Name.  

    A list of Class Label or Class Name appears
    .

  4. Select a class for which the Text Indexes are to be created.

    This table explains the list of eligible properties:
    Properties Description

    Property Label

    The name of the Class Property to be indexed.

    Property DB Name

    The DB name of the Class Property to be indexed.

    Data Type

    The data type of the Class Property. This can be:
    • CHAR
    • VARCHAR2 or LONG

    Length

    The length of Class Property to be indexed.

    Is Indexed

    Indicates if the Text Index already exists for the Class Property.

  5. Select a class property from the list and click Create. The Text Index is created, following which:

  • The Column Index Type attribute of the Property is updated to 9 (Text Index).

  • The Is Column Indexed attribute is updated to 1 (Yes).

Log file, cr_ctx_index_<ORACLE_SID>.log is created in the log directory. Check this log file for possible error messages. The following error messages are defined for this process:

  • Class DB Name not found.
  • Class Property DB Name not found.
  • Class Property's datatype is invalid to create a Text Index.
  • Length of Class Property must be greater than 4 characters.
  • The tablespace CISCTX_INDEX_SPACE does not exist.
     
If a Text Index is not created successfully, it is marked as created internally by Oracle. But the index cannot be used or altered. In such cases, you need to drop the Text Index and re-create it.

Dropping Text Index

 

 

This task explains how to drop a text index:
  1. From the navigation pad, select Tools >Text Management > Text Index.

    The Text Index page appears
    .

  2. To drop a Text Index, select a class property from the list and click Drop.

    After the Text Index is successfully dropped, the Is Column Indexed attribute is updated to NO.