Naming standards -- that lead to implicit/explicit constraint and index creation.
Date: Wed, 16 Nov 2011 09:54:09 -0500
Message-ID: <C95D75DD2E01DD4D81124D104D317ACA1B8BAEEEBE_at_JAXMSG01.crowley.com>
There has been discussion on this before on Oracle-L as I have googled and come across some of it. I am having trouble recreating the cases for using Explicit indexes, over implicit (as shown by query three in time).
Specifically for case 1, creating and index that 'could' be used by an unique or primary key constraint. Thus expdp/impdp would drop the implicit index and use the 'could' one. I wonder if you could suggest one.
For case 2, dropping the unique or primary key constraint would drop the associated index. (I wonder if that is also true for a Foreign Key constraint that is using a unique index - Theoretical muse)
If it matters, then maybe PK,PX, UK,UX is worth it, and the standard would be expanded to create these in a certain way.
Can someone suggest an Index, or show why this behavior is not happening with my below example?
set echo off
EXAMPLE ONE DROP TABLE IMPLICIT_TABLE; Table dropped.
DROP TABLE EXPLICIT_TABLE; Table dropped.
CREATE TABLE IMPLICIT_TABLE (
2 PRIM_COL number constraint IMPLICIT_TABLE_PK PRIMARY KEY
3 using index ( CREATE UNIQUE INDEX IMPLICIT_TABLE_PX ON 4 IMPLICIT_TABLE(PRIM_COL)), 5 UNIQ_COL number constraint IMPLICIT_TABLE_UNIQ_COL_UK UNIQUE 6 using index ( create UNIQUE index IMPLICIT_TABLE_UNIQ_COL_UX on 7 IMPLICIT_TABLE(UNIQ_COL)),
8 junk varchar2(10)
9 );
Table created.
CREATE INDEX IMPLICIT_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
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_NON_UNIQUE_IX
2 ON EXPLICIT_TABLE(PRIM_COL,UNIQ_COL);
Index created.
INSERT INTO EXPLICIT_TABLE VALUES (1,1,'Explicit');
1 row created.
commit;
Commit complete.
set echo off
################ RESULTS ######################## select index_name, index_type, uniqueness, generated 2 from user_indexes
3 where table_name in ('IMPLICIT_TABLE','EXPLICIT_TABLE') 4 order by index_name;
INDEX_NAME INDEX_TYPE UNIQUENES G
------------------------------ --------------------------- --------- -
EXPLICIT_NON_UNIQUE_IX NORMAL NONUNIQUE N EXPLICIT_TABLE_PX NORMAL UNIQUE N EXPLICIT_TABLE_UNIQ_COL_UX NORMAL UNIQUE N IMPLICIT_NON_UNIQUE_IX NORMAL NONUNIQUE N IMPLICIT_TABLE_PX NORMAL UNIQUE N IMPLICIT_TABLE_UNIQ_COL_UX NORMAL UNIQUE N
6 rows selected.
select constraint_name, constraint_type, index_name, generated
2 from user_constraints
3 where table_name in ('IMPLICIT_TABLE','EXPLICIT_TABLE')
4 order by constraint_name;
CONSTRAINT_NAME C INDEX_NAME GENERATED17 order by index_name, table_name;
------------------------------ - ------------------------------ --------------
EXPLICIT_TABLE_PK P EXPLICIT_TABLE_PX USER NAME EXPLICIT_TABLE_UNIQ_COL_UK U EXPLICIT_TABLE_UNIQ_COL_UX USER NAME IMPLICIT_TABLE_PK P IMPLICIT_TABLE_PX USER NAME IMPLICIT_TABLE_UNIQ_COL_UK U IMPLICIT_TABLE_UNIQ_COL_UX USER NAME select 2 obj_t.owner table_owner, obj_t.object_name table_name, 3 obj_i.owner index_owner, obj_i.object_name index_name, 4 decode(bitand(ind.property,4096), 4096, 'YES', 'NO') implicit, 5 decode(bitand(ind.property,1), 1, 'YES', 'NO') unique_index 6 from 7 dba_objects obj_t, 8 dba_objects obj_i, 9 sys.ind$ ind 10 where 11 ind.bo# = obj_t.object_id 12 and ind.obj# = obj_i.object_id 13 and obj_i.owner = 'DBMON' 14 and obj_i.object_name in 15 ('IMPLICIT_TABLE_PX', 'IMPLICIT_TABLE_UNIQ_COL_UX','EXPLICIT_TABLE_PX', 'EXPLICIT_TABLE_UNIQ_COL_UX', 16 'EXPLICIT_NON_UNIQUE_IX','IMPLICIT_NON_UNIQUE_IX')
TABLE_OWNE TABLE_NAME INDEX_OWNE INDEX_NAME IMPLICIT UNIQUE
---------- --------------- ---------- ------------------------------ -------- ------
DBMON EXPLICIT_TABLE DBMON EXPLICIT_NON_UNIQUE_IX NO NO DBMON EXPLICIT_TABLE DBMON EXPLICIT_TABLE_PX NO YES DBMON EXPLICIT_TABLE DBMON EXPLICIT_TABLE_UNIQ_COL_UX NO YES DBMON IMPLICIT_TABLE DBMON IMPLICIT_NON_UNIQUE_IX NO NO DBMON IMPLICIT_TABLE DBMON IMPLICIT_TABLE_PX YES YES DBMON IMPLICIT_TABLE DBMON IMPLICIT_TABLE_UNIQ_COL_UX YES YES
6 rows selected.
set echo off
Step 1) expdp, impdp
EXPORT TABLES, DROP TABLES, IMPORT TABLES
Hit any key to continue
select index_name, index_type, uniqueness, generated
2 from user_indexes
3 where table_name in ('IMPLICIT_TABLE','EXPLICIT_TABLE')
4 order by index_name;
INDEX_NAME INDEX_TYPE UNIQUENES G
------------------------------ --------------------------- --------- -
EXPLICIT_NON_UNIQUE_IX NORMAL NONUNIQUE N EXPLICIT_TABLE_PX NORMAL UNIQUE N EXPLICIT_TABLE_UNIQ_COL_UX NORMAL UNIQUE N IMPLICIT_NON_UNIQUE_IX NORMAL NONUNIQUE N IMPLICIT_TABLE_PX NORMAL UNIQUE N IMPLICIT_TABLE_UNIQ_COL_UX NORMAL UNIQUE N
6 rows selected.
select constraint_name, constraint_type, index_name, generated
2 from user_constraints
3 where table_name in ('IMPLICIT_TABLE','EXPLICIT_TABLE')
4 order by constraint_name;
CONSTRAINT_NAME C INDEX_NAME GENERATED17 order by index_name, table_name;
------------------------------ - ------------------------------ --------------
EXPLICIT_TABLE_PK P EXPLICIT_TABLE_PX USER NAME EXPLICIT_TABLE_UNIQ_COL_UK U EXPLICIT_TABLE_UNIQ_COL_UX USER NAME IMPLICIT_TABLE_PK P IMPLICIT_TABLE_PX USER NAME IMPLICIT_TABLE_UNIQ_COL_UK U IMPLICIT_TABLE_UNIQ_COL_UX USER NAME select 2 obj_t.owner table_owner, obj_t.object_name table_name, 3 obj_i.owner index_owner, obj_i.object_name index_name, 4 decode(bitand(ind.property,4096), 4096, 'YES', 'NO') implicit, 5 decode(bitand(ind.property,1), 1, 'YES', 'NO') unique_index 6 from 7 dba_objects obj_t, 8 dba_objects obj_i, 9 sys.ind$ ind 10 where 11 ind.bo# = obj_t.object_id 12 and ind.obj# = obj_i.object_id 13 and obj_i.owner = 'DBMON' 14 and obj_i.object_name in 15 ('IMPLICIT_TABLE_PX', 'IMPLICIT_TABLE_UNIQ_COL_UX','EXPLICIT_TABLE_PX', 'EXPLICIT_TABLE_UNIQ_COL_UX', 16 'EXPLICIT_NON_UNIQUE_IX','IMPLICIT_NON_UNIQUE_IX')
TABLE_OWNE TABLE_NAME INDEX_OWNE INDEX_NAME IMPLICIT UNIQUE
---------- --------------- ---------- ------------------------------ -------- ------
DBMON EXPLICIT_TABLE DBMON EXPLICIT_NON_UNIQUE_IX NO NO DBMON EXPLICIT_TABLE DBMON EXPLICIT_TABLE_PX NO YES DBMON EXPLICIT_TABLE DBMON EXPLICIT_TABLE_UNIQ_COL_UX NO YES DBMON IMPLICIT_TABLE DBMON IMPLICIT_NON_UNIQUE_IX NO NO DBMON IMPLICIT_TABLE DBMON IMPLICIT_TABLE_PX YES YES DBMON IMPLICIT_TABLE DBMON IMPLICIT_TABLE_UNIQ_COL_UX YES YES
6 rows selected.
set echo off
Step 2) Drop Constraints, check Indexes
Hit any key to continue
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, index_type, uniqueness, generated
2 from user_indexes
3 where table_name in ('IMPLICIT_TABLE','EXPLICIT_TABLE')
4 order by index_name;
INDEX_NAME INDEX_TYPE UNIQUENES G
------------------------------ --------------------------- --------- -
EXPLICIT_NON_UNIQUE_IX NORMAL NONUNIQUE N EXPLICIT_TABLE_PX NORMAL UNIQUE N EXPLICIT_TABLE_UNIQ_COL_UX NORMAL UNIQUE N IMPLICIT_NON_UNIQUE_IX NORMAL NONUNIQUE N select constraint_name, constraint_type, index_name, generated 2 from user_constraints
3 where table_name in ('IMPLICIT_TABLE','EXPLICIT_TABLE') 4 order by constraint_name;
no rows selected
select
2 obj_t.owner table_owner, obj_t.object_name table_name,
3 obj_i.owner index_owner, obj_i.object_name index_name,
4 decode(bitand(ind.property,4096), 4096, 'YES', 'NO') implicit,
5 decode(bitand(ind.property,1), 1, 'YES', 'NO') unique_index
6 from
7 dba_objects obj_t,
8 dba_objects obj_i,
9 sys.ind$ ind
10 where
11 ind.bo# = obj_t.object_id
12 and ind.obj# = obj_i.object_id
13 and obj_i.owner = 'DBMON'
14 and obj_i.object_name in
15 ('IMPLICIT_TABLE_PX', 'IMPLICIT_TABLE_UNIQ_COL_UX','EXPLICIT_TABLE_PX', 'EXPLICIT_TABLE_UNIQ_COL_UX',
16 'EXPLICIT_NON_UNIQUE_IX','IMPLICIT_NON_UNIQUE_IX')
17 order by index_name, table_name;
TABLE_OWNE TABLE_NAME INDEX_OWNE INDEX_NAME IMPLICIT UNIQUE
---------- --------------- ---------- ------------------------------ -------- ------
DBMON EXPLICIT_TABLE DBMON EXPLICIT_NON_UNIQUE_IX NO NO DBMON EXPLICIT_TABLE DBMON EXPLICIT_TABLE_PX NO YES DBMON EXPLICIT_TABLE DBMON EXPLICIT_TABLE_UNIQ_COL_UX NO YES DBMON IMPLICIT_TABLE DBMON IMPLICIT_NON_UNIQUE_IX NO NO
spool off
Joel Patterson
Database Administrator
904 727-2546
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Nov 16 2011 - 08:54:09 CST