Oracle 执行计划(2)-基数 cardinality【即估算一个操作返回的行数】
来源:互联网 发布:网络课程怎么加速看 编辑:程序博客网 时间:2024/05/29 10:04
Oracle 执行计划(2)-基数 cardinality
执行计划的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));
insertinto 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 studentgroup 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_statisticswhere 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_tableswhere 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。
执行计划是根据统计信息来计算,来估算返回行的数量。//执行计划是根据表user_tab_col_statistics里的列DENSITY以及操作涉及的表的总行数,来估算返回行的数量。
(基本)选择率=DENSITY(或者是1/ NUM_DISTINCT) 。 返回本文开头部分看看表列的信息中的值。这就是基本选择率。
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 |
“where子句里只出现表上的某一列的多个条件”这种情况时的公式:
基本选择率+基本选择率+基本选择率= 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衰减思想。
注释:16时的基数也是73? 不是1200*1/12-(16-12)*9.09吗?
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
- (5)执行计划的代价估算
- Oracle 执行计划(3)-两表连接基数
- (1)oracle单表选择率(selectivity)——计算执行计划的基数
- 【Oracle 优化器】基数反馈 (Cardinality Feedback)功能
- ORACLE 执行计划中cost cardinality bytes cpu_cost io_cost解释
- oracle执行计划中NESTED LOOPS SEMI (即半嵌套循环)的解释
- oracle执行计划中NESTED LOOPS SEMI (即半嵌套循环)的解释
- oracle并行查询操作的执行计划
- 基数(cardinality)排序算法
- oracle的cardinality feedback
- 查询计划中集的势(Cardinality)的计算
- MySQL 执行计划中代价估算的输出信息
- ORACLE的执行计划
- ORACLE的执行计划
- 苦逼程序猿想找兼职 各位给点意见
- Linux设备驱动之USB hub驱动(续)
- linux实验_添加内核模块-完整版本_百度文库
- 笔记本电脑所有浏览器都打不开网页,手机却全能正常访问
- 将时间转换成指定的格式
- Oracle 执行计划(2)-基数 cardinality【即估算一个操作返回的行数】
- 通俗易懂的“僵死进程与孤儿进程”讲解
- iOS 视图控制器(UIViewController)剖析
- HxD(十六进制编码处理工具) 1.7中文版
- mac 上生成 RSA
- 轮廓处理函数
- java里String Stringbuffer Stringbuilder三者的区别
- javascript放大镜效果的简单实现
- VS 2010下一次性配置opencv(32位和64位相同)