3. Extensible Indexes:

3.1 General Comments

The default installation (create.sql) creates public synonyms for the package 'ddpackage' and all of the operators. The one place where synonyms aren't allowed is in indextype specifications; indextypes must be refered to by their full names when creating indexes as a user other than c$dcischem (eg. create index <name> on small(smi) indextype is c$dcischem.ddblob).

All four of the extensible indexes create Oracle tables to maintain their required internal data. The ddexact, ddgraph, and ddrole indextypes use regular Oracle tables to store the data with a regular Oracle index for fast retrieval; the ddblob indextype uses an Oracle table with a rows containing BLOB data. These internal tables and indexes are maintained automatically by the index code; there is normally no need for a user or application developer to know about these auxillary tables.

There are two situations where the developer or DBA must pay attention to these auxillary tables. First, on index creation, the auxillary data tables and indexes are created in the users default tablespace, using default storage parameters. The auxillary table creation can be modified by using numerous optional parameters to the 'create index' command. Second, if an index gets corrupted, the developer or DBA may need to delete the auxillary data table and index manually.

3.2 Exact Lookup Indextype (ddexact)

This indextype supports the exact() operator. This is the least interesting index, as it implements a simple string comparison between the indexed column and the query. In that sense, the built in BTree index within Oracle has the similar functionality. The BTree index even supports the "greater than" and "less than" operators, which this index doesn't.

The main advantages of the ddexact indextype is that it supports the direct comparison of CLOB datatypes in SQL; the built-in BTree index does not. Also, in some situations, the BTree index has limits on the length of VARCHAR2 string which may be indexed.

This indextype uses an auxillary data table within Oracle to maintain its index data. This table is kept in sync with the base table in real time and strictly obeys the Oracle transaction model for all DML operations. The name of the auxillary data table is: <index name> || '_DDT'. An Oracle BTree index is also created. The index is named: <index name> || '_DDI'.

The index can be created on a VARCHAR2 or CLOB column with the following general statement:

  SQL> create index <index name> on <table> ( <column> )
         indextype is c$dcischem.ddexact;

The valid operator comparison using the index are:

   exact(...) = 1
   exact(...) = 0

Note that the "!=" comparison does not use the index and will use the functional form. All other predicate comparisons will return the error: "C$DCISCHEM-024: Incorrect predicate for index operator".

This index makes no assumptions about the content of the indexed column. The applications are responsible for normalizing both the data in the indexed column and and queries to the exact() operator. This allows the ddexact indextype to properly handle both Unique and Absolute SMILES without complication. In the following example, the USMILES column is normalized using smi2cansmi(smiles, 0) and the ASMILES column is normalized using smi2cansmi(smiles, 1). Note also that when performing the exact() query on a column, the query is normalized using the same method as the column data.

  SQL> create table test (id number, usmiles varchar2(4000),
         asmiles varchar2(4000));

  SQL> desc test;
  Name                               Null?    Type
  --------------------------------- -------- ----------------------------
  ID                                         NUMBER
  USMILES                                    VARCHAR2(4000)
  ASMILES                                    VARCHAR2(4000)

  SQL> insert into test values (1234, smi2cansmi('Cl/C=C/Cl', 0),
                                      smi2cansmi('Cl/C=C/Cl', 1));

  SQL> select * from test;
  
        ID  USMILES   ASMILES
  --------  -------   -------
    1234    ClC=CCl   Cl/C=C/Cl

  SQL> create index usmi_index on test(usmiles) indextype 
         is c$dcischem.ddexact;
  SQL> create index asmi_index on test(asmiles) indextype 
         is c$dcischem.ddexact;

  SQL> select * from test 
         where exact(usmiles, smi2cansmi('Cl/C=C/Cl', 0)) = 1;

        ID  USMILES   ASMILES
  --------  -------   -------
    1234    ClC=CCl   Cl/C=C/Cl

  SQL> select * from test 
         where exact(asmiles, smi2cansmi('Cl/C=C/Cl', 1)) = 1;

        ID  USMILES   ASMILES
  --------  -------   -------
    1234    ClC=CCl   Cl/C=C/Cl

