Function-Based Indexes And CURSOR_SHARING = FORCE

In general it is known that Function-Based Indexes (FBIs) can no longer be used by the optimizer if the expression contains literals and CURSOR_SHARING = FORCE / SIMILAR (deprecated) turns those literals into bind variables. Jonathan Lewis described the issue quite a while ago here in detail.

In a recent OTN thread this issue was raised again, but to my surprise when I played around with a test case that mimicked the OP's problem query I found that (certain) Oracle versions have some built-in logic that enable FBI usage for certain cases where you would expect them to be not usable.

If you test the following code on versions from 10.2.0.4 (possibly earlier) up to and including version 11.2.0.3 then you'll notice some interesting details:

create table t
as
select * from all_objects;

create index t_idx on t (owner || ' ' || object_name);

exec dbms_stats.gather_table_stats(null, 't')

set echo on linesize 200 pagesize 0

alter session set cursor_sharing = force;

select /*+ full(t) */ * from t where owner || ' ' || object_name = 'BLA';

select * from table(dbms_xplan.display_cursor);

select /*+ index(t) */ * from t where owner || ' ' || object_name = 'BLA';

select * from table(dbms_xplan.display_cursor);

select /*+ index(t) */ * from t where owner || 'A' || object_name = 'BLA';

select * from table(dbms_xplan.display_cursor);

Here is the relevant output I got from 11.2.0.1 for example:

SQL> alter session set cursor_sharing = force;

Session altered.

SQL>
SQL> select /*+ full(t) */ * from t where owner || ' ' || object_name = 'BLA';

no rows selected

SQL>
SQL> select * from table(dbms_xplan.display_cursor);
SQL_ID  ar3tw7r1rvawk, child number 0
-------------------------------------
select /*+ full(t) */ * from t where owner || :"SYS_B_0" || object_name
= :"SYS_B_1"

Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |   284 (100)|          |
|*  1 |  TABLE ACCESS FULL| T    |     1 |   117 |   284   (2)| 00:00:04 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OWNER"||' '||"OBJECT_NAME"=:SYS_B_1)


19 rows selected.

SQL>
SQL> select /*+ index(t) */ * from t where owner || ' ' || object_name = 'BLA';

no rows selected

SQL>
SQL> select * from table(dbms_xplan.display_cursor);
SQL_ID  6kzz3vw5x8x3b, child number 0
-------------------------------------
select /*+ index(t) */ * from t where owner || :"SYS_B_0" ||
object_name = :"SYS_B_1"

Plan hash value: 470836197

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |       |       |     4 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     1 |   117 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_IDX |     1 |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T"."SYS_NC00016$"=:SYS_B_1)


20 rows selected.

SQL>
SQL> select /*+ index(t) */ * from t where owner || 'A' || object_name = 'BLA';

no rows selected

SQL>
SQL> select * from table(dbms_xplan.display_cursor);
SQL_ID  6kzz3vw5x8x3b, child number 1
-------------------------------------
select /*+ index(t) */ * from t where owner || :"SYS_B_0" ||
object_name = :"SYS_B_1"

Plan hash value: 3778778741

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |       |       | 52472 (100)|          |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T     |   724 | 84708 | 52472   (1)| 00:10:30 |
|   2 |   INDEX FULL SCAN           | T_IDX | 72351 |       |   420   (1)| 00:00:06 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OWNER"||:SYS_B_0||"OBJECT_NAME"=:SYS_B_1)


20 rows selected.

Looking at the statement text that results from "CURSOR_SHARING = force" we can spot the expected bind variables instead of the literals, and this should result in a corresponding predicate that doesn't match the FBI expression. However, when looking at the filter expression in the predicate section (when forcing a full table scan) we can spot something interesting: It still shows the literal, which doesn't correspond to the predicate of the rewritten query text.

The next execution shows that the FBI really can be used despite the bind variable replacement taking place, and the final execution shows that the cursor sharing works correctly in that sense that a new child cursor got created for the same SQL text with a different plan and different predicate section when using a different literal in the original SQL text. V$SQL_SHARED_CURSOR shows "HASH_MATCH_FAILED" which is described as "No existing child cursors have the unsafe literal bind hash values required by the current cursor", which makes sense and probably means that the corresponding bind variable is marked as "unsafe" internally.

This optimisation shows only up if there is a suitable FBI - if there's no corresponding expression the SQL text and predicate section match. Furthermore it only supports certain expressions - Jonathan's example shows that in general it's true that these rewrites prevent FBI usage. And obviously it ceases to work in 11.2.0.4 and 12c. Whether this is a bug or a feature I don't know, but since it only seems to apply to certain expressions it's probably not that relevant anyway.

As Jonathan points out in his note you can always work around the general problem by hiding the expression in a view, and since 11g of course a proper virtual column definition is the better approach, which doesn't expose this problem either.

Even better would be the proper usage of bind variables and not using forced cursor sharing, but there are still many installations out there that rely on that feature.

Exchange Partition, Virtual Columns And Column Statistics

Here is an odd bug that can lead to some nasty side effects when using the EXCHANGE PARTITION technique. It is probably there for a very long time, simply because it depends on the usage of virtual columns, and the basic technique of virtual columns was introduced way back in the Oracle 8i times with the introduction of Function Based Indexes.

The problem isn't the exchange partition operation itself, but the accompanying swap of object statistics information, in particular the column statistics.

Look the following sequence of DDL and DML commands and pay then special attention to the output for the column statistics before and after the EXCHANGE PARTITION operation:

