RE: naming conventions (constraints and indexes)

From: <Joel.Patterson_at_crowley.com>
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                     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
3 rows selected.
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

Original text of this message