Naming standards -- that lead to implicit/explicit constraint and index creation.
Date: Wed, 16 Nov 2011 14:47:04 -0500
Message-ID: <C95D75DD2E01DD4D81124D104D317ACA1B8BAEF48E_at_JAXMSG01.crowley.com>
Oracle version 10.2.0.4
I would appreciate an example where an Index is created such that by creating an implicit primary key or unique index on the table, would cause one of the indexes not to be imported.
The below scenario shows three ways to create the tables, constraints and indexes.
The First time the unique and primary key constraints are dropped: IMPLICIT TABLES LOOSE THEIR UNIQUE INDEXES, and PRIMARY KEY INDEXES. i.e. All IMPLICIT INDEXES Dropped.
After Exporting and importing.
All Indexes, IMPLICIT AND EXPLICIT where imported Successfully.
(This is the scenario where I would like an example were all would not be imported).
NOW HOWEVER NOTE, ALL NINE OF THEM ARE EXPLICIT INDEXES!
The Second time the unique and primary key constraints are dropped, (after import):
ALL INDEXES REMAIN -- and REMAIN EXPLICIT,
Because they are now Explicit, they do not drop when dropping the constraints.
This is leading to a standard akin to the EXPLICIT_TABLE. I would like to document all the reasons I find.
(Script is available)
set echo off
EXAMPLE ONE DROP TABLE IMPLICIT_TABLE1; Table dropped.
DROP TABLE IMPLICIT_TABLE; Table dropped.
DROP TABLE EXPLICIT_TABLE; Table dropped.
CREATE TABLE IMPLICIT_TABLE1 (
2 PRIM_COL number constraint IMPLICIT_TABLE1_PK PRIMARY KEY
3 using index ( CREATE UNIQUE INDEX IMPLICIT_TABLE1_PX ON 4 IMPLICIT_TABLE1(PRIM_COL)), 5 UNIQ_COL number constraint IMPLICIT_TABLE1_UNIQ_COL_UK UNIQUE 6 using index ( create UNIQUE index IMPLICIT_TABLE1_UNIQ_COL_UX on 7 IMPLICIT_TABLE1(UNIQ_COL)),
8 junk varchar2(10)
9 );
Table created.
CREATE INDEX IMPLICIT_TABLE1_NON_UNIQUE_IX 2 ON IMPLICIT_TABLE1(PRIM_COL,UNIQ_COL); Index created.
INSERT INTO IMPLICIT_TABLE1 values (1,1,'Implicit1');
1 row created.
set echo off
EXAMPLE TWO
CREATE TABLE EXPLICIT_TABLE (
2 PRIM_COL number ,
3 UNIQ_COL number,
4 junk varchar2(10)
5 );
Table created.
CREATE UNIQUE INDEX EXPLICIT_TABLE_PX
2 on EXPLICIT_TABLE (PRIM_COL);
Index created.
ALTER TABLE EXPLICIT_TABLE add (
2 CONSTRAINT EXPLICIT_TABLE_PK PRIMARY KEY (PRIM_COL)
3 USING INDEX EXPLICIT_TABLE_PX);
Table altered.
CREATE UNIQUE INDEX EXPLICIT_TABLE_UNIQ_COL_UX 2 ON EXPLICIT_TABLE (UNIQ_COL); Index created.
ALTER TABLE EXPLICIT_TABLE add
2 CONSTRAINT EXPLICIT_TABLE_UNIQ_COL_UK UNIQUE (UNIQ_COL)
3 USING INDEX EXPLICIT_TABLE_UNIQ_COL_UX;
Table altered.
CREATE INDEX EXPLICIT_TABLE_NON_UNIQUE_IX 2 ON EXPLICIT_TABLE(PRIM_COL,UNIQ_COL); Index created.
INSERT INTO EXPLICIT_TABLE values (1,1,'Explicit');
1 row created.
set echo off
EXAMPLE THREE
CREATE TABLE IMPLICIT_TABLE (
2 PRIM_COL number ,
3 UNIQ_COL number,
4 junk varchar2(10)
5 );
Table created.
ALTER TABLE IMPLICIT_TABLE
2 ADD CONSTRAINT IMPLICIT_TABLE_PK PRIMARY KEY (PRIM_COL) 3 USING INDEX (CREATE UNIQUE INDEX IMPLICIT_TABLE_PX ON IMPLICIT_TABLE(PRIM_COL));
Table altered.
ALTER TABLE IMPLICIT_TABLE
2 ADD CONSTRAINT IMPLICIT_TABLE_UNIQ_COL_UK UNIQUE (UNIQ_COL) 3 USING INDEX (CREATE UNIQUE INDEX IMPLICIT_TABLE_UNIQ_COL_UX ON IMPLICIT_TABLE(UNIQ_COL));
Table altered.
CREATE INDEX IMPLICIT_TABLE_NON_UNIQUE_IX 2 ON IMPLICIT_TABLE(PRIM_COL,UNIQ_COL); Index created.
INSERT INTO IMPLICIT_TABLE values (1,1,'Implicit');
1 row created.
commit;
Commit complete.
set echo off
################ RESULTS ######################## select index_name, table_name, index_type, uniqueness, generated 2 from user_indexes
3 where table_name in ('IMPLICIT_TABLE','IMPLICIT_TABLE1','EXPLICIT_TABLE') 4 order by table_name, index_name;
INDEX_NAME TABLE_NAME INDEX_TYPE UNIQUENES G ------------------------------ --------------- --------------------------- --------- - EXPLICIT_TABLE_NON_UNIQUE_IX EXPLICIT_TABLE NORMAL NONUNIQUE N EXPLICIT_TABLE_PX EXPLICIT_TABLE NORMAL UNIQUE N EXPLICIT_TABLE_UNIQ_COL_UX EXPLICIT_TABLE NORMAL UNIQUE N IMPLICIT_TABLE_NON_UNIQUE_IX IMPLICIT_TABLE NORMAL NONUNIQUE N IMPLICIT_TABLE_PX IMPLICIT_TABLE NORMAL UNIQUE N IMPLICIT_TABLE_UNIQ_COL_UX IMPLICIT_TABLE NORMAL UNIQUE N IMPLICIT_TABLE1_NON_UNIQUE_IX IMPLICIT_TABLE1 NORMAL NONUNIQUE N IMPLICIT_TABLE1_PX IMPLICIT_TABLE1 NORMAL UNIQUE N IMPLICIT_TABLE1_UNIQ_COL_UX IMPLICIT_TABLE1 NORMAL UNIQUE N
9 rows selected.
select constraint_name, constraint_type, index_name, table_name, generated
2 from user_constraints
3 where table_name in ('IMPLICIT_TABLE','IMPLICIT_TABLE1','EXPLICIT_TABLE')
4 order by table_name, constraint_name;
CONSTRAINT_NAME C INDEX_NAME TABLE_NAME GENERATED ------------------------------ - ------------------------------ --------------- -------------- EXPLICIT_TABLE_PK P EXPLICIT_TABLE_PX EXPLICIT_TABLE USER NAME EXPLICIT_TABLE_UNIQ_COL_UK U EXPLICIT_TABLE_UNIQ_COL_UX EXPLICIT_TABLE USER NAME IMPLICIT_TABLE_PK P IMPLICIT_TABLE_PX IMPLICIT_TABLE USER NAME IMPLICIT_TABLE_UNIQ_COL_UK U IMPLICIT_TABLE_UNIQ_COL_UX IMPLICIT_TABLE USER NAME IMPLICIT_TABLE1_PK P IMPLICIT_TABLE1_PX IMPLICIT_TABLE1 USER NAMEIMPLICIT_TABLE1_UNIQ_COL_UK U IMPLICIT_TABLE1_UNIQ_COL_UX IMPLICIT_TABLE1 USER NAME 6 rows selected.
select
2 obj_t.object_name table_name, obj_i.object_name index_name,
3 decode(bitand(ind.property,4096), 4096, 'YES', 'NO') implicit,
4 decode(bitand(ind.property,1), 1, 'YES', 'NO') unique_index
5 from
6 dba_objects obj_t,
7 dba_objects obj_i,
8 sys.ind$ ind
9 where
10 ind.bo# = obj_t.object_id
11 and ind.obj# = obj_i.object_id
12 and obj_i.owner = 'DBMON'
13 and obj_i.object_name in
14 ('IMPLICIT_TABLE_PX', 'IMPLICIT_TABLE_UNIQ_COL_UX', 'IMPLICIT_TABLE_NON_UNIQUE_IX',
15 'IMPLICIT_TABLE1_PX', 'IMPLICIT_TABLE1_UNIQ_COL_UX', 'IMPLICIT_TABLE1_NON_UNIQUE_IX',
16 'EXPLICIT_TABLE_PX', 'EXPLICIT_TABLE_UNIQ_COL_UX', 'EXPLICIT_TABLE_NON_UNIQUE_IX')
17 order by table_name, index_name;
TABLE_NAME INDEX_NAME IMPLICIT UNIQUE
--------------- ------------------------------ -------- ------
EXPLICIT_TABLE EXPLICIT_TABLE_NON_UNIQUE_IX NO NO EXPLICIT_TABLE EXPLICIT_TABLE_PX NO YES EXPLICIT_TABLE EXPLICIT_TABLE_UNIQ_COL_UX NO YES IMPLICIT_TABLE IMPLICIT_TABLE_NON_UNIQUE_IX NO NO IMPLICIT_TABLE IMPLICIT_TABLE_PX YES YES IMPLICIT_TABLE IMPLICIT_TABLE_UNIQ_COL_UX YES YES IMPLICIT_TABLE1 IMPLICIT_TABLE1_NON_UNIQUE_IX NO NO IMPLICIT_TABLE1 IMPLICIT_TABLE1_PX YES YES IMPLICIT_TABLE1 IMPLICIT_TABLE1_UNIQ_COL_UX YES YES
9 rows selected.
set echo off
Steps:
1) EXPORT TABLES 2) DROP CONSTRAINTS and Look. 3) DROP TABLES from another session and IMPORT TABLES 4) Look 5) DROP CONSTRAINTS again and look.
AFTER EXPORTING TABLES, Hit any key to continue. Now we will see what happens after dropping constraints.
Step 2 drop constraints and look.
" IMPLICIT TABLES WILL LOOSE THEIR UNIQUE INDEXES, and PRIMARY KEY INDEXES." " i.e. All IMPLICIT INDEXES Dropped."
ALTER TABLE IMPLICIT_TABLE1 DROP CONSTRAINT IMPLICIT_TABLE1_PK; Table altered.
ALTER TABLE IMPLICIT_TABLE1 DROP CONSTRAINT IMPLICIT_TABLE1_UNIQ_COL_UK; Table altered.
ALTER TABLE EXPLICIT_TABLE DROP CONSTRAINT EXPLICIT_TABLE_PK; Table altered.
ALTER TABLE EXPLICIT_TABLE DROP CONSTRAINT EXPLICIT_TABLE_UNIQ_COL_UK; Table altered.
ALTER TABLE IMPLICIT_TABLE DROP CONSTRAINT IMPLICIT_TABLE_PK; Table altered.
ALTER TABLE IMPLICIT_TABLE DROP CONSTRAINT IMPLICIT_TABLE_UNIQ_COL_UK; Table altered.
select index_name, table_name, index_type, uniqueness, generated
2 from user_indexes
3 where table_name in ('IMPLICIT_TABLE','IMPLICIT_TABLE1','EXPLICIT_TABLE')
4 order by table_name, index_name;
INDEX_NAME TABLE_NAME INDEX_TYPE UNIQUENES G ------------------------------ --------------- --------------------------- --------- - EXPLICIT_TABLE_NON_UNIQUE_IX EXPLICIT_TABLE NORMAL NONUNIQUE N EXPLICIT_TABLE_PX EXPLICIT_TABLE NORMAL UNIQUE N EXPLICIT_TABLE_UNIQ_COL_UX EXPLICIT_TABLE NORMAL UNIQUE N IMPLICIT_TABLE_NON_UNIQUE_IX IMPLICIT_TABLE NORMAL NONUNIQUE N IMPLICIT_TABLE1_NON_UNIQUE_IX IMPLICIT_TABLE1 NORMAL NONUNIQUE N select constraint_name, constraint_type, index_name, table_name, generated 2 from user_constraints
3 where table_name in ('IMPLICIT_TABLE','IMPLICIT_TABLE1','EXPLICIT_TABLE') 4 order by table_name, constraint_name;
no rows selected
select
2 obj_t.object_name table_name, obj_i.object_name index_name,
3 decode(bitand(ind.property,4096), 4096, 'YES', 'NO') implicit,
4 decode(bitand(ind.property,1), 1, 'YES', 'NO') unique_index
5 from
6 dba_objects obj_t,
7 dba_objects obj_i,
8 sys.ind$ ind
9 where
10 ind.bo# = obj_t.object_id
11 and ind.obj# = obj_i.object_id
12 and obj_i.owner = 'DBMON'
13 and obj_i.object_name in
14 ('IMPLICIT_TABLE_PX', 'IMPLICIT_TABLE_UNIQ_COL_UX', 'IMPLICIT_TABLE_NON_UNIQUE_IX',
15 'IMPLICIT_TABLE1_PX', 'IMPLICIT_TABLE1_UNIQ_COL_UX', 'IMPLICIT_TABLE1_NON_UNIQUE_IX',
16 'EXPLICIT_TABLE_PX', 'EXPLICIT_TABLE_UNIQ_COL_UX', 'EXPLICIT_TABLE_NON_UNIQUE_IX')
17 order by index_name, table_name;
TABLE_NAME INDEX_NAME IMPLICIT UNIQUE
--------------- ------------------------------ -------- ------
EXPLICIT_TABLE EXPLICIT_TABLE_NON_UNIQUE_IX NO NO EXPLICIT_TABLE EXPLICIT_TABLE_PX NO YES EXPLICIT_TABLE EXPLICIT_TABLE_UNIQ_COL_UX NO YES IMPLICIT_TABLE1 IMPLICIT_TABLE1_NON_UNIQUE_IX NO NO IMPLICIT_TABLE IMPLICIT_TABLE_NON_UNIQUE_IX NO NO
set echo off
Step 3 drop tables from another session and import. Then look.
AFTER IMPORTING TABLES,
All Indexes, IMPLICIT AND EXPLICIT have been imported Sucessfully
EXCEPT NOW ALL NINE OF THEM ARE EXPLICIT!
--- Need that example index that will cause one not to import.
AFTER IMPORTING TABLES, Hit any key to continue.
select index_name, table_name, index_type, uniqueness, generated
2 from user_indexes
3 where table_name in ('IMPLICIT_TABLE','IMPLICIT_TABLE1','EXPLICIT_TABLE')
4 order by table_name, index_name;
INDEX_NAME TABLE_NAME INDEX_TYPE UNIQUENES G ------------------------------ --------------- --------------------------- --------- - EXPLICIT_TABLE_NON_UNIQUE_IX EXPLICIT_TABLE NORMAL NONUNIQUE N EXPLICIT_TABLE_PX EXPLICIT_TABLE NORMAL UNIQUE N EXPLICIT_TABLE_UNIQ_COL_UX EXPLICIT_TABLE NORMAL UNIQUE N IMPLICIT_TABLE_NON_UNIQUE_IX IMPLICIT_TABLE NORMAL NONUNIQUE N IMPLICIT_TABLE_PX IMPLICIT_TABLE NORMAL UNIQUE N IMPLICIT_TABLE_UNIQ_COL_UX IMPLICIT_TABLE NORMAL UNIQUE N IMPLICIT_TABLE1_NON_UNIQUE_IX IMPLICIT_TABLE1 NORMAL NONUNIQUE N IMPLICIT_TABLE1_PX IMPLICIT_TABLE1 NORMAL UNIQUE N IMPLICIT_TABLE1_UNIQ_COL_UX IMPLICIT_TABLE1 NORMAL UNIQUE N
9 rows selected.
select constraint_name, constraint_type, index_name, table_name, generated
2 from user_constraints
3 where table_name in ('IMPLICIT_TABLE','IMPLICIT_TABLE1','EXPLICIT_TABLE')
4 order by table_name, constraint_name;
CONSTRAINT_NAME C INDEX_NAME TABLE_NAME GENERATED ------------------------------ - ------------------------------ --------------- -------------- EXPLICIT_TABLE_PK P EXPLICIT_TABLE_PX EXPLICIT_TABLE USER NAME EXPLICIT_TABLE_UNIQ_COL_UK U EXPLICIT_TABLE_UNIQ_COL_UX EXPLICIT_TABLE USER NAME IMPLICIT_TABLE_PK P IMPLICIT_TABLE_NON_UNIQUE_IX IMPLICIT_TABLE USER NAME IMPLICIT_TABLE_UNIQ_COL_UK U IMPLICIT_TABLE_UNIQ_COL_UX IMPLICIT_TABLE USER NAME IMPLICIT_TABLE1_PK P IMPLICIT_TABLE1_PX IMPLICIT_TABLE1 USER NAMEIMPLICIT_TABLE1_UNIQ_COL_UK U IMPLICIT_TABLE1_UNIQ_COL_UX IMPLICIT_TABLE1 USER NAME 6 rows selected.
select
2 obj_t.object_name table_name, obj_i.object_name index_name,
3 decode(bitand(ind.property,4096), 4096, 'YES', 'NO') implicit,
4 decode(bitand(ind.property,1), 1, 'YES', 'NO') unique_index
5 from
6 dba_objects obj_t,
7 dba_objects obj_i,
8 sys.ind$ ind
9 where
10 ind.bo# = obj_t.object_id
11 and ind.obj# = obj_i.object_id
12 and obj_i.owner = 'DBMON'
13 and obj_i.object_name in
14 ('IMPLICIT_TABLE_PX', 'IMPLICIT_TABLE_UNIQ_COL_UX', 'IMPLICIT_TABLE_NON_UNIQUE_IX',
15 'IMPLICIT_TABLE1_PX', 'IMPLICIT_TABLE1_UNIQ_COL_UX', 'IMPLICIT_TABLE1_NON_UNIQUE_IX',
16 'EXPLICIT_TABLE_PX', 'EXPLICIT_TABLE_UNIQ_COL_UX', 'EXPLICIT_TABLE_NON_UNIQUE_IX')
17 order by table_name, index_name;
TABLE_NAME INDEX_NAME IMPLICIT UNIQUE
--------------- ------------------------------ -------- ------
EXPLICIT_TABLE EXPLICIT_TABLE_NON_UNIQUE_IX NO NO EXPLICIT_TABLE EXPLICIT_TABLE_PX NO YES EXPLICIT_TABLE EXPLICIT_TABLE_UNIQ_COL_UX NO YES IMPLICIT_TABLE IMPLICIT_TABLE_NON_UNIQUE_IX NO NO IMPLICIT_TABLE IMPLICIT_TABLE_PX NO YES IMPLICIT_TABLE IMPLICIT_TABLE_UNIQ_COL_UX NO YES IMPLICIT_TABLE1 IMPLICIT_TABLE1_NON_UNIQUE_IX NO NO IMPLICIT_TABLE1 IMPLICIT_TABLE1_PX NO YES IMPLICIT_TABLE1 IMPLICIT_TABLE1_UNIQ_COL_UX NO YES
9 rows selected.
set echo off
Step 5)
Drop Constraints again, but this time AFTER IMPORT of original tables, Look.
ALL INDEXES REMAIN -- and REMAIN EXPLICIT, therefore none drop.
ALTER TABLE IMPLICIT_TABLE1 DROP CONSTRAINT IMPLICIT_TABLE1_PK; Table altered.
ALTER TABLE IMPLICIT_TABLE1 DROP CONSTRAINT IMPLICIT_TABLE1_UNIQ_COL_UK; Table altered.
ALTER TABLE EXPLICIT_TABLE DROP CONSTRAINT EXPLICIT_TABLE_PK; Table altered.
ALTER TABLE EXPLICIT_TABLE DROP CONSTRAINT EXPLICIT_TABLE_UNIQ_COL_UK; Table altered.
ALTER TABLE IMPLICIT_TABLE DROP CONSTRAINT IMPLICIT_TABLE_PK; Table altered.
ALTER TABLE IMPLICIT_TABLE DROP CONSTRAINT IMPLICIT_TABLE_UNIQ_COL_UK; Table altered.
select index_name, table_name, index_type, uniqueness, generated
2 from user_indexes
3 where table_name in ('IMPLICIT_TABLE','IMPLICIT_TABLE1','EXPLICIT_TABLE')
4 order by table_name, index_name;
INDEX_NAME TABLE_NAME INDEX_TYPE UNIQUENES G ------------------------------ --------------- --------------------------- --------- - EXPLICIT_TABLE_NON_UNIQUE_IX EXPLICIT_TABLE NORMAL NONUNIQUE N EXPLICIT_TABLE_PX EXPLICIT_TABLE NORMAL UNIQUE N EXPLICIT_TABLE_UNIQ_COL_UX EXPLICIT_TABLE NORMAL UNIQUE N IMPLICIT_TABLE_NON_UNIQUE_IX IMPLICIT_TABLE NORMAL NONUNIQUE N IMPLICIT_TABLE_PX IMPLICIT_TABLE NORMAL UNIQUE N IMPLICIT_TABLE_UNIQ_COL_UX IMPLICIT_TABLE NORMAL UNIQUE N IMPLICIT_TABLE1_NON_UNIQUE_IX IMPLICIT_TABLE1 NORMAL NONUNIQUE N IMPLICIT_TABLE1_PX IMPLICIT_TABLE1 NORMAL UNIQUE N IMPLICIT_TABLE1_UNIQ_COL_UX IMPLICIT_TABLE1 NORMAL UNIQUE N
9 rows selected.
select constraint_name, constraint_type, index_name, table_name, generated
2 from user_constraints
3 where table_name in ('IMPLICIT_TABLE','IMPLICIT_TABLE1','EXPLICIT_TABLE')
4 order by table_name, constraint_name;
no rows selected
select
2 obj_t.object_name table_name, obj_i.object_name index_name,
3 decode(bitand(ind.property,4096), 4096, 'YES', 'NO') implicit,
4 decode(bitand(ind.property,1), 1, 'YES', 'NO') unique_index
5 from
6 dba_objects obj_t,
7 dba_objects obj_i,
8 sys.ind$ ind
9 where
10 ind.bo# = obj_t.object_id
11 and ind.obj# = obj_i.object_id
12 and obj_i.owner = 'DBMON'
13 and obj_i.object_name in
14 ('IMPLICIT_TABLE_PX', 'IMPLICIT_TABLE_UNIQ_COL_UX', 'IMPLICIT_TABLE_NON_UNIQUE_IX',
15 'IMPLICIT_TABLE1_PX', 'IMPLICIT_TABLE1_UNIQ_COL_UX', 'IMPLICIT_TABLE1_NON_UNIQUE_IX',
16 'EXPLICIT_TABLE_PX', 'EXPLICIT_TABLE_UNIQ_COL_UX', 'EXPLICIT_TABLE_NON_UNIQUE_IX')
17 order by table_name, index_name;
TABLE_NAME INDEX_NAME IMPLICIT UNIQUE
--------------- ------------------------------ -------- ------
EXPLICIT_TABLE EXPLICIT_TABLE_NON_UNIQUE_IX NO NO EXPLICIT_TABLE EXPLICIT_TABLE_PX NO YES EXPLICIT_TABLE EXPLICIT_TABLE_UNIQ_COL_UX NO YES IMPLICIT_TABLE IMPLICIT_TABLE_NON_UNIQUE_IX NO NO IMPLICIT_TABLE IMPLICIT_TABLE_PX NO YES IMPLICIT_TABLE IMPLICIT_TABLE_UNIQ_COL_UX NO YES IMPLICIT_TABLE1 IMPLICIT_TABLE1_NON_UNIQUE_IX NO NO IMPLICIT_TABLE1 IMPLICIT_TABLE1_PX NO YES IMPLICIT_TABLE1 IMPLICIT_TABLE1_UNIQ_COL_UX NO YES
9 rows selected.
spool off
Joel Patterson
Database Administrator
904 727-2546
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Nov 16 2011 - 13:47:04 CST