NOTE: Because this indextype accesses the base table during a index scan, operations which modify the base table based on the results of the index scan aren't allowed. This is similar to the case where a trigger on a column attempts to modify the column data. The message which one receives is the following:

  SQL> delete from <table> where exact(smiles, 'CCN') = 1;

  ERROR at line 1:
  ORA-29903: error in executing ODCIIndexFetch() routine
  ORA-29400: data cartridge error
  ERROR ORA-04091: table C$DCISCHEM.T1 is mutating, trigger/function may
  not see it
  C$DCISCHEM-101: CARTRIDGE ERROR
  ORA-06512: at "C$DCISCHEM.DDEXACT_IM", line 159
  ORA-06512: at line 1

In order to perform this function, one must use two steps. One option would be:

  SQL> create table <temptable> (zapcol) as
          select rowid from <table> where exact(smiles, 'CCN') = 1;
  SQL> delete from <table> where rowid in 
          (select zapcol from <temptable>)

3.3 Graph and Tautomer Indextype (ddgraph)

This indextype supports the graph(), tautomer(), usmiles() and asmiles() operators. It creates a tabular index of the graph and tautomeric information (hydrogen count and net charge) for rapid retrieval. The indexed search only requires an index lookup based on the graph of the query, followed by a secondary comparison of the net charge, hydrogen count, unique smiles, or absolute smiles, depending on the search type.

This indextype uses a special data table within Oracle to maintain its index data. This table is kept in sync with the base table in real time and strictly obeys the Oracle transaction model for DML operations. The name of the auxillary data table is: <index name> || '_DDT'. An Oracle BTree index is also created. The index is named: <index name> || '_DDI'.

The index can be created on a VARCHAR2 or CLOB column with the following general statement:

  SQL> create index <index name> on <table> ( <SMILES column> )
         indextype is c$dcischem.ddgraph;

The valid operator comparison using the index are:

   graph(...) = 1
   graph(...) = 0
   tautomer(...) = 1
   tautomer(...) = 0
   usmiles(...) = 1
   usmiles(...) = 0
   asmiles(...) = 1
   asmiles(...) = 0

Note that the "!=" comparison does not use the index and will use the functional form. All other predicate comparisons will return the error: "C$DCISCHEM-024: Incorrect predicate for index operator".

Unlike the ddexact index, the ddgraph indextype always interprets the column as SMILES. Invalid SMILES are rejected. There is no concern with the ddgraph indextype as to whether the SMILES are Unique or Absolute, as stereochemical and isomeric information are discarded for the graph() and tautomer() lookups and the isomeric information is handled properly for the usmiles() and asmiles() lookups.

NOTE: As with the ddexact indextype, this indextype accesses the base table during a index scan. See the previous section on the limitations to performing modifications of the base table based on the results of an index scan.

3.4 Role Indextype (ddrole)

This indextype supports component level searches for exact molecules within molecules or reactions by role. It creates a tabular index of each component in every SMILES by role. Component level searches are implemented simply by a index lookup of the component, followed by validation of the role.

This indextype uses a special data table within Oracle to maintain its index data. This table is kept in sync with the base table in real time and strictly obeys the Oracle transaction model for all DML operations. The name of the auxillary data table is: <index name> || '_DDT'. An Oracle BTree index is also created. The index is named: <index name> || '_DDI'.

The index can be created on a VARCHAR2 or CLOB column with the following general statement:

  SQL> create index <index name> on <table> ( <SMILES column> )
         indextype is c$dcischem.ddrole;

The valid operator comparison using the index are:

   reactant(...) = 1
   agent(...) = 1
   product(...) = 1
   component(...) = 1

Note that the "!=" comparison does not use the index and will use the functional form. All other predicate comparisons will return the error: "C$DCISCHEM-024: Incorrect predicate for index operator". Also note that, unlike the other three indextypes, in 4.81 the 'inverse' queries are not implemented (eg. component = 0). These will be added in a later release.