-- EXCHANGE_PARTITION_VIRTUAL_COLS.SQL -- -- Simple example that demonstrates that differences in Virtual Columns -- between two tables do not prevent an EXCHANGE PARTITION operation -- -- But Column Statistics are screwed up after the EXCHANGE PARTITION operation -- if you have inconsistencies in the INTERNAL_COLUMN_IDs caused by the Virtual Columns -- -- Either EXCHANGE PARTITION should fail or handle this correctly -- -- Reproduced: 10.2.0.4, 10.2.0.5, 11.2.0.1, 11.2.0.2, 11.2.0.3 set echo on linesize 400 column column_name format a30 drop table t_x; purge table t_x; drop table t; purge table t; -- The partitioned table create table t partition by range (id) ( partition p_default values less than (maxvalue) ) as select rownum as id , 'DESC' || rownum as attr000 , rpad('x', 100) as some_char from dual connect by level <= 10000 ; -- Add a virtual column here alter table t add (virt_col as (attr000 || 'BLA')); -- Note that the same problem will show up with other methods that generate a virtual column -- -- So one of the below statements instead of above explicit virtual column definition will show a similar problem in column statistics -- -- exec dbms_stats.gather_table_stats(null, 'T', method_opt => 'for columns (attr000 || ''BLA'') size 1') -- create index t_idx on t (attr000 || 'BLA'); -- Add two non-virtual columns afterwards alter table t add (non_virt_col1 varchar2(100), non_virt_col2 varchar2(100)); -- Some data update t set non_virt_col1 = 'y', non_virt_col2 = 'z'; commit; -- The non-partitioned table create table t_x as select rownum as id , 'DESC' || rownum as attr000 , rpad('x', 100) as some_char , cast('y' as varchar2(100)) as non_virt_col1 -- Data is different from T for this column , cast('a' as varchar2(100)) as non_virt_col2 from dual connect by level <= 10000 ; exec dbms_stats.gather_table_stats(null, 't') exec dbms_stats.gather_table_stats(null, 't_x') -- Correct Column Statistics here for both objects select b.internal_column_id , a.column_name , a.num_distinct , a.low_value , a.high_value , a.density from user_part_col_statistics a , user_tab_cols b where a.table_name = 'T' and b.table_name = 'T' and a.partition_name = 'P_DEFAULT' and a.table_name = b.table_name and a.column_name = b.column_name order by b.internal_column_id; select b.internal_column_id , a.column_name , a.num_distinct , a.low_value , a.high_value , a.density from user_tab_col_statistics a , user_tab_cols b where a.table_name = 'T_X' and b.table_name = 'T_X' and a.table_name = b.table_name and a.column_name = b.column_name order by b.internal_column_id; -- Exchange doesn't complain since on physical level the tables are identical -- But logically the INTERNAL COLUMN_IDs are different -- Hence the Statistics on partition level of T and global level of T_X are now screwed up for all columns that have different INTERNAL COLUMN_IDs in the two tables alter table t exchange partition p_default with table t_x including indexes without validation; -- Bad Column Statistics here, see for example T.VIRT_COL or T_X.NON_VIRT_COL1 select b.internal_column_id , a.column_name , a.num_distinct , a.low_value , a.high_value , a.density from user_part_col_statistics a , user_tab_cols b where a.table_name = 'T' and b.table_name = 'T' and a.partition_name = 'P_DEFAULT' and a.table_name = b.table_name and a.column_name = b.column_name order by b.internal_column_id; select b.internal_column_id , a.column_name , a.num_distinct , a.low_value , a.high_value , a.density from user_tab_col_statistics a , user_tab_cols b where a.table_name = 'T_X' and b.table_name = 'T_X' and a.table_name = b.table_name and a.column_name = b.column_name order by b.internal_column_id; column column_name clear

The relevant Column Statistics for T before and after the exchange partition operation:

INTERNAL_COLUMN_ID COLUMN_NAME NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY ------------------ ------------------------------ ------------ ---------------------------------------------------------------- ---------------------------------------------------------------- ---------- 1 ID 10000 C102 C302 .0001 2 ATTR000 10000 4445534331 4445534339393939 .0001 3 SOME_CHAR 1 7820202020202020202020202020202020202020202020202020202020202020 7820202020202020202020202020202020202020202020202020202020202020 1 4 VIRT_COL 10000 444553433130303030424C41 4445534339424C41 .0001 5 NON_VIRT_COL1 1 79 79 1 6 NON_VIRT_COL2 1 7A 7A 1 INTERNAL_COLUMN_ID COLUMN_NAME NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY ------------------ ------------------------------ ------------ ---------------------------------------------------------------- ---------------------------------------------------------------- ---------- 1 ID 10000 C102 C302 .0001 2 ATTR000 10000 4445534331 4445534339393939 .0001 3 SOME_CHAR 1 7820202020202020202020202020202020202020202020202020202020202020 7820202020202020202020202020202020202020202020202020202020202020 1 4 VIRT_COL 1 79 79 1 5 NON_VIRT_COL1 1 61 61 1 6 NON_VIRT_COL2

and for T_X:

INTERNAL_COLUMN_ID COLUMN_NAME NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY ------------------ ------------------------------ ------------ ---------------------------------------------------------------- ---------------------------------------------------------------- ---------- 1 ID 10000 C102 C302 .0001 2 ATTR000 10000 4445534331 4445534339393939 .0001 3 SOME_CHAR 1 7820202020202020202020202020202020202020202020202020202020202020 7820202020202020202020202020202020202020202020202020202020202020 1 4 NON_VIRT_COL1 1 79 79 1 5 NON_VIRT_COL2 1 61 61 1 INTERNAL_COLUMN_ID COLUMN_NAME NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY ------------------ ------------------------------ ------------ ---------------------------------------------------------------- ---------------------------------------------------------------- ---------- 1 ID 10000 C102 C302 .0001 2 ATTR000 10000 4445534331 4445534339393939 .0001 3 SOME_CHAR 1 7820202020202020202020202020202020202020202020202020202020202020 7820202020202020202020202020202020202020202020202020202020202020 1 4 NON_VIRT_COL1 10000 444553433130303030424C41 4445534339424C41 .0001 5 NON_VIRT_COL2 1 79 79 1

Not only this can lead to situations where columns simply end up with wrong (shifted) or no column statistics at all, but it is even possible to have columns with statistics that are simply illegal because the internal representation of the low / high values for example isn't compatible with the actual column data type.

If you think about how that information is stored in the data dictionary (along the so called INTERNAL column id, there is also a plain COLUMN_ID for display purposes and a SEGMENT column id that defines how the columns are stored in the blocks) and how Oracle very likely performs the swap of object statistic when exchanging partitions the potential problem becomes obvious.

It is however very unlikely to hit that bug for several reasons:

- There are several reasons why you usually want to gather the statistics on the exchanged partition after the exchange took place. If you want to make use of the SIZE AUTO functionality that automatically determines the generation of column histograms based on column usage, or want to ensure that higher level statistics are maintained via aggregation, or if you want to benefit from more recent features like Incremental Partition Statistics, for example.

