Re: Indexes

> My misunderstanding is that when you create a primary or unique key,
> the index is created automatically.  So, why when you drop the
> constraint does the index not go with it?  Or the other way?

There are two ways in handling the index associated with Primary key.

1. Let the DB handle the index when you create the primary key. In this case, you can't control the storage characteristics of the index and/or the tablespace in which it is stored. Not appropriate for very large tables. In this case, when the key is dropped the index will also be dropped.
2. You create the index first with the customized storage characteristics and then let the primary key use that index. In this case, the index won't be dropped since both are independent objects linked by you.

create table test_tab (no number primary key,name varchar2(10)) -- system created index for the PK

create table test_tab1 (no number,name varchar2(10))

create unique index idx_test_tab1 on test_tab1(no)

alter table test_tab1 add constraint pk_test_tab1 primary key (no) using index --PK uses our own index

select * from user_indexes where table_name in ('TEST_TAB','TEST_TAB1') select constraint_name,constraint_type,table_name,index_name from user_constraints where table_name in ('TEST_TAB','TEST_TAB1') -- index_name shows the index used by the PK

alter table test_tab drop primary key

alter table test_tab1 drop primary key

select * from user_indexes where table_name in ('TEST_TAB','TEST_TAB1')

• only the user created index will remain whereas the system created one would have been dropped.

--Soma Received on Sat Jun 13 2009 - 08:01:22 CDT