Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Question about Append hint in Insert

RE: Question about Append hint in Insert

From: Jacques Kilchoer <>
Date: Mon, 21 Jun 2004 16:54:12 -0700
Message-ID: <B5C5F99D765BB744B54FFDF35F60262109F87914@irvmbxw02>

I forgot one "non-" in the e-mail and so my previous one was incorrect. Re-sending.

If you don't use the "keep index" option on the "alter table ... drop constraint ... " as described by Jared Still, then the behaviour has changed in Oracle 10 when you drop a constraint. Taking the four examples below

Table 1 - constraint enforced by a unique index, but the index has additional columns
Table 2 - constraint enforced by NON-unique index on the same columns Table 3 - constraint enforced by index "implicitly" created along with constraint
Table 4 - constraint enforced by "pre-created" unique index on the same columns

In all versions of Oracle, tables 1 through 3 will behave the same - dropping the constraint on tables 1 and 2 will leave the index; dropping the constraint on table 3 will drop the index.

In Oracle 9.2 and earlier, dropping the constraint will drop the index for table 4.
In Oracle 10.1, dropping the constraint will leave the index for table 4 - Oracle is "smart" enough to remember that the index was created explicitly before the constraint.

You can test the behaviour with this script:

drop table doctor_specialty ;
drop table conversion_chart ;
drop table orders ;
drop table zip ;

--table 1

create table conversion_chart
  (country_currency1 number (3) not null,    country_currency2 number (3) not null,    exchange_rate1 number,
   exchange_rate2 number
  ) ;
create unique index conversion_chart_uqidx1 on   conversion_chart (country_currency1, country_currency2, exchange_rate1) ;
alter table conversion_chart
  add (constraint conversion_chart_pk

        primary key (country_currency1, country_currency2)
       ) ;

--table 2

create table doctor_specialty
  (doctor_id number,
   specialty_code varchar2 (4),
   board_certified varchar2 (1)
  ) ;
create index doctor_specialty_idx1 on
  doctor_specialty (specialty_code, doctor_id) ; alter table doctor_specialty
  add (constraint doctor_specialty_pk

        primary key (doctor_id, specialty_code)
       ) ;

--table 3

create table orders

   (order_id number constraint orders_pk primary key,     order_date date
   ) ;

--table 4

create table zip (zipcode number (5) not null, city varchar2 (40)) ; create unique index zip_uqidx1 on zip (zipcode) ; alter table zip add (constraint zip_uq1 unique (zipcode)) ;

alter table doctor_specialty
  drop constraint doctor_specialty_pk ;
alter table conversion_chart
  drop constraint conversion_chart_pk ;
alter table orders drop constraint orders_pk ; alter table zip drop constraint zip_uq1 ;

select a.table_name, b.index_name
 from user_tables a, user_indexes b
 where a.table_name in ('DOCTOR_SPECIALTY', 'CONVERSION_CHART', 'ORDERS', 'ZIP')
       and a.table_name = b.table_name (+)  order by 1, 2 ;

drop table doctor_specialty ;
drop table conversion_chart ;
drop table orders ;
drop table zip ;

Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.

Archives are at FAQ is at
Received on Mon Jun 21 2004 - 18:51:01 CDT

Original text of this message