Kaley's (mostly) Oracle Database Blog
Hi! I'm Kaley Crum, Oracle DBA.
Welcome to my (mostly) Oracle database blog.


Costing a Full Table Scan – Adjusted Multiblock Read

Reading in Johnathan Lewis’ book, or on Martin Widlake’s blog, they both talk about “adjusted” or “modified” multi-block read counts.

What is this number?  Where does it come from?

Basically, what’s previously been said about this number is that Oracle “adjusts” the speed of your multi-block read count to compensate for time differences.

Continue Reading

Summing Interval Data Types

Something I’ve occasionally thought would be really nice is if Oracle had a good way of summing up interval data types, much the same way that you can SUM() number data types.  Occasionally I’ve designed processes that record start and stop timestamps of operations.  If you want to know how long the operation lasted, you could do the end timestamp minus the start timestamp to get a DAY TO SECOND INTERVAL data type.

If I want to know the total runtime of a set of operations, there’s not a nice, slick, easy way to do this.

Usually, I get away with some jankedy code like this:

First, you gotta cast the timestamps as dates (meaning you lose any fractional-seconds).

Then you subtract the start date from the end date.  That gives you a number, which represents the number of days (possibly fractional).

Oracle then allows you to sum up those numbers.

If you want the number back in interval format, you can supply it to a NUMTODSINTERVAL() function, passing in ‘DAY’ as the second argument.

It sucks, it’s not smooth, it loses precision, the maintaining developer has to double-take to try to figure out what it is that I’m doing.  Wouldn’t it be nice if there was some smooth way to just add up intervals?

Well, after giving it some thought, I decided to create my own…

Continue Reading

Access and Filter – Is There an Echo in Here? (Why did Oracle Repeat the Same Filter Twice?)

There are lots of resources that will tell you about the “access” and “filter” lines you see in the Predicates section of your execution plan.  Most resources will tell you…”access is more efficient than filter.” And, generally, that’s true.  Access tells us where to go in our index (thereby eliminating rows before we reach them) whereas filter tells us once we’ve arrived at a row that “it’s no good and must be discarded.”

But you may have noticed times when there seems to be and echo in your plan, and it repeats the same predicate filter in both the FILTER and ACCESS section of your execution plan.  What then?  Why does Oracle need both?  Shouldn’t just one be enough?

Continue Reading

Quickly Creating a Billion-Row Test Table

Lots of times, when I’m testing theories, I need a large test table to see how things scale.  Here’s a script that will quickly create such a table with a primary key/unique index, along with a bitmap index.  The table has 10 partitions (this is necessary to allow 10 concurrent “append” inserts).  DBMS_PARALLEL_EXECUTE is used to build the table in parallel, and DBMS_PCLXUTIL is used to build the bitmap index in parallel.

Continue Reading

Nested Loops and Join Order

There’s actually a lot of subtleties to nested loops.  When they’re explained, they’re most commonly represented in the following fashion:

But!  That’s clearly overly simplified.

For example, if the Oracle code looked like that, then join order clearly wouldn’t matter for nested loops if both tables were being full scanned.  But, as you’ll see shortly, join order matters a lot!

Continue Reading

Splitting Rows Out with SQL

Suppose you have the following table:

It looks like this:


But you want it to look like this:

How do you fix this using SQL? Continue Reading