A component is defined as a single SMILES part of a larger molecule or reaction SMILES. A component is a useful concept for finding a molecule within a larger mixture, or finding a molecule which appears anywhere within a reaction. In all cases queries must be single component SMILES, else no hits are found. See the section on the component() operator for examples.

Like the ddexact index, the ddrole index makes no assumptions about the canonicalization of the SMILES in the index column. Hence, the column must be normalized by the application to the appropriate SMILES and the query on the column must match that normalization (see the explanation re. ddexact above).

NOTE: As with the ddexact indextype, this indextype accesses the base table during a index scan. See the previous section on the limitations to performing modifications of the base table based on the results of an index scan.

3.5 SMARTS and Similarity Search Indextype (ddblob)

This index caches the SMILES and fingerprints in a table of binary large objects for rapid searching. Substructure and similarity searches are implemented through this blob-based image of the structural data. The blobs are persistant; they are automatically stored in the Oracle database and retrieved for processing. The blobs are kept synchronized with the base table in real time and strictly obey the Oracle transaction model for all DML operations. The name of the auxillary data table which contains the BLOB data is: <index name> || '_DDT'.

The index can be created on either a SMILES or fingerprint column, provided that the fingerprints are all the same size. If the index is created on a SMILES column, all seven searches are implemented on that column: contains(), isin(), matches(), tanimoto(), tversky(), euclid(), similarity(), and fingertest(). If the index is created on a fingerprint column, the four fingerprint-specific searches (tanimoto(), tversky(), euclid(), and fingertest()) are supported for that column.

The query for tanimoto(), tversky(), euclid(), similarity(), and fingertest() can be either a fingerprint or a SMILES. If a SMILES, it will be used to create a normal fingerprint of the appropriate size for the index which will be used for comparison. If the query is a fingerprint, the size of the fingerprint must match the size of the fingerprint in the column (or in the blob for a SMILES index). The indexes and operators never perform automatic folding of fingerprints during searches.

The index can be created on a VARCHAR2 or CLOB column with the following general statements:

  -- Default internal fingerprint size of 512 bits

  SQL> create index <index name> on 
         <table> ( <SMILES column> ) indextype is c$dcischem.ddblob;

  -- Specify internal fingerprint size of 64 bits

  SQL> create index <index name> on 
         <table> ( <SMILES column> ) indextype is c$dcischem.ddblob
         parameters ('fpsize=64');

  -- Rather than generating the fingerprints, perform the initial index
     creation from the fp column

  SQL> create index <index name> on 
         <table> ( <SMILES column> ) indextype is c$dcischem.ddblob
         parameters ('initfpcolumn=<FP column>');

  -- Rather than generating the scaffold data, perform the initial index
     creation from the sc column

  SQL> create index <index name> on 
         <table> ( <SMILES column> ) indextype is c$dcischem.ddblob
         parameters ('initsccolumn=<SC column>');

  -- Use stored fingerprints and scaffold data to create the index

  SQL> create index <index name> on 
         <table> ( <SMILES column> ) indextype is c$dcischem.ddblob
         parameters ('initfpcolumn=<FP column> initsccolumn=<SC column>');

  -- Fpsize taken from size of data in FP column (fpsize parameter, if it were
     specified, must match the size of data in the column)

  SQL> create index <index name> on 
         <table> ( <FP column> ) indextype is c$dcischem.ddblob;

The index creation command takes three optional parameters: fpsize, initfpcolumn, and initsccolumn. The 'fpsize' parameter is the number of bits that the blob index will use for fingerprint creation.

When indexing a SMILES column, the ddblob indextype will use the 'fpsize' parameter for the creation size for fingerprints within the internal blob index or a default value of 512 bits if the 'fpsize' is not specified.

When indexing a fingerprint column, all fingerprints within that column must match the 'fpsize' parameter. If the 'fpsize' parameter is not specified, the size of the first fingerprint encountered by the index code is used. Any fingerprints which do not match this size are rejected by the index.

