Search notes:
Oracle: SQL Loader - Example 13: make a unique index unusable
Creating the table
A table with a primary key is created and populated with three records:
create table tq84_tab_with_pk (
id number,
txt varchar2(10),
--
constraint tq84_tab_pk primary key (id)
);
insert into tq84_tab_with_pk values (1, 'foo');
insert into tq84_tab_with_pk values (2, 'bar');
insert into tq84_tab_with_pk values (3, 'baz');
commit;
Data to be loaded
This is the data to be populated into the table.
Note: it contains id=2, which was already inserted into the table.
7,seven
11,eleven
2,double
5,five
The control file
options (
direct=true,
errors=0,
parallel=false
)
unrecoverable
load data infile "tab_with_pk.dat"
into table tq84_tab_with_pk
append
fields terminated by ","
(
id,
txt
)
The control file uses direct=true and unrecoverable. So, Oracle does not verify the unique indexes while the data is loaded.
Data loaded
select * from tq84_tab_with_pk order by id finds that the table now contains two records whose id is 2.
Unusable index
Since the unique constraint is now violated Oracle sets the index to unusable:
select status from user_indexes where index_name = 'TQ84_TAB_PK';