Oracle Scratchpad

September 7, 2010

CBO Surprise

Filed under: CBO,Execution plans,Oracle,subqueries — Jonathan Lewis @ 7:00 pm BST Sep 7,2010

Well, it surprised me!

I’ve said for a very long time that in principle, ignoring bugs and restrictions, the optimizer will always choose the lowest cost option during its search for an execution path. It turns out that this isn’t true. In comment 5 of this note I had written about a possible bug relating to function-based indexes I was told that there are cases where the optimizer follows a rule that allows it to ignore the lowest cost path if it is derived from a range-based predicate involving unpeekable bind variables.

The trouble is, any statement made about “bind variables” may also apply in any circumstances where the optimizer sees: “unknown value”. Here’s a simplified example that I find a little worrying (running on 11.1):

rem     Script:         index_range.sql
rem     Author:         Jonathan Lewis
rem     Dated:          June 2010

create table t1
with generator as (
	select	--+ materialize
		rownum id
	from dual
	connect by
		rownum <= 10000
	rownum                  id,
	mod(rownum-1,20)        scattered,
	trunc(rownum / 500)     clustered,
	lpad(mod(rownum,2),10)  ind_pad,
	lpad(rownum,10,'0')     small_vc,
	rpad('x',100)           padding
	generator	v1,
	generator	v2
	rownum  <= 40000 -- > comment to avoid WordPress format issue

create index t1_equi  on t1(scattered, ind_pad, clustered);
create index t1_range on t1(clustered, scattered);

                ownname      => user,
                tabname      => 'T1',
                cascade      => true,
                method_opt   => 'for all columns size 1'

set autotrace traceonly explain

	scattered = 10
and	clustered between (select 40 from dual)
	          and     (select 41 from dual)

	/*+ index(t1 t1_range) */
	scattered = 10
and	clustered between (select 40 from dual)
	          and     (select 41 from dual)

set autotrace off

The SQL with its “select constant from dual” may look a little artificial – but it represents a strategy that is used quite commonly; and it’s just one of several code patterns that can kick the optimizer into using the arithmetic for “unknown value at compile time” (another would the case where you use sys_context(), possibly in fine-grained access control – a.k.a. row-level security or virtual private database).

I’ve got two possible indexes on the table that could be used to assist this query – and one of them is significantly larger than the other because it has an extra column (ind_pad) in the middle which does not appear in the where clause. which index is the optimizer going to use ?

-- Default path
| Id  | Operation                   | Name    | Rows  | Bytes | Cost  |
|   0 | SELECT STATEMENT            |         |     5 |    85 |    17 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1      |     5 |    85 |    13 |
|*  2 |   INDEX RANGE SCAN          | T1_EQUI |     9 |       |     9 |
|   3 |    FAST DUAL                |         |     1 |       |     2 |
|   4 |    FAST DUAL                |         |     1 |       |     2 |

Predicate Information (identified by operation id):
   2 - access("SCATTERED"=10 AND "CLUSTERED">= (SELECT 40 FROM
              "CLUSTERED"= (SELECT 40 FROM "SYS"."DUAL" "DUAL") AND
              "SCATTERED"=10 AND "CLUSTERED"<= (SELECT 41 FROM "SYS"."DUAL" "DUAL"))

The optimizer has selected the path with the higher cost – and it’s hard-coded to do this in response to the nature of the predicates and the available choice of indexes. We have two indexes, with predicate usage as follows:

t1_equi (scattered, ind_pad, clustered) with predicates that are: (equality, omitted, range)
t1_range (clustered, scattered) with predicates that are: (range, equality)

The important point is that we have a predicate on column clustered which is range-based with values that are unknown at optimisation time (even though the human eye can see that the values are going to be 40 and 41). Because of this uncertainty the optimizer is coded to bypass the index that starts with this suspect column and use the (higher cost) index where there is a known starting predicate. In effect the optimizer seems to be saying: “in the worst case scenario t1_range might end up doing an index full scan but t1_equi will only have to do a partial range scan”.

I’ve still got a lot of examples I want to work through to see how far ranging this rule is and if it ever gets ignored (what happens, for example, to joins with range-based predicates), but I hope that this preliminary note acts a useful clue when you next see Oracle ignoring the lowest cost path.

Update Sept 2013

I’ve used a subquery “(select constant from dual)” to hide a value from the optimizer in the examples above – but I’ve recently discovered that this no longer works in 12c or It looks as if those versions know that dual is a special case and peek inside the subquery to get the value before doing the rest of the optimization.  (Fix control 11813257  looks relevant; it has the description : “selectivity of predicate with subquery returning constant” and it turns out that it’s not just dual that makes a difference – though it’s the most likely one that people will use.)


  1. I’ve csme across this once in a complex statement that I didn’t have time to figure out what odd CBO “quirk” I was dealing with- Excellent examples and clear explanations, thank you, Jonathan!

    Comment by Kellyn Pedersen — September 8, 2010 @ 2:20 am BST Sep 8,2010 | Reply

  2. Jonathan,

    I am afraid revelations like these are making me “atheist” about Oracle :)
    On a serious note, thanks for sharing this.

    Comment by Narendra — September 8, 2010 @ 10:38 am BST Sep 8,2010 | Reply

  3. […] Update (31st Oct 2010):  I did a little extra work on this issue a few weeks later ago (see comments and pingback below), but forgot to link forward to the new article. My original conclusions were wrong; for more details see: […]

    Pingback by FBI Bug « Oracle Scratchpad — October 31, 2010 @ 8:31 am BST Oct 31,2010 | Reply

  4. […] this version of Oracle this resulted in the predicates being treated as “guesses on an index range scan” – which resulted in the optimizer ignoring the appropriate index (until hinted) even though […]

    Pingback by FBI Bug « Oracle Scratchpad — December 30, 2011 @ 5:48 pm BST Dec 30,2011 | Reply

  5. […] The hinted path has a lower cost than the default path. I think this may be another case of an “unknowable” range scan being ignored in favour of a known […]

    Pingback by Conditional SQL – 5 | Oracle Scratchpad — March 2, 2018 @ 12:49 pm BST Mar 2,2018 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by