Using jc_compare multiple times in where clause

Hello

I have been using jc_compare with great success in queries such as :

SELECT *

from structure_table

 WHERE  jc_compare(structure, 'C(C1C(C(C(C(O1)OC2C(OC(C(C2O)O)O)CO)O)O)O)O', 't:s') = 1 

However when I add an additional jc_compare clause like so :

SELECT *

from structure_table

 WHERE  jc_compare(structure, 'C(C1C(C(C(C(O1)OC2C(OC(C(C2O)O)O)CO)O)O)O)O', 't:s') = 1

         OR jc_compare(structure, 'CN1C=NC2=C1C(=O)N(C(=O)N2C)C', 't:s') = 1

Hi Steve,

If two jc_idxtype operators are combined with an
OR condition in the WHERE clause, the optimizer is willing to use
domain index scans only if the row sets resulting from the domain index
scans can be combined using a BITMAP OR operation. If the BITMAP OR operation is not available (as with Oracle 10g Standard
Edition), the optimizer will always invoke the jc_idxtype operators in
functional mode as downstream filters -- typically on top of a full
table index scan. The query plan would quickly reveal the truth.

You can try rephrasing the two jc_compare conditions as the UNION of two sub-SELECTS.

You can also try using one sinlge jc_compare with the two query structures contcatnated as the second parameter to jc_compare. (Separate the query structures with a new line character.)

Thanks

Peter

Thank you very much for your quick and clear reply Peter, this makes sense and yes, we are on Oracle SE.  The query plans were pretty bad.

I had missed the point in the documentation about separating SMILES by carriage return.  I think this will do us fine for now, and I am interested in the comment in "Future Plans" regarding more complex set operators.

Thanks,

Steve

Sorry Peter... one more thing.  Does the same thing apply re: multiple SMILES strings for the jcf.hitColorAndAlign function?  i.e can I highlight the occurrences of multiple substructures in one go?

Thanks,

Steve H

Hi Steve,

The jcf.hitColorAndAlign function accepts only one query structure -- but it's an entirely different beast performance-wise. (It accepts a comma-separated list of rowids for multiple hits, though...)

Regarding the more complex set operators: they're still on the agenda with not too high priority.

Thanks

Peter