Oracle 执行计划（2）-基数
执行计划的cardinality列。基数是说从数据表，结果集或者索引返回多少行数据。
基数=表行数据*选择率。所以讲基数重点是讲选择率，选择率的计算！
版本10G 默认 表空间信息：
|
TABLESPACE_NAME |
USERS |
|
BLOCK_SIZE |
8192 |
|
EXTENT_MANAGEMENT |
LOCAL |
|
ALLOCATION_TYPE |
SYSTEM |
|
SEGMENT_SPACE_MANAGEMENT |
AUTO |
|
DEF_TAB_COMPRESSION |
DISABLED |
|
BIGFILE |
NO |
比如我们建个表
Create table student ( id number,name varchar2(50),birday date,month_no number(2))；
insert into student
select
trunc(dbms_random.value(1,1200)) id,
a.object_name,
sysdate-rownum as birday,
trunc(dbms_random.value(1,13)) month_no
from all_objects a
where rownum<=1200
分析下
SQL> analyze table student compute statistics;
看看数据分布
select month_no ,count(1) from student group by month_no;
|
MONTH_NO |
COUNT(1) |
|
1 |
104 |
|
2 |
87 |
|
3 |
100 |
|
4 |
91 |
|
5 |
114 |
|
6 |
102 |
|
7 |
102 |
|
8 |
96 |
|
9 |
100 |
|
10 |
114 |
|
11 |
97 |
|
12 |
93 |
数据分布得比较均匀
看看表列的情况:
select * from user_tab_col_statistics where table_name='STUDENT'
|
TABLE_NAME |
COLUMN_NAME |
NUM_DISTINCT |
LOW_VALUE |
HIGH_VALUE |
DENSITY |
NUM_NULLS |
NUM_BUCKETS |
AVG_COL_LEN |
|
STUDENT |
ID |
762 |
C102 |
C20C61 |
0.00131233595800525 |
0 |
1 |
3 |
|
STUDENT |
NAME |
1192 |
41434345535324 |
5741524E494E475F53455454494E475324 |
0.000838926174496644 |
0 |
1 |
14 |
|
STUDENT |
BIRDAY |
1200 |
786C0C1F100D2E |
7870040D100D2E |
0.000833333333333333 |
0 |
1 |
7 |
|
STUDENT |
MONTH_NO |
12 |
C102 |
C10D |
0.0833333333333333 |
0 |
1 |
2 |
列名解释:
1 NUM_DISTINCT 该列有多少个不同的值,非空.
2 LOW_VALUE 该列最小值
3 HIGH_VALUE 该列最大值
4 DENSITY 该列密度 如果有直方图的话才有此值计算
5 NUM_NULLS 该列空置多少
6 NUM_BUCKETS 该列采用直方图用多少桶
7 AVG_COL_LEN 该列平均长度
select * from user_tables where table_name='STUDENT'
|
AVG_ROW_LEN |
NUM_ROWS |
EMPTY_BLOCKS |
BLOCKS |
|
33 |
1200 |
3 |
13 |
表信息： 分别是平均长度，数据行数，空块数，全部块数。
OK 我们把数据搞定了！接下来就可以谈谈选择率了，看下这样的语句的执行计划。
SQL> set autotrace on
SQL> select count(*) from STUDENT where month_no=12;
COUNT(*)
----------
93
已用时间: 00: 00: 00.01
执行计划
----------------------------------------------------------
Plan hash value: 4030891848
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 4 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 2 | | |
|* 2 | TABLE ACCESS FULL| STUDENT | 100 | 200 | 4 (0)| 00:00:01 |
------------------------------------------------------------------------------
SQLPLUS环境里的ROWS就是基数
实际有93行而计划里说有100行，根据公式 基数=行数*选择率。那么选择率=基数/行数 => ？=100/1200=1/12.
执行计划是根据统计信息来计算，来估算返回行的数量。
选择率=1/ NUM_DISTINCT 或者是DENSITY 。 返回上面看看表列的信息中的值。这就是基本选择率。
1 有空值情况下的选择率
把每个月的生日置空10 共120.
update student set month_no =null where month_no=1 and rownum<=10;
…
update student set month_no =null where month_no=12 and rownum<=10;
再度分析下表，并查看表列信息，可以看到month_no列NUM_NULLS=120
再次运行上面的语句得到执行计划：
SQL> select count(*) from STUDENT where month_no=12;
COUNT(*)
----------
83
已用时间: 00: 00: 00.00
执行计划
----------------------------------------------------------
Plan hash value: 403089184
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 4 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 2 | | |
|* 2 | TABLE ACCESS FULL| STUDENT | 90 | 180 | 4 (0)| 00:00:01 |
这里的基数变成了90了
公式 选择率=基本选择率*(num_rows-num_nulls)/num_rows。
这里的基本选择率是1/num_distinct 因为使用了= 的条件。
公式推导 => ?=1/12*(1200-120)/1200=0.075
基数=1200*0.075 =90
2 使用列表 in(…)
1 回滚 update student set month_no=12 where month_no is null and rownum<=10;
2 分析表
3 查看表列信息 发现NUM_NULLS 变回了0值。
4 执行语句：select count(*) from STUDENT where month_no in(6,7,8);
SQL> select count(*) from STUDENT where month_no in(6,7,8);
COUNT(*)
----------
300
已用时间: 00: 00: 00.00
执行计划
----------------------------------------------------------
Plan hash value: 4030891848
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 4 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 2 | | |
|* 2 | TABLE ACCESS FULL| STUDENT | 300 | 600 | 4 (0)| 00:00:01 |
公式=基本选择率+基本选择率+基本选择率= 3/12
如果in(15) or in(15，16) 基数分别 73，146 而不是100，200. 因为超过最大值，当超过最大与最小值距离(11) 为0.
距离15-12=3 间距12-1=11 平均递减100/11=9.09 15的基数100-(3*9.09)=72.73=73。这说明ORACLE衰减思想。
3 区间谓词的选择率
1 month_no >8 ,>=8,<8,<=8
2 between 6 and 9
3 >=6 and <=9; >=6 and <9; >6 and <=9 ; >6 and <9
4 >12
5 between 25 and 30
6 >=:val1,> < <=:val1 绑定变量
7 >= :val1 and <= :val2 between :val1 and :val2
公式=需要空间除以可用空间
可用空间=high_vlaue-low_value=12-1=11
需要空间=（high_vlaue-limit） 或（limit-low_value）或（high_vlaue-low_value）
1 month_no>8 （high_vlaue-limit）/（high_vlaue-low_value）=(12-8)/11=4/11
2 month_no>=8 因为=是基本选择率 1/12 因此 4/11+1/12
3 month_no < 8 小于用最小值 上面大于用最大值 (limit-low_value) /（high_vlaue-low_value）=(8-1)/11=7/11
4 between 6 and 9 or >=6 and <=9: （9-6）/(12-1)+1/12+1/12
5 (>=6,<9) (6>,<=9) :(9-6)/(12-1)+1/12
6 >6 and < 9 :(9-6)/(12-1)
7 >=:val1 固定选择率5%
8 >=:val1 and <=:val2 固定比率 2.5%
9 超过范围将逐步衰减。
4 双谓词
1 month >8 or month <=8
2 month >8 and month<=8
谓词1的选择率(12-8)/(12-1)=4/11=0.363636364
谓词2的选择率 (8-1)/(12-1)+1/12=7/11+1/12=0.71969697
公式 1 (谓词1 AND 谓词2)= 谓词1选择率*谓词2的选择率 因此第2个是 4/11*(7/11+1/12)= 0.261707989
公式 2 (谓词1 or 谓词2)= 谓词1选择率+谓词2的选择率-(谓词1选择率*谓词2的选择率)
=4/11+((7/11+1/12)-( 4/11*(7/11+1/12)))
=0.363636364+0.71969697- 0.261707989
=0.821625344
基数=1200*0.821625344=985.9504128=986
看下执行计划：
SQL> select count(*) from student where month_no >8 or month_no<=8;
COUNT(*)
----------
1200
已用时间: 00: 00: 00.01
执行计划
----------------------------------------------------------
Plan hash value: 4030891848
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 4 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 2 | | |
|* 2 | TABLE ACCESS FULL| STUDENT | 986 | 1972 | 4 (0)| 00:00:01 |
------------------------------------------------------------------------------