The 'initfpcolumn' and 'initsccolumn' parameters give the names of the columns which contain the initial set of fingerprints and scaffolds for index creation on a SMILES column. Normally, when creating a ddblob index on a column of SMILES, fingerprints and scaffold data must be generated for every SMILES in the table. The 'initfpcolumn' and 'initsccolumn' options allow the first creation to occur by loading the data from a separate columns in the same base table.

These two options, if provided, are only used during index creation. They are only for convenience. Inserts, deletes, and updates of the SMILES column occur via the normal mechanism. Inserts, deletes, and updates of the given FP and SC columns do not affect the SMILES index after index creation (there is no ongoing transactional integrity between the given FP and SC columns and the index). The options are solely for index creation efficiency; the pre-generated fingerprints and scaffold data can be stored in the base table with the SMILES to decrease computational overhead for ddblob index creation. Fingerprints and scaffold data which are present in the given columns are assumed to be correct. Null data in the columns cause the fingerprints and scaffold data to be computed by the ddblob indextype code.

The valid operator comparison using the index are:

   contains(...) = 1
   contains(...) = 0
   matches(...) = 1
   matches(...) = 0
   isin(...) = 1
   isin(...) = 0
   fingertest(...) = 1
   fingertest(...) = 0

Tanimoto(), tversky(), and euclid() functions return values in the range of 0.0 - 1.0, inclusive. Similarity() returns numeric values based on the expression provided. These index operators support the full range of comparison functions: ">", ">=", "<", "<=", and "=".

Note that the "!=" comparison does not use the index and will use the functional form. All other predicate comparisons will return the error: "C$DCISCHEM-024: Incorrect predicate for index operator".

The index operators all take an optional numeric parameter which is the desired count of rows returned. The query will return up to the number of rows desired. The rows returned are those "nearest" to the query which match the predicate criteria. Note that "nearest" means larger values for tanimoto() and tversky() and smaller values for euclid(). In the case of contains() and isin(), the value used for the nearest comparison is the computed tanimoto() value. In the case of matches(), the value used is the number of bits set in each target hit.

For the similarity() operator, the default behavior is to 'figure out' whether the given expression is a distance (eg. smaller is better) or a similarity (eg. larger is better) from the expression. If needed, this can be overridden with an addition to the expression argument. The valid values are:

      similarity(arg1, arg2, 'similarity=')
      similarity(arg1, arg2, 'distance=')

Note that these are only used internally for the index implementation when the optional 'count' parameter is provided. This allows the index to return the 'count' best hits, taking into account the sense of the comparison.

  -- Returns the ten best hits.  These will be the ten highest tanimoto
  -- values in the table.

  select * from test where tanimoto(smiles, 'NCCc1ccccc1', 10) >= 0.0;

  -- Returns the ten best hits.  These will be the ten smallest euclidean
  -- distance values.

  select * from test where euclid(smiles, 'NCCc1ccccc1', 10) >= 0.0;

  -- Returns the ten best hits, provided that they are all greater than
  -- 0.9 tanimoto.  Otherwise, just returns those.

  select * from test where tanimoto(smiles, 'NCCc1ccccc1', 10) > 0.9;

  -- Returns the ten best hits below 0.5 tanimoto.

  select * from test where tanimoto(smiles, 'NCCc1ccccc1', 10) < 0.5;

For a simple table, one need not create a fingerprint column or use the fingerprint indexes. One only needs to create an index on a SMILES column to enable all structural searching functions.

  -- We specified a preferred size for the internally generated fingerprints
  -- as 1024 bits, overriding the default of 512 bits.  These fingerprints
  -- are internal to the blob and aren't visible.

  SQL> create index asmi_blob_index on test(asmiles) indextype is
         c$dcischem.ddblob parameters ('fpsize=1024');
  
  SQL> select * from test where matches(asmiles, '[N;H1,H2]ccC=O') = 1;
  SQL> select * from test where tanimoto(asmiles, 'c1ccccc1') > 0.8;

  -- A fingerprint can be used directly as a query for tanimoto(),
  -- euclid(), fingertest() and tversky() instead of a SMILES.  The 
  -- fingerprint size must match the size of the fingerprints used in
  -- index creation.  In this case, 1024 bits.

  SQL> select * from test where 
         tanimoto(asmiles, smi2fp('OC(=O)CS', 0, 7, 1024)) > 0.8;

