Primary key creation using dbms_metadata ddl and after impdp

I had a problem where code stopped working the same way after I had recreated a primary key for the developers.  Initially they asked me to recreate an index, which I did. Then they asked me for a primary key so I created it using the index.  Their code started to error where it had not done before..

What they should have asked for was a primary key not a primary key using an existing index. There is a big difference in the way they behave when you delete the key. 

With a primary key that created it's own index, if you drop the key, the index goes with it.

If you create a primary key on an existing index, and then drop the key, the index stays in place, just the constraint gets dropped.

I'll show you how it works, but for a really great explanation, I also recommend you go have a look at http://viralpatel.net/blogs/understanding-primary-keypk-constraint-in-oracle/ as it explains it very well.

Here we go:

Create primary key allowing Oracle to create index

drop table tbl_test;
CREATE TABLE tbl_test ( col_1 NUMBER,col_2 NUMBER);
ALTER TABLE tbl_test ADD CONSTRAINT tbl_test_pk PRIMARY KEY(col_1);

select index_name, table_name from dba_indexes where index_name='TBL_TEST_PK';
INDEX_NAME                     TABLE_NAME
------------------------------ ------------------------------
TBL_TEST_PK                    TBL_TEST

SELECT constraint_name, constraint_type, index_name   FROM user_constraints
  WHERE table_name = 'TBL_TEST';
CONSTRAINT_NAME                C INDEX_NAME
------------------------------ - ------------------------------
TBL_TEST_PK                    P TBL_TEST_PK


This statement is the key bit of information about whether it will delete the index with the key drop or not, it;s explanation is at Jonathon Lewis' Scratchpad http://jonathanlewis.wordpress.com/2012/04/19/drop-constraint/

select do.object_name, do.object_id, bitand(ind$.property,4096), ind$.property
from   sys.ind$    ind$, dba_objects do 
where  do.owner='SYSTEM' 
and do.object_name = 'TBL_TEST_PK'
and do.object_id=ind$.obj#;
OBJECT_NAME
--------------------------------------------------------------------------------
 OBJECT_ID BITAND(IND$.PROPERTY,4096)   PROPERTY
---------- -------------------------- ----------
TBL_TEST_PK
     50284                       4096       4097

Dropping the primary key means the automatically created index also gets dropped.

ALTER TABLE tbl_test DROP PRIMARY KEY;
Table altered.
select index_name, table_name from dba_indexes where index_name='TBL_TEST_PK';
no rows selected

SELECT constraint_name, constraint_type, index_name   FROM user_constraints
  WHERE table_name = 'TBL_TEST';
no rows selected

select do.object_name, do.object_id, bitand(ind$.property,4096), ind$.property
from   sys.ind$    ind$, dba_objects do 
where  do.owner='SYSTEM' 
and do.object_name = 'TBL_TEST_PK'
and do.object_id=ind$.obj#;
no rows selected



Create a primary key using an existing index

drop table tbl_test2;
CREATE TABLE tbl_test2 ( col_1 NUMBER, col_2 NUMBER);
CREATE UNIQUE INDEX idx_col_1 ON tbl_test2(col_1);
ALTER TABLE tbl_test2 ADD CONSTRAINT tbl_test2_pk PRIMARY KEY(col_1) USING
INDEX idx_col_1;

select index_name, table_name from dba_indexes where index_name='IDX_COL_1';
INDEX_NAME                     TABLE_NAME
------------------------------ ------------------------------
IDX_COL_1                      TBL_TEST2

SELECT constraint_name, constraint_type, index_name   FROM user_constraints
  WHERE table_name = 'TBL_TEST2';
CONSTRAINT_NAME                C INDEX_NAME
------------------------------ - ----------------
TBL_TEST2_PK                   P IDX_COL_1

select do.object_name, do.object_id, bitand(ind$.property,4096), ind$.property
from   sys.ind$    ind$, dba_objects do 
where  do.owner='SYSTEM' 
and do.object_name='IDX_COL_1'
and do.object_id=ind$.obj#;
OBJECT_NAME
--------------------------------------------------------------------------------
 OBJECT_ID BITAND(IND$.PROPERTY,4096)   PROPERTY
---------- -------------------------- ----------
IDX_COL_1
     50286                          0          1

dropping the primary key will not delete the index

