Oracle Musings

Laurent’s post on column qualification reminded me of a conversation I had with one of my staff about column naming standards.  But before I get to that conversation, I’d like to share my favorite example about using table aliases.

I’m a big fan of always using table aliases and always qualifying column names.  My favorite example of why it’s a good idea to do it is as follows:

select * from v$session where saddr in (select saddr from v$transaction)

Can you spot the problem here?  There isn’t an saddr column in v$transaction.  But if you run that query, you don’t get an error.  In fact, you get all of the sessions from v$session — when you probably only wanted the sessions which had active transactions.  If you were in the habit of always qualifying the table names like so:

select s.* from v$session s where s.saddr in (select t.saddr from v$transaction t)

You’d get ORA-00904: “T”.”SADDR”: invalid identifier.

You can argue about whether or not the original statement should throw an error, but it doesn’t matter.  If you used the good practice of always aliasing tables and qualifying columns, you wouldn’t need to waste your breath.

One “argument” I hear against qualifying column names comes from people who have a policy of making sure every column name in the system is unique, usually by including some kind of table name abbreviation prefix on every column in a table.  My staff member asked me my opinion of this.  Bleh.  I’ve got nothing really against it, other than I think it’s not feasible to enforce and it hampers readability.  I also don’t like how that policy treats foreign key columns — usually inconsistently.  And in the end, it seems to just be an excuse for not having to qualify columns in queries anyway