Using Oracle Text in Instant JChem

Introduction

Oracle Text is an extension (Oracle data cartridge) to the core functionality of the Oracle Database. It's purpose is to enhance the performance and provide some additional search features on the indexed text fields. IJC uses Oracle Text search statements if it detects the Oracle Text index on the field. There are some limitations and prerequisites which we try to cover in this document from the IJC point of view as much as possible. However the most limitations and advanced settings coming from Oracle Text directly and must be handled in the database itself. For more details we recommend to read technical information on Oracle's website .

Setting up Oracle Text

First the index has to be created in the database. Again it is better to do it properly according the official documentation and your particular needs. Just as an example there is only one compulsory step to make it working. The following SQL statement will create simple Oracle Text index with default settings:

CREATE INDEX index_name ON db_table_name(text_column_name) INDEXTYPE IS CTXSYS.CONTEXT;

There are many more parameters you would like to use probably to maximize the efficiency and customize the index to your needs.
 
A more usable sample code for creating Oracle Text index use a lexer and synchronize the index every time the data are changed. It's important to have the index synchronized. Otherwise the index does not have most recent information about the values in indexed column.
 
call ctxsys.ctx_ddl.create_preference('basic','BASIC_LEXER');
create index act_list_species_idx on wombat_act_list(bio_species) indextype is ctxsys.context PARAMETERS ('LEXER basic SYNC ( ON COMMIT)');

If the index was created on an existing IJC table column. The field (or the whole table) has to be re-promoted in order to take changes in IJC schema and allow indexed search to be used in user mode. If you need help with it, please refer to [ editing fields in IJC help page |http://www.chemaxon.com/instantjchem/ijc_latest/docs/user/help/htmlfiles/editing_database/editing_fields.html#promote].
Re-promotion is not needed if the meta data in IJC_ tables are updated automatically (i.e. sometimes when IJC version is upgraded). But this is a rare case and you always be asked for permission during IJC schema initialization.

Searching with Oracle Text in IJC

The text index will be used for searching on the indexed field only when contains operator is used. This is the default operator for the text fields from IJC 6.0 (it's used when no operator is explicitely defined). All other search operators will not benefit from the index.

There are a few differences with the "classical" search definition by using contains operator.

  • Search will be case insensitive if using text index.

  • Possible usage of an additional query specification: e.g. contains rat or human

  • Only the row containing exact word as specified are returned. It means that contains hum does not retrieve human; but contains hum% does

Limitations

If you set-up "static" Oracle Text index without synchronization (first, simple example in this document), the index will be static and will only cover the text present in the time of index creation. It will not be able to recognize the data changes in the indexed columns and may not found all rows.

This and also other limitations can be overcome by the right settings in Oracle. However it's beyond the scope of this document. Please consult more details with your DB administrator.

The most important limitation coming from implementation in IJC is that you can use only the queries with CONTAINS query operator which fulfill the following syntax: CONTAINS(field_name, 'XXX') > 0 . Where XXX is a text string you search for in the IJC. You can't edit other parameters in the Text query at the moment. But you can use every operator (e.g. AND, OR, NEAR) which can be defined somewhere in XXX string.

Searching with Text indexes currently does not work for entities based on database view and for SQL Entities. We would like to make it possible to search on DB view entities in the future.