Oracle Musings

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?