Hi Tom
I have tried to reproduce reenabling of index. I am using direct path load. After first start of data everithing was OK.
But after second load with same data I expected rows into ex table, but see result below:

Invoice_10 is partition table with 12 part

mfapp> truncate table invoice_10;

Table truncated.

mfapp> alter table INVOICE_10 drop CONSTRAINT PK_INVOICE_10;

Table altered.

mfapp> ALTER TABLE INVOICE_10 ADD (
2 CONSTRAINT PK_INVOICE_10
3 PRIMARY KEY (YEAR, INVOICE_NO ,TILL_NO)
4 USING INDEX
5 TABLESPACE MEGAFINS_INDEX_LARGE
6 STORAGE (
7 INITIAL 10 M
8 NEXT 10 M
9 MINEXTENTS 1
10 MAXEXTENTS UNLIMITED
11 )
12 LOCAL
13 );

Table altered.

control file for loading is:
OPTIONS ( ERRORS=10000, ROWS=5000, BINDSIZE=220000 )
LOAD DATA
APPEND
INTO TABLE INVOICE_10
reenable disabled_constraints exceptions ex
WHEN (1) <> '*'
FIELDS TERMINATED BY '^'
.....

after first load:
mfapp> select count(*) from invoice_10;

COUNT(*)
----------
790

mfapp> select count(*) from ex;

COUNT(*)
----------
0

mfapp> desc ex;
Name Null? Type
------------------------------- -------- ----
ROW_ID ROWID
OWNER VARCHAR2(30)
TABLE_NAME VARCHAR2(30)
CONSTRAINT VARCHAR2(30)

this is OK.

NEXT step:

mfapp> alter table invoice_10 disable CONSTRAINT PK_INVOICE_10;

Table altered.

and start again Direct Path loading with same data:

mfapp> select count(*) from invoice_10;

COUNT(*)
----------
1580

mfapp> select count(*) from ex;

COUNT(*)
----------
0

Q1) Why? Is there somenting with partition tables? Can you explain,please?

thanks
kiro

During a direct load (SQL Loader), primary key constraints are not disabled.
But before direct path load as insert /*+ append */, primary (unique) keys must be disabled, what means the PK, UK's indexes are dropped. And that's the pain - in the moment of enabling PK, UK's - the storage information about the indexes before is gone, and those indexes are created with defaults!
I would expect that when enabling PK, UK's, the indexes are created with the same parameters as they were defined before disabling PK, UK's.
Is't there any way how to achieve this?