If one wants structural searches on multiple SMILES columns within a single table, then an index must be created on each SMILES column in the table. Furthermore, we can create and index fingerprint columns also. The index automatically will adjust fingerprint operations based on the size of the data in the fingerprint column. So, for example, if a column of fingerprints is added to our test table and populated:

  SQL> alter table test add (fp varchar (15));
  SQL> update test set fp = smi2fp(usmiles, 0, 7, 64);

  -- This index, on the FP column, uses the size of the fingerprints in 
  -- the column (64 bits, in this case) in index creation.

  SQL> create index fp_blob_index on test(fp) indextype is
         c$dcischem.ddblob;

  -- As with an index on a SMILES column, a SMILES query or a fingerprint
  -- can be used as a query for tanimoto(), euclid(), fingertest(),
  -- similarity(), and tversky().

  SQL> select smi2fp('OC(=O)CS', 0, 7, 64) from dual;

  SMI2FP('OC(=O)CS',0,7,64)
  --------------------------
  IM9vA4w127g.2

  -- The following two queries give identical results.

  SQL> select * from test where tanimoto(fp, 'IM9vA4w127g.2') > 0.8;
  SQL> select * from test where tanimoto(fp, 'OC(=O)CS') > 0.8;

There are some additional subtlties explained using the following example queries:

  -- This screens and searches using the index; internally it uses 1024 bit
  -- fingerprints.  The output is generated using the functional form, at 
  -- its default of 512 bits.  Remember that the functional forms will use a
  -- fingerprint size of 512 bits unless one or both of the parameters is a 
  -- fingerprint.  The tanimoto values output by the functional form may not
  -- match our search criteria based on the folding-related differences
  -- between 1024-bit and 512-bit fingerprints.

  SQL> select smiles, tanimoto(asmiles, 'NCCc1ccccc1') from test
         where tanimoto(asmiles, 'NCCc1ccccc1') > 0.8;

  -- This screens and searches using the index; internally using 1024 bit
  -- fingerprints.  The output is generated using the functional form; this
  -- time calculated at 1024 bits.  By passing a fingerprint of 1024 bits
  -- to the functional form of tanimoto(), we force its use of 1024 bits.
  -- In this case, the tanimoto values output by the functional form will
  -- exactly match those used by the index.

  SQL> select smiles, tanimoto(asmiles, smi2fp('NCCc1ccccc1', 0, 7, 1024))
         from test where tanimoto(asmiles, 'NCCc1ccccc1') > 0.8;

  -- This searches using the index f1, using the 128 bit fingerprints from
  -- the actual column for comparison.  Again, the tanimoto values computed
  -- by the functional form are calculated at 512 bits and will likely not
  -- match those used by the index for the query.

  SQL> select smiles, tanimoto(fp, 'NCCc1ccccc1') from test
         where tanimoto(fp, 'NCCc1ccccc1') > 0.8;

3.6 Alter Index Command

The alter index command provides the ability to perform maintainence operations on the indexes themselves. At this time the only supported maintainence operations are: renaming an index and rebuilding an index.

Indexes of all four extensible indextypes can be renamed with the alter index ... rename command:

SQL> alter index <oldname> rename to <newname>;

Renames an extensible index and the auxillary tables which are associated with it.

The alter index ... rebuild command causes the index to be dropped and recreated from scratch. For the ddblob indextype, the special parameters clause "parameters ('compress=true')" causes the ddblob indextype to be repacked, rather than rebuilt. For the ddblob indextype, when a row is deleted from the base table on which a ddblob index exists unused space is left in the index data structure. Over time, this unused space will cause a decrease in search performance. After many delete or update operations it is recommended that the index either be rebuild or compressed. The compress operation is typically faster than a full rebuild. Generally, after 20% of the rows in a table have been deleted or updated it is advantagous to compress a ddblob index.

