Oracle 11g: Multi-Column Correlation Without Extended Stats

Today I’ve been trying to reproduce in an 11g database one of the problems I faced with 10g—one on those problems Riyaj described in his Multi-Column Correlation and Extended Stats in Oracle 11g post. And the fun part is that I wasn’t able to reproduce it. Yet just setting optimizer_features_enable='10.2.0.4' made it show up again. It was as though Oracle can detect Multi-Column Correlation without Extended Statistics. How is this possible?

You don’t need any complicated schemas to check this out for yourself; just one table as below:

create table x (
         a number, 
         b number, 
         c number);

begin
  for i in 1..1000 loop
    for j in 1..10 loop
      insert into x values (j,j,j);
    end loop;
  end loop;
end;
/

commit;

exec dbms_stats.gather_table_stats(-
                 user,-
                 'X');

Execute a query with a WHERE clause containing two correlated columns:

explain plan for select c from x where a=1 and b=1;

select * from table(dbms_xplan.display);

--------------------------------------------------------------------------
| Id  | Operation	  | Name | Rows  | Bytes | Cost (%CPU)| Time	 |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	 |   100 |   900 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| X	 |   100 |   900 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------

The optimizer estimates the number of rows returned as 100, which is equivalent to considering that each one of the conditions in the WHERE clause returns 1000 rows, and that those columns are not correlated. (In that case that’s obviously wrong, because a equals b). So, no change with 10g? If you now create a multi-column index on (a,b), it estimates changes:

alter session set events '10053 trace name context level 1';

create index xidx on x(a,b);

explain plan for select /*+ no_index */ c from x where a=1 and b=1;

select * from table(dbms_xplan.display);

--------------------------------------------------------------------------
| Id  | Operation	  | Name | Rows  | Bytes | Cost (%CPU)| Time	 |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	 |  1000 |  9000 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| X	 |  1000 |  9000 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------

alter session set events '10053 trace name context off';

You can see from the 10053 trace that, as is the case with extended statistics, there is a correction factor for the number of matching row estimate:

Access path analysis for X
***************************************
SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for X[X]
  ColGroup (#1, Index) XIDX
    Col#: 1 2    CorStregth: 10.00
  ColGroup Usage:: PredCnt: 2  Matches Full: #1  Partial:  Sel: 0.1000
  Table: X  Alias: X
    Card: Original: 10000.000000  Rounded: 1000  Computed: 1000.00  Non Adjusted: 1000.00
  Access Path: TableScan
    Cost:  7.16  Resp: 7.16  Degree: 0
      Cost_io: 7.00  Cost_cpu: 2412429
      Resp_io: 7.00  Resp_cpu: 2412429
  ColGroup Usage:: PredCnt: 2  Matches Full: #1  Partial:  Sel: 0.1000
  ColGroup Usage:: PredCnt: 2  Matches Full: #1  Partial:  Sel: 0.1000
  Access Path: index (AllEqRange)
    Index: XIDX
    resc_io: 23.00  resc_cpu: 554643
    ix_sel: 0.100000  ix_sel_with_filters: 0.100000
    Cost: 23.04  Resp: 23.04  Degree: 1
  Best:: AccessPath: TableScan
         Cost: 7.16  Degree: 1  Resp: 7.16  Card: 1000.00  Bytes: 0

And, obviously, that was not the case in 10g:

alter session set optimizer_features_enable='10.2.0.4';

explain plan for select /*+ no_index 10204 */ c from x where a=1 and b=1;

select * from table(dbms_xplan.display);

--------------------------------------------------------------------------
| Id  | Operation	  | Name | Rows  | Bytes | Cost (%CPU)| Time	 |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	 |   100 |   900 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| X	 |   100 |   900 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------

alter session set optimizer_features_enable='11.1.0.7';

Every change can have some drawbacks:

explain plan for select /*+ no_index */ c from x where a=1 and b=2;

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

--------------------------------------------------------------------------
| Id  | Operation	  | Name | Rows  | Bytes | Cost (%CPU)| Time	 |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |	 |  1000 |  9000 |     7   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| X	 |  1000 |  9000 |     7   (0)| 00:00:01 |
--------------------------------------------------------------------------

SQL> select count(*) 
  2    from dba_stat_extensions
  3   where table_name='X';

  COUNT(*)
----------
	 0

In my case, the move to 11g has been really beneficial and without any change other than the upgrade itself. This quirk, however, does give me the feeling that once again, the move to a new release could yet have a few surprises.