最近在阅读崔华老师《基于Oracle的SQL优化》,测试其中动态采样以及多列统计信息用例时,在自己测试环境结果与书中有出入,进行了一些研究,发现直方图是对测试影响的根因,特将研究过程以及基于Oracle的多列关联查询相关知识整理一下,分享出来。
- 测试版本为11.2.0.4.190115 PSU版本
- 测试用例以及一些知识来自崔华老师《基于Oracle的SQL优化》
- 下文主要涉及Oracle多列统计信息、基数反馈。
1.创建测试表收集统计信息
SQL> create table t2 (c1 varchar2(1),c2 char(2000),n1 number, n2 number);
SQL> insert into t2 select 'a','a',trunc(dbms_random.value(0,20)),trunc(dbms_random. value(0,25)) from dba_objects where rownum<10001;
SQL> commit;
SQL> exec dbms_stats.gather_table_stats(ownname=>'HR',tabname=>'T2',CASCADE=>true, estimate_percent=>100);
2 执行SQL,查看cardinality
2.1 T2表统计信息
***********
Table Level
***********
Table Number Empty Average Chain Average Global User Sample Date
Name of Rows Blocks Blocks Space Count Row Len Stats Stats Size MM-DD-YYYY
--------------- ------------------ -------- ------------ ------- -------- ------- ------ ------ ------------------ ----------
T2 10,000 3,394 0 0 0 #### YES NO 10,000 04-16-2021
Elapsed: 00:00:00.03
Column Column Distinct Number Number Global User Sample Date
Name Details Values Density Buckets Nulls Stats Stats Size MM-DD-YYYY
------------------------- ------------------------ ------------ ------- ------- ---------- ------ ------ ------------------ ----------
C1 VARCHAR2(1) 1 1 1 0 YES NO 10,000 04-16-2021
C2 CHAR(2000) 1 1 1 0 YES NO 10,000 04-16-2021
N1 NUMBER(22) 20 0 1 0 YES NO 10,000 04-16-2021
N2 NUMBER(22) 25 0 1 0 YES NO 10,000 04-16-2021
2.2 执行SQL,查看cardinality
SQL> select * from t2 where n1=3 and n2=3 and c1='a';
Execution Plan
----------------------------------------------------------
Plan hash value: 1513984157
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0