SQL> alter index <indexname> rebuild;

Rebuilds the index. Reclaims unused space, rebuilds all hashtables, checks consistancy of the index.

3.7 Import / Export of Daycart Indexes

Oracle supports the import and export of domain-specific indexes. The Daycart indexes can be exported and imported along with other database objects.

The default behavior of Oracle is that when one exports a table which has a domain index created on one of it's columns, the description of that domain index is stored in the export file with the base table. On import, Oracle will attempt to recreate the domain index on the target machine after the base table data has been loaded.

In order for this to work properly, several guidelines must be followed:

• Derived data tables that Daylight uses do not need to be exported explicitly. All of the tables with names ####_DDT should not be exported with the base table data. Only the base tables should be exported.
• If one performs a user or database export, the derived tables will be skipped automatically by Oracle. This is appropriate; the derived data will be regenerated during the import on the target instance.
• the Daylight cartridge must be installed on the target Oracle instance before beginning the import operation.

Import and export of Daycart indexes is generally transparent if one doesn't attempt to deal explicitly with the derived tables. Note than an import operation will include index creation time, so long runtimes during the import step are not unusual.

3.8 Index Creation Options

A large number of optional parameters can be passed as part of a Daycart index creation. These are passed in the 'parameters' field of the create index function:

SQL> create index my_daycart_index on my_table (my_column) 
        indextype is c$dcischem.ddexact 
        parameters ('table_tablespace=users logging=false');

The parameters are a whitespace-separated list of name=value pairs. The double-quote character can be used to include whitespace in a parameter value.

There are several parameters which are specific to the ddblob indextype and which control the fingerprint generation for that indextype. Those parameters are discussed in the ddblob indextype section. The parameters discussed here are used to modify the storage parameters for the auxillary tables and indexes which are created. Each of the parameters described in this section result in the modification of "CREATE TABLE" or "CREATE INDEX" commands within the Daycart code.

The following options apply to all Daycart indextypes (ddexact, ddgraph, ddrole, ddblob) and modify the characteristics of the auxillary data table created for the index:

table_tablespace=<value>

Sets the tablespace for the INDEX_DDT table on creation. See the Oracle documentation for "CREATE TABLE ... TABLESPACE <value> ..." for more information.

table_logging=<TRUE|FALSE>

Sets the logging/nologging value for the INDEX_DDT table on creation. A value of TRUE enables logging, FALSE disables logging. See the Oracle documentation for "CREATE TABLE ... LOGGING for more information.

table_cache=<TRUE|FALSE>

Sets the cache/nocache value for the INDEX_DDT table on creation. A value of TRUE enables caching, FALSE disables caching. See the Oracle documentation for "CREATE TABLE ... CACHE for more information.

table_initrans=<value>
table_pctfree=<value>
table_pctused=<value>
table_maxtrans=<value>

Sets initrans, pctfree, pctused, and maxtrans values for the INDEX_DDT table on creation. See the Oracle documentation for "CREATE TABLE" for more information on each of these physical attributes and their use.

table_storage=<value>

Sets storage clause for the INDEX_DDT table on creation. Since the storage clause often contains multiple delimited values this clause often requires additional quoting. Use double-quotes within the parameters value. For example:

CREATE INDEX my_index on my_table(my_column) 
indextype is c$dcischem.ddexact
parameters ('table_storage="minextents 2 maxextents 5"');

The following options apply to the ddexact, ddgraph, and ddrole indextypes and modify the characteristics of the auxillary index which is created as part of the Daycart index:

index_tablespace=<value>

Sets the tablespace for the INDEX_DDI index on creation. See the Oracle documentation for "CREATE INDEX ... TABLESPACE <value> ..." for more information.

index_logging=<TRUE|FALSE>

