In it’s current design, Schema Compare does not show indexes which are used to enforce constraints. We handle the indexes which are used by constraints by including the USING INDEX clause with our ALTER TABLE…ADD CONSTRAINT commands in our tables. This can be verified by running the following statement from SQL Nav:
FROM ALL_INDEXES I
WHERE OWNER = ‘SCOTT’
AND NOT EXISTS (SELECT ‘x’ FROM ALL_CONSTRAINTS C WHERE C.OWNER = I.OWNER AND C.INDEX_NAME = I.INDEX_NAME)
Whilst this may seem like we have missed something, if you perform a compare & synch of two full schemas, you will end up with the same number of indexes on both the target and source databases. The only difference will be that some of the indexes may not have the same name.
For example, if on the source you create a table by executing something like:
CREATE TABLE CUSTOMER
CREATE UNIQUE INDEX UI_CUSTOMER ON (CustomerID)…
ALTER TABLE CUSTOMER
ADD CONSTRAINT PK_CUSTOMER PRIMARY KEY (CustomerID)
And on the target create the same table by excluding the CREATE UNIQUE INDEX statment, the structure of the table, indexes and constraints is the same. The only difference will be the name of the index. On the source database, the unique index will be called UI_CUSTOMER, whereas on the target database, the unique index will be called PK_CUSTOMER.