Home » Other » General » Question about what the content should be for a database standards document (Oracle 11.2.0.2 on Solaris 10)
Question about what the content should be for a database standards document [message #584389] Mon, 13 May 2013 10:24 Go to next message
orausern
Messages: 731
Registered: December 2005
Location: Hyderabad
Senior Member
Hi Experts,

I am working as development DBA in a J2EE project and have a task to create a database standards document. The document should be used as a reference by the developers when they submit requests for database changes - like adding or making changes to the database schemas (adding tables/modifying them etc.). I thought of what would be the right content for this type of standards document and I think that such a document should have the following as database standards:

1) Naming standards for the database objects.
This will include all database objects like tables, columns, primary keys, foreign keys, views, triggers, procedures etc.. For example a standard can be defined like: all primary keys should be named as PK_TABLENAME. All indexes should be named as <TABLE_NAME>_IDX01 etc.

2) Use of Oracle reserved words should be avoided in the schema object names.

3) The names should be descriptive so as to make their purpose self evident.

4) Wherever possible, tables should have primary keys.

5) Wherever possible columns should be defined NOT NULL -as it helps performance (Indexes on NULLABLE columns are not used by the optimizer).

6) When submitting request for a new table/tables, typical queries against these new tables should also be worked out and based on that indexes that will be needed for the new tables should be defined. In case this is not clear at the time of initial table creation, it should be clarified within 3 weeks (just a reasonable period to be mentioned here).

Is this fair enough content forthe standards document or does it miss some important aspects? Note that we do not have to cover in this document standards for database creation or RAC installation etc. It is a document of standards to be followed by developers as a reference when submitting database changes. I will be thankful for your review.

Thanks,
Re: Question about what the content should be for a database standards document [message #584396 is a reply to message #584389] Mon, 13 May 2013 12:08 Go to previous messageGo to next message
BlackSwan
Messages: 22792
Registered: January 2009
Senior Member
http://www.oracle.com/technetwork/java/codeconv-138413.html

I am sorry to see that GOOGLE is broken for you.
Re: Question about what the content should be for a database standards document [message #584397 is a reply to message #584396] Mon, 13 May 2013 12:19 Go to previous messageGo to next message
orausern
Messages: 731
Registered: December 2005
Location: Hyderabad
Senior Member
Well - my scenario is not related to PL/SQL code but about standards for specifying or creating DDLs in the database.
Re: Question about what the content should be for a database standards document [message #584398 is a reply to message #584389] Mon, 13 May 2013 12:54 Go to previous messageGo to next message
joy_division
Messages: 4515
Registered: February 2005
Location: East Coast USA
Senior Member
nirav_hyd wrote on Mon, 13 May 2013 11:24

5) Wherever possible columns should be defined NOT NULL -as it helps performance (Indexes on NULLABLE columns are not used by the optimizer).


Where did you hear this?
SQL> create table tt1 (c1 varchar2(10));

Table created.

SQL> create index ii1 on tt1(c1);

Index created.

SQL> insert into tt1 values ('A');

1 row created.

SQL> set autotrace traceonly explain
SQL> select * from SQL> tt1 where c1='A'
  2  /

Execution Plan
----------------------------------------------------------
Plan hash value: 3296136881

-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     7 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| II1  |     1 |     7 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("C1"='A')

Note
-----
   - dynamic sampling used for this statement

SQL> drop index ii1;

Index dropped.

SQL> select * from tt1 where c1='A'
  2  /

Execution Plan
----------------------------------------------------------
Plan hash value: 2058534797

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     7 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| TT1  |     1 |     7 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("C1"='A')

Note
-----
   - dynamic sampling used for this statement

Re: Question about what the content should be for a database standards document [message #584400 is a reply to message #584398] Mon, 13 May 2013 16:49 Go to previous message
Flyby
Messages: 144
Registered: March 2011
Location: Belgium
Senior Member
5) is actually a good point because by default statistics are not gathered for nulls
Previous Topic: Implementation of database vault, VPD & TDE
Next Topic: ADBA vs Apps DBA
Goto Forum:
  


Current Time: Sun Sep 21 01:46:20 CDT 2014

Total time taken to generate the page: 0.09351 seconds