Oracle Scratchpad

June 4, 2013


Filed under: ANSI Standard,Bugs,lateral view,Oracle — Jonathan Lewis @ 9:09 am BST Jun 4,2013

Here’s a suggestion to help you avoid wasting time. If you ever include the rowid in a query – not that that should happen very commonly – make sure you give it an alias, especially if you’re using ANSI SQL. If you don’t, you may find yourself struggling to work out why you’re getting an irrational error message. Here’s an example that appeared recently on the OTN forum, with the output cut-n-pasted from a system running


July 16, 2012

ANSI Outer 2

Filed under: ANSI Standard,CBO,Execution plans,Oracle — Jonathan Lewis @ 4:55 pm BST Jul 16,2012

A comment on a recent post of mine pointed me to a question on the OTN SQL and PL/SQL Forum where someone had presented a well-written test case of an odd pattern of behaviour in ANSI SQL. I made a couple of brief comments on the thread, but thought it worth highlighting here as well. The scripts to create the required tables (plus a few extras) are all available on OTN. If you create only the four tables needed and all their indexes you will need about 1.3GB of space.

The core of the problem is this: there is a three table join which does a hash join involving an index fast full scan on a particular index; when you add a fourth table to the join this fast full scan turns into a full tablescan for no obvious reason. Here are the queries, with the plans that I got when running (My final plan is slightly different from the plan shown on OTN – I have a right outer hash join to the last table where the OP had a nested loop outer – but the difference is not significant). The queries, with their execution plans, are below- the three table join first:

January 31, 2011

ANSI Outer

Filed under: ANSI Standard,CBO,Execution plans,lateral view,Oracle — Jonathan Lewis @ 6:59 pm BST Jan 31,2011

Here’s an example of ANSI SQL that does something in a fashion that arguably looks a little tidier than the strategy you have to adopt in Oracle. As so often when I compare Oracle syntax and ANSI syntax it’s an example that relates to an outer join. We start with two tables – as usual I have locally managed tablespaces, 8KB blocks, 1MB uniform extents and freelist management. I’ve also disabled system statistics (CPU costing):


December 3, 2010

ANSI – argh

Filed under: ANSI Standard,CBO,Execution plans,Hints,Ignoring Hints — Jonathan Lewis @ 7:30 pm BST Dec 3,2010

I’m not keen on ANSI standard SQL – even though it is, technically, the strategic option and even though you have to use it for full outer joins and partitioned outer joins.

One reason for disliking it is that it “separates join predicates from filter predicates” – a reason often given in praise of the syntax which, to my mind, claims a spurious distinction and introduces a mechanism that makes it harder to keep mental track of what’s going to happen as you walk  through the join order. (I have to admit that I was temporarily fooled into thinking it was quite a nice idea – in an abstract sort of way.)

March 20, 2008


Filed under: ANSI Standard,CBO,Infrastructure,Troubleshooting — Jonathan Lewis @ 4:26 pm BST Mar 20,2008

Someone sent me an email a little while ago about a problem they were having with two databases that were using different execution plans for the same query. 

But the two databases were believed to be identical, and the optimizer was running rule-based in both cases, so it shouldn’t have been possible to get different execution plans.


The Rubric Theme. Create a free website or blog at


Get every new post delivered to your Inbox.

Join 3,514 other followers