Problem solve Get help with specific problems with your technologies, process and projects.

Creating an INDEX on primary key columns before creating a primary key

Better to create an INDEX on primary key columns before creating a primary key.

As a DBA, you sometimes have to enable and disable primary keys. When you disable a primary key, the primary key index will also be dropped; and when you enable it, the entire index will again be created. Dropping an index and creating an index by enabling and disabling primary keys causes a lot of system overhead.

To avoid this, always create an index on primary key columns before creating a primary key. This is explained by the following example which uses Oracle 8.x onward:

/* Let us create a table named xtest2 having a primary key say on x as follows: */
ora816 SQL :> create table xtest2 (x  number,y char(2), constraint xtest_PK primary key (x) );
ora816 SQL :> select index_name from user_indexes where table_name='XTEST2';
INDEX_NAME
------------------------------
XTEST_PK
/* You have seen that index name XTEST_PK has created by primary key */
ora816 SQL :> alter table xtest2 disable primary key;
ora816 SQL :> select index_name from user_indexes where table_name='XTEST2';
no rows selected
/* Notice that by disabling a primary key, the index is also dropped. */

ora816 SQL :> alter table xtest2 enable primary key;
ora816 SQL :> select index_name from user_indexes where table_name='XTEST2';
INDEX_NAME
------------------------------
XTEST_PK
/* Notice that by enabling a primary key, the index is again created.  But could you image if the primary key is on heavily populated table?  How much system overhead,resources and downtime are required? */

ora816 SQL :>
/* Now create a table with disable primary key as follows: */
ora816 SQL :> create table xtest2 (x  number,y char(2), constraint xtest_PK primary key (x) disable);
ora816 SQL :> select index_name from user_indexes where table_name='XTEST2';
no rows selected
/* You will notice that there is no index created as above.  Now create index on x as follows: */
ora816 SQL :> create index xtest_PK on xtest2(x);
ora816 SQL :> select index_name from user_indexes where table_name='XTEST2';
INDEX_NAME
------------------------------
XTEST_PK

/* Enable the primary key. */
ora816 SQL :> alter table xtest2 enable primary key;
ora816 SQL :> select index_name from user_indexes where table_name='XTEST2';
INDEX_NAME
------------------------------
XTEST_PK
/* Disabling the primary key does not drop index. */
ora816 SQL :> alter table xtest2 disable primary key;
Table altered.
ora816 SQL :> select index_name from user_indexes where table_name='XTEST2';
INDEX_NAME
------------------------------
XTEST_PK

For More Information


This was last published in May 2001

Dig Deeper on Oracle and SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close