Column order - suggestions please

[padma]

Hi,

Can anyone tell me if there is an oracle standard for creating the table columns in a particular order (like primary key first and all the not nulls next etc.....& the long & LOBS at the end) or oracle does not care about the order?

Can anyone tell me if there is a solid document from oracle on this?

Also, does the order of columns have anything to do with the performance of the queries and the DMLs?

Thanks in advance

Padma

[Halo]

AFAIK, there is no particular order in which the columns are created in a table. Performance depends mainly on the indexes created and how the DML is written.

[sambavan]

Here is an article from pinnacle:

10-SECOND TUTORIAL: SQL Programming Standards
---------------------------------------------------------
I know I'm getting into dangerous territory by bringing
up the "S" word - standards -- but I feel that this is a
subject that's important enough to warrant discussion,
even though I run the risk of being flamed by the
experienced PL/SQL developers out there, who might
disagree with my "proposed" standards.

In my view, the purpose of PL/SQL standards is to:
(a) Define a framework for coding PL/SQL programs;
(b) Simplify program maintenance and debugging if all
PL/SQL programs on a project or within an
organization conform to certain established
conventions;
(c) Conform reasonably well to industry rules such that
new hires can easily adapt to the coding standards;
and
(d) Show commitment to the quality of the application.

In order for the standards to be successful, we should
provide a framework that covers every aspect of PL/SQL
coding:
(a) Object naming conventions for all database objects:
* Tables, views, and partitions
* Constraints: primary keys, unique keys, foreign
keys, check constraints
* Index naming conventions
* Stored programs, triggers
-- When to use them? Which one to use?
* Sequences, etc.
(b) Database table and column definitions
* Is there a company-wide abbreviation list for naming
tables and columns (e.g., DESC for "description" and
NUM for "number")?
* Is there a standard list of column definitions
(e.g., the description field is always
VARCHAR2(2000) for all applications)?
* When to use nullable columns vs. mandatory columns?
* When to provide column-level default values?
* Should there be one global table for all reference
codes or multiple reference code tables?
* Which datatype to use: VARCHAR2 vs. CHAR?
(c) Operating system filenames
* Do these scripts contain the commands for creating
database objects? What is the filename, and what
is the file extension for each type of script?
(d) PL/SQL coding standards
* When to use stored procedures vs. functions vs.

database packages
* In-line documentation format
* Code indentation
-- Three characters? Should tabs be allowed?
* Variable naming conventions: local vs. global vs.
input/output parameters
-- Use of underscore vs. mixed case
(e.g., TotalEmployees vs. total_employees)
* Procedure parameters: when to provide defaults?
* PL/SQL type naming conventions: record type, PL/SQL
tables, etc.
* Cursors: when to declare cursors and how to use them
* Coding style
-- Conditional logic: how to code IF-THEN-ELSE
-- Looping logic: use of WHILE vs. FOR vs. LOOP
controls
* Uppercase vs. lowercase vs. mixed case
-- Oracle's recommendation is that Oracle keywords
be entered in uppercase, and everything else in
lowercase (e.g., SELECT emp_id FROM employees;)
* Exception handling: how to raise exceptions, how to
propagate exceptions to the calling routines, how to
specify error message code and text
* Debugging: how to trace program exception
* Transaction handling and logging: do standard tables
exist to log row changes?
* Utility programs: are there utility procedures for
logging, execution tracing, error reporting, etc.?
(e) SQL coding
* Define standards for writing SELECT, INSERT, UPDATE,
and DELETE operations
* SELECT statement
-- Define rules for query hints
-- Define table listing rules in the FROM clause
-- Specify ordering of columns in the WHERE clause
-- Specify the use of explicit data conversion
functions in the WHERE clause
* Indentation: how to line up the SQL clauses within
each statement

