Index ProceduresΒΆ

These procedures operate on domain indexes and return PL/SQL tables. The test data must already have been loaded into Oracle to run the commands shown in this section.

TableIndexSubstructSqlFilter(<owner>, <table>, <column>, <sql_filter>, <query>, [<max_hits>], [<query_type>]) returns a PL/SQL table of ROWIDS for the index owner.table.column for structures which match both the SMARTS substructure query and the sql_filter. The sql_fiter query should return a single column of ROWIDS, which are then tested against the substructure query. Set query_type = ‘mdl’ to use an MDL mol block as a query (use the ‘|’ character to represent newline in the query). Note that the SQL in sql_filter will be run by the C$ARNACHM1 user so full schema names of tables are required.

select id
   from nci_open m
  where rowid in
   (select * from
     table(
      C$ARNACHM1.chem_structure.tableIndexSubstructSqlFilter
       ('ARNACHM1_TEST', 'NCI_OPEN', 'SMILES',
           'select rowid from ARNACHM1_TEST.nci_open where rownum < 30000',
           '[#6]c2nc1ccccc1o2', -1)));

TableIndexSimilaritySqlFilter(<owner>, <table>, <column>, <sql_filter>, <query>, <max_hits>) returns a PL/SQL table of ROWIDS and smilarity scores for the index owner.table.column for structures which both pass the sql_filter and have at least min_similarity similarity with the query structure. The sql_fiter query should return a single column of ROWIDS, which are then tested against the similarity query. Note that the SQL in sql_filter will be run by the C$ARNACHM1 user so full schema names of tables are required.

select id, score
  from table(
    C$ARNACHM1.chem_structure.tableIndexSimilaritySqlFilter
     ('ARNACHM1_TEST', 'NCI_OPEN', 'SMILES',
      'select rowid from arnachm1_test.nci_open where rownum < 30000',
      'CCCc1ccc(cc1)S(=O)(=O)Nc2cc(on2)C',
      0.7, -1)) s,
    nci_open m
 where m.rowid = s.hit_rowid;

Previous topic

Utility Functions

Next topic

Implementation Notes