RE: Naming standards -- that lead to implicit/explicit constraint and index creation.
- From: <Joel.Patterson@xxxxxxxxxxx>
- To: <Joel.Patterson@xxxxxxxxxxx>, <Freek.DHooge@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 16 Nov 2011 09:58:06 -0500
OK. Hold on. I focused to much and somehow did not create the implicit
indexes.... Duh
I'll be back (maybe).
... Arnold S.
Joel Patterson
Database Administrator
904 727-2546
_____________________________________________
From: Patterson, Joel
Sent: Wednesday, November 16, 2011 9:54 AM
To: Freek.DHooge@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: Naming standards -- that lead to implicit/explicit constraint and
index creation.
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 GENERATED
------------------------------ - ------------------------------ --------------
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')
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
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 GENERATED
------------------------------ - ------------------------------ --------------
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')
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
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
