| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Question about Append hint in Insert
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 unique index on the same columns
Table 3 - constraint enforced by index "implicitly" created along with
constrtaint
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 =3D b.table_name (+)
order by 1, 2 ;
drop table doctor_specialty ;
drop table conversion_chart ;
drop table orders ;
drop table zip ;
 |
 |