ALTER TABLE tbl_test2 DROP PRIMARY KEY;

select index_name, table_name from dba_indexes where index_name='IDX_COL_1';
INDEX_NAME                     TABLE_NAME
------------------------------ ------------------------------
IDX_COL_1                      TBL_TEST2

SELECT constraint_name, constraint_type, index_name   FROM user_constraints
  WHERE table_name = 'TBL_TEST2';
no rows selected


select do.object_name, do.object_id, bitand(ind$.property,4096), ind$.property
from   sys.ind$    ind$, dba_objects do 
where  do.owner='SYSTEM' 
and do.object_name='IDX_COL_1'
and do.object_id=ind$.obj#;
OBJECT_NAME
--------------------------------------------------------------------------------
 OBJECT_ID BITAND(IND$.PROPERTY,4096)   PROPERTY
---------- -------------------------- ----------
IDX_COL_1
     50286                          0          1



So now we understand how dropping the primary key affects the index it is based on, we know it makes a big difference how it is actually created.

Now look at what happens when the constraints are imported:

drop table tbl_test;
CREATE TABLE tbl_test ( col_1 NUMBER,
 col_2 NUMBER);
ALTER TABLE tbl_test ADD CONSTRAINT tbl_test_pk PRIMARY KEY(col_1);

drop table tbl_test2;
CREATE TABLE tbl_test2 ( col_1 NUMBER, col_2 NUMBER);
CREATE UNIQUE INDEX idx_col_1 ON tbl_test2(col_1); 
ALTER TABLE tbl_test2 ADD CONSTRAINT tbl_test2_pk PRIMARY KEY(col_1) USING INDEX idx_col_1;


select do.object_name, do.object_id, bitand(ind$.property,4096), ind$.property
from   sys.ind$    ind$, dba_objects do 
where  do.owner='SYSTEM' 
and do.object_name in ('TBL_TEST_PK','IDX_COL_1')
and do.object_id=ind$.obj#;
OBJECT_NAME
--------------------------------------------------------------
 OBJECT_ID BITAND(IND$.PROPERTY,4096)   PROPERTY
---------- -------------------------- ----------
IDX_COL_1
     50290                          0          1
TBL_TEST_PK
     50288                       4096       4097

CREATE DIRECTORY dpump_dir2 AS '/home/oracle';

vi myparfile

CONTENT=ALL
DIRECTORY=DPUMP_DIR2
REUSE_DUMPFILES=Y
TABLES=
system.tbl_test
system.tbl_test2

expdp system dumpfile=mytest.dmp logfile=mytest.log parfile=myparfile

drop table tbl_test;
drop table tbl_test2;

impdp system full=Y content=ALL directory=DPUMP_DIR2 dumpfile=mytest.dmp logfile=mytestimp.log

The tables, indexes and constraints are back:

select index_name, table_name from dba_indexes where index_name='TBL_TEST_PK';
INDEX_NAME                     TABLE_NAME
------------------------------ ------------------------------
TBL_TEST_PK                    TBL_TEST

SELECT constraint_name, constraint_type, index_name   FROM user_constraints  WHERE table_name = 'TBL_TEST';
CONSTRAINT_NAME                C INDEX_NAME
------------------------------ - ------------------------------
TBL_TEST_PK                    P TBL_TEST_PK

select index_name, table_name from dba_indexes where index_name='IDX_COL_1';
INDEX_NAME                     TABLE_NAME
------------------------------ ------------------------------
IDX_COL_1                      TBL_TEST2

SELECT constraint_name, constraint_type, index_name   FROM user_constraints  WHERE table_name = 'TBL_TEST2';
CONSTRAINT_NAME                C INDEX_NAME
------------------------------ - ------------------------------
TBL_TEST2_PK                   P IDX_COL_1


So far so good, bit now look:


select do.object_name, do.object_id, bitand(ind$.property,4096), ind$.property
from   sys.ind$    ind$, dba_objects do 
where  do.owner='SYSTEM' 
and do.object_name in ('TBL_TEST_PK','IDX_COL_1')
and do.object_id=ind$.obj#;

OBJECT_NAME
-------------------------------------------------------------------------------
 OBJECT_ID BITAND(IND$.PROPERTY,4096)   PROPERTY
---------- -------------------------- ----------
IDX_COL_1
     50335                          0          1