If you follow this recommended technique you won't have any problems on the partitioned object since the statistics gathered after the exchange operation won't be affected by the bug

However, depending on what you do with the swapped table segment, you might still encounter problems since, without refreshing the statistics on that table you'll potentially end up with screwed up column statistics.

- Until the arrival of more recent features like Virtual Columns and Extended Statistics that under the covers are based on the same technique it is very likely that both objects involved in the exchange partition operation are carefully maintained synchronously with regard to structural changes like column or index additions, since otherwise the EXCHANGE PARTITION operation might fail with corresponding errors

From 11g on however you can far simpler fall into the trap of getting out-of-sync internal column ids by simply gathering Extended Statistics on or by adding a Virtual Column to only one of the two objects. The EXCHANGE PARTITION operation won't fail, but if the Virtual Columns were added in a different order or real columns were added afterwards (to both objects) the column statistics of the affected columns will be screwed up.

Note that the official documentation is not exactly helpful, since it explicitly says here that the Virtual Columns don't need to be in sync between the two objects because only the real columns are relevant for the EXCHANGE PARTITION operation itself.

Furthermore from a pure technical point of view it is a legal approach to gather statistics on the table segment to be exchanged into the partitioned object and rely on EXCHANGE PARTITION to swap properly the statistics between the two segments, which in this case only affects the partition level statistics of the particular partition being exchanged (unless you play with the undocumented parameter "_minimal_stats_aggregation") and the table level statistics of the unpartitioned object.

If you follow this practice and do some of the following:

- Add Function Based Indexes to only one of the objects or both objects in different order, or mixed with additions of other, real columns

- Add Extended Statistics or Virtual Columns only to one of the objects and afterwards add real columns

then the resulting column statistics will be screwed up for all columns that don't have a matching INTERNAL column id.

Oracle obviously simply just swaps the two object_ids in the underlying statistics dictionary tables, which is the most obvious and efficient operation, but unfortunately produces wrong column statistics results if the INTERNAL column ids don't match.

The operation would probably be much more complex if Oracle tried to handle this situation correctly.

The possible workarounds are simple:

- Either prevent such a situation upfront, by ensuring that all operations including those that generate Virtual Columns are always done in sync to the two objects involved

- Gather statistics on both objects after the exchange partition operation took place

The client has opened an SR; it will be interesting to hear what Oracle says.

Forced Cursor Sharing And Virtual Columns

So you have that application that cannot be changed but makes use of some weird expressions that screw up the cardinality estimates of the optimizer.

Consider this simple example:

create table t as select rownum as id , case when mod(rownum, 100000) = 0 then 'y' else 'n' end as flag , rpad('x', 100) as filler from dual connect by level <= 1000000 ; exec dbms_stats.gather_table_stats(null, 't', method_opt => 'for all columns size 1 for columns flag size 2', estimate_percent => 30) select flag, count(*) from t group by flag; explain plan for select count(*) from t where flag = 'y' ; set linesize 200 tab off pagesize 0 select * from table(dbms_xplan.display);

We have a table with a very skewed "FLAG" column with just a few occurrences of 'y'. The histogram on the column allows the optimizer to recognize this.

Notice that I haven't used the AUTO_SAMPLE_SIZE default, due to the potential problem of 11g in particular when using low sample sizes for a histogram on a column having rare values leading to inconsistent column and histogram statistics - but for larger tables the AUTO_SAMPLE_SIZE in 11g gives you much better basic column statistics, so I don't advise in general to not use AUTO_SAMPLE_SIZE. It might be worth to use a separate GATHER_TABLE_STATS call only for the columns with histograms using an explicit sample size, but using AUTO_SAMPLE_SIZE for the remaining columns.

From running this script on 11.2.0.1 I get this output:

SQL> create table t 2 as 3 select 4 rownum as id 5 , case 6 when mod(rownum, 100000) = 0 7 then 'y' 8 else 'n' 9 end as flag 10 , rpad('x', 100) as filler 11 from 12 dual 13 connect by 14 level <= 1000000 15 ; Table created. SQL> SQL> exec dbms_stats.gather_table_stats(null, 't', method_opt => 'for all columns size 1 for columns flag size 2', estimate_percent => 30) PL/SQL procedure successfully completed. SQL> SQL> select flag, count(*) from t group by flag; F COUNT(*) - ---------- y 10 n 999990 SQL> SQL> explain plan for 2 select count(*) from t where flag = 'y' 3 ; Explained. SQL> SQL> set linesize 200 tab off pagesize 0 SQL> SQL> select * from table(dbms_xplan.display); Plan hash value: 2966233522 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 2 | 4292 (2)| 00:00:52 | | 1 | SORT AGGREGATE | | 1 | 2 | | | |* 2 | TABLE ACCESS FULL| T | 8 | 16 | 4292 (2)| 00:00:52 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("FLAG"='y') 14 rows selected.

So the cardinality estimate for the FLAG column is in the right ballpark as I've prevented problems with the histogram. But now we have this expression, so let's see what happens then to the estimates:

explain plan for select count(*) from t where nvl(upper(flag), 'Y') = 'Y' ; select * from table(dbms_xplan.display);

which gives me this output from my 11.2.0.1 session:

SQL> explain plan for 2 select count(*) from t where nvl(upper(flag), 'Y') = 'Y' 3 ; Explained. SQL> SQL> select * from table(dbms_xplan.display); Plan hash value: 2966233522 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 2 | 4308 (2)| 00:00:52 | | 1 | SORT AGGREGATE | | 1 | 2 | | | |* 2 | TABLE ACCESS FULL| T | 9992 | 19984 | 4308 (2)| 00:00:52 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(NVL(UPPER("FLAG"),'Y')='Y') 14 rows selected. SQL>

As expected, the optimizer doesn't have a clue and falls back to a hardcoded one percent default estimate.

Since this is 11g, let's generate extended statistics for the expression with a histogram and re-check the estimates:

exec dbms_stats.gather_table_stats(null, 't', method_opt => 'for columns (nvl(upper(flag), ''Y'')) size 2', estimate_percent => 30) explain plan for select count(*) from t where nvl(upper(flag), 'Y') = 'Y' ; select * from table(dbms_xplan.display);

which gives me

