| Question about what the content should be for a database standards document [message #584389] |
Mon, 13 May 2013 10:24  |
orausern
Messages: 826
Registered: December 2005
|
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 #584398 is a reply to message #584389] |
Mon, 13 May 2013 12:54  |
joy_division
Messages: 4963
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