Here are some examples of our standards:
(a) Object naming conventions
* Table name
<AP>_table_name where <AP> refers to the application
name (e.g., HR_EMPLOYEES)
-- Note that we use plurals for tables and don't
use abbreviations.
* Primary key: <AP>_table_name_PK
(e.g., HR_EMPLOYEES_PK)
* Unique keys: <AP>_table_name_UKnn
(e.g., HR_EMPLOYEES_PK)
* Unique keys: <AP>_table_name_UKnn
(e.g., HR_EMPLOYEES_UK01)
* Foreign keys: <AP>_table_name_FKnn
(e.g., HR_EMPLOYEES_FK01)
* Check constraints: <AP>_table_name_CHKnn
(e.g., HR_EMPLOYEES_CHK01)
* Indexes: <AP>_table_name_IDXnn
(e.g., HR_EMPLOYEES_IDX01)
* Stored program names
-- First of all, we always implement stored programs
using database packages; we don't have standalone
procedures and functions.
-- Package name: pkg_program_name
(e.g., pkg_employees)
-- Package public procedure/function name
(e.g., p_insert_employee, f_count_employees)
-- Package private procedure/function name
(e.g., lp_check_employee, lf_count_departments)
* Triggers
-- <trg>_table_name_<r|s><b|a><i|u|d>
(e.g., trg_employees_rbiud to denote row level,
before insert/update/delete trigger)
(b) Database table and column definitions
* Use VARCHAR2 instead of CHAR datatype
* For numeric columns, try to define them as NOT NULL
with a DEFAULT value of 0. This greatly simplifies
mathematical operations on these columns.
* As a general rule, use one global reference table
for all reference codes that remain static and
share common columns -- i.e., code name, code value,
and code description (e.g., gender). Create separate
reference tables for codes that might change, and
require native database support for foreign keys.
(c) Operating system file names
* We use .sps for the database package specification
file and .spb for the package body script.
* At the end of the SQL command for creating
procedural logic, include the SHOW ERRORS command.

In the next issue, I'll discuss our standards for SQL and
PL/SQL coding.

Hope this would help you.
Sam

[chrisrlong]

For my .02, my columns are always:

PK,
FKs...
AKs...
Regular columns - Standard (name, description, etc.)
Regular columns - Specific
TimeStamp column

As for the article from Pinnacle:
"(b) Database table and column definitions
* Use VARCHAR2 instead of CHAR datatype
* For numeric columns, try to define them as NOT NULL
with a DEFAULT value of 0. This greatly simplifies
mathematical operations on these columns.
* As a general rule, use one global reference table
for all reference codes that remain static and
share common columns -- i.e., code name, code value,
and code description (e.g., gender). "

Such sweeping generalities can get one in trouble - especially such dubious ones. I would not suggest following any of the above 3 tips.

- Chris

[sambavan]

Yeah, I'll buy that argument of chris. Chris, why not you give the refereal of your past thread on this issue. I'm really lazy to searching and posting :p

Sam

[padma]

Hi everyone,

Thanks for your replies. I found a document in which oracle suggests to put all the 'null' columns at the end. My production DBAs suggest that I list the PK first and all the bigger columns at the end. I am not sure which way to go. I think, it finally comes to what the data looks like and how the table structure change in the future (if we add columns)....

Any thoughts???

Thanks
Padma

[chrisrlong]

sambavan, I'm not sure if I ever had any posts related to column order. However (shameless plug), once I'm done with my book, you'll find my thoughts on the subject in there :)

- Chris

[sambavan]

Yeah, I know that you hadn't posted any discussion on the column order. But What I ment was that of pando's discussion on the NOT NULL constaraint and your suggestions on it. Any how for those who wonder what the heck I talk here, pl. refer the link :D

[url]http://dbasupport.com/forums/showthread.php?threadid=6946[/url]

Sam

[chrisrlong]

gotcha - forgot about that one.

Sheesh, but I can go off on a topic sometimes, eh? :)

- Chris