The Hitchhiker’s Guide to the EXPLAIN PLAN Part 22: The Final Exam! (Part III We Don’t Use Databases; We Don’t Use Indexes)

If you were the owner of a book store and you received a shipment of books, how would you go about putting the books on the shelves? Would you stand at the door of your store and fling each book onto any shelf where you saw some space? No! Then why do all of us store data that way? The default organization of data in table is “heap” which means that incoming records are stored (more...)

The Hitchhiker’s Guide to the EXPLAIN PLAN Part 21: The Final Exam! (Part II)

In addition to the basic evaluation approach of Kimball and the bail-out option, Oracle Database offers a lightning-fast option which uses “bitmap” indexes. A bitmap index is composed of multiple “bitmaps” instead of primary key and ROWID pairs like conventional indexes. Each bitmap in a bitmap index is an array of bits (0s and 1s) and tracks exactly one of the values that occurs in the indexed column. The 1s in a bitmap indicate that (more...)

The Hitchhiker’s Guide to the EXPLAIN PLAN Part 20: The Final Exam! (Part I)

According to Ralph Kimball, there is only one basic evaluation approach—along with a “bail out” option—in a star schema: evaluate the constraints on the dimension tables, prepare a list of composite keys of the fact table, and use a composite index on the fact table to fetch the data from the fact table. When you notice that the list of composite candidate keys is too long, you bail out to a full-table scan in which (more...)

Oracle 12c: Temporal Validity, multiple on one table

During a trial run for my presentation at Oracle Open World "Oracle 12c for Developers", you can find the slides on slideshare. there was a question regarding "Temporal Validity".
What is Temporal Validity?
The documentation says it best:

Temporal Validity Support lets you associate one or more valid time dimensions with a table and have data be visible depending on its time-based validity, as determined by the start and end dates or time stamps of (more...)

The Hitchhiker’s Guide to the EXPLAIN PLAN Part 19: Practice Exam IV

Instead of evaluating all join possibilities by brute force, the optimizer adopts reasonable strategies in the hope that they will lead to reasonably efficient query plans. The oldest strategy in the book is to pick a “driving” table and then keep picking another table with which to join until all tables have been joined. However, this depends on the ability to perform “view merging” if the query contains views and “subquery unnesting” if the query (more...)

DBMS_UTILITY.EXPAND_SQL_TEXT and temporal validity in 12c

12c gave us two new features among others: DBMS_UTILITY.EXPAND_SQL_TEXT and temporal validity. DBMS_UTILITY.EXPAND_SQL_TEXT is advertised as a means to expand SQL text that is using views, but it is also very useful to see how Oracle internally rewrites some SQL queries. Here I'm going to use it to see how temporal validity queries are actually executed. All tests are done using Enterprise Edition.

First I'll create a table for (more...)

5 minutes: Grant Role to Package

The EOUC (EMEA Oracle User Group Community) hosts a special session on sunday during Oracle Open World. This session contains twelve things about Oracle 12c. Each of these 12 things is hosted by a different speaker, so this means that each section is only five minutes long (Strictly guarded by Debra Lilley).
Thankfully I was chosen to be one of the speaker during this special session, very excited and also very scared. (more...)

The Hitchhiker’s Guide to the EXPLAIN PLAN Part 18: Practice Exam III

Every time the optimizer chooses to join two data sets using the hash-join method, it has to decide which of the two data sets will be the in-memory lookup table. The smaller of the two data sets is the logical choice. The lookup table is always placed on the left hand side of the join node. This can result in zig-zag trees.(read more)

My Speaking Schedule for Oracle Open World 2014

A quick post to let you know about the two presentations that I’ll be doing at Oracle Open World 2014.

Session ID:         UGF4482
Session Title:     “Getting Started with SQL Pattern Matching in Oracle Database 12c
Venue / Room:  Moscone South – 301
Date and Time:  9/28/14, 13:30 – 14:15

Session ID:          CON4493
Session Title:      “Regular Expressions (more...)

The Hitchhiker’s Guide to the EXPLAIN PLAN Part 17: Practice Exam II

