ANSI SQL

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.

Here’s a little catch (cut and paste from a session running under 9.2.0.6):

SQL> alter session set optimizer_mode = rule; 

Session altered. 

SQL> set autotrace traceonly explain
SQL> select t1.n2, t2.n2
  2  from t1 left join t1 t2 on t1.n1 = t2.n1
  3  ; 

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=RULE (Cost=192 Card=450000 Bytes=7200000)
   1    0   HASH JOIN (OUTER) (Cost=192 Card=450000 Bytes=7200000)
   2    1     TABLE ACCESS (FULL) OF 'T1' (Cost=75 Card=150000 Bytes=1200000)
   3    1     INDEX (FAST FULL SCAN) OF 'T1_I1' (NON-UNIQUE) (Cost=25 Card=150000 Bytes=1200000)

Notice how line 0 of the plan shows that we are running RULE based. But every line has a cost, and the join method is a hash join – which is an option only available to the cost based optimizer.

The use of ANSI syntax has caused the RBO to hand off to the CBO. Once you’re running cost-based with no statistics, there are all sorts of little things that might be enough to cause unexpected behaviour in execution plan.