To create an index for a varchar2 column containing molecules represented as SMILES strings use:
create index molecules_index on nci_open(smiles) indextype is c$arnachm1.structureIndexType;
To create an index for a CLOB column containing molecules represented as MDL MOL or SDF blocks use:
create index molecules_index2 on nci_open(sdf) indextype is c$arnachm1.structureIndexType;
To create an index for a BLOB column containing molecules encoded as OEMol byte arrays use:
create index molecules_index3 on nci_open(oemol) indextype is c$arnachm1.structureIndexType;
Creating an index may require several minutes per 100K structrues. To run all the commands in this section, the index must be created beforehand. The option exists for this index to be created during the automated install process.
To commit the change log table into the serialized Java object (emptying the change log table for the index) use:
alter index molecules_index rebuild;
To completely rebuild the index (deleting the current change log table and serialized Java object then rebuilding that object) use:
alter index molecules_index rebuild parameters('full');
To load the index into the RMI server memory use:
alter index molecules_index parameters('load');
To unload/remove the index from the memory of the RMI server use:
alter index molecules_index parameters('unload');
To add all molecules in the index to the RMI server OEMol structure cache use:
alter index molecules_index parameters('add_to_cache');
This command will do nothing if the cache is not enabled.
The index will automatically adjust during column renames, table renames and table truncates.
To delete the index use:
drop index molecules_index;