DISABLE CONSTRAINT CASCADE DROP INDEX 不留index

Indexes Associated With Primary Key Constraints Of Imported Tables Are Not Dropped When Constraints Are Disabled (Doc ID 887208.1)

Create a table with primary key constraint with "using index" clause. There are no indexes defined for this table before the primary key constraint is created. As such, when creating the constraint, an unique index with the same name with the constraint name is created. If the constraint is disabled, the index is dropped.

The table is exported and then imported. After the import when the constraint is disabled the index is not dropped anymore.

This happens with both Data Pump and classical export/import tools.

NOTE:  In the testcase content below, the user information and metadata used represents fictitious data.  Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner.

Example:

1. Create the table and its primary key.

sqlplus /nolog

SQL> conn test/test
Connected.

SQL> create table pktest( n integer );

Table created.

SQL> alter table pktest add (
2 constraint pk_pktest
3 primary key(n)
4 USING INDEX
5 );

Table altered.

SQL> select index_name from user_indexes where table_name = 'PKTEST';

INDEX_NAME
------------------------------
PK_PKTEST

SQL> select constraint_name, constraint_type, index_name, status
2 from user_constraints where table_name='PKTEST';

CONSTRAINT_NAME C INDEX_NAME STATUS
------------------------------ - ------------------------------ --------
PK_PKTEST P PK_PKTEST ENABLED

SQL> alter table pktest disable CONSTRAINT pk_pktest;

Table altered.

SQL> select constraint_name, constraint_type, index_name, status
2 from user_constraints where table_name='PKTEST';

CONSTRAINT_NAME C INDEX_NAME STATUS
------------------------------ - ------------------------------ --------
PK_PKTEST P DISABLED

SQL> select index_name from user_indexes where table_name = 'PKTEST';

no rows selected


2. Export table:

expdp test/test tables=pktest directory=dp_dir dumpfile=test.dmp

Export: Release 10.2.0.4.0 - Production on Monday, 14 September, 2009 15:20:25

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "TEST"."SYS_EXPORT_TABLE_01": test/******** tables=pktest directory=dp_dir dumpfile=test.dmp
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "TEST"."PKTEST" 0 KB 0 rows
Master table "TEST"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_TABLE_01 is:
D:\DP_DIR\TEST.DMP
Job "TEST"."SYS_EXPORT_TABLE_01" successfully completed at 15:20:38

3. Drop the table:

SQL> conn test/test
Connected.
SQL> drop table pktest;

Table dropped.

4. Import it:

impdp test/test tables=pktest directory=dp_dir dumpfile=test.dmp

Import: Release 10.2.0.4.0 - Production on Monday, 14 September, 2009 16:24:10

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "TEST"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "TEST"."SYS_IMPORT_TABLE_01": test/******** tables=pktest directory=dp_dir dumpfile=test.dmp
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TEST"."PKTEST" 0 KB 0 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Job "TEST"."SYS_IMPORT_TABLE_01" successfully completed at 16:24:13


5. Check the primary key constraint and its index behavior:

SQL> conn test/test
Connected.
SQL> select constraint_name, constraint_type, index_name, status
2 from user_constraints where table_name='PKTEST';

CONSTRAINT_NAME C INDEX_NAME STATUS
------------------------------ - ------------------------------ --------
PK_PKTEST P PK_PKTEST ENABLED

SQL> select index_name from user_indexes where table_name = 'PKTEST';

INDEX_NAME
------------------------------
PK_PKTEST

SQL> alter table pktest disable CONSTRAINT pk_pktest;

Table altered.

SQL> select constraint_name, constraint_type, index_name, status
2 from user_constraints where table_name='PKTEST';

CONSTRAINT_NAME C INDEX_NAME STATUS
------------------------------ - ------------------------------ --------
PK_PKTEST P DISABLED

SQL> select index_name from user_indexes where table_name = 'PKTEST';

INDEX_NAME
------------------------------
PK_PKTEST

CHANGES

CAUSE

The issue is related to the fact that the import will execute the following statements:

-- CONNECT TEST
-- new object type path is: TABLE_EXPORT/TABLE/TABLE
CREATE TABLE "TEST"."PKTEST"
( "N" NUMBER(*,0)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ;

-- new object type path is: TABLE_EXPORT/TABLE/INDEX/INDEX
CREATE UNIQUE INDEX "TEST"."PK_PKTEST" ON "TEST"."PKTEST" ("N")
PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" PARALLEL 1 ;

ALTER INDEX "TEST"."PK_PKTEST" NOPARALLEL;

-- new object type path is: TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
ALTER TABLE "TEST"."PKTEST" ADD CONSTRAINT "PK_PKTEST" PRIMARY KEY ("N")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ENABLE;

NOTE: the above statements are obtained by reading the Data Pump export dump file by means impdp with SQLFILE parameter.

The import will create the objects in the following order:
- creates the tables
- creates the unique index
- creates the primary key constraint with "using index" clause. As the index is existing at constraint creation time it will use the above created index (as per documentation).

This is slightly different than how the table was initially created: when the table was created the unique index did not exist and was created by the constraint.

This problem was investigated in:
Bug 4172692 - INDEX REMAINS AFTER IMPORTED CONSTRAINT IS DISABLED
that was closed as "Not a bug":
and also in:
Bug 6741244 - ORA-00001 WHEN MAKING INSERT ON TABLES GENERATED BY IMPORT
closed as duplicate of Bug 4172692 above.

Bugs conclusions were:
"This is a minor issue introduced in 10g due to an improvement in 10g such than DISABLE of a constraint only drops the supporting index only if it was created ***automatically**** for the constraint .
ie: 10g DISABLE of a constraint no longer drops the supporting index if it was a pre-existing index".

AND

" The indexfile of the dump,has
CREATE UNIQUE INDEX ...
and
ALTER TABLE .... ADD CONSTRAINT

The index is explicitly created before the constraint is added. In 10g DISABLE/DROP of a constraint no longer drops the supporting index if it was a pre-existing index".

This is the the intended and current behavior of import. All the primary key constraints will be built on existing indexes. Hence, in target database when disabling the constraints the unique indexes will NOT be dropped.

SOLUTION

After the import use the cascade option to disable the constraint and drop its associated index:

SQL> alter table pktest DISABLE CONSTRAINT pk_pktest CASCADE DROP INDEX;