constraint and index creation
Date: Thu, 10 Nov 2011 10:02:04 -0500
Message-ID: <C95D75DD2E01DD4D81124D104D317ACA1B8A2E252F_at_JAXMSG01.crowley.com>
I think I missed something.
> You might want to be carefull about using the "use index" clause to create a supporting index while creating the constraint.
Such an index will be regarded by Oracle as an implicit index.
Both examples shows IMPLICIT
Both examples use the using index clause.
First) alter table t add constraint t_pk primary key (veld1) using index (create unique index t_i on t (veld1)); Second) alter table t add primary key (veld1) using index (create unique index t_i on t (veld1));
The First example has a USER generated name, T_I. The Second example has a SYSTEM generated name, SYS_C00112556. -- Clearly I do not want this!!!
> This has as effect that the index will not be recreated when performing an export / import if there is another index (unique or nonunique) which can be used by Oracle to support the constraint.
- Both examples show IMPLICIT?
> Also when dropping the constraint the index supporting it will also be dropped by default (if it is an unique index).
-- unless you use KEEP INDEX clause.
I reran your queries and added another table creating indexes without UNIQUE modifier below (just to have a third example) for reference:
CREATE TABLE EXAMPLE_TABLE (
2 PRIM_COL number constraint EXAMPLE_TABLE_PK primary key
3 using index ( CREATE INDEX EXAMPLE_TABLE_PX ON 4 EXAMPLE_TABLE(PRIM_COL)), 5 UNIQ_COL number constraint EXAMPLE_TABLE_UNIQ_COL_UK UNIQUE 6 using index ( create index EXAMPLE_TABLE_UNIQ_COL_UX on 7 EXAMPLE_TABLE(UNIQ_COL)),
8 junk varchar2(10)
9 );
Table created.
create table t (veld1 number(10));
Table created.
insert into t values (1);
1 row created.
insert into example_table values (1,1,'Example');
1 row created.
commit;
Commit complete.
alter table t add constraint t_pk primary key (veld1) 2 using index (create unique index t_i on t (veld1));
Table altered.
select index_name, index_type, uniqueness, generated 2 from user_indexes where table_name in ('T', 'EXAMPLE_TABLE');
INDEX_NAME INDEX_TYPE UNIQUENES G
------------------------------ --------------------------- --------- -
EXAMPLE_TABLE_PX NORMAL NONUNIQUE N
EXAMPLE_TABLE_UNIQ_COL_UX NORMAL NONUNIQUE N
T_I NORMAL UNIQUE N
select constraint_name, constraint_type, index_name, generated
2 from user_constraints where table_name in ('T', 'EXAMPLE_TABLE');
CONSTRAINT_NAME C INDEX_NAME GENERATED
------------------------------ - ------------------------------ --------------
EXAMPLE_TABLE_PK P EXAMPLE_TABLE_PX USER NAME
EXAMPLE_TABLE_UNIQ_COL_UK U EXAMPLE_TABLE_UNIQ_COL_UX USER NAME
T_PK P T_I 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 ('T_I', 'EXAMPLE_TABLE_PX', 'EXAMPLE_TABLE_UNIQ_COL_UX')
16 order by table_name, index_name ;
TABLE_OWNER TABLE_NAME INDEX_OWNER INDEX_NAME IMPLICIT UNIQUE_INDEX -------------------- -------------------- -------------------- ------------------------------ -------------------- -------------------- DBMON EXAMPLE_TABLE DBMON EXAMPLE_TABLE_PX YES NO DBMON EXAMPLE_TABLE DBMON EXAMPLE_TABLE_UNIQ_COL_UX YES NO DBMON T DBMON T_I YES YES
alter table t drop constraint t_pk;
Table altered.
select index_name from user_indexes where table_name in ('T', 'EXAMPLE_TABLE');
INDEX_NAME
EXAMPLE_TABLE_PX
EXAMPLE_TABLE_UNIQ_COL_UX alter table t add primary key (veld1)
2 using index (create unique index t_i on t (veld1));
Table altered.
select index_name, index_type, uniqueness, generated 2 from user_indexes where table_name in ('T', 'EXAMPLE_TABLE');
INDEX_NAME INDEX_TYPE UNIQUENES G
------------------------------ --------------------------- --------- -
EXAMPLE_TABLE_PX NORMAL NONUNIQUE N
EXAMPLE_TABLE_UNIQ_COL_UX NORMAL NONUNIQUE N
T_I NORMAL UNIQUE Y
select constraint_name, constraint_type, index_name, generated
2 from user_constraints where table_name in ('T', 'EXAMPLE_TABLE');
CONSTRAINT_NAME C INDEX_NAME GENERATED
------------------------------ - ------------------------------ --------------
EXAMPLE_TABLE_PK P EXAMPLE_TABLE_PX USER NAME
EXAMPLE_TABLE_UNIQ_COL_UK U EXAMPLE_TABLE_UNIQ_COL_UX USER NAME
SYS_C00112556 P T_I GENERATED 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 ('T_I', 'EXAMPLE_TABLE_PX', 'EXAMPLE_TABLE_UNIQ_COL_UX')
16 order by table_name, index_name;
TABLE_OWNER TABLE_NAME INDEX_OWNER INDEX_NAME IMPLICIT UNIQUE_INDEX -------------------- -------------------- -------------------- ------------------------------ -------------------- -------------------- DBMON EXAMPLE_TABLE DBMON EXAMPLE_TABLE_PX YES NO DBMON EXAMPLE_TABLE DBMON EXAMPLE_TABLE_UNIQ_COL_UX YES NO DBMON T DBMON T_I YES YES
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Nov 10 2011 - 09:02:04 CST