SQL> exec dbms_stats.gather_table_stats(null, 't', method_opt => 'for all columns size 1 for columns flag size 2 for columns (nvl(upper(flag), ''Y'')) size 2', estimate_percent => 30) PL/SQL procedure successfully completed. SQL> SQL> explain plan for 2 select count(*) from t where nvl(upper(flag), 'Y') = 'Y' 3 ; Explained. SQL> SQL> select * from table(dbms_xplan.display); Plan hash value: 2966233522 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 4 | 4308 (2)| 00:00:52 | | 1 | SORT AGGREGATE | | 1 | 4 | | | |* 2 | TABLE ACCESS FULL| T | 8 | 32 | 4308 (2)| 00:00:52 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(NVL(UPPER("FLAG"),'Y')='Y') 14 rows selected.

So that looks good again. Now let's combine this with CURSOR_SHARING = FORCE:

alter session set cursor_sharing = force; explain plan for select count(*) from t where nvl(upper(flag), 'Y') = 'Y' ; select * from table(dbms_xplan.display); select count(*) from t where nvl(upper(flag), 'Y') = 'Y'; select * from table(dbms_xplan.display_cursor);

Notice in particular the EXPLAIN PLAN output and the actual execution plan used at runtime:

SQL> alter session set cursor_sharing = force; Session altered. SQL> SQL> explain plan for 2 select count(*) from t where nvl(upper(flag), 'Y') = 'Y' 3 ; Explained. SQL> SQL> select * from table(dbms_xplan.display); Plan hash value: 2966233522 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 4 | 4308 (2)| 00:00:52 | | 1 | SORT AGGREGATE | | 1 | 4 | | | |* 2 | TABLE ACCESS FULL| T | 8 | 32 | 4308 (2)| 00:00:52 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(NVL(UPPER("FLAG"),'Y')='Y') 14 rows selected. SQL> SQL> select count(*) from t where nvl(upper(flag), 'Y') = 'Y'; 10 SQL> SQL> select * from table(dbms_xplan.display_cursor); SQL_ID 8jzw0jwwuk83f, child number 0 ------------------------------------- select count(*) from t where nvl(upper(flag), :"SYS_B_0") = :"SYS_B_1" Plan hash value: 2966233522 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 4308 (100)| | | 1 | SORT AGGREGATE | | 1 | 2 | | | |* 2 | TABLE ACCESS FULL| T | 9998 | 19996 | 4308 (2)| 00:00:52 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(NVL(UPPER("FLAG"),:SYS_B_0)=:SYS_B_1) 19 rows selected.

So there are two important takeaways:

1. EXPLAIN PLAN doesn't care about CURSOR_SHARING=FORCE. You have to be very careful when CURSOR_SHARING=FORCE is active regarding the execution plans you see from EXPLAIN PLAN and the actual ones used at runtime. This also means that the AUTOTRACE feature of SQL*Plus might lie to you, too.

2. The literal replacement performed by CURSOR_SHARING=FORCE prevents the optimizer from making use of the Virtual Column / Extended Statistics, because the expression has changed, hence we are back to the bad cardinality estimate and the additional information is useless to the optimizer in this scenario

Footnote

If you really need to use CURSOR_SHARING=FORCE due to some badly written application, I always advise to minimize the scope. If feasible, don't run the whole instance in that mode, but limit the setting to either the corresponding sessions (for example, via LOGON triggers) or even to certain processing parts of the application (via corresponding ALTER SESSION SET CURSOR_SHARING = FORCE/EXACT calls).

Note that all this applies to CURSOR_SHARING=SIMILAR, too, but since its use is deprecated anyway (see MOS document 1169017.1) I haven't mentioned it here before.

Finally, related technologies like Function Based Indexes and Virtual Columns are very likely affected in a similar way regarding the resulting cardinality estimates.

Multi-Column Joins, Expressions and 11g

Introduction

I've already outlined in one of my previous posts that getting a reasonable cardinality estimate for multi-column joins can be tricky, in particular when dealing with correlated column values in the join columns.

Since Oracle 10g several "Multi-Column Join Cardinality" sanity checks have been introduced that prevent a multi-column join from producing too low join cardinalities - this is controlled via the "_optimizer_join_sel_sanity_check" internal parameter that defaults to true from 10g on.

It looks like that if you upgrade to 11g this version adds just another twist to this issue. If you happen to have expressions as part of your join predicates then in 10g these are still covered by the multi-column join cardinality sanity checks as long as at least one side of the join refers to simple columns, but this seems no longer to be the case from 11g on.

Note that if those expressions are already covered by corresponding function-based indexes in pre-11g then this problem will not show up as described here - in fact, adding corresponding indexes is one of the possible fixes as I'll outline below.

A working example

Let's have a look at a simple example to demonstrate the potential upgrade issue. This code snippet creates a table with 1000 rows - ID_50 and ID_CHAR_50 both hold 50 distinct values and the two columns are correlated.

create table t
as
select
rownum as id
, mod(rownum, 50) + 1 as id_50
, 'ABC' || to_char(mod(rownum, 50) + 1) as id_char_50
, case when mod(rownum, 2) = 0 then null else mod(rownum, 100) + 1 end as id_50_null
, case when mod(rownum, 2) = 0 then null else 'ABC' || to_char(mod(rownum, 100) + 1) end as id_char_50_null
from
dual
connect by
level <= 1000
;

exec dbms_stats.gather_table_stats(null, 't', method_opt => 'for all columns size 1')

In 10.2.0.4 if you check the cardinality estimates of the following query you'll see the "Multi-Column Join Cardinality" check kicking in:

explain plan for
select /*+ optimizer_features_enable('10.2.0.4') */
/* opt_param('_optimizer_join_sel_sanity_check', 'false') */
count(*)
from
t t1
, t t2
where
t1.id_50 = t2.id_50
and t1.id_char_50 = t2.id_char_50
;

select * from table(dbms_xplan.display(null, null, 'BASIC +ROWS'));

--------------------------------------------
| Id | Operation | Name | Rows |
--------------------------------------------
| 0 | SELECT STATEMENT | | 1 |
| 1 | SORT AGGREGATE | | 1 |
| 2 | HASH JOIN | | 1000 |
| 3 | TABLE ACCESS FULL| T | 1000 |
| 4 | TABLE ACCESS FULL| T | 1000 |
--------------------------------------------

