Choral API Usage

This document describes how to use the API of JChem Choral.

Please be sure, the chemical knowledge behind this API is the same as the knowledge used by JChem Base. In the background of the chemical structure searches, the same molecular comparisons, transformations, calculations are executed as in the case of JChem Base. The differences are collected here.

Create Table

Use standard Oracle methods.

The column type of chemical structures must be CLOB.

Insert Into Table

Use standard Oracle methods.

Filter out the invalid molecules from the table

The invalid, erroneous molecules must be deleted from the table before the CREATE INDEX statement is executed. The use of isvalidmolecule function is recommended for identifying the invalid molecules.

SELECT * FROM <table_name> WHERE isvalidmolecule(<structure_column_name>)=0;

Example

SELECT + FROM mytable WHERE isvalidmolecule(mol)=0;

From version 20.10, non-valid structures do not need to be filtered out because they also will be indexed. However, they won't hit any structure, including themselves.

Create domain index as table_owner

Prerequisite:

Execute privilege on <type>_idxtype

CREATE INDEX <index_name> ON <table_name>(<structure_column_name>) INDEXTYPE IS <type>_idxtype;

Where

<type> can be one of the type file names present in /data/types/ when the service was initialized

The column to be indexed must be CLOB (Varchar2 is handled as well, but not recommended).

Example

CREATE INDEX myidx ON mytable(mol) INDEXTYPE IS choral_owner.sample_idxtype;

Use short index names!

Don't create index with column names in apostrophes. It won't fail, but the index won't be used at search!

--don't use this: CREATE INDEX <index_name> ON <table_name>('<structure_column_name>') INDEXTYPE IS <type>_idxtype;

Cache loading

The cache is loaded when the first search is executed, but it can be loaded intentionally at any time using the following statements:

call load_molecule_cache('index_owner', 'index_name');
call load_fingerprint_cache('index_owner', 'index_name');

Both statements are recommended to be executed if the necessary memory is available.

In the case of low memory setup use only the load_fingerprint_cache function.

Execute chemical searches as searcher user

Prerequisites:

Execute privilege on <type>_search and <type>_relevance operators.

Select privilege on the table.

Substructure, duplicate, superstructure, full fragment searches

Search a query structure on a target table

SELECT [/*+ <hints> */] * FROM <table_owner>.<table_name>
WHERE <choral_owner>.<type>_search(<structure_column_name>, <query_structure>, <search_type>[,integer])=1 [ORDER BY <type>_relevance(integer)];

Search a query structure on a target structure

SELECT * FROM DUAL WHERE <choral_owner>.<type>_search(<target_structure>, <query_structure>, <search_type>)=1;

Where

<type> can be one of the type file names present in /data/types/ when the service was initialized

<Search_type> can be:

SUBSTRUCTURE

DUPLICATE

SUPERSTRUCTURE

FULLFRAGMENT (Available from version 20.15)

<hints>: see Adding Hints

Examples

SELECT * FROM table_owner.mytable WHERE choral_owner.sample_search(mol, 'CCC', 'SUBSTRUCTURE')=1;
 
SELECT /*+ DOMAIN_INDEX_SORT() */ * FROM table_owner.mytable WHERE choral_owner.sample_search(mol, 'CCC','SUBSTRUCTURE',1)=1 ORDER BY choral_owner.sample_relevance(1);

Search for a limited number of the most relevant hits

Example

SELECT * FROM (
SELECT /*+ DOMAIN_INDEX_SORT() */ * FROM table_owner.mytable WHERE
choral_owner.sample_search(mol, 'benzene', 'SUBSTRUCTURE',
1) = 1 ORDER BY choral_owner.sample_relevance(1)
) WHERE rownum <= 10;

Full fragment search

From version 20.15, the following syntax is available:

Example

SELECT * FROM table_owner.mytable WHERE choral_owner.sample_search(mol, 'CCC', 'FULLFRAGMENT')=1;

In older versions than 20.15, only the following syntax works.

Full fragment search can be executed by transforming the query structure in a way that it matches only a full fragment of the target structure and by executing a substructure search on this modified query structure. The transformation adds 's*' query search property to all atoms.

Example

SELECT * FROM table_owner.mytable WHERE choral_owner.sample_search(mol, 'CCC','SUBSTRUCTURE','FULLFRAGMENT')=1;

