• Source data resides in a VARCHAR2 Smiles string
• Table column
• Object attribute

• Daylight index data structure resides in a BLOB

The index data is stored in an ancillary index table. This table contains a couple of columns. One is a Binary Large Object, also known as a BLOB (shown as HASH here). This column contains the index structure used in any queries. The second column is called REFRESH_NUMBER, and is used for caching the index (which is discussed below). This table contains exactly one row.

• Composite index data structure (Dave's invention)

• Index structure supports a number of operations:
• Equals
• Tautomers
• Contains
• Matches
• Similar, with Rank/Score
• Nearest
• LookFor

• An Oracle8i extensible index implements the following:

  DDL Methods
  ODCIindexCreate: Create the index (including any index tables)
  ODCIindexDrop: Drop the index
  ODCIindexAlter: Alter the index (as in REBUILD)
  ODCIindexTruncate: Truncate the index
  DML Methods
  ODCIindexInsert: Insert a new row
  ODCIindexDelete: Delete a row
  ODCIindexUpdate: Update a row
  Query Operator Methods
  ODCIindexStart: Called at the start of a query
  ODCIindexFetch: Returns Query results as ROWID or array of ROWID
  ODCIindexClose: Called at the end of a query
• Index methods can be written using PL/SQL, C, Java

• For each query operator:
• Index Implementation
• Functional Implementation

     create or replace type SmilesIndexIM as object
     (
         -- Query context/state
         db_loc RAW(8), 
         lob_loc RAW(8),

         -- DDL Methods
         member function ODCIGetInterfaces(...),
         member function ODCIIndexCreate(...),
         member function ODCIIndexDrop(...),

         -- DML Methods
         ODCIIndexInsert (...)  return number,
         member function ODCIIndexDelete (...) return number,
         member function ODCIIndexUpdate (...) return number,

         -- Query Operator Methods
         -- IndexStart method for Contains
         member function ODCIIndexStart(..., strt number, 
                                        stop number,
                                        cmpval varchar2)
                                        return number,
         -- IndexStart method for SIMILAR
         member function ODCIIndexStart(..., strt number, 
                                        stop number,  
                                        cmpval varchar2, 
                                        threshold float)
                                        return number,
         member function ODCIIndexFetch(nrows number, 
                                        rids OUT
                                        sys.odciridlist) 
                                        return number,
         member function ODCIIndexClose return number
     )

     create or replace indextype SmilesIndex
     for
       Contains(VARCHAR2,VARCHAR2),
       LookFor(VARCHAR2,VARCHAR2),
       Tautomers(VARCHAR2,VARCHAR2),
       Matches(VARCHAR2,VARCHAR2),
       Similar(VARCHAR2,VARCHAR2,FLOAT),
       Nearest(VARCHAR2,VARCHAR2,INTEGER)
     using SmilesIndexIM ;

     -- Create sample table of compounds
     create table Compound
     (ID	number,
      Name	varchar2(256),
      Smiles	varchar2(4000) ...
     );

     -- Create index on smiles column
     create index on Compound(Smiles) 
            INDENXTYPE IS SmilesIndex ... ;

     -- Perform similarity query and return score
     select Score(1)
     from Compound
     where Similar(Smiles, 'S=PS', .1, 1)=1;

     -- Select compounds similar to given structure

     select Name, Smiles
     from Compound
     where Contains(Smiles, 'CN(C)CCCN')=1;

     -- Select the nearest 10 compounds to given structure
     select Score(1)
     from Compound
     where Nearest(Smiles, 'S=PS', 10)=1;

The start, fetch, and close methods of a query operator run in a separate external process, called extproc. There is one external process per session. The external process is separate from the database process, and CAN NOT bring down the server in the event of a program error in the index. The data from the buffer cache is passed to the external process via standard OCI requests to read the data from the BLOB. This data is stored in memory and passed to the Dayblob interface to process the query. The results of the query are the ROWIDs which are then passed back to the database along with any ancillary information, such as score.

The index itself can be large. A 1 million compound table would have an index table that is about 136M, and the entire amount could be allocated for each session using the index. To deal with this issue, we recommend session multiplexing, discussed below.

Common queries run in a short period of time, so the best way to deal with this problem is to place an application tier between the user and the database and multiplex the requests to the available sessions in the database. This allows you to process all the queries but keep a handle on the memory usage on the server.

Caching is turned on and off via a procedure call and is under the user's control. The cache is automatically reloaded when a change occurs to the index. This synchronization is handled by a sequence number in the index table (REFRESH_NUMBER) and the sequence number of a package holding the memory for the index. When these numbers match, the memory is in synch with the table. When these numbers differ, such as when a transaction is commited in this or another session, the two numbers will be different and the index is marked to be reloaded at the start of the next query.