Oracle Database Best Practices (Part 1)

General guidelines for DB objects naming convention and Column Data Type selections

When working with Oracle Database for development or maintenance projects, developers very often tend to name DB objects in their database with naming conventions as per their style and not follow recommended standards. Some developers do not like to use “_” within names of DB objects like Primary, Foreign keys, while some tend to use “-” within the names, and others might have their own personal taste for naming the DB objects.

Same is true for choosing the Data Types for the database columns. Developers tend to choose any random value, for example, when declaring a VARCHAR2 type column.

I’m sharing below general guidelines on how to follow certain naming convention for DB objects and choosing Data Type for DB columns.

  • TABLE
  • INDEX
  • SEQUENCE
  • CONSTRAINT
  • LOB
  • SQL Nested Table TYPE
  • TRIGGER
  • VIEW and MATERIALIZED VIEW
  • DIRECTORY
  • EXTERNAL TABLE

Using the correct and most specific data type for each database column that is created, increases data integrity, decreases storage requirements and improves performance.

  • The correct data type increases data integrity by acting as a constraint. For example, if you use a datetime data type for a column of dates, then only dates can be stored in that column. However, if you use a character or numeric data type for the column, then eventually someone will store a character or numeric value that does not represent a date. You could write code to prevent this problem, but it is more efficient to use the correct data type.
  • Using the most specific length or precision affect storage requirements. If you give every column the maximum length or precision for its data type, then your application needlessly allocates many megabytes of RAM.
  • Correct data type improves performance because the incorrect data type can result in the incorrect execution plan.
  • Refer to below table to select the data types based on the requirements.

The above guidelines are applicable for other relational databases as well!

Developer at Oracle. All Things Data. 5xOCI. Always Curious. Views my own.