OraFAQ Forum: SQL & PL/SQL » Options for definitions of INDEXES used by CONSTRAINTS

Home » SQL & PL/SQL » SQL & PL/SQL » Options for definitions of INDEXES used by CONSTRAINTS

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Options for definitions of INDEXES used by CONSTRAINTS [message #642632]

Tue, 15 September 2015 16:10

mjm22
Messages: 54
Registered: January 2010
Location: Singapore

Member

Hi,

I had an interesting conversation today regarding INDEX definition for PRIMARY KEY CONSTRAINTS. So, I am interested in determining if there is any difference between the below three options for control of specifying an index when adding a constraint..

I have a table

CREATE TABLE test_table AS (account_no NUMBER(10), account_category NUMBER(1), currency_code VARCHAR2(3))
/

On which I want to create a primary key on account_no and account_category, but I want to specify the index so I more control (tablespace, etc).
I can think of a few ways to do this:

1. Create Primary Key with a using index clause to specify the unique INDEX

ALTER TABLE test_table
  ADD CONSTRAINT pk_test_table
    PRIMARY KEY (account_no, account_category)
    USING INDEX (CREATE UNIQUE INDEX pk_test_table ON test_table (account_no, account_category);
/

2. Create Index and then Primary Key with a using index clause, naming the index

CREATE UNIQUE INDEX pk_test_table ON test_table
(account_no, account_category)
/

ALTER TABLE test_table
  ADD CONSTRAINT pk_test_table
    PRIMARY KEY (account_no, account_category)
    USING INDEX pk_test_table
/

3. Create Index and then Primary Key with a using index clause and let Oracle figure out index

CREATE UNIQUE INDEX pk_test_table ON test_table
(account_no, account_category)
/

ALTER TABLE test_table
  ADD CONSTRAINT pk_test_table
    PRIMARY KEY (account_no, account_category)
    USING INDEX
/

My question is, is there any fundamental difference in implementation between the above options? As I understand it, the final option is not so good, if there is more than one index on the table using the primary key columns then Oracle may not select the one I intend?

Other than that is there any benefit of using one method over the other?

Thanks in advance,

Mike

Report message to a moderator