Sets the logging/nologging value for the INDEX_DDI index on creation. A value of TRUE enables logging, FALSE disables logging. See the Oracle documentation for "CREATE INDEX ... LOGGING for more information.

index_initrans=<value>
index_pctfree=<value>
index_maxtrans=<value>

Sets initrans, pctfree, pctused, and maxtrans values for the INDEX_DDI index on creation. See the Oracle documentation for "CREATE INDEX" for more information on each of these physical attributes and their use.

index_storage=<value>

Sets storage cluase for the INDEX_DDI index on creation. Since the storage clause often contains multiple delimited values this clause often requires additional quoting. The proper quoting syntax is double-quotes for the index_storage value. For example:

SQL> CREATE INDEX my_index on my_table(my_column) 
      indextype is c$dcischem.ddexact
      parameters ('index_storage="minextents 2 maxextents 5"');

internally results in:

CREATE INDEX MY_INDEX_DDI ... STORAGE ( minextents 2 maxextents 5 ) ...

The following options only apply to the ddblob indextype and modify the characteristics of the BLOB datatypes which are created as part of the Daycart index:

lob_tablespace=<value>

Sets the tablespace for the BLOB datatypes on creation. See the Oracle documentation for "CREATE TABLE ... LOB (...) STORE AS ( TABLESPACE <value> ) ..." for more information.

lob_cache=<TRUE|FALSE>

Sets the cache/nocache value for the BLOB datatypes on creation. A value of TRUE enables caching (and forces logging), FALSE disables caching.See the Oracle documentation for "CREATE TABLE ... LOB (...) STORE AS ( CACHE ) ... " for more information. The default behavior, for efficiency sake, is that logging is disabled during index creation.

lob_logging=<TRUE|FALSE>

Sets the logging/nologging value for the BLOB datatypes on creation. A value of TRUE enables logging, FALSE disables logging. See the Oracle documentation for "CREATE TABLE ... LOB (...) STORE AS ( LOGGING ) ... " for more information. The default behavior, for efficiency sake, is that logging is disabled during index creation.

lob_storage=<value>

Sets storage cluase for the BLOB datatypes on creation. Since the storage clause often contains multiple delimited values this clause often requires additional quoting. The proper quoting syntax is double-quotes for the index_storage value. For example:

SQL> CREATE INDEX my_index on my_table(my_column) 
      indextype is c$dcischem.ddblob
      parameters ('index_storage="minextents 2"');

internally results in:

CREATE TABLE MY_INDEX_DDT ... LOB (...) STORE AS ( STORAGE (minextents 2) ) ...

3.9 Partitioned Domain Indexes

When creating a Daycart index against a range-partitioned table, use the "local" clause on the indextype to indicate that a partitioned index is desired. One can also provide names for the index partitions:

 SQL< create index idx_test_blob on test(isomer)
      indextype is ddblob local
      (partition pe1, partition pe2, partition pe3)

The names "pe1", "pe2" and "pe3" are used for the creation of the backing index tables (eg. *_DDT). When a DayCart partitioned indextype is modified using the "alter index" command, the underlying *_DDT table is modified by DayCart. The user does not need to perform any other maintanence within DayCart.

Individual partitions within a range-partitioned index can be rebuilt with:

 SQL> alter index idx_test_blob rebuild partition pe2;

Individual partitions within a range-partitioned index can be renamed with:

 SQL> alter index idx_test_blob rename partition pe2 to pe2x;

Most other operations on partitioned tables are transparently handled by Daycart. More information about range partitioned tables and Oracle indextypes can be found in the Oracle documentation at: http://otn.oracle.com.

One note for Oracle version 10g:

Oracle 10g now allows for a short recovery time of dropped tables and indexes. Basically this works just like a recycle bin on a desktop computer. Dropped tables and indexes are kept in an "Oracle internal Recycle bin state" until the space is needed again. This means that while the space used is free for new create statements, the tablespace will show the space taken by objects which start with "$BIN...". You can issue the SQL command "purge recyclebin" to remove these ghost objects from the object tables. More information can be found in the Oracle documentation at http://otn.oracle.com.