really? (but, instead of drop -- set unusable, skip_unusable_indexes=true, do the thing and rebuild!!!  don't drop!!!!)

but anyway (did it in 817 up at least)


ops$tkyte@ORA920.LOCALHOST> create table t ( x int primary key );
Table created.

  1* insert /*+ append */ into t select rownum from all_users
ops$tkyte@ORA920.LOCALHOST> /

36 rows created.

ops$tkyte@ORA920.LOCALHOST> select * from t;
select * from t
              *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel

ops$tkyte@ORA920.LOCALHOST> commit;

Commit complete.

ops$tkyte@ORA920.LOCALHOST> insert /*+ append */ into t select rownum from all_users;
insert /*+ append */ into t select rownum from all_users
*
ERROR at line 1:
ORA-00001: unique constraint (OPS$TKYTE.SYS_C003125) violated

I didn't drop any constraints here. 

The point is that the speed of direct path load insert (insert /*+ append */) for table with disabled PK, UK's, plus time spent on enabling PK, UK's (creating indexes) is much faster then speed of the same statement with enabled PK, UK's.
But there are some annoying side effects regarding unique indexes when enabling PK, UK's.
As I understood they do not exist for direct path load using SQL*Loader.

system@OD6I> @connect system/osystemi@od6i
Connected.

GLOBAL_NAME
----------------------------------------------------------------------------------------------------
system@OD6I

Elapsed: 00:00:00.00
system@OD6I> grant connect, resource to a identified by a;

Grant succeeded.

Elapsed: 00:00:00.60
system@OD6I> @connect a/a@od6i
Connected.

GLOBAL_NAME
----------------------------------------------------------------------------------------------------
a@OD6I

Elapsed: 00:00:00.10
a@OD6I> create table dv_all_objects as
2 select * from all_objects
3 where 1=0
4 /

Table created.

Elapsed: 00:00:00.20
a@OD6I> ed
Wrote file afiedt.buf

1 ALTER TABLE DV_all_objects
2 ADD (CONSTRAINT DV_all_obj_pk PRIMARY KEY
3* (object_ID) using index tablespace users)
a@OD6I> /

Table altered.

Elapsed: 00:00:00.50
a@OD6I> select index_name, status, tablespace_name from user_indexes
2 where table_name='DV_ALL_OBJECTS';

INDEX_NAME STATUS TABLESPACE_NAME
------------------------------ -------- ------------------------------
DV_ALL_OBJ_PK VALID USERS

Elapsed: 00:00:00.20

-- OK, index DV_ALL_OBJ_PK for PK DV_ALL_OBJ_PK is in tablespace users

a@OD6I> insert /* +append */ into DV_all_objects
2 select * from all_objects;

21954 rows created.

Elapsed: 00:00:28.41
a@OD6I> truncate table DV_all_objects;

Table truncated.

Elapsed: 00:00:07.80
a@OD6I> alter index DV_ALL_OBJ_PK unusable;

Index altered.

Elapsed: 00:00:00.40
a@OD6I> alter session set skip_unusable_indexes=true
2 ;

Session altered.

Elapsed: 00:00:00.00
a@OD6I> insert /* +append */ into DV_all_objects
2 select * from all_objects;
insert /* +append */ into DV_all_objects
*
ERROR at line 1:
ORA-01502: index 'A.DV_ALL_OBJ_PK' or partition of such index is in unusable state

Elapsed: 00:00:00.20

-- Oops! It does not work for indexes belonging to PK, UK's !
-- BTW, no surprise, I've found this in J. Lewis, Practical Oracle

a@OD6I> alter table DV_all_objects disable constraint DV_ALL_OBJ_PK;

Table altered.

Elapsed: 00:00:00.00
a@OD6I> select index_name, status, tablespace_name from user_indexes
2 where table_name='DV_ALL_OBJECTS';

no rows selected

Elapsed: 00:00:00.20

-- Index for PK is gone !!!!!!!!!!

a@OD6I> insert /* +append */ into DV_all_objects
2 select * from all_objects;

21953 rows created.

Elapsed: 00:00:13.19

a@OD6I> alter table DV_all_objects enable constraint DV_ALL_OBJ_PK;

Table altered.

Elapsed: 00:00:03.35

-- Well, much faster 16:54 sec comparing to 28:41 sec

a@OD6I> select index_name, status, tablespace_name from user_indexes
2 where table_name='DV_ALL_OBJECTS';

INDEX_NAME STATUS TABLESPACE_NAME
------------------------------ -------- ------------------------------
DV_ALL_OBJ_PK VALID SYSTEM

Elapsed: 00:00:00.10

-- Index for PK was created, BUT in default tablespace for user A
-- i.e.'SYSTEM' instead of 'USERS', as it was before disabling PK

use a deferrable constraint -- really nice for when you want to dislable a primary key from time to time without totally dropping the index:

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t ( x int, constraint t_pk primary key (x) deferrable );

Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> alter table t disable constraint t_pk;

Table altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM> alter index t_pk unusable;

Index altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM> alter session set skip_unusable_indexes=true;

Session altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert /*+ append */ into t select rownum from all_users;

46 rows created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> alter index t_pk rebuild;

Index altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM> alter table t enable constraint t_pk;

Table altered.



 

Just a note that the hint syntax gets ignored unless the "+" is the first character after the comment.

-- hint used
select /*+ full(emp) */ ename from emp where empno = 7934;
Operation Object Name Rows Bytes Cost TQ In/Out PStart PStop

SELECT STATEMENT Hint=CHOOSE 1 1
TABLE ACCESS FULL EMP 1 8 1

-- hint ignored
select /* +full(emp) */ ename from emp where empno = 7934;
Operation Object Name Rows Bytes Cost TQ In/Out PStart PStop

SELECT STATEMENT Hint=CHOOSE 1 1
TABLE ACCESS BY INDEX ROWID EMP 1 8 1
INDEX UNIQUE SCAN EMP_PRIMARY_KEY 1

Thanks for response. Nevertheless, some more thoughts.
In case of existing non-unique indexes on column(s) which should be PK, UK, we can create primary or unique key on this column(s)using index(no matter if deferrable or non deferrable). The index (even if non-unique) is reused by PK, or UK. When disabling PK, UK, the index is not dropped, which is cool, much bigger flexibility comparing to PK+unique index. For me, the strategy for creating PK, UK's on table seems like :
1) Create non-unique index on PK, UK's column(s)
2) Create PK, UK's using index
So, do we need unique indexes at all? Or, is there any performance cons when going the way above?

Hi Tom,

In general what events may cause to have a disable constraint like disable foreign key ? I know that you can manually disable or enable a constraint. But what else could disable a foreign key automatically?

Thanks for your time,
Arash

Excellent !!!

Hi Tom,

How would you do a load (conventional way) on a table that have many indexes, when using a Truncate in the ctl file. Since the table is truncate, the unusables indexes are then put back as valid. Would you DROP/CREATE the indexes?

We recently moved to 9.2.0.4 and have found that if Loader, direct-path, is used and duplicated exist in the input, the Loader job does not signal a warning but it loads the rows and sets the PK index to UNUSABLE. Is that the normal function? I recall that 8.1.7 returned a warning status out of the Loader step.

Obviously, when another load is tried (or something that uses the index) it fails because an index or index partition is unusable. If unique data is loaded in one session and is duplicated in another, that does result in the Loader step signaling a warning. This seems misleading and inconsistent.

Duplicate data within a load is not considered an error (warning) even though it results in the index becoming unusable, but between loads does.

Hello,
the last comment you mentionned concerning Oracle 9i r2 behavior using direct path and primary key is that it is a normal behavior that primary keys are left in an unusable state if duplicates are found (!?). Ok, you state just the obvious, but then, the index cannot be rebuild because duplicates are still there.
So, your answer doesn't help at all.
thanks