Who's online

There are currently 0 users and 23 guests online.

Recent comments


December 2010

ANSI Full Outer Join, Ready or Not?

December 30, 2010 (Modified January 1, 2011) When I read pages 101-103 of the book “Pro Oracle SQL” a couple of days ago, I was reminded of a couple of things.  This section of the book describes full outer joins, showing the ANSI syntax and Oracle syntax to perform a full outer join.  If you read [...]

WordPress 3.0.4 Released…

WordPress 3.0.4 has been released. This is quite an important released because it fixes,

“a core security bug in our HTML sanitation library, called KSES. I would rate this release as “critical.””

Get upgrading soon…




Here’s a posting on OTN that demonstrates a piece of SQL that uses inline scalar subqueries which are all “existence” tests to produce (presumably) a set of flags describing the state of a particular item of data.

I’ve linked to it because I contributed a comment about the implications of the cost figures that appeared in the execution plan for two of the “exists” subqueries. Essentially “existence” is optimized as a “first_rows(1)” operation – which results in two lines of the plan showing two different costs for table scans of the same table.

Update 30th Dec:
If you follow the OTN note you’ll see that the original poster was confused by my comments about the relative costs of the two tablescans, so I’ve whipped up a quick script to clarify the point. It uses my typical “reproducible” setup of 1MB uniform extents, 8KB block size, freelist management and disabling system statistics. Here’s the starting data set:

create table t1
with generator as (
	select	--+ materialize
		rownum id
	from dual
	connect by
		rownum <= 10000
	rownum			id,
	mod(rownum,1000)	n_1000,
	mod(rownum,5000)	n_5000,
	mod(rownum,10000)	n_10000,
	lpad(rownum,10,'0')	small_vc,
	rpad('x',100)		padding
	generator	v1,
	generator	v2
	rownum <= 100000

		ownname		 => user,
		tabname		 =>'T1',
		estimate_percent => 100,
		method_opt 	 => 'for all columns size 1'


Notice that any given value of n_1000 appears every 1,000 rows, any given value of n_5000 appears every 5,000 rows, and any given value for n_10000 appears every 10,000 rows. Think about the effect this has on Oracle’s prediction of how much work it has to do when asked to find a particular row under first_rows(1) optimisation (which is the optimisation strategy triggered by the “rownum = 1″ predicates below):

set autotrace traceonly explain

select count(*) from t1;
select id from t1 where	n_1000  =  500 and rownum = 1;
select id from t1 where	n_5000  = 2500 and rownum = 1;
select id from t1 where	n_10000 = 5000 and rownum = 1;

set autotrace off

The more rows you have to scan (on average) to find a given value, the more costly the tablescan becomes. (The initial select count(*) is there to demonstrate Oracle’s estimate of the cost of scanning the whole table).

| Id  | Operation          | Name | Rows  | Cost  |
|   0 | SELECT STATEMENT   |      |     1 |   283 |
|   1 |  SORT AGGREGATE    |      |     1 |       |
|   2 |   TABLE ACCESS FULL| T1   |   100K|   283 |

| Id  | Operation          | Name | Rows  | Bytes | Cost  |
|   0 | SELECT STATEMENT   |      |     1 |     9 |     5 |
|*  1 |  COUNT STOPKEY     |      |       |       |       |
|*  2 |   TABLE ACCESS FULL| T1   |     2 |    18 |     5 |
   1 - filter(ROWNUM=1)
   2 - filter("N_1000"=500)

| Id  | Operation          | Name | Rows  | Bytes | Cost  |
|   0 | SELECT STATEMENT   |      |     1 |     9 |    16 |
|*  1 |  COUNT STOPKEY     |      |       |       |       |
|*  2 |   TABLE ACCESS FULL| T1   |     2 |    18 |    16 |
   1 - filter(ROWNUM=1)
   2 - filter("N_5000"=2500)

| Id  | Operation          | Name | Rows  | Bytes | Cost  |
|   0 | SELECT STATEMENT   |      |     1 |     9 |    30 |
|*  1 |  COUNT STOPKEY     |      |       |       |       |
|*  2 |   TABLE ACCESS FULL| T1   |     2 |    18 |    30 |
   1 - filter(ROWNUM=1)
   2 - filter("N_10000"=5000)

And now, check the costs of the tablescans in the existence subqueries for the following:

	(select 1 from dual where exists (select null from t1 where n_1000  =  500)) n_1000,
	(select 1 from dual where exists (select null from t1 where n_5000  = 2500)) n_5000,
	(select 1 from dual where exists (select null from t1 where n_10000 = 5000)) n_10000

| Id  | Operation          | Name | Rows  | Bytes | Cost  |
|   0 | SELECT STATEMENT   |      |     1 |       |     2 |
|*  1 |  FILTER            |      |       |       |       |
|   2 |   FAST DUAL        |      |     1 |       |     2 |
|*  3 |   TABLE ACCESS FULL| T1   |     2 |     8 |     5 |
|*  4 |  FILTER            |      |       |       |       |
|   5 |   FAST DUAL        |      |     1 |       |     2 |
|*  6 |   TABLE ACCESS FULL| T1   |     2 |     8 |    16 |
|*  7 |  FILTER            |      |       |       |       |
|   8 |   FAST DUAL        |      |     1 |       |     2 |
|*  9 |   TABLE ACCESS FULL| T1   |     2 |     8 |    30 |
|  10 |  FAST DUAL         |      |     1 |       |     2 |

Predicate Information (identified by operation id):
   1 - filter( EXISTS (SELECT /*+ */ 0 FROM "T1" "T1" WHERE "N_1000"=500))
   3 - filter("N_1000"=500)
   4 - filter( EXISTS (SELECT /*+ */ 0 FROM "T1" "T1" WHERE "N_5000"=2500))
   6 - filter("N_5000"=2500)
   7 - filter( EXISTS (SELECT /*+ */ 0 FROM "T1" "T1" WHERE "N_10000"=5000))
   9 - filter("N_10000"=5000)

VirtualBox 4.0.0. Changes to VBoxManage Syntax…

I just got home to find a question about my RAC on VirtualBox article. The poster was having problems creating the virtual disks using the commands in the article. A quick scan through the docs reveals a number of changes to the way VirtualBox 4.0.0 handles disks and also changes to the VBoxManage syntax. I’ve amended the article to include a version of the commands for version 4.0.0 which seem to create and attach the shared disks in the same state, but it will be a few days before I get to test this properly.

So what’s happened that affects shared disk setup?

First, you can’t create a shareable disk. You have to create the disk (createhd), then modify it to shareable (modifyhd). That sounds fine, but there is an issue. The manual says,

“Before VirtualBox 4.0, it was necessary to call VBoxManage openmedium before a medium could be attached to a virtual machine; that call “registered” the medium with the global VirtualBox media registry. With VirtualBox 4.0 this is no longer necessary; media are added to media registries automatically. The “closemedium” call has been retained, however, to allow for explicitly removing a medium from a registry”

Well that is not entirely true. When you create a new disk it is not visible in the media manager until it is attached to a VM. That means you have to create the disk, attach it to a VM and then convert it to shareable. If you try to modify it before attaching it to a VM you get told the disk doesn’t exist. This just feels wrong.

As a minor annoyance, the VM detail pane doesn’t notice the “–type” change so the disks still display as normal unless you restart VirtualBox, or click the “Storage” link for the VM and come straight out, which seems to get the screen to update.

This is not a big deal, it’s just a little annoying as the old syntax was more straight forward. I’m sure it was done for a good reason… :)