Double bond stereo option: dbsmarkedonly

By default, CIS query matches only with CIS target and TRANS query matches only with TRANS target. If matching of CIS query with both CIS and TRANS targets or matching of TRANS query with both CIS and TRANS targets is aimed, then the query molecule has to be transformed.

The option dbsmarkedonly is provided to accomplish this transformation.

Example

SELECT * FROM table_owner.mytable WHERE choral_owner.sample_search(mol, 'C\C=C\C', 'SUBSTRUCTURE','DBSMARKEDONLY')=1;
SELECT * FROM table_owner.mytable WHERE choral_owner.sample_search(mol, 'C\C=C\C', 'SUBSTRUCTURE','DBSMARKEDONLY..FULLFRAGMENT')=1;

Ignore tetrahedral stereo

The tetrahedral stereo information stored on the query structure can be ignored using IGNORETETRAHEDRALSTEREO option.

SELECT * FROM table_owner.mytable WHERE choral_owner.sample_search(mol, 'C[C@H](N)C(O)=O', 'SUBSTRUCTURE','IGNORETETRAHEDRALSTEREO')=1;
SELECT * FROM table_owner.mytable WHERE choral_owner.sample_search(mol, 'C[C@H](N)C(O)=O', 'SUBSTRUCTURE','IGNORETETRAHEDRALSTEREO..FULLFRAGMENT')=1;

Similarity search

SELECT * FROM <table_owner>.<table_name> WHERE <choral_owner>.<type>_search(<structure_column_name>, <query_structure>, 'SIMILARITY'[,integer])<operator><threshold_number> [ORDER BY <type>_relevance(integer)];

Where

<type> can be one of the type file names present in /data/types/ when the service was initialized

<Search_type> can be:

<operator> can be: <= or >=

<threshold_number>: number between 0 and 1

Examples

SELECT * FROM table_owner.mytable WHERE choral_owner.sample_search(mol, 'CCC','SIMILARITY')>=0.9;
 
select /*+ DOMAIN_INDEX_SORT() */ * FROM table_owner.mytable
WHERE choral_owner.sample_search(mol, 'CCC','SIMILARITY',1)>=0.9
ORDER BY choral_owner.sample_relevance(1);

Example for limit

SELECT * FROM (SELECT /*+ DOMAIN_INDEX_SORT() */ * FROM table_owner.mytable
WHERE choral_owner.sample_search(mol, 'CCC','SIMILARITY',1)>=0.9
ORDER BY choral_owner.sample_relevance(1)) WHERE rownum<=100;

Example for limit and offset

SELECT * FROM (SELECT /*+ DOMAIN_INDEX_SORT() */ rownum r, t.* FROM table_owner.mytable t
WHERE choral_owner.sample_search(mol, 'CCC','SIMILARITY',1)>=0.9
ORDER BY choral_owner.sample_relevance(1))
WHERE r BETWEEN 101 AND 200 AND rownum <= 100;

Example for selecting similarity value as well

SELECT mol,choral_owner.sample_search(mol, 'CCC','SIMILARITY') FROM table_owner.mytable
WHERE choral_owner.sample_search(mol, 'CCC','SIMILARITY')>=0.9;

Reaction search

The following search types are supported not only for molecules but for reactions as well.

  • substructure search

  • superstructure search

  • full fragment search

  • duplicate search

Reaction specific query features - like different positions of the reaction arrow - are taken into account. See examples here in Table 2.

Tautomer search

If you want to execute tautomer search, the <type>_search you must refer to a molecule type with tautomer = GENERIC or tautomer = CANONIC_GENERIC_HYBRID (available from version 20.12) tautomer mode.

tautomer = GENERIC tautomer mode

How is chemical matching of the query and the target executed in tautomer search? The generic tautomer - representing all theoretically possible tautomers - of the target is matched with the query structure itself. This method is applied in substructure search, full fragment search, duplicate, and superstructure search.

tautomer = CANONIC_GENERIC_HYBRID tautomer mode

It is a hybrid tautomer search mode, generic tautomers are compared at substructure, and similarity search , while normal canonical tautomers are compared at duplicate search. In full fragment search from version 20.12 to 20.14 generic tautomers are used, while from version 20.15 normal canonical tautomers are compared.

