USING index clause

Question

I saw the following statement in a db patch:

ALTER TABLE tablename ADD PRIMARY KEY (somepk_columnname) USING INDEX;

I wanted to look up what USING INDEX does here, but only got from google, that it lets me specify some storage specific stuff, etc.

My question really is, what exactly happens here? Does the db use some default values here? Creates an index for the PK in the default tablespace? I thought that an index is created for every pk per default...

Solution 1

http://docs.oracle.com/cd/B28359_01/server.111/b28286/clauses002.htm#SQLRF52209

Using Indexes to Enforce Constraints

When defining the state of a unique or primary key constraint, you can specify an index for Oracle to use to enforce the constraint, or you can instruct Oracle to create the index used to enforce the constraint.

using_index_clause You can specify the using_index_clause only when enabling unique or primary key constraints. You can specify the clauses of the using_index_clause in any order, but you can specify each clause only once.

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.

Solution 2

The response above was very helpful but I also wanted to use a specific tablespace for my unique key. This worked for me:

alter table tablename add constraint uk_name unique (col1, col2) using index tablespace tablespace_name;