Slow search - ChemAxon Forum Archive

User 2f347cbe91

02-09-2008 12:29:20

Hi,

I am making a search in a table with ~3000 entries with this query:

SELECT /*+ CHOOSE */ SGC.COMPOUND.MOLFILE

FROM JCHEM.MOLTABLE, SGC.COMPOUND

WHERE ((jcf_containsb(JCHEM.MOLTABLE.CD_STRUCTURE,

(select bmolfile from SGC.tempmolfile where pkey = 27365958)) = 1))

AND JCHEM.MOLTABLE.SGCID=SGC.COMPOUND.SGCID

It goes on and on without giving any result.

I have for example this indexes:

CREATE INDEX JCHEM.MOLTABLE_IDX ON JCHEM.MOLTABLE (CD_STRUCTURE)

INDEXTYPE IS JCHEM.JC_IDXTYPE

NOPARALLEL;

CREATE INDEX JCHEM.MOLTABLE_IX ON JCHEM.MOLTABLE(SGCID)

CREATE UNIQUE INDEX SGC.COMPOUND_SGCID_UNIQUE ON SGC.COMPOUND (SGCID)

Is there anything else I can do to fasten up the search?

Thanks

ChemAxon aa7c50abf8

02-09-2008 15:07:05

The first obvious modification (strongly recommended) is to use operators in such cases (after the WHERE clause) instead of functions. jcf_XXX object or objects in the jcf package are functions and the Oracle Optimizer will never use index scan. (Index scan is typically required for fast structure search.) Use jc_contains http://www.chemaxon.com/jchem/doc/guide/cartridge/cartapi.html#jc_contains or jc_compare(..., ..., 't:s') (http://www.chemaxon.com/jchem/doc/guide/cartridge/cartapi.html#jc_compare.

If this still doesn't bring sufficient improvement, I suggest rewriting the query to give more chance to the structure search being executed in index-scan mode:

Code:

SELECT SGC.COMPOUND.MOLFILE

FROM SGC.COMPOUND WHERE SGC.COMPOUND.SGCID IN (SELECT JCHEM.MOLTABLE.SGCID FROM JCHEM.MOLTABLE

WHERE jc_containsb(JCHEM.MOLTABLE.CD_STRUCTURE, (select bmolfile from SGC.tempmolfile where pkey = 27365958)) = 1)

User 2f347cbe91

09-09-2008 08:17:29

OK, when I use

SELECT /*+ CHOOSE */ SGC.COMPOUND.MOLFILE

FROM JCHEM.MOLTABLE, SGC.COMPOUND

WHERE ((jc_containsb(JCHEM.MOLTABLE.CD_STRUCTURE, (select bmolfile from SGC.tempmolfile where pkey = 27367524)) = 1)) AND JCHEM.MOLTABLE.SGCID=SGC.COMPOUND_OXFORD.SGCID

it doesn't find jc_containsb. If I add jchem.jc_containsb I get the following error message:

ORA-29902: error in executing ODCIIndexStart() routine

ORA-29532: Java call terminated by uncaught Java exception: java.rmi.ServerException: RemoteException occurred in server thread; nested exception is:

java.rmi.RemoteException: No password found. Please, set password by calling jchem_core_pkg.use_password or jchem_core_pkg.set_password.

ORA-06512: at "JCHEM.JCHEM_BLOB_PKG", line 74

ORA-06512: at "JCHEM.JC_IDXTYPE_IM", line 269

I do this as a user that has the role JCHEM_USER. Is there some grant I have missed?

Thanks