Limitations:

  • SMARTS atoms and SMARTS bonds in the query structures are not supported.

  • The use of bond lists in query structures may slow down the search.

NOT (substructure, duplicate, etc.) search conditions

There are no specific operators for NOT (substructure, duplicate, etc.) search conditions. The SQL language offers some techniques to implement it, and be careful, because in many cases the performance impact can be huge.

1. using NOT keyword: simple to write and maintain, but it doesn't use the index, so it usually doesn't perform well, and only advised if there are some other, very selective conditions in the query:

SELECT * FROM <table_owner>.<table_name>
WHERE NOT (<choral_owner>.<type>_search(<structure_column_name>, <query_structure>, <search_type>)=1);

2. using MINUS keyword: more difficult to write and maintain, but it uses the index, so it usually performs well, and is advised unless there are other, very selective conditions in the query:

SELECT * FROM <table_owner>.<table_name>
MINUS
SELECT * FROM <table_owner>.<table_name>
WHERE <choral_owner>.<type>_search(<structure_column_name>, <query_structure>, <search_type>)=1;

Interpreting a molecule string using a specific format

As molecule strings are ambiguous in some cases, it is possible to interpret the given molecule string according to the given molecule format by the following way:

Example

'CCC{smiles}'
'CCC{smarts}'

In the query structures, the molecule strings which can be interpreted ambiguously like 'CCC' are - by default - handled as SMILES.

Chemical terms

Function chemterm makes possible to calculate chemical terms.

chemterm('chemical_term', 'structure')

Example

SELECT choral_owner.chemterm('mass','CCC') FROM DUAL;

Add chemical term column to a table

Example

ALTER TABLE mytable ADD (mass number);
UPDATE TABLE mytable SET mass=choral_owner.chemterm('mass', mol)

Calculating chemical terms on the standardized structure

