12c Fixed Subquery

It’s been about 8 months since I posted a little note about a “notable change in behaviour” of the optimizer when dealing with subqueries in the where clause that could be used to return a constant, e.g.:

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

There’s been a note at the start of the script ever since saying: Check if this is also true for any table with ‘select fixed_value from table where primary = constant’ I finally had a few minutes this morning (San Francisco time) to check – and it does, in both 11.2.0.4 and 12.1.0.2. With the t1 table from the previous article run the following:

rem
rem     Script:         12c_subq_selectivity.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Jun 2013
rem
rem     Last tested
rem             12.2.0.1
rem             12.1.0.2
rem             11.2.0.4
rem

drop table t2 purge;

create table t2 (
        n1 number(6) not null,
        n2 number(6) not null
);

alter table t2 add constraint t2_pk primary key(n1);

insert into t2 values(1,10000);
insert into t2 values(2,90000);

set autotrace traceonly explain

select * from t1
where   id between (select 10000 from t2 where n1 = 1)
           and     (select 90000 from t2 where n1 = 1)
;

set autotrace off

Instead of the historic 5% of 5% selectivity, the plan shows the optimizer predicting (approximately) the 80,000 rows that it will actually get:

----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       | 80003 |  8828K|   218   (4)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL | T1    | 80003 |  8828K|   218   (4)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN| T2_PK |     1 |    13 |     0   (0)| 00:00:01 |
|*  3 |   INDEX UNIQUE SCAN| T2_PK |     1 |    13 |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("ID"<= (SELECT 90000 FROM "T2" "T2" WHERE "N1"=1) AND    -- > edit to avoid WordPress format issue
              "ID">= (SELECT 10000 FROM "T2" "T2" WHERE "N1"=1))
   2 - access("N1"=1)
   3 - access("N1"=1)

I can’t think it’s very likely that anyone has written SQL that looks like this – but I’m often surprised by what I see in the field, so if this style looks familiar and you’re still on 11.2.0.3 or lower, watch out for changes in execution plan on the upgrade to 11.2.0.4 or 12c.