create index ... parameters('RegenerateTable') -> illegal

Hello,





Regarding Jchem Cartridge:





I've created a jchem-table and put an index onto it:





CREATE INDEX jc_idx ON steptest(cd_structure) INDEXTYPE IS jc_idxtype;





this works fine.


then i copy some records of steptest into steptest2:





create table steptest2 as select * from steptest where rownum < 500





works fine also.


then i try to create an index on this table. i get an error indicating that i should create the index with 'RegenerateTable'-option. Which i do:





CREATE INDEX jc_idx ON steptest2(cd_structure) INDEXTYPE IS jc_idxtype parameters('RegenerateTable');





this leads to an uncaught jave-exception telling me that 'RegenerateTable' is not a valid option. What's happening there?





Thanks,


Stephan

Do you use JChem tables (structure tables created with JChemManager or using the JChem Java API)? This option cannot be used with regular tables containing molecular structures. If you are trying to apply this option to a JChem table and you still get this error message, then this is probably a bug. I am going to look into it. Please, use the JChemManager GUI to regenerate JChem tables in the mean time.

I see now. Of course, steptest2 is not a JChem table. I do not suggest to do this:

Quote:

create table steptest2 as select * from steptest where rownum < 500

steptest is probably a JChem table (created with JChemManager or using the JChem Java API), isn't it? (I infer this from the column name cd_structure.) If it is, then the above statement will create a regular table with the structure of a JChem table and copy all the chemical information (fingerprints) into the new table. Creating a jc_idxtype index on the steptest2.cd_structure column would result into a duplication of the fingerprints into yet another table: the index table for the index on the steptest2.cd_structure. To do what I vaguely guess you want to do (duplicate a subset of the steptest JChem table into steptest2), I suggest to create a JChem table steptest2 first with JChemManager and then:

Code:

insert into steptest2 (select * from steptest where rownum < 500)

At this point you should be able to index steptest2. Note, however, that you probably already have an index called jc_idx (on steptest), so may want to select another name for the new index.





Note, also, that the recommended way of inserting into a JChem table is via the jc_insert(b) function (see http://www.chemaxon.com/jchem/doc/guide/cartridge/cartapi.html#jc_insert ). The reason is that a regular SQL INSERT will not update the structure cache for the table with the newly inserted structures. This is not a problem, if the structure cache has not yet been built at all -- which is probably the case with stepstep2 at this point. (The structure cache is built during the first structure search in the table.) (jc_insert(b) will also go through the process of calculating the fingerprints and other chemical information for the structures [you do not need this for this test], so it will be much slower than a plain SQL INSERT.)





Alternatively, if you want to create a regular structure table (non-JChem table), create one like:

Code:

create stepstep2 (id number primary key, struct blob, <more-columns-if-needed>...);

then

Code:

insert into steptest2 (id, struct) (select cd_id, cd_structure from steptest where rownum < 500)

then you can index steptest2.struct with jc_idxtype.

ok, thanks for clearifying, i understand the problem now.