Chemical term is calculated on the input structure without standardization. Combine with the standardize method to calculate chemical terms on the standardized structure:

  1. Standardize the structure before inserting into the database columns:

    INSERT INTO <your_table> (mol, ... ) VALUES (sample_standardize(<your structure>), ... )
  2. Store both the not standardized and the standardized structures:

    INSERT INTO <your_table> (mol,standardized_mol ... ) VALUES (<your structure>, choral_owner.sample_standardize(<your structure>, ... )
  3. Standardize on the fly before calling chemical terms:

    select choral_owner.chemterm(<your_chemical_terms>, choral_owner.sample_standardize(<your_structure>));

Structure checkers and fixers

The chemterm function makes possible to execute structure checker and fixer operations.

SELECT chemterm('check(''checkerAction’')','molecule') FROM DUAL;
SELECT chemterm('fix(''checkerAction->fixerAction'')','molecule') FROM DUAL;

Example:

SELECT chemterm('check(''explicith..doublebondstereoerror'')','[H]C(C)C=C |w:3.2|') FROM DUAL;
SELECT chemterm('fix(''missingatommap->mapmolecule'')','CCO') FROM DUAL;

See examples in Chemical Terms/Structure checkers documentation page. The available checkers and and their respective fixers are found here.

See the use of custom structure checkers and fixers described for JChem PostgreSQL Cartridge. The only difference for JChem Choral is the configuration file to be used in place of /etc/default/jchem-psql.
Set -Dchemaxon.structurechecker.factory.ExternalCheckerFixerConfiguration=/<dir>/externalcheckersfixers.xml value in [install dir]/run-choral.vmoptions and [install dir]/choral-service.vmoptions files.

Standardize

(Available since version 19.19.)

Calculating the standardized structure

The standardization steps are determined by the molecule type definition. The returned value is a the standardized structure in MRV format

<choral_owner>.<type>_standardize('molecule')

where

molecule = a Molecule string

<type> can be one of the type file names present in /data/types/ when the service was initialized

Example:

SELECT choral_owner.sample_standardize('C1=CC=CC=C1');

Molconvert

Conversion to molecule formats

The use of ChemAxon's MolConverter is supported with some limitations:

molconvert('structure','format')

where

structure = a Molecule in any of the following formats

format = mrv, mol, rgf, sdf, rdf, csmol, csrgf, cssdf, csrdf, cml, smiles, cxsmiles, abbrevgroup, sybyl, mol2, pdb, xyz, inchi, or name; the output options of the file formats can also be set

Example

SELECT choral_owner.molconvert('CC', 'mrv') FROM DUAL;
SELECT choral_owner.molconvert('CC', 'mrv:H') FROM DUAL

Conversion to base64 encoded binary formats (image)

Molecules can be converted to binary image formats (png, jpeg, msbmp, pov, svg, emf, tiff, eps) or other binary formats (pdf) in Base64 encoded form.

Example

SELECT choral_owner.molconvert('CC','base64:png') FROM DUAL;

isvalidmolecule

The function isvalidmolecule defines whether a given chemiical structure string represents a valid molecule or not.

isvalidmolecule('structure')

where

molecule = a Molecule string or a CLOB type column name storing chemical structures

The output is

1: valid molecule

0: invalid molecule

Example

SELECT choral_owner.isvalidmolecule('CC') FROM DUAL;

Cost estimation

Cost estimation is automatically set by the installer.

If you want to switch it off, run the sql file found in choral/sql/ folder.:

choral_disassoc_stats.sql

Performance Tuning

Below we gathered some steps that can improve the speed of select statements containing chemical search.

Choral service location

The Choral service performing the chemical searches can be installed on the same server as the Oracle server or can be installed on a separate server. If it is installed on a separate server then actions involving heavy network traffic can be affected significantly.

Some examples:

  • Chemical index creation

  • Substructure searches resulting very large amount of hits, e.g.

    SELECT COUNT(*) FROM my_big_table WHERE sample_search(mol, 'c1ccccc1', 'SUBSTRUCTURE')=1;

    Benzene ring is very frequent in organic structures thus in a table with several million structures there can be several million hits.

  • Substructure searches requiring functional execution, e.g.

    SELECT COUNT(*) FROM my_big_table WHERE sample_search(mol, 'c1ccccc1', 'SUBSTRUCTURE')=1 AND id < 100;

    In this example it is better to retrieve the first hundred IDs and check the substructure condition for each of them separately. But this will result nearly hundred calls to the service, which will be slower if it is on a separate server.

However, if the Oracle Server is heavily used for other than chemical search then it may be better to put the service on a separate machine with a good network connection because high CPU usage of the Oracle Service can slow down the highly parallelized chemical search.

Memory setup

Chemical search can reach its optimal performance if all data required for search are located in the service cache. However, it is possible that there is not enough memory available on the server to store everything in the memory, in this case we would advise to use the low memory setup, which favors storing fingerprints in the cache to storing structural representation of molecules. Fingerprints are needed in each search type, for each chemical search, having the structures available in the memory is usually required only for searches with many hits. There is a memory calculator available, which can be used to get the optimal environment set up in your case.

Notices to memory calculator

Type files to modify are located in folder data/types/ and not in the folder specified by the calculator page.

The calculated Xmx parameter must be copied into files choral-service.vmoptions and run-choral.vmoptions (not into the file specified on the calculator page).

All other properties calculated by the Xmx calculator must be copied into the configuration file config/choral.conf (not into the file specified on the calculator page).

The infix '.runtime' must be omitted from keys presented on the calculator page as the example below shows.

com.chemaxon.jchem.psql.label.cachePolicy=LRU
com.chemaxon.jchem.psql.label.cachedObjectCount=1000000
com.chemaxon.jchem.psql.molecule.cachePolicy=LRU
com.chemaxon.jchem.psql.molecule.cachedObjectCount=1000000
com.chemaxon.jchem.psql.fingerprint.cachedObjectCount=1320000

Gather statistics

Cost estimation of chemical searches is automatically set up at the installation. However, statistics should be gathered for the table that we are going to search to help Oracle optimizer:

call dbms_stats.gather_table_stats(ownname => '<PLAIN USER>', tabname => 'TABLE_NAME', estimate_percent => 100);

Alternatively, statistics can be gathered for the whole schema:

call dbms_stats.gather_schema_stats(ownname => '<PLAIN USER>', estimate_percent => 100);

Limit the size of the result set

Most of the chemical searches will be really fast if the above two steps are taken, it should take no more than some milliseconds for several million structures. But if the number of hits is very high then it can take longer. In this case it is worth limiting the number of expected results because hits are returned in the order of their relevance and thus the most relevant hits will be returned anyway. In a real time environment the user will not be able to process hundreds of thousands of hits, thus limiting it in order to improve performance is beneficial.

Example of retrieving the hundred most relevant substructure search results:

SELECT * FROM (SELECT /*+ DOMAIN_INDEX_SORT() */ * FROM table_owner.mytable
WHERE choral_owner.sample_search(mol, 'CCC','SUBSTRUCTURE',1)=1
ORDER BY choral_owner.sample_relevance(1)) WHERE rownum<=100;

Adding hints

The speed of the searches can reach its full potential by adding hints to the searches. The hint DOMAIN_INDEX_SORT() is critical to set:

DOMAIN_INDEX_SORT()
Important at chemical searches if relevance ordering is used. Doesn’t affect the search speed otherwise.
Technical background: informs the planner that the search results are already given in the order of relevance so there is no need to fetch all the values and sort it afterwards.

Avoid planning for only chemical search

Cost estimation by the Oracle Query Planner may cost much time compared to the whole search time in some cases of substructure search without other filter conditions, especially in the limited search case. In this case when only substructure search is performed in the select statement it is always better to use the domain index to perform the search then to do a full table scan. Thus, query planning is unnecessary. If a hint is given to the optimizer to use the index cost estimation will still be performed but it can be avoided with a workaround.

Here is a code example to avoid planning by introducing a dummy function and wrapping the query structure into calling this dummy function:

CREATE OR REPLACE FUNCTION avoid_planning(v varchar2) RETURN varchar2 IS
BEGIN
RETURN v;
END;
/
 
SELECT * FROM test WHERE sample_search(mol,avoid_planning('C'),'SUBSTRUCTURE') = 1;

Tuning cost factors for Oracle Query Planner

Choral cartridge provides functions to the Oracle Query Planner to enable estimating the cost of the chemical searches. Currently cost estimation can not be calibrated automatically to the given environment but the factors influencing its behavior can be changed manually.

There are six values in the CHORAL_SETTINGS table of the Choral owner. By updating these values the costs of chemical searches can be influenced.

INDEX_SCREEN_FACTOR
Factor for the cost of fingerprint screening performed using the Choral domain index. Increasing it mildly increases the cost of index usage for substructure and duplicate search.

INDEX_ABAS_FACTOR
Factor for the cost of atom by atom search performed using the Choral domain index. Increasing it significantly increases the cost of index usage for substructure search queries with many hits.

INDEX_SIMILARITY_FACTOR
Factor for the cost of similarity search performed using the Choral domain index. Increasing it increases the cost of index usage for similarity search.

FUNCTION_SCREEN_FACTOR
FUNCTION_ABAS_FACTOR
FUNCTION_SIMILARITY_FACTOR
These are the same factors respectively for functional execution.

These values can be updated to fit the current environment for example by measuring time required for the same query by executing it with index usage or functional execution and setting cost factor values to switch from one to the other at the right moment.

For example, there is a given value for the limit on the ID below where functional and index execution take roughly the same time:

SELECT COUNT(*) FROM my_big_table WHERE sample_search(mol, 'c1ccccc1Cl', 'SUBSTRUCTURE')=1 AND id<=<limit for id>;

To find this ID value, one can run the same query with hint for index or functional execution with different ID values and thus get to the value where their time match:

SELECT /*+ INDEX(my_big_table my_big_table_chemical_index) */ COUNT(*) FROM my_big_table
WHERE sample_search(mol, 'c1ccccc1Cl', 'SUBSTRUCTURE')=1 AND id<=<limit for id>;
 
SELECT /*+ NO_INDEX(my_big_table my_big_table_chemical_index) */ COUNT(*) FROM my_big_table
WHERE sample_search(mol, 'c1ccccc1Cl', 'SUBSTRUCTURE')=1 AND id<=<limit for id>;

Let us say that this value is N. When this value is found then the above described factors can be increased or decreased so that Oracle Query Planner would change the execution plan for the query

SELECT COUNT(*) FROM my_big_table WHERE sample_search(mol, 'c1ccccc1Cl', 'SUBSTRUCTURE')=1 AND id<=<limit for id>;

around the value N. The execution plan chosen by the planner can be checked by executing

EXPLAIN PLAN FOR SELECT COUNT(*) FROM my_big_table WHERE sample_search(mol, 'c1ccccc1Cl', 'SUBSTRUCTURE')=1 AND id<=<limit for id>;
SELECT * FROM table(dbms_xplan.DISPLAY);