Oracle 执行计划(2)-基数 cardinality
来源:互联网 发布:上古卷轴男性捏脸数据 编辑:程序博客网 时间:2024/05/29 03:08
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 |
------------------------------------------------------------------------------
- Oracle 执行计划(2)-基数 cardinality
- Oracle 执行计划(2)-基数 cardinality【即估算一个操作返回的行数】
- oracle 查询计划中的基数cardinality概念
- oracle中执行计划中的cardinality
- Oracle 执行计划(3)-两表连接基数
- 【Oracle 优化器】基数反馈 (Cardinality Feedback)功能
- ORACLE 执行计划中cost cardinality bytes cpu_cost io_cost解释
- (1)oracle单表选择率(selectivity)——计算执行计划的基数
- 基数(cardinality)排序算法
- ORACLE 执行计划2
- ORACLE PL/SQL Developer工具执行计划无COST和cardinality,bytes等列值显示问题
- oracle 查询计划中基数问题
- Oracle执行计划详解 2
- ORACLE执行计划(一)
- ORACLE执行计划(二)
- 关于mysql 索引自动优化机制: 索引选择性(Cardinality:索引基数)
- cbo优化中的基数(cardinality)介绍
- cbo优化中的基数(cardinality)介绍
- 各类经典搜索算法(Search Algorithms)的简单介绍和C++实现
- windows下的PHP环境搭建
- 多线程的创建方式
- 图片通过asp.net上传到mysql数据库
- jquery AJAX清除IE缓存问题
- Oracle 执行计划(2)-基数 cardinality
- android x系统定义颜色
- Android平台刷机包 修改制作方法
- extjs4 grid 新增、删除、修改
- 转换中的字节数差异
- SQL Server 返回修改后的数据
- Linux的配置文件
- NSTimer的使用以及 史上最简单的,NSTimer暂停和继续
- Cross-Validation算法性能的评估