If you activate the commented hint to disable the sanity check, you'll end up with a different estimate that corresponds simply to the selectivity of each single join predicate multiplied: 1/50 * 1/50 * 1000 * 1000 = 400.

--------------------------------------------
| Id | Operation | Name | Rows |
--------------------------------------------
| 0 | SELECT STATEMENT | | 1 |
| 1 | SORT AGGREGATE | | 1 |
| 2 | HASH JOIN | | 400 |
| 3 | TABLE ACCESS FULL| T | 1000 |
| 4 | TABLE ACCESS FULL| T | 1000 |
--------------------------------------------

If now one or more expressions get introduced on one side of the join, in 10.2.0.4 the result will still correspond to the one with the sanity check enabled:

explain plan for
select /*+ optimizer_features_enable('10.2.0.4') */
/* opt_param('_optimizer_join_sel_sanity_check', 'false') */
count(*)
from
t t1
, t t2
where
t1.id_50 = case when t2.id_50 is null then -1 else t2.id_50 end
and t1.id_char_50 = t2.id_char_50
;

select * from table(dbms_xplan.display(null, null, 'BASIC +ROWS'));

--------------------------------------------
| Id | Operation | Name | Rows |
--------------------------------------------
| 0 | SELECT STATEMENT | | 1 |
| 1 | SORT AGGREGATE | | 1 |
| 2 | HASH JOIN | | 1000 |
| 3 | TABLE ACCESS FULL| T | 1000 |
| 4 | TABLE ACCESS FULL| T | 1000 |
--------------------------------------------

But if the same is repeated in 11.1 or 11.2, you'll end up with this result - as you can see the sanity checks have not been used and we get the same result as in 10.2.0.4 with disabled sanity checks - by the way, depending on the expressions (and on which sides of the join they get used), you might even end up with a different cardinality estimate based on default selectivities like 1/100 - this is controlled via the "_use_column_stats_for_function" parameter that defaults to true in recent releases and therefore some simpler join expressions still use the underlying column statistics:

--------------------------------------------
| Id | Operation | Name | Rows |
--------------------------------------------
| 0 | SELECT STATEMENT | | 1 |
| 1 | SORT AGGREGATE | | 1 |
| 2 | HASH JOIN | | 400 |
| 3 | TABLE ACCESS FULL| T | 1000 |
| 4 | TABLE ACCESS FULL| T | 1000 |
--------------------------------------------

This change in behaviour can lead to dramatic changes in the cardinality estimates and hence to different execution plans - potentially performing much worse than before. The change in this example here is not that significant but it can easily lead to very low cardinality estimates if the join columns do have a high number of distinct values.

New 11g Features

I believe the issue has been introduced by the new Oracle 11g feature of virtual columns and extended statistics on expressions and column groups. In fact these new features provide a possible workaround for the issue: By creating a corresponding virtual column or extended statistics on the expressions used as part of the join the sanity check can be re-enabled in 11g.

exec dbms_stats.gather_table_stats(null, 't', method_opt =>'for columns (case when id_50 is null then -1 else id_50 end) size 1')

or alternatively:

alter table t add (virtual_col1 as (case when t2.id_50 is null then -1 else t2.id_50 end));

exec dbms_stats.gather_table_stats(null, 't', method_opt =>'for columns virtual_col1 size 1')

As already outlined above, another possible workaround is adding a corresponding function-based index:

create index t_idx_func1 on t (case when id_50 is null then -1 else id_50 end);

Since adding a function-based index adds a similar hidden virtual column to the table as the extended statistics does the net effect will be the same but of course with the additional overhead of maintaining the index.

Column Groups with Expressions - Correlated Column Values

Of course if we really would like to take advantage of the new features with correlated column values what we should try to do is creating a column group on the combined expressions to allow the optimizer to detect the correlation, but unfortunately mixing expressions/virtual columns with column groups is explicitly mentioned in the documentation as not supported (yet), which can be confirmed:

SQL> exec dbms_stats.gather_table_stats(null, 't', method_opt =>'for columns (case when id_50 is null then -1 else id_50 end, id_char_50) size 1')
BEGIN dbms_stats.gather_table_stats(null, 't', method_opt =>'for columns (case when id_50 is null then -1 else id_50 end, id_char_50) size 1'); END;

*
ERROR at line 1:
ORA-20001: Error when processing extension - missing right parenthesis

SQL> exec dbms_stats.gather_table_stats(null, 't', method_opt =>'for columns (virtual_col1, id_char_50) size 1')
BEGIN dbms_stats.gather_table_stats(null, 't', method_opt =>'for columns (virtual_col1, id_char_50) size 1'); END;

*
ERROR at line 1:
ORA-20001: Error when processing extension - virtual column is referenced in a
column expression
ORA-06512: at "SYS.DBMS_STATS", line 20337
ORA-06512: at "SYS.DBMS_STATS", line 20360
ORA-06512: at line 1

Yet the strange thing is that the desired effect can easily be achieved by adding a corresponding multi-column function-based index like this:

create index t_idx3 on t (case when id_50 is null then -1 else id_50 end, id_char_50);

So this is one area where virtual columns / extended statistics are not yet equivalent to function-based indexes. However there is one significant difference between the index and the extended statistics column group approach: Whereas the former can be used to derive the number of distinct values if the index is an exact match to the column group the latter creates a virtual column combining the columns into a single expression using the undocumented SYS_OP_COMBINED_HASH function. Histograms can be generated on that virtual column which can be helpful in the case of correlated and skewed column values. Note that in my tests the join cardinality calculation based on column groups did not take any existing histograms on the virtual column into account, however single-table access predicates could make use of the histogram - but it's something that might be possible in future releases, but it's not possible to derive that information from the index on the column group.

Repeat above EXPLAIN PLAN now, first with 10.2.0.4 optimizer settings:

explain plan for
select /*+ optimizer_features_enable('10.2.0.4') */
count(*)
from
t t1
, t t2
where
t1.id_50 = case when t2.id_50 is null then -1 else t2.id_50 end
and t1.id_char_50 = t2.id_char_50
;

select * from table(dbms_xplan.display(null, null, 'BASIC +ROWS'));

--------------------------------------------
| Id | Operation | Name | Rows |
--------------------------------------------
| 0 | SELECT STATEMENT | | 1 |
| 1 | SORT AGGREGATE | | 1 |
| 2 | HASH JOIN | | 1000 |
| 3 | TABLE ACCESS FULL| T | 1000 |
| 4 | TABLE ACCESS FULL| T | 1000 |
--------------------------------------------