TBL_TEST_PK
     50334                          0          1


So this time the index is NOT dropped in either type of primary key when the primary key is dropped:

ALTER TABLE tbl_test DROP PRIMARY KEY;

select index_name, table_name from dba_indexes where index_name='TBL_TEST_PK';
INDEX_NAME                     TABLE_NAME
------------------------------ ------------------------------
TBL_TEST_PK                    TBL_TEST

SELECT constraint_name, constraint_type, index_name   FROM user_constraints  WHERE table_name = 'TBL_TEST';
no rows selected

ALTER TABLE tbl_test2 DROP PRIMARY KEY;
Table altered.

select index_name, table_name from dba_indexes where index_name='IDX_COL_1';

INDEX_NAME                     TABLE_NAME
------------------------------ ------------------------------
IDX_COL_1                      TBL_TEST2

SELECT constraint_name, constraint_type, index_name   FROM user_constraints  WHERE table_name = 'TBL_TEST2';
no rows selected


Impdp simply creates the keys as if they were created on existing indexes, it doesn't recreate them as they were built originlally.  I think this sucks.

dbms_metadata doesn't get it right either:

dbms_metadata also doesn't understand that primary keys have been created differently, but it treats them the opposite way to datapump:
                                                     
       
Create an inline primary key
                                                                      
 create table test (
 snorm   number,
 snurm   varchar2(40),
snerm   date,
 snirm   varchar2(4),
  constraint test_pk primary key(snorm));


select dbms_metadata.get_ddl ('TABLE','TEST','SYSTEM') from dual;
                                                                             
  CREATE TABLE "SYSTEM"."TEST"                                               
   ( "SNORM" NUMBER,                                                           
 "SNURM" VARCHAR2(40),                                                         
 "SNERM" DATE,                                                                 
 "SNIRM" VARCHAR2(4),                                                          
  CONSTRAINT "YINK_PK" PRIMARY KEY ("SNORM")                                   
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255                               
  TABLESPACE "USERS"  ENABLE                                                   
   ) SEGMENT CREATION DEFERRED                                                 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255                                
 NOCOMPRESS LOGGING                                                            
  TABLESPACE "USERS"                                                           
                                                                               
 select dbms_metadata.get_dependent_ddl('INDEX','TEST','SYSTEM') from dual;
                                                                             
  CREATE UNIQUE INDEX "SYSTEM"."TEST_PK" ON "SYSTEM"."TEST" ("SNORM")      
  PCTFREE 10 INITRANS 2 MAXTRANS 255                                           
  TABLESPACE "USERS"                                                           
                                                                               

Create a primary key on an existing index:

create table test2(
   snorm   number,
   snurm   varchar2(40),
   snerm   date,
   snirm   varchar2(4));

create index test2_idx on test2(snorm);

alter table test2 add constraint test2_pk primary key(snorm) using index test2_idx;

 select dbms_metadata.get_ddl('TABLE','TEST2','SYSTEM') from
                                                                             
  CREATE TABLE "SYSTEM"."TEST2"                                               
   ( "SNORM" NUMBER,                                                           
 "SNURM" VARCHAR2(40),                                                         
 "SNERM" DATE,                                                                 
 "SNIRM" VARCHAR2(4),                                                          
  CONSTRAINT "TEST2_PK" PRIMARY KEY ("SNORM")                                   
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS            
  TABLESPACE "USERS"  ENABLE                                                   
   ) SEGMENT CREATION DEFERRED                                                 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255                                
 NOCOMPRESS LOGGING                                                            
  TABLESPACE "USERS"                                                           
                                                                               
SQL> select dbms_metadata.get_dependent_ddl('INDEX','TEST2','SYSTEM') from dual;
                                                                             
  CREATE INDEX "SYSTEM"."TEST2_IDX" ON "SYSTEM"."TEST2" ("SNORM")            
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS                        
  TABLESPACE "USERS"                                                           
                                                                               


dbms_metadata produces the same ddl for both despite them being created very differently.

So using dbms_metadata to produce create database scripts could cause issues.  And incase you are wondering Toad does the same thing.

So if developers code relies on the index being retained or not when they drop constraints (as they often do before large data loads) then recreating uat databases using these methods could store up a heap of trouble later.


*Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit


Further reading:  https://nzdba.wordpress.com/tag/ind/