Re: Question about Append hint in Insert

9i lets you do this as long as you use the keep index syntax...  For 8i 
your statement is correct...  Modifying Jared's example...

SQL> drop table x;

Table dropped.

SQL>
SQL> create table x
  2    as
  3   select distinct object_name
  4    from user_objects
  5  /

Table created.

SQL>
SQL> create unique index x_idx on x(object_name)
  2  /

Index created.

SQL>
SQL> alter table x add constraint x_pk
  2    primary key(object_name)
  3    using index x_idx
  4  /

Table altered.

SQL>
SQL> select index_name , uniqueness, status
  2    from user_indexes
  3   where table_name = 'X'
  4  /

INDEX_NAME                     UNIQUENES STATUS
------------------------------ --------- --------
X_IDX                          UNIQUE    VALID

SQL>
SQL> select constraint_name, constraint_type, status
  2    from user_constraints
  3   where table_name = 'X'
  4  /

CONSTRAINT_NAME                C STATUS
------------------------------ - --------
X_PK                           P ENABLED

SQL>
SQL> alter table x drop primary key keep index;

Table altered.

SQL>
SQL> select index_name , uniqueness, status
  2    from user_indexes
  3   where table_name = 'X'
  4  /

INDEX_NAME                     UNIQUENES STATUS
------------------------------ --------- --------
X_IDX                          UNIQUE    VALID

SQL>
SQL> select constraint_name, constraint_type, status
  2    from user_constraints
  3   where table_name = 'X'
  4  /

no rows selected

SQL>

Rachel Carmichael wrote:

>you may DROP the constraint without losing the index.... 
>
>if you drop a pk constraint, even if the unique index has been created
>separately, the index goes away too (this is from memory but I seem to
>recall spending a lot of time testing this ... and cursing)
>
>if the index is non-unique, dropping the constraint will not drop the
>index.
>
>can you try your test case several different ways (I'm swamped, don't
>have time to run this)
>
>1)  same code, except drop the constraint not disable
>2)  create a unique index before you create the constraint, then drop
>constraint (should still drop the index)
>3)  create a non-unique index before you create the constraint, then
>drop constraint (should keep the index)
>
>and please, if I'm wrong, let the list know, not just me :)
>
>Rachel
>  
>

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: