Oracle CONSTRAINT vs, CONSTRAINT *Using Index*

Question

If a table has a constraint but no index, can I assume that the only difference is that I am leaving it to Oracle to determine how determine whether changes to the table violate the constraint, perhaps resulting in Oracle having to do table scans to determine it?

CREATE TABLE SchemaName."Table1" (
  field_one VARCHAR2(10 BYTE) NOT NULL,
  field_two VARCHAR2(30 BYTE) NOT NULL,
  field_three DATE NOT NULL,
  (etc)
  CONSTRAINT client_iu1 UNIQUE (field_one,field_two,field_three)
);

vs.

CREATE TABLE SchemaName."Table1" (
  field_one VARCHAR2(10 BYTE) NOT NULL,
  field_two VARCHAR2(30 BYTE) NOT NULL,
  field_three DATE NOT NULL,
  (etc)
   CONSTRAINT client_ak1 UNIQUE (field_one,field_two,field_three) USING INDEX fds_base.client_iu1
);

Answer 1

Oracle creates the index in all cases for unique (and primary key constraints).

The using index statement allows you to either:

• Specify the name of the index in the constraint statement.
• Using an existing index for a new constraint.

In fact, I don't find either of these particularly useful in practice. If you don't given an explicit index, Oracle creates the appropriate index for the constraint.