RE: naming conventions (constraints and indexes)
Date: Thu, 10 Nov 2011 09:11:06 -0500
Message-ID: <C95D75DD2E01DD4D81124D104D317ACA1B8A2E23F9_at_JAXMSG01.crowley.com>
Duh, thx
Joel Patterson
Database Administrator
904 727-2546
From: Phillip Jones [mailto:phil_at_phillip.im] Sent: Thursday, November 10, 2011 8:55 AM To: Patterson, Joel
Cc: ORACLE-L
Subject: Re: naming conventions (constraints and indexes)
Is the object owner really 'FDH' ?
Looks like it's 'DBMON' if your sqlplus prompt is anything to go by.
Cheers,
Phil
On Thu, Nov 10, 2011 at 1:46 PM, <Joel.Patterson_at_crowley.com<mailto:Joel.Patterson_at_crowley.com>> wrote:
The table creation was actually taken from an example where Tom Kyte responded to a question as to the best way/order to create the index/constratint, 28 Apr 2008.
http://asktom.oracle.com/pls/asktom/f?p0:11:1892071625408060::::P11_QUESTION_ID:36858373078604
However, as part of this process I will review a best practices method of creating these things as has been pointed out in a couple places.
Personally, I have been kind of living with the index drop issue for a long long time, so it wasn't a major concern. I think I'm going to return and review the issue again while I'm on the subject of naming and standards.
I cannot seem to get any rows returned from your select from dba_objects query 10.2.0.4 or 11.2.0.1.
DBMON _at_ lawrtdev> 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.
DBMON _at_ lawrtdev>
DBMON _at_ lawrtdev> create table t (veld1 number(10));
Table created.
DBMON _at_ lawrtdev> insert into t values (1);
1 row created.
DBMON _at_ lawrtdev> insert into example_table values (1,1,'Example');
1 row created.
DBMON _at_ lawrtdev> commit;
Commit complete.
DBMON _at_ lawrtdev> alter table t add constraint t_pk primary key (veld1) 2 using index (create unique index t_i on t (veld1));
Table altered.
DBMON _at_ lawrtdev>
DBMON _at_ lawrtdev> 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_UNIQ_COL_UX NORMAL NONUNIQUE N EXAMPLE_TABLE_PX NORMAL NONUNIQUE N T_I NORMAL UNIQUE N
3 rows selected.
DBMON _at_ lawrtdev>
DBMON _at_ lawrtdev> select constraint_name, constraint_type, index_name, generated
2 from user_constraints where table_name in ('T', 'EXAMPLE_TABLE');
CONSTRAINT_NAME C INDEX_NAME GENERATED3 rows selected.
------------------------------ - ------------------------------ --------------
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
DBMON _at_ lawrtdev> DBMON _at_ lawrtdev> DBMON _at_ lawrtdev> 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#<http://ind.bo> = obj_t.object_id 12 and ind.obj# = obj_i.object_id 13 and obj_i.owner = 'FDH' 14 and obj_i.object_name = 'T_I' 15 -- ('T_I', 'EXAMPLE_TABLE_PX', 'EXAMPLE_TABLE_UNIQ_COL_UX')16 order by table_name, index_name ;
no rows selected
DBMON _at_ lawrtdev>
DBMON _at_ lawrtdev> alter table t drop constraint t_pk;
Table altered.
DBMON _at_ lawrtdev>
DBMON _at_ lawrtdev> select index_name from user_indexes where table_name in ('T', 'EXAMPLE_TABLE');
INDEX_NAME
EXAMPLE_TABLE_UNIQ_COL_UX
EXAMPLE_TABLE_PX 2 rows selected.
DBMON _at_ lawrtdev>
DBMON _at_ lawrtdev> alter table t add primary key (veld1)
2 using index (create unique index t_i on t (veld1));
Table altered.
DBMON _at_ lawrtdev>
DBMON _at_ lawrtdev> 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_UNIQ_COL_UX NORMAL NONUNIQUE N EXAMPLE_TABLE_PX NORMAL NONUNIQUE N T_I NORMAL UNIQUE Y
3 rows selected.
DBMON _at_ lawrtdev>
DBMON _at_ lawrtdev> 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#<http://ind.bo> = obj_t.object_id 12 and ind.obj# = obj_i.object_id 13 and obj_i.owner = 'FDH' 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;
no rows selected
DBMON _at_ lawrtdev>
DBMON _at_ lawrtdev> set echo off
Joel Patterson
Database Administrator
904 727-2546<tel:904%20727-2546>
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Nov 10 2011 - 08:11:06 CST