这个是对动态采样的一个补充。
人为的创建一个组合列统计信息，只要谓词条件中出现组合列某一列的时候，就不在使用之前的计算方式，从而避免CBO执行计划评估偏差问题。
创建组合列：
dbms_stats.create_extended_stats('SH','T2','(n1,n2)');
收集组合列统计信息：
SQL> exec dbms_stats.gather_table_stats('SH','T2',method_opt=>'for columns (n1,n2) size auto');
模拟环境：
还是拿原先的环境试验；
SQL> select count(1) from t2 where n1=3 and n2=3 and c1='a';
COUNT(1)
----------
498
返回结果集为498
SQL> select * from table(dbms_xplan.display_cursor(null,0));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 1tpvtscuhy3b6, child number 1
-------------------------------------
select count(1) from t2 where n1=3 and n2=3 and c1='a'
Plan hash value: 4191549303
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|* 2 | INDEX RANGE SCAN| IDX_T2 | 25 | 200 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("N1"=3 AND "N2"=3 AND "C1"='a')
Note
-----
- cardinality feedback used for this statement
查看执行计划，发现执行计划返回的rows为 25，跟实际返回结果集498严重偏差。
创建组合列：
SQL> set serveroutput on
SQL> declare
cg_name varchar2(30);
begin
cg_name:=sys.dbms_stats.create_extended_stats('SH','T2','(n1,n2)');
end;
/
SQL> select object_id from user_objects where object_name='T2';
OBJECT_ID
----------
87688
SQL> select col#,intcol#,name from sys.col$ where obj#=87688;
COL# INTCOL# NAME
---------- ---------- ------------------------------------------------------------
1 1 C1
2 2 C2
3 3 N1
4 4 N2
0 5 SYS_STUBZH0IHA7K$KEBJVXO5LOHAS
可以看到，创建组合列实际上就是在原表T2上附件了一个‘SYS_STUBZH0IHA7K$KEBJVXO5LOHAS
’列。
SQL> exec dbms_stats.gather_table_stats('SH','T2',method_opt=>'for columns (n1,n2) size auto');
SQL> select count(1) from t2 where n1=3 and n2=3 and c1='a';
COUNT(1)
----------
498
SQL> /
COUNT(1)
----------
498
一定要连续执行2次，
SQL> select * from table(dbms_xplan.display_cursor(null,0));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 1ngdx15skn5cm, child number 1
-------------------------------------
select count(1) from t2 where n1=3 and n2=3 and c1='a'
Plan hash value: 4191549303
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|* 2 | INDEX RANGE SCAN| IDX_T2 | 498 | 3984 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("N1"=3 AND "N2"=3 AND "C1"='a')
Note
-----
- cardinality feedback used for this statement
查看执行计划，发现返回的rows 是 498 跟实际返回结果集一致。
得出结论：通过创建多列统计信息，也可以实现动态采样的功能。