The opposite of the deep-left tree is the deep-right tree. Each qualifying row in the driving table is checked against again in-memory hash-lookup tables.(read more)

Order, Order.. Sorting Happens Last

While preparing for my session at Oracle Open World on "Oracle 12c for Developers" I ran into a little remarkable thing.

When sorting a dataset, the sorting is always done last. That is what I was taught anyway. There is probably some obscure way to detect the exact execution plan, but personally I never bothered to go and investigate.
When I was created some test scripts I found there was a way to see that (more...)

The Hitchhiker’s Guide to the EXPLAIN PLAN Part 16: Practice Exam I

The deep-left tree clearly illustrates the fundamental strategy by which a SQL query is converted into a series of join operations: first a “driving” table is selected from amongst the tables involved in the query and then the remaining tables are joined to it using the nested-loop method.(read more)

The Hitchhiker’s Guide to the EXPLAIN PLAN Part 15: Oracle-in-the-box

The OTN Developer Day VM is the greatest thing since sliced bread and I use it in all my experiments and demonstrations. It uses Linux as the operating system and is pre-loaded with a fully-functional Oracle 12c database with all the options. I don’t have to go through any installation headaches, my laptop is not messed up, and—best of all—I can take a snapshot of a known good state and revert to it at will. (more...)


A comparison of sucking data into a table over a db link using DBMS_PARALLEL_EXECUTE.

This particular example is based on something I needed to do in the real world, copying data from one database into another over a db link. Datapump is not available to me. Tables in question happen to be partitioned by a date-like number (boo!) hence some of the specific actions in the detail.

I think it’s a good example of (more...)

Demystifying Oracle Unpivot

A couple of years ago I posted a simple example using PIVOT, converting rows to columns with the classic example of figures by months.

Oracle 11g R1 also introduced the UNPIVOT function, allowing columns to be converted into rows.


I've created an example that lists cities by row, but two attractions as two columns, with pairing attributes describing the reason for the attraction.
create table aus_attractions(id  number, city varchar2(50)
, attraction1 varchar2(50)
, (more...)

★ Database as a Storage (DBaaS) vs. Thick Database

A recent addition to my Oracle PL/SQL library is the book Oracle PL/SQL Performance Tuning Tips & Techniques by Michael Rosenblum and Dr. Paul Dorsey.

I agree with Steven Feuerstein’s review that “if you write PL/SQL or are responsible for tuning the PL/SQL code written by someone else, this book will give you a broader, deeper set of tools with which to achieve PL/SQL success”.

In the foreword of the book, Bryn Llewellyn writes:

The (more...)

Are You Using BULK COLLECT and FORALL for Bulk Processing Yet?

Steven Feuerstein was dismayed when he found in a PL/SQL procedure a cursor FOR loop that contained an INSERT and an UPDATE statements.

That is a classic anti-pattern, a general pattern of coding that should be avoided. It should be avoided because the inserts and updates are changing the tables on a row-by-row basis, which maximizes the number of context switches (between SQL and PL/SQL) and consequently greatly slows the performance of the code. Fortunately, (more...)

Tokenizing a String : Using Regular Expressions

In my previous blog post I gave some PL/SQL that performed the tokenising of a string. Check out this blog post here.

Thanks also to the people who sent me links examples of how to tokenise a string using the MODEL clause. Yes there are lots of examples of this out there on the interest.

While performing the various searches on the internet I did come across some examples of using Regular Expressions to extract (more...)

The Hitchhiker’s Guide to the EXPLAIN PLAN Part 14: Damn the Cost, Full Speed Ahead

The cost displayed in query plans is a misleading and useless piece of information. It is an estimate that is more likely to be wrong than correct. By default, it is computed using particular values of bind variables and therefore does not apply to subsequent executions with different values. And, it is measured in units of SREADTIM, not clock seconds.(read more)

BUCKET_WIDTH: Calculating the size of the bucket

Some time ago I had some blog posts introducing some of the basic Statistical function available in Oracle. Here are the links to these.