Introduction:

This whitepaper/presentation describes the new features and functionality planned for the 4.8 release of DayCart. There are numerous new administrative capabilities that make managing DayCart indexes simpler, several new chemical functions and capabilities, and an entirely new architecture for the SSS/similarity indextype.

Administrative Capabilities:

• Alter index ... rename ...

  Renames and index and it's "backing tables".

• Alter index ... rebuild

  Rebuilds the internal data structure for the index. Compacts the ddblob index, removing holes from deleted data. A index drop and recreation after lots of deletes (> 20% of base table) is no longer necessary; the rebuild is much faster. It is a no-op for ddexact, ddgraph, and ddrole indexes.

• Toolkit error queue now dumped to the log for debugging.

• Import and Export of tables with DayCart indexes.

  One outstanding issue; parameters to the index in the export file.

• Physical Attributes and Storage Parameters on index creation.

  Each of the four DayCart indextypes creates tables and indexes for it's own use. The 4.7x DayCart didn't allow users to control the creation of those tables and indexes. Version 4.8 will.

  Indextype	Schema Objects Created
  ddexact	table, index
  ddgraph	table, index
  ddrole	table, index
  ddblob	table, 2 LOB columns

  Options are passed as name=value pairs in the "parameters" clause of the index creation. For example:

   create index example_indx on mytable(smiles) 
     indextype is c$dcischem.ddexact
     parameters ('table_tablespace=user_ts ' ||
                 'index_tablespace=index_ts ' ||
                 'table_storage="initial 20000000"');
  

  There are three classes of options:

  • table_### - passed to the table creation.

    table_tablespace, table_logging, table_cache, table_initrans, table_pctfree, table_pctused, table_maxtrans, table_storage

  • index_### - passed to the index creation

    index_tablespace, index_logging, index_initrans, index_pctfree, index_maxtrans, index_storage

  • lob_### - passed to the table creation as lob parameters

    lob_tablespace, lob_cache, lob_logging, lob_storage

• Index Creation Option: fpinitcolumn=<column>

  Applies only to the ddblob indextype.

  The value is the column from which the index code can take fingerprints for the initial index creation.

  Table	Column	Parameters	Creation time (mm:ss)
  NCI	SMILES	none	6:59
  NCI	FP	none	0:40
  NCI	SMILES	initfpcolumn=fp	0:49
  Spresipreps	SMILES	none	75:05
  Spresipreps	FP	none	8:26
  Spresipreps	SMILES	initfpcolumn=fp	11:27

  1x800Mhz PIII, Linux RH6.2, Oracle 8.1.6 SE, DayCart 4.8_020218
  NCI (126705 SMILES), Spresipreps (1097027 SMILES).

  IMPORTANT CAVEATS: The column is used as a convenience for index creation; no ongoing relationship between the columns is established.

  The fingerprint column must contain regular structural fingerprints for the SMILES in the indexed column. Otherwise searching will be broken using that index.

  If fingerprint values are null or the wrong size they are calculated like normal. After index creation, all fingerprints for inserts are calculated normally.

New Functionality:

• New normalization function & operator - partnorm(). An n-tuple part reordering function for normalization of part data. Works for molecules, mixtures, and reactions. For example:

  select smi2cansmi('CN(C)C.CI>>C[N+](C)(C)C.[I-]', 0),
    partnorm('CN(C)C.CI>>C[N+](C)(C)C.[I-]',
             '59.1,141.9,74.1,126.9', 1, 0) from dual;
  
  

  returns:

  CI.CN(C)C>>[I-].C[N+](C)(C)C
  141.9,59.1,126.9,74.1
  
  

