Development Standards, Part 1 – SQL

First up in our work on development standards refers to SQL.  A reminder that these are just my opinions, based on what I’ve seen work — I’m perfectly willing to hear counter-arguments, proposals and even full-blown critiques of what’s wrong, what’s better or otherwise :-)

I like to keep the overriding goal in place here — these standards should not place undue burdens on developers.  They should be simple enough that even novice developers can abide by them without requiring a ton of knowledge, but also be appreciated by savvy developers.

(It does help if the developer has some knowledge of how Oracle works in order to appreciate the standard — hence why I continue to believe that you want people with some DBA experience to be your SQL developers instead of trying to teach the Java / PHP and Perl people this stuff).

Anyway, I’ve got very few standards for SQL statements:

  1. Whenever there are 2 or more tables in the statement, always alias each table and every referenced column.  Be consistent — don’t use schema.tablename.columnname in one place and then alias.columnname in another.  You don’t need to “standardize” on table aliases across all systems, just within your statement.
  2. I prefer the old-style syntax to the new, fancy ANSI-style join syntax, but that’s just me.  But whatever you do, pick one and stick with it or at least state that all new code will adhere to it and all rewrites will bake it in.
  3. Use bind variables unless you have a absolute reason not to — one you can actually articulate.
  4. Learn to use sqlplus AUTOTRACE and run the statement against a representative data set (we’ll talk about that later) using representative bind variables.  Your goal is 5-10 consistent gets per table join per row retrieved or processed.  (I don’t know how to represent this kind of information in other databases — another reason I like Oracle is that it gives you this kind of statistical feedback “for free”).
  5. Use as few hints as possible — let the optimizer earn its keep (and justify part of the price you’re paying for a robust database).
  6. Try to look at things from the database’s perspective — what information does it have about the data it’s trying to work with?  Understand that it usually does the best it can with the information it has on hand.
  7. Minor nit — don’t rely on GROUP BY for data ordering.  If you want something sorted, use the ORDER BY clause — that’s what it’s there for.
  8. Read the Functions section in the SQL Reference Guide.  You’d be surprised how often you can do something without having to write 3GL code.

A lot of people like to quibble about the AUTOTRACE item.  “Why 5-10, why not 1-2…” — the truth is that I don’t care all that much about the particular number.  What I do care about is that the system uses as few resources as possible related to the size of the result set it’s asked to produce.  The ultimate goal is to produce the largest possible result set using very few “work units” (ultimately, time).  A simple standard and process to measure it is all that is needed.

This simple set of rules usually produces pretty good SQL, even if your developers are new to the game.

Leave a Reply

Posting code can be a pain. To make sure your code doesn't get eaten, you may want to pre-format it first by using HTML Encoder