Re: Oracle XE: Import error

sybrandb wrote:

> indexes=n will skip index creation. Primary key constraints are created
> regardless.

I tried that and found that indexes for primary keys and unique constraints are always created (regardless of this option), but - in contrast to before - the indexes are now implicitly created along with the constraints instead of explicitly before the constraints are created.

This leads to the exact desired behavior: When the respective PK or unique constraint is dropped, then the corresponding index is also removed.

> It looks like you didn't originally use
> alter table blah add constraint ... primary key *using index*, because
> the index you complain about is just a standalone index. Which
> explains, as constraint creation and index creation is now a 2 step
> process, why it wasn't dropped.

Yes, you're perfectly right.

Yet still: The "using index" addition should only by relevant when the index is already there before the PK constraint is added. Even when I leave it away, like in my example

  create table TestTable (testid smallint constraint pk_test primary key);

then the index is implicitly created and linked to the PK, so that it is removed when the PK is dropped. This only changes after exporting and re-importing without the "indexes=n" option. And that's why...

> I would call it a 'feature', not a bug.

...I still consider it a bug. The behavior of indexes just should not CHANGE when the schema is exported and re-imported. If a certain index was linked to a constraint before the export, then it should also be after the import. If it was not linked before the export, it should also not be linked after the import.

However, now that I understand the issue it will no longer be a practical problem. Thank you very much for pointing out what actually happens behind the scenes when a schema is imported! Received on Wed Nov 29 2006 - 14:55:13 CST