Home > DBA, Oracle, SQL > The Hitchhiker’s Guide to the EXPLAIN PLAN (Act II)

The Hitchhiker’s Guide to the EXPLAIN PLAN (Act II)


Over at ToadWorld:

Part 5: SQL Sucks!

Part 6: Trees Rule

Part 7: Don’t pre-order your EXPLAIN PLAN

Part 8: Tree Menagerie

Bonus article: Equivalence of Relational Algebra and Relational Calculus

The story so far:

  • A relational database is “a database in which: the data is perceived by the user as tables (and nothing but tables)  and the operators available to the user for (for example) retrieval are operators that derive ‘new’ tables from ‘old’ ones.” (An Introduction to Database Systems by Chris Date)
  • SQL is a non-procedural language; that is, a SQL query specifies what data is needed but does not specify how to obtain it. The “query optimizer” automagically constructs a query execution plan for us.
  • The query execution plan can and does change when the inputs (values of bind variables, data distribution statistics, etc.) change. This comes as a great surprise to everybody but that’s how it was always intended to work.
  • A huge problem with relational databases is that semantically equivalent statements do not result in the same run-time query execution plan. That’s not how it was ever intended to work.
  • The EXPLAIN PLAN documents the query execution plan used by Oracle Database; that is, it documents the sequence of relational algebra operations that Oracle Database uses at run-time to execute any particular SQL query.
  • An EXPLAIN PLAN is a “tree” structure corresponding to a relational algebra expression. It is printed in “pre-order” sequence (visit the root of the tree, then traverse each subtree—if any—in pre-order sequence) but should be read in “in-order” sequence (first traverse each subtree—if any—in in-order sequence, then only visit the root of the tree).
  • The Oracle documentation incorrectly states that “The execution order in EXPLAIN PLAN output begins with the line that is the furthest indented to the right.”
About these ads
Categories: DBA, Oracle, SQL
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 771 other followers

%d bloggers like this: