hang using jcf_ with Oracle 9i

Hello,





Is there a topic that covers your known issue:





"Indexing hangs on large tables when using Oracle 9i


Indexing columns containing more than 1 million structures never completes. Workaround: Either specify the buildtype=ext parameter for the index or upgrade to Oracle 10g. "





Presumably this parameter "buildtype=ext" is specified when you create a new index for your Oracle 9i table...is the syntax for creating an index on a JChem table similar to jc_idx_type? Querying my table via the JSP application always completes fine and quite quickly (useStructureCache=false), however using the cartridge functions jcf_equals / jcf_compare do not complete on my 1million + tables.





Thanks for any comments,





Daniel.

Daniel,





The jcf_ search functions are by orders of magnitude slower than the searches in the JSP example -- even with structure caching turned off in the example. The reason is that the jcf_ search functions perform a full graph search for each and every structure in your table whereas the JSP example performs a pre-screening based on the fingerprints. (This pre-screening is done in one single SQL statement and is pretty fast.) Depending on the efficiency of the fingerprints for the given query this can make a huge difference.





Try to use the jc_ search operators (after the WHERE clause) wherever you can.





Peter





PS:


The default value of the "buildtype" index parameter is "ext" since JChem version 3.0 and no other value is accepted any more.

Thanks for your comments.





No problem, shall use an operator instead, although for some reason I thought that you had withdrawn them!?





So, is it true that domain index are not automatically created on JCHEM tables? It appears that way? No deterimental effects or unexpected problems by placing a jc_idxtype on a JChem table.cd_smiles column?





Thank you,


Daniel.

Quote:

No problem, shall use an operator instead, although for some reason I thought that you had withdrawn them!?

No, we have not.

Quote:

So, is it true that domain index are not automatically created on JCHEM tables?

No, they are not automatically created. You can use JChem tables with the Java API of JChem Base after all -- without JChem Cartridge and Oracle domain indexes.

Quote:

No detrimental effects or unexpected problems by placing a jc_idxtype on a JChem table.cd_smiles column?

Absolutely none. If you use large structures (such as Molfiles), I suggest to put the jc_idxtype index on the cd_structure column. But with smiles, putting it on the cd_smiles column is mostly fine.





Peter