Oracle Index – Using Index on Unique Constraint

indexoracletablespaces

Using Druid to generate SQL script for Oracle, I have seen a difference between the version 3.10 and 3.13 and I would like to understand what going on.

SQL generated by Druid 3.10 :

CREATE TABLE ATTRIBUTE_VALUE_LABEL
  (
    ID         NUMBER(18),
    ATTRVALID  NUMBER(18)       not null,
    LANGUAGE   NVARCHAR2(2)     not null,
    NAME       NVARCHAR2(250)   not null,
    CREATED    DATE             default SYSDATE,
    MODIFIED   DATE,
    CONSTRAINT PK_ATTRIBUTE_VALUE_LABEL primary key(ID) USING INDEX TABLESPACE &&TSINDEX,
    CONSTRAINT XA_ATTRIBUTE_VALUE_LABEL_2 unique(ATTRVALID,LANGUAGE) USING INDEX TABLESPACE &&TSINDEX,
    CONSTRAINT FK_ATTRIBUTE_VALUE_LABEL_1 foreign key(ATTRVALID) references ATTRIBUTE_VALUE(ID) on delete CASCADE
  );

SQL generated by Druid 3.13 :

CREATE TABLE ATTRIBUTE_VALUE_LABEL
  (
    ID         NUMBER(18),
    ATTRVALID  NUMBER(18)       not null,
    LANGUAGE   NVARCHAR2(2)     not null,
    NAME       NVARCHAR2(250)   not null,
    CREATED    DATE             default SYSDATE,
    MODIFIED   DATE,
    CONSTRAINT PK_ATTRIBUTE_VALUE_LABEL primary key(ID) USING INDEX TABLESPACE &&TSINDEX,
    CONSTRAINT XA_ATTRIBUTE_VALUE_LABEL_2 unique(ATTRVALID,LANGUAGE),
    CONSTRAINT FK_ATTRIBUTE_VALUE_LABEL_1 foreign key(ATTRVALID) references ATTRIBUTE_VALUE(ID) on delete CASCADE
  );

As you can see, the only difference is that Druid don't generate anymore a USING INDEX TABLESPACE &&TSINDEX after the UNIQUE constraint.

I suspect that there is a reason for why Druid don't generate anymore this USING INDEX thing on the UNIQUE constraint (could have something to do with the fact that there already a USING INDEX on the PRIMARY constraint), but I would like to understand why.

I have two questions then :

  1. Can this cause some error on Oracle ?
  2. Do this change something for the index used by the unique constraint ? Not in the right table space ?

Best Answer

If the USING INDEX clause is omitted, the index will be still created automatically with the same name as the constraint, in the same tablespace where table is created. This will not cause an error.

http://docs.oracle.com/database/121/SQLRF/clauses002.htm#SQLRF52225

  • If you specify schema.index, then Oracle attempts to enforce the constraint using the specified index. If Oracle cannot find the index or cannot use the index to enforce the constraint, then Oracle
    returns an error.

  • If you specify the create_index_statement, then Oracle attempts to create the index and use it to enforce the constraint. If Oracle
    cannot create the index or cannot use the index to enforce the
    constraint, then Oracle returns an error.

  • If you neither specify an existing index nor create a new index, then Oracle creates the index. In this case:

    • The index receives the same name as the constraint.

    • If table is partitioned, then you can specify a locally or globally partitioned index for the unique or primary key constraint.

Related Question