No change, however, if you repeat the same now with 11.1 or 11.2 optimizer settings:

--------------------------------------------
| Id | Operation | Name | Rows |
--------------------------------------------
| 0 | SELECT STATEMENT | | 1 |
| 1 | SORT AGGREGATE | | 1 |
| 2 | HASH JOIN | | 20000 |
| 3 | TABLE ACCESS FULL| T | 1000 |
| 4 | TABLE ACCESS FULL| T | 1000 |
--------------------------------------------

In this particular case the 11g cardinality estimate with the multi-column function-based index is spot on.

As already explained in the previous post Oracle 11g does now take advantage of indexes that 10g didn't - in 10g this required unique indexes.

Although this is good news, and the cardinality estimates in general should change for the better, it still means that even with suitable indexes in place you might end up with significant cardinality estimate changes after the upgrade to 11g that require testing.

The Single-Column Workarounds

With a single, non-combined statistics expression (using one of the methods shown above) in 11g we are at least back to the 10.2.0.4 cardinality estimate with the sanity checks enabled:

--------------------------------------------
| Id | Operation | Name | Rows |
--------------------------------------------
| 0 | SELECT STATEMENT | | 1 |
| 1 | SORT AGGREGATE | | 1 |
| 2 | HASH JOIN | | 1000 |
| 3 | TABLE ACCESS FULL| T | 1000 |
| 4 | TABLE ACCESS FULL| T | 1000 |
--------------------------------------------

Interestingly if the column group is covered by an index then in 11g the sanity check is also still enabled - and the order and position in the index apparently doesn't matter in this particular case, it just has to be an index covering the columns/expressions used - possibly among other columns/expressions.

Of course this workaround can have other side effects: First of all you introduce more work because DBMS_STATS needs to gather statistics for the underlying virtual columns added - and if want to use extended statistics on expressions rather than virtual columns you can only have a limited number of statistics extensions per table (I'm not sure why this restriction exists and it can be worked around by using virtual columns instead). Also the additional virtual columns count towards the hard limit of 1,000 columns per table.

Furthermore if you happen to use the same expressions as filter predicates the cardinality estimates very likely will again change with the workaround in place - mind you, it will probably lead to improved cardinality estimates, but nevertheless it means a change that needs to be tested.

Here is a cardinality estimate for the sample join expression used as filter without the workaround:

explain plan for
select count(*) from t where case when id_50 is null then -1 else id_50 end = :b1;

select * from table(dbms_xplan.display(null, null, 'BASIC +ROWS'));

-------------------------------------------
| Id | Operation | Name | Rows |
-------------------------------------------
| 0 | SELECT STATEMENT | | 1 |
| 1 | SORT AGGREGATE | | 1 |
| 2 | TABLE ACCESS FULL| T | 10 |
-------------------------------------------

But with the workaround in place:

explain plan for
select count(*) from t where case when id_50 is null then -1 else id_50 end = :b1;

select * from table(dbms_xplan.display(null, null, 'BASIC +ROWS'));

-------------------------------------------
| Id | Operation | Name | Rows |
-------------------------------------------
| 0 | SELECT STATEMENT | | 1 |
| 1 | SORT AGGREGATE | | 1 |
| 2 | TABLE ACCESS FULL| T | 20 |
-------------------------------------------

More Complex Expressions

The new features allow 11g to use the sanity checks (or cardinality estimates derived from index statistics) even in cases where 10g would not be able to use them - if you for example happen to have expressions on both sides of the join, in 10g the sanity checks get disabled, but using the corresponding expressions in 11g allows to have the sanity checks enabled.

For more complex expressions - that are probably a clear indication of a design issue - the 11g extended statistics/virtual columns also allow get improved cardinality estimates in general - 10g would resort to some hard-coded selectivity like 1/100 for equi-joins - 11g would be able to cover that as well:

explain plan for
select /* optimizer_features_enable('10.2.0.4') */
/* opt_param('_optimizer_join_sel_sanity_check', 'false') */
count(*)
from
t t1
, t t2
where
nvl(t1.id_50 + t1.id_50_null, -1) = nvl(t2.id_50 + t2.id_50_null, -1)
and nvl(t1.id_char_50 || t1.id_char_50_null, 'bla') = nvl(t2.id_char_50 || t2.id_char_50_null, 'bla')
;

select * from table(dbms_xplan.display(null, null, 'BASIC +ROWS'));

--------------------------------------------
| Id | Operation | Name | Rows |
--------------------------------------------
| 0 | SELECT STATEMENT | | 1 |
| 1 | SORT AGGREGATE | | 1 |
| 2 | HASH JOIN | | 100 |
| 3 | TABLE ACCESS FULL| T | 1000 |
| 4 | TABLE ACCESS FULL| T | 1000 |
--------------------------------------------

Expressions like that will disable the sanity check and also not use any underlying column statistics and therefore fall back to built-in, hard-coded defaults.

But when creating corresponding extended statistics / virtual columns / multi-column function-based indexes in 11g we are able to take advantage of the sanity checks (at least) and get improved cardinality estimates in general:

exec dbms_stats.gather_table_stats(null, 't', method_opt =>'for columns (nvl(id_50 + id_50_null, -1)) size 1')

exec dbms_stats.gather_table_stats(null, 't', method_opt =>'for columns (nvl(id_char_50 || id_char_50_null, ''bla'')) size 1')

--------------------------------------------
| Id | Operation | Name | Rows |
--------------------------------------------
| 0 | SELECT STATEMENT | | 1 |
| 1 | SORT AGGREGATE | | 1 |
| 2 | HASH JOIN | | 1000 |
| 3 | TABLE ACCESS FULL| T | 1000 |
| 4 | TABLE ACCESS FULL| T | 1000 |
--------------------------------------------

Summary

Expressions used as part of multi-column join predicates can cause a lot of trouble when upgrading from 10g to 11g - fortunately there are viable workarounds available if you want to keep the optimizer features of 11g enabled - in other words restricting to 10g optimizer features is of course also a workaround but usually not a desired long-term solution.

Note that there are cases where multi-column function-based indexes offer improved cardinality estimates in 11g over that of virtual columns or extended statistics - but at the price of maintaining an additional (potentially wide) index, requiring additional storage and at the risk of other plans changing by either using the index or being indirectly influenced by the additional index statistics available.

Some of the side-effects of the additional index could be addressed by leaving such an index in unusable state, but this again might have other, undesirable side effects, like statistics gathering jobs failing with error messages about unusable indexes etc.

Frequency histograms - edge cases

Oracle introduced with the 10.2.0.4 patch set a significant change how non-existing values are treated when there is a frequency histogram on a column / expression. See Jonathan Lewis' blog post which is probably the most concise description of the issue. In a nutshell the change is about the following (quoted from Jonathan's post): "If the value you supply does not appear in the histogram, but is inside the low/high range of the histogram then the cardinality will be half the cardinality of the least frequently occurring value that is in the histogram".

