• Dayblob procedures
• Index build/update
• Search

• Oracle8i extensible index methods
• DDL
• DML
• Query
• Source data table

 
• Index table

 

 
 
 
 
 
 
 
 
 
 

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

• Daylight index data structure resides in a BLOB

• Composite index data structure (Dave's invention)

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

• An Oracle8i extensible index implements the following methods:

    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(...)  return number, 
  member function ODCIIndexCreate(...)  return number, 
  member function ODCIIndexDrop(...)  return number, 
  
  -- DML Methods 
  member function 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), 
  Similar(varchar2, varchar2, float), 
  ... 

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) using 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; 

---

Resource Considerations  
 
 

---

Session Multiplexing