Column Naming

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 :-)

More content

So, recently I’ve switched from Bloglines to Google Reader for my daily feed fix.  I switched for 2 reasons:

  1. Better mobile access
  2. Better handling of high-volume feeds or feeds I read infrequently

I got tired of waiting for Bloglines to load up a 100+ item feed, especially on my phone.

I also like Google Reader’s ability to share items, and so I’ve added a new feature to this blog — on the right-hand side you can see links to posts on other blogs from around the web which make up a kind of “best-of” from my daily reading.

Enjoy!

More interview questions

This weekend, Robert Vollman posted some of his ideas for SQL Interview questions.  I thought I’d share mine as well.  Mine are very simple, and really don’t call for single word answers.  When giving them, I used to give people a “grade” for their answer.  One of A, B, C and D (sometimes F).  “A” meant they fully understood the concept behind the question.  “F” meant they totally didn’t know and guessed wrong.  Sometimes I gave extra credit when they said they didn’t know the answer, and then asked me — I thought that demonstrated an innate curiosity.

Oh well, without further fuss, here are my questions:

SQL

What’s an inline view?
Can you have a SELECT clause in a SELECT column list?
If yes, what does it do when it retrieves no rows, more than one row?
What are analytic functions and give an example?
What does the NO_MERGE hint do?
What does the CARDINALITY hint do?
What’s your rule of thumb for index vs full-table scan?
What are the benefits and problems of parallel hints?
In implicit type conversions does Oracle convert numbers to chars or chars to numbers?

PL/SQL

How can you pass an array to / from a PL/SQL proc?
How can you join a table to a PL/SQL array?  Any issues to be aware of?
Can you use analytic functions in PL/SQL?
What do you use to write PL/SQL code?

Performance

What view shows what sessions are waiting on?
What’s an enqueue wait?
How can you tell which tables are being locked?
How can you tell which tables are having high row-level locking contention?
What’s a logical I/O?
How do you identify “bad” statements?  What do you consider bad?
How do you measure TEMP space usage?
How can you tell how long a statement will take before it finishes?
What is a “fetch across commit”?
How can you tell how much undo / rollback size a statement is using?

Physical Layout / Design

What’s the difference between a session and a process?
What is MTS and why would you use it?
What’s a function based-index?
What’s an IOT?
Can you create an index on an IOT?  What’s in it?
What’s a single-table hash-cluster?
Explain the difference between a global index and local index.
What’s a queue table?
What’s the difference between an LMT and a DMT?
What does AUTOALLOCATE mean?
What does DROP COLUMN do?
What does ALTER TABLE ADD COLUMN DO?  What happens if you add a DEFAULT VALUE?
Do you need to index columns which are foreign keys?  Why or why not?
What’s Advanced Queuing?
What’s XMLTYPE?
What’s online table redefinition?  How does it work?

Logical Layout / Design

What’s the CACHE parameter on sequence creation for?

Got any change?

After 5 years at Network Solutions, I’ve decided to embark on a new journey and have taken a position at Agilex Technologies.  It’s a brand-new startup, so we’re still designing our services and buying equipment for our R&D lab.

The upshot is that I’ll be a bit erratic in posting until we get things cruising along, probably around July 1.

In the meantime, I’ll try to get some new content up, so keep stopping by!