• New comparison function & index/search operator - component(). This is the 'part' query. It uses the ddrole index and works for molecules, mixtures, and reactions. For example:

  component('C1CCOC1.c1ccccc1, 'C1CCOC1') => TRUE component('CI.CN(C)C>>[I-].C[N+](C)(C)C', 'CI') => TRUE

• New comparison function & index/search operator - isin(). This is the equivalent of the SMILES substructure search. That is, find all molecules in the database which can be embedded in the query, assuming opened valences (Hydrogens removed).

  isin('c1ccccc1', 'NCCc1cccc1') => TRUE

  The above example is identical in behavior to:

  contains('NCCc1ccccc1', 'c1ccccc1')

  The isin() function is necessary in DayCart solely for fast searching. Because of the way that Oracle dispatches searches to external indexes (based on the column supplied as the first argument to an operator), isin() is needed.

• CLOB Support - Long Strings in the cartridge.

  In 4.8, every DayCart function and operator which takes or returns a string (VARCHAR2 in the 4.7x cartridge) will be able to take or return a CLOB.

  Note that our actual implementation is different that what I've described at previous meetings. This is for compatability with Oracle's 9i direction with respect to CLOB support.

  • Functions which return a NUMBER:

    
    fsmi2amw(smi IN VARCHAR2) => NUMBER
    fsmi2amw(smi IN CLOB) => NUMBER
    
    fcontains(s1 IN VARCHAR2, s2 IN VARCHAR2) => NUMBER
    fcontains(s1 IN VARCHAR2, s2 IN CLOB) => NUMBER
    fcontains(s1 IN CLOB, s2 IN VARCHAR2) => NUMBER
    fcontains(s1 IN CLOB, s2 IN CLOB) => NUMBER
    
    

    All combinations of input string arguments are supported.

  • Functions which return a STRING:

    
    fsmi2cansmi(smi IN VARCHAR2, type IN NUMBER) => VARCHAR2
    fsmi2cansmi(smi IN CLOB, type IN NUMBER) => CLOB
    
    fatomnorm( smi IN VARCHAR2, list IN VARCHAR2, ...) => VARCHAR2
    fatomnorm( smi IN CLOB, list IN VARCHAR2, ...) => VARCHAR2
    fatomnorm( smi IN VARCHAR2, list IN CLOB, ...) => CLOB
    fatomnorm( smi IN CLOB, list IN CLOB, ...) => CLOB
    
    

    The returned type is determined by the type of the "key" input argument.

  • DayCart Indextypes.

    The Indextypes operate correctly on VARCHAR2 and CLOB columns and support all combinations of input string (query) arguments.

  The cool thing about this is that it's handled transparently by Oracle. Operator overloading takes care of calling the "right" function, the API that you see as a user is unchanged.

  
  SQL> desc test
   Name          Null?    Type
   ------------- -------- -------------
   SMI           NOT NULL VARCHAR2(428)
   NSC           NOT NULL NUMBER(6)
   CAS_RN        NOT NULL VARCHAR2(11)
  
  SQL> desc testc
   Name          Null?    Type
   ------------- -------- -------------
   SMI           NOT NULL CLOB
   NSC           NOT NULL NUMBER(6)
   CAS_RN        NOT NULL VARCHAR2(11)
  
  SQL> select smi, smi2amw(smi) from test;
  SMI                                      SMI2AMW(SMI)
  ---------------------------------------- ------------
  CC1=CC(=O)C=CC1=O                           122.12134
  S(Sc1nc2ccccc2s1)c3nc4ccccc4s3               332.4908
  Nc1ccc2C(=O)c3ccccc3C(=O)c2c1                223.2268
  Cc1ccc2C(=O)c3ccccc3C(=O)c2c1N(=O)=O         267.2363
  ...
  
  SQL> select smi, smi2amw(smi) from testc;
  SMI                                      SMI2AMW(SMI)
  ---------------------------------------- ------------
  CC1=CC(=O)C=CC1=O                           122.12134
  S(Sc1nc2ccccc2s1)c3nc4ccccc4s3               332.4908
  Nc1ccc2C(=O)c3ccccc3C(=O)c2c1                223.2268
  Cc1ccc2C(=O)c3ccccc3C(=O)c2c1N(=O)=O         267.2363
  ...
  
  SQL> select count(1) from test where contains(smi, 'NCCc1ccccc1') = 1
  
    COUNT(1)
  ----------
          19
  
  SQL> select count(1) from testc where contains(smi, 'NCCc1ccccc1') = 1
  
    COUNT(1)
  ----------
          19
  
  

• New architecture for ddblob indextype.

  The 4.8 cartridge will have an new data organization for the data in the DDBLOB indextype. Operationally this is transparent to the users, however the net result is that for most of the searches we can do more prescreening of the database. Performance improvements of 2 - 10X are seen in some searches.

  Tanimoto(), tversky(), euclid() and "nearest" searches on all the queries benefit most, contains(), matches() and isin() are impacted less. No searches are slower in 4.8 (so far).

  Index creation is somewhat slower; the time spent doing database I/O is somewhat higher.

  The basic idea is that instead of a single data structure containing all the SMILES and fingerprints for searching, the index is organized into multiple data structures based on the fingerprints. Depending on the query, we can eliminate from consideration large percentages of the index data without ever having to look at it.

• Better Predicate Handling. Comparison predicates in the indexes are handled more robustly. Screening is used for all cases.

  
  contains(column, query) = 1
  contains(column, query) = 0
  tanimoto(column, query) > {0.0 - 1.0}
  tanimoto(column, query) < {0.0 - 1.0}
  tanimoto(column, query) >= {0.0 - 1.0}
  tanimoto(column, query) <= {0.0 - 1.0}
  tanimoto(column, query) = {0.0 - 1.0}
  
  

  Also, as per the above examples, isin(), fingertest(), matches(), tverksy(), and euclid() behave properly.

• The "Nearest" search.

  The basic concept; find hits which match a criteria and then select based on their similarity to the target.

  The early cartridge versions has a nearest() function, but that didn't go into the 4.7 release. The idea has resurfaced in a different form.

  Using the "operator overloading", we're adding the nearest search to six of the seven search operators available in the ddblob indextype. The functions optionally take an additional numeric argument which is the maximum number of hits to return.

  
  select ... where tanimoto(column, query, 10) > 0.5;
  select ... where tversky(column, query, 0.8, 0.8, 20) > 0.8;
  select ... where euclid(column, query, 100) < 0.2;
  
  

  Conceptually, both the search criteria and the pruning of the hitlist based on number of nearest hits occur.

  The nearest idea has a directionality; for euclid() lower numbers are better, for tanimoto() and tversky() higher numbers are better.

  
  select ... where contains(column, query, 10) = 1
  select ... where contains(column, query, 10) = 0
  select ... where isin(column, query, 10) = 1
  select ... where fingertest(column, query, 10) = 1
  
  

  Contains(), isin(), and fingertest() use the tanimoto metric as the criteria for the nearest sort.

4.8 Upgrade/Compatibility:

• Versions: 8.1.7, 9.0.1+, Standard edition, Enterprise edition.

• Platforms: Solaris 7 & 8, Linux RH 7.x, IRIX6.5x for 8.1.7.

• Since the 4.8 data structures are different, a full DayCart upgrade will be required. The basic procedure will be:
  1. Drop all 4.7 DayCart indexes,
  2. Drop 4.7 DayCart (clean.sql),
  3. Create 4.8 DayCart (create.sql),
  4. Recreate 4.8 indexes.