I'm still a bit puzzled why Oracle introduced such a significant change to the optimizer with a patch set, but one of the most obvious reasons might be that the change allows to generate frequency histograms using a rather low sample size, because there is no longer a similar threat as before when the frequency histogram misses one of the existing values (which would then return an estimate of 1 if they didn't appear in the histogram).

In fact when using the default DBMS_STATS.AUTO_SAMPLE_SIZE Oracle exactly does this: It uses by default a quite low sample size to perform the additional runs required for each histogram - probably an attempt to minimize the additional work that needs to be done for histogram generation.

In it is however quite interesting to see how exactly this behaviour together with the new treatment of non-existing values can turn into a threat as a recent thread on OTN demonstrated.

Consider the following scenario: You have a column with a highly skewed data distribution; there is a single, very popular value, and a few other, very unpopular values.

Now you have a query type that filters on this column and frequently searches for non-existing values. In order to speed up the query an index has been created on the column, and in order to make the optimizer aware of the fact that the data distribution is highly skewed a histogram is generated, so that the optimizer should favor the index only for those unpopular respectively non-existing values.

The following test case (run on 11.1.0.7) emulates this scenario:

create table t1 (
id number(*, 0)
, t_status number(*, 0)
, vc varchar2(100)
);

-- 1 million rows
-- One very popular value
-- Two very unpopular values
-- in column T_STATUS
insert /*+ append */ into t1 (id, t_status, vc)
with generator as (
select /*+ materialize */
level as id
from
dual
connect by
level <= 10000
)
select /*+ use_nl(v1, v2) */
rownum as id
, case
when rownum <= 10
then 1
when rownum <= 20
then 2
else 0
end as t_status
, rpad('x', 100) as vc
from
generator v1
, generator v2
where
rownum <= 1000000;

commit;

create index t1_idx on t1 (t_status);

exec dbms_stats.gather_table_stats(null, 'T1', method_opt => 'for all columns size 1 for columns t_status size 254')

The first interesting point are the generated column statistics:

SQL>
SQL> -- Note that the basic column statistics
SQL> -- are generated with a much higher sample size
SQL> -- The histogram however was created with a sample size
SQL> -- of 5,000 rows only
SQL> -- Therefore we get three distinct values but only a single bucket
SQL> -- But not always => Potential instability issue
SQL> select
2 column_name
3 , num_distinct
4 , sample_size
5 from
6 user_tab_col_statistics
7 where
8 table_name = 'T1';

COLUMN_NAME NUM_DISTINCT SAMPLE_SIZE
--------------- ------------ -----------
ID 1000000 1000000
T_STATUS 3 5499
VC 1 1000000

SQL>
SQL> -- This results in a single bucket
SQL> -- But not always => Potential instability issue
SQL> select
2 column_name
3 , endpoint_number
4 , endpoint_value
5 from
6 user_tab_histograms
7 where
8 table_name = 'T1'
9 and column_name = 'T_STATUS';

COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------- --------------
T_STATUS 5499 0

Notice the inconsistency: The basic column statistics (number of distinct values, low value, high value) obviously have been generated with a much higher sample size (in fact a compute in this case) than the histogram on T_STATUS. The histogram therefore misses the unpopular values and consists only of a single value - the very popular one.

Now watch closely what happens to the cardinality estimates of the non-existing values:

SQL>
SQL> -- The popular value
SQL> explain plan for
2 select
3 *
4 from
5 t1
6 where
7 t_status = 0
8 ;

Explained.

SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 999K| 102M| 4319 (2)| 00:00:52 |
|* 1 | TABLE ACCESS FULL| T1 | 999K| 102M| 4319 (2)| 00:00:52 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("T_STATUS"=0)

13 rows selected.

SQL>
SQL> -- A non-existing value
SQL> explain plan for
2 select
3 *
4 from
5 t1
6 where
7 t_status = 1000
8 ;

Explained.

SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 500K| 51M| 4316 (2)| 00:00:52 |
|* 1 | TABLE ACCESS FULL| T1 | 500K| 51M| 4316 (2)| 00:00:52 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("T_STATUS"=1000)

13 rows selected.

SQL>
SQL> -- Two non-existing values
SQL> explain plan for
2 select
3 *
4 from
5 t1
6 where
7 t_status in (1000, 2000)
8 ;

Explained.

SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1000K| 102M| 4325 (3)| 00:00:52 |
|* 1 | TABLE ACCESS FULL| T1 | 1000K| 102M| 4325 (3)| 00:00:52 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("T_STATUS"=1000 OR "T_STATUS"=2000)

13 rows selected.

SQL>

Ouch, ouch: Whereas the estimate for the popular value is correct, the estimates for the unpopular values are totally way-off - in fact using an IN clause with two non-existing values gets an estimate of all rows contained in the table.

The explanation: Since the least popular value is the single bucket covering virtually all rows, half of it is still 50% of the total cardinality - so two non-existing values in an IN clause end up with a selectivity of 1.

Furthermore the usual decay to values outside the low/high column values doesn't apply here either - no matter what non-existing values get used, the overestimation stays the same.

Update April 2012: It's probably worth to point out that this behaviour of ignoring the low/high values can only be observed when the basic column statistics Number of Distinct Values (NDV) is not consistent with the corresponding histogram. If both are consistent (for example, both show one distinct value) then the out-of-range detection will work as expected.
This also means that since in 10.2 the histogram and the basic column statistics tend to be more consistent than in 11g due to the new AUTO_SAMPLE_SIZE algorithm the problem by default doesn't show up in 10.2.