Hash Joins – What is Wrong with this Statement?

December 29, 2010 I started reading the book “Pro Oracle SQL” a couple of days ago, and I am having trouble putting it down.  Other than a couple of minor word substitutions and intended, but not specified, qualifying words, the first 100 pages of the book are fantastic (I suspect that many of these cases [...]

Delphix deals before the end of year

It's let's make a deal time at Delphix. If you have ever had the fun of closing an Oracle deal at their fiscal year end, you know what happens. Not only is it fiscal year end but Delphix is also millimeters away from adding an extra figure to their end of quarter total and I'm betting that now is the last time these low of prices will ever be seen - just my view point from being inside the castle.

Excel connect to Oracle – 64bit and 32bit issues

Wow, thanks to

Process Monitor

I was able track down why I couldn’t connect to Oracle from Excel.

I had wanted to try some of the examples Charles Hooper has posted on connecting to and monitoring Oracle, for example

I kept getting the error “Provider not found”
Now what kind of trace info is there for an error like this in Excel? None AFAIK. Time to start guessing.
I’m on windows 7 64 bit. I have the 64bit 11gR2 Oracle installed.  Excel shows up in task manager as “EXCEL.EXE  *32″. My first guess was, “oh, excel must want the 32bit libraries” so I got the 32 bit instant client from Oracle. Unzipped them into a directory and put them first into the path. Still no dice.
Did a lot of google searches and turned up that I needed


but this wasn’t in any of the instant client zips that I downloaded from

Turns out it’s in a download halfway down the page:

*Instant Client Package – ODAC: Includes ODP.NET, Oracle Services for MTS, Oracle Providers for ASP.NET, Oracle Provider for OLE DB, and OO4O with Oracle Instant Client

Downloaded this, put all the dlls in the first directory in my path. Still no dice.

I tried “strace”, but it gave no output. Rrrr.

Then I tried process monitor – bingo.

With process monitor, I filtered on processes containing “excel”, ran the connect, got tons of output, but knew it was a problem with libraries. I found “oraoledb10.dll” among the output. It was listed in an old Oracle 10 install directory. Oracle 10 32bit had been initially been installed. The install gave no warnings but bombed out late in the game so I remvoed the 10g and I installed Oracle 11gR2 64bit. (Oracle 11gR2 32bit won’t even begin the install)
So now, I scoured the registry with regedit and found the old oraoledb10.dll here


I changed this to the new location of oraoledb11.dll
and now it works.

TRON: Legacy…

Just got back from TRON: Legacy. The word “Legacy” seems ever so apt.

I went to see the first TRON film when it came out. I loved it. I was blown away. I wanted it all to be real. Remember this was 1982. The year after the ZX81 was released. I had a ZX81 and I was totally amazed by computers. It didn’t seem like a massive leap to think of programs as little people running around inside the box doing amazing things. It was a brave new world.

So 28 years later we have the sequel. It looks amazing and has a brilliant sound track but the fatal flaw is the concept of programs running round like people. They keep mentioning the “grid” as if that brings the concept up to date. It’s not little people running round in the box. It’s little people running round in “the grid”. Oh please. Give me a break. If that were the case there would be two giant fat people called Facebook and Google and a bunch of ants.

If I were them I would have re-imagined it entirely and replaced the original concept with a submersive virtual reality program. Doh! Already been done by eXistenZ and The Matrix

TRON was amazing in its time. TRON: Legacy isn’t. The best thing that could happen to this craptacular film is they chop it up and turn it into a visually spectacular extended video for the Daft Punk soundtrack. I would probably buy that DVD.



Migrating to wordpress

I'm migrating to wordpress. In 5 seconds you should be redirected to

DB Optimizer