关于USING INDEX  

两种情况：
1.对于创建约束时隐式创建的索引，在做删除操作的时候:   9i~10g都会连带删除该索引

2.对于先创建索引，再创建约束（使用到此索引）这种情况:
9i版本：需要区分索引是否唯一：
如果索引是唯一的，则删除约束的时候，会连带删除索引；如果非唯一的，则不会删除索引。
10g版本：无论索引是否唯一，都只是删除约束，索引不会删除。
可以参考metalink文档：309821.1

The information in this document applies to:

Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 10.2.0.0
This problem can occur on any platform.

Symptoms

Drop constraint does not drop a associated index in 10G

Cause

In 10g, a change was made to an internal function "atbdui" to not to drop the user index when the constraint using the index is dropped.

In 9i, this behavior. is different the user index
gets dropped when the constraint is dropped.

Select index_name,generated from dba_indexes where index_name='< ;index_name >';

Generated column would show 'Y' If the index is system generated.If 'N' ,it is user generated.

Solution

This behaviour is because of the code changes made in 10G.

Use the workaround:

alter table < table > drop constraint < constraint > drop index;

Note

If the index is non unique, we can still use them for enforcing primary key constraints or unique constraints.

But dropping the constraint does not drop the non-unique index.
This behaviour is seen from 8174 to 10.2.0.0.

Sample Output

SQL> create table test( a number );

Table created.

SQL> create index ind on test ( a );

Index created.

SQL> alter table test add constraint c1_pk primary key(a) using index;

Table altered.

SQL> select index_name from user_indexes where table_name='TEST';

INDEX_NAME
------------------------------
IND

SQL> alter table test drop constraint c1_pk;

Table altered.

SQL> select index_name from user_indexes where table_name='TEST';

INDEX_NAME
------------------------------
IND

References

Note 292096.1 - Encountering ora-00955 when trying to create primary key on an imported table in 10g

Keywords

USING~INDEX;