These overestimates have obviously a significant impact - here the suitable index doesn't get used - more complex plans might turn even into a complete disaster.

The default behaviour that histograms are generated with a much lower sample size than the basic column statistics also introduces a kind of instability - try to run the example several times. Sometimes one of the unpopular values might be caught by the default histogram generation, sometimes not. The effect is dramatic: If one of the unpopular values gets caught, the estimates will be reasonable again, since then the least popular value do have a very low cardinality - if not, you get exactly the opposite result just demonstrated.

If the old behaviour gets re-activated, the results are as expected with the same set of statistics:

SQL>
SQL> -- Switch off new behaviour
SQL> alter session set "_fix_control" = '5483301:off';

Session altered.

SQL>
SQL> -- The popular value
SQL> explain plan for
2 select
3 *
4 from
5 t1
6 where
7 t_status = 0
8 ;

Explained.

SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 999K| 102M| 4319 (2)| 00:00:52 |
|* 1 | TABLE ACCESS FULL| T1 | 999K| 102M| 4319 (2)| 00:00:52 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("T_STATUS"=0)

13 rows selected.

SQL>
SQL> -- A non-existing value
SQL> explain plan for
2 select
3 *
4 from
5 t1
6 where
7 t_status = 1000
8 ;

Explained.

SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 546753835

--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 107 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 107 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T1_IDX | 1 | | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("T_STATUS"=1000)

14 rows selected.

SQL>
SQL> -- Two non-existing values
SQL> explain plan for
2 select
3 *
4 from
5 t1
6 where
7 t_status in (1000, 2000)
8 ;

Explained.

SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3743026710

---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 107 | 5 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 107 | 5 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T1_IDX | 1 | | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("T_STATUS"=1000 OR "T_STATUS"=2000)

15 rows selected.

Switching back to the former treatment that non-existing values lead to an estimate of 1 will fix this issue, however since this has a potential impact on every execution plan thorough regression testing would be required with this used as a global setting.

Increasing the sample size is another option, if it ensures that unpopular values get caught by the histogram generation, so that the least popular value of the histogram is one with a low cardinality. Note however that this will increase the amount of work necessary to gather the statistics for the histograms.

SQL>
SQL> -- Gather statistics with 100% sample size
SQL> exec dbms_stats.gather_table_stats(null, 'T1', estimate_percent => null, method_opt => 'for all columns size 1 for columns t_status size 254')

PL/SQL procedure successfully completed.

SQL>
SQL> -- Now the statistics are more representative
SQL> select
2 column_name
3 , num_distinct
4 , sample_size
5 from
6 user_tab_col_statistics
7 where
8 table_name = 'T1';

COLUMN_NAME NUM_DISTINCT SAMPLE_SIZE
--------------- ------------ -----------
ID 1000000 1000000
T_STATUS 3 1000000
VC 1 1000000

SQL>
SQL> -- The histogram now covers also the unpopular values
SQL> select
2 column_name
3 , endpoint_number
4 , endpoint_value
5 from
6 user_tab_histograms
7 where
8 table_name = 'T1'
9 and column_name = 'T_STATUS';

COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------- --------------
T_STATUS 999980 0
T_STATUS 999990 1
T_STATUS 1000000 2

SQL>
SQL> -- Switch back on new behaviour
SQL> alter session set "_fix_control" = '5483301:on';

Session altered.

SQL>
SQL> -- A non-existing value
SQL> explain plan for
2 select
3 *
4 from
5 t1
6 where
7 t_status = 1000
8 ;

Explained.

SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 546753835

--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 107 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 107 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T1_IDX | 1 | | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("T_STATUS"=1000)

14 rows selected.

SQL>
SQL> -- Two non-existing values
SQL> explain plan for
2 select
3 *
4 from
5 t1
6 where
7 t_status in (1000, 2000)
8 ;

Explained.

SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3743026710

---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 107 | 5 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 107 | 5 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | T1_IDX | 1 | | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("T_STATUS"=1000 OR "T_STATUS"=2000)

15 rows selected.

As suggested by Jonathan Lewis in the OTN thread, another elegant solution to the problem of searching for non-existing values would be to add a virtual column that filtered out the popular values. This approach has several advantages if applicable: The index maintained is very small, minimizes the maintenance effort (and might also address index-efficiency related issues in case of frequent updates to the column) and solves the histogram issue since the histogram will only cover the unpopular values:

SQL>
SQL> -- Add a virtual column (the same could be achieved using a function-based index instead for pre-11g versions)
SQL> alter table t1 add (t_status_unpop as (case when t_status != 0 then t_status end));

Table altered.

SQL>
SQL> exec dbms_stats.gather_table_stats(null, 'T1', method_opt => 'for columns t_status_unpop size 254')

PL/SQL procedure successfully completed.

SQL>
SQL> -- Since the virtual column only covers
SQL> -- the unpopular values, the histogram
SQL> -- will be very precise even with a low sample size
SQL> select
2 column_name
3 , num_distinct
4 , sample_size
5 from
6 user_tab_col_statistics
7 where
8 table_name = 'T1';

COLUMN_NAME NUM_DISTINCT SAMPLE_SIZE
--------------- ------------ -----------
ID 1000000 1000000
T_STATUS 3 1000000
VC 1 1000000
T_STATUS_UNPOP 2 20

SQL>
SQL> -- The histogram only covers the unpopular values
SQL> select
2 column_name
3 , endpoint_number
4 , endpoint_value
5 from
6 user_tab_histograms
7 where
8 table_name = 'T1'
9 and column_name = 'T_STATUS_UNPOP';

COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------- --------------
T_STATUS_UNPOP 10 1
T_STATUS_UNPOP 20 2

SQL>
SQL> -- Two non-existing values
SQL> -- So we don't run into the same problem here
SQL> -- The estimate is reasonable for non-existing values
SQL> explain plan for
2 select
3 *
4 from
5 t1
6 where
7 t_status_unpop in (1000, 2000)
8 ;

Explained.

SQL>
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 108 | 4470 (6)| 00:00:54 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 108 | 4470 (6)| 00:00:54 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("T_STATUS_UNPOP"=1000 OR "T_STATUS_UNPOP"=2000)

13 rows selected.

I haven't created an index on the virtual column, but the estimate is correct and a suitable index would get used if it existed.