JChem index objects left behind after dropping table

ChemAxon aa7c50abf8

13-01-2009 21:51:34

Quote:
When a structure table, containing the JChem index is dropped, it appears to leave behind the underlying tables associated with the JChem index. Is this behaviour normal? I would have expected the index tables to disappear once the data table has been deleted...
    


When a table is dropped, Oracle "normally" makes sure that the indexes associated with that table are also dropped. This includes notifying the relevant domain index implementations of indexes being dropped using the same notification mechanism as when DROP INDEX is called by the user. The DROP INDEX semantics include dropping all objects associated with the index being dropped.


One possible scenario, in which database objects associated with domain indexes might survive the removal of the base table, is when the domain index implementation is removed (or becomes invalid) without the dependent domain index having been dropped. In such cases, any index which had been created using the domain index will be invalidated and disassociated from the structure table: these old indexes become sort of zombies. If you then drop the structure table, the implementation which could have safely done the work associated with dropping the domain index is already gone.


There might be other scenarios, in which the kind of disassociation by Oracle described above takes place.


Note that starting with JChem 5.1.4, indexes can be "upgraded in-place": http://www.chemaxon.com/jchem/doc/admin/cartridge.html#upgrade_guided_50 . With earlier JChem versions, the upgrade procedure involved dropping the domain index implementation which led to the scenario described above.