12c Subqueries

When you upgrade you often find that some little detail (of the optimizer) that didn’t receive a lot of attention in the “New Features” manuals introduces a few dramatic changes in execution plans. Here’s one example of a detail that is likely to catch a few unlucky people. We start with a very simple table which is just and id column with some padding, and then show the effect of a change in the handling of “constant subqueries”. Here’s my data set:

rem
rem     Script:         12c_subq_selectivity.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Jun 2013
rem 

create table t1
as
with generator as (
        select  --+ materialize
                rownum id
        from dual
        connect by
                level <= 1e5
)
select
        rownum                  id,
        lpad(rownum,6)          small_vc,
        rpad('x',100,'x')       padding
from
        generator
;

begin
        dbms_stats.gather_table_stats(
                ownname          => user,
                tabname          =>'T1',
                method_opt       => 'for all columns size 1'
        );
end;
/

alter table t1 add constraint t1_pk primary key (id);

Now I’m going to select all the rows for a couple of ranges of IDs, showing you the execution plan for each case – but you’ll notice that I’ve tried to “hide” the ranges from the optimizer by putting them into a “select from dual”. How does Oracle 12c cope ?

select
        *
from    t1
where   id between (select 10001 from dual)
           and     (select 20000 from dual)
;

---------------------------------------------------------------------------------------------
| Id  | Operation                           | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |       | 10001 |  1103K|   188   (1)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T1    | 10001 |  1103K|   188   (1)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | T1_PK | 10001 |       |    22   (0)| 00:00:01 |
|   3 |    FAST DUAL                        |       |     1 |       |     2   (0)| 00:00:01 |
|   4 |    FAST DUAL                        |       |     1 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID">= (SELECT 10001 FROM "SYS"."DUAL" "DUAL") AND "ID"<= (SELECT
              20000 FROM "SYS"."DUAL" "DUAL"))

select
        *
from    t1
where   id between (select 10001 from dual)
           and     (select 90000 from dual)
;

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 80002 |  8828K|   218   (4)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   | 80002 |  8828K|   214   (4)| 00:00:01 |
|   2 |   FAST DUAL       |      |     1 |       |     2   (0)| 00:00:01 |
|   3 |   FAST DUAL       |      |     1 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("ID">= (SELECT 10001 FROM "SYS"."DUAL" "DUAL") AND "ID"<=
              (SELECT 90000 FROM "SYS"."DUAL" "DUAL"))

Notice how 12c had managed to see constant values, even though they are hidden inside subqueries, and produced appropriate plans as the ranges change. Why does this matter ? Here’s the plan for the second query when you set the optimizer_features_enable back to 11.2.0.3:

select
        /*+ optimizer_features_enable('11.2.0.3') */
        *
from    t1
where   id between (select 10001 from dual)
           and     (select 90000 from dual)
;

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |   250 | 28250 |    14   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |   250 | 28250 |    10   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_PK |   450 |       |     2   (0)| 00:00:01 |
|   3 |    FAST DUAL                |       |     1 |       |     2   (0)| 00:00:01 |
|   4 |    FAST DUAL                |       |     1 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ID">= (SELECT 10001 FROM "SYS"."DUAL" "DUAL") AND "ID"<=
              (SELECT 90000 FROM "SYS"."DUAL" "DUAL"))

In 11.2.0.3 Oracle produces an estimate of 450 index entries and 250 rows – even for the 80,000 row case. These are the basic “selectivity guesses” for “unknown value”, and show the odd inconsistency that the optimizer displays between index guesses and table guesses.

If you’ve used this “hide the value” type of code, watch out for plan changes on the upgrade.

Footnote:

This change actually appears in the upgrade from 11.2.0.3 to 11.2.0.4 – but I’ve labelled it in the 12c upgrade list because I’m guessing it was introduced in 12.1 and backported to 11.2.0.4 as so many other little details appear to have been. There is a fix-control you can switch off to stop the effect if you really have to (check with Oracle Support first, of course): ‘alter session set “_fix_control”=’11813257:0”;