AI 助理
备案控制台
开发者社区 数据库 文章 正文

oracle单表选择率（selectivity）——计算执行计划的基数

2017-11-19 1179
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介：

CBO优化器是基于对当前经过特定测试的数据集中预期的行比率估计来计算基数的。此处的行数之比是一个数值，称为选择率（selectivity）。得到选择率之后，将其与输入行数进行简单相乘既可得到基数。

在理解选择性之前，必须得对user_tab_col_statistics视图有一定了解：

[sql]  view plain  copy
 
 print?
  1. SQL> desc user_tab_col_statistics  
  2.  名称                                      是否为空? 类型  
  3.  ----------------------------------------- -------- ----------------------------  
  4.  TABLE_NAME                                         VARCHAR2(30)  表名  
  5.  COLUMN_NAME                                        VARCHAR2(30)  列名  
  6.  NUM_DISTINCT                                       NUMBER        列中distinct值的数目  
  7.  LOW_VALUE                                          RAW(32)       列的最小值  
  8.  HIGH_VALUE                                         RAW(32)       列的最大值  
  9.  DENSITY                                            NUMBER        当对列创建了直方图，则值不再等于1/NUM_DISTINCT。  
  10.  NUM_NULLS                                          NUMBER        列中的NULL值数目。  
  11.  NUM_BUCKETS                                        NUMBER        Number of buckets in histogram for the column  
  12.  LAST_ANALYZED                                      DATE          最近分析时间。  
  13.  SAMPLE_SIZE                                        NUMBER        分析样本大小。  
  14.  GLOBAL_STATS                                       VARCHAR2(3)   对分区采样，则-NO，否则-YES。  
  15.  USER_STATS                                         VARCHAR2(3)   统计信息由用户导入，则YES，否则-NO。  
  16.  AVG_COL_LEN                                        NUMBER        列的平均长度(以字节为单位)  
  17.  HISTOGRAM                                          VARCHAR2(15)  Indicates existence/type of histogram: NONE、FREQUENCY、HEIGHT BALANCED  

下面创建一张测试表，并收集统计信息：

[sql]  view plain  copy
 
 print?
  1. SQL> create table audience as  
  2.   2  select  
  3.   3    trunc(dbms_random.value(1,13))  month_no  
  4.   4  from  
  5.   5    all_objects  
  6.   6  where  
  7.   7    rownum <= 1200  
  8.   8  ;  
  9.   
  10. 表已创建。  
  11.   
  12. SQL> begin  
  13.   2    dbms_stats.gather_table_stats(  
  14.   3      user,  
  15.   4      'audience',  
  16.   5      cascade => true,  
  17.   6      estimate_percent => null,  
  18.   7     );ethod_opt => 'for all columns size 1'  
  19.     method_opt => 'for all columns size 1'  
  20.   8  );  
  21.   9  end;  
  22.  10  /  
  23.   
  24. PL/SQL 过程已成功完成。  

先查看一下上面表和列的统计信息：

[sql]  view plain  copy
 
 print?
  1. SQL> select t.TABLE_NAME, t.NUM_ROWS, t.BLOCKS, t.SAMPLE_SIZE  
  2.   2    from user_tables t;  
  3.   
  4. TABLE_NAME   NUM_ROWS     BLOCKS SAMPLE_SIZE  
  5. ---------- ---------- ---------- -----------  
  6. AUDIENCE         1200          5        1200  
  7.   
  8. SQL> select s.table_name,  
  9.        s.column_name,  
  10.        s.num_distinct,  
  11.   4         s.low_value,  
  12.        s.high_value,  
  13.        s.density,  
  14.   7         s.num_nulls,  
  15.   8         s.sample_size,  
  16.   9         s.avg_col_len  
  17.  10    from user_tab_col_statistics s;  
  18.   
  19. TABLE_NAME COLUMN_NAM NUM_DISTINCT LOW_VALUE  HIGH_VALUE    DENSITY  NUM_NULLS SAMPLE_SIZE AVG_COL_LEN  
  20. ---------- ---------- ------------ ---------- ---------- ---------- ---------- ----------- -----------  
  21. AUDIENCE   MONTH_NO             12 C102       C10D       .083333333          0        1200           3  
  22.   
  23. SQL> select rawtohex(1), rawtohex(12) from dual;  
  24.   
  25. RAWT RAWT  
  26. ---- ----  
  27. C102 C10D  
[sql]  view plain  copy
 
 print?
  1. SQL> select dump(1,16),dump(12,16) from dual;  
  2.    
  3. DUMP(1,16)        DUMP(12,16)  
  4. ----------------- -----------------  
  5. Typ=2 Len=2: c1,2 Typ=2 Len=2: c1,d  
  6.    
  7. SQL> select utl_raw.cast_to_number('c102'),utl_raw.cast_to_number('c10d') from dual;  
  8.    
  9. UTL_RAW.CAST_TO_NUMBER('C102') UTL_RAW.CAST_TO_NUMBER('C10D')  
  10. ------------------------------ ------------------------------  
  11.                              1                             12   --可以看见上面的LOW_VALUE和HIGH_VALUE的值分别就是1和12.  
[sql]  view plain  copy
 
 print?
  1. SQL> select count(a.month_no) from AUDIENCE a;  
  2.   
  3. COUNT(A.MONTH_NO)  
  4. -----------------  
  5.              1200     --可以看见，这里的值和NUM_ROWS是一样的。  
  6.   
  7. SQL> select count(distinct a.month_no) from AUDIENCE a;  
  8.   
  9. COUNT(DISTINCTA.MONTH_NO)  
  10. -------------------------  
  11.                        12       --可以看见，这里的值也和NUM_DISTINCT的值是一样的。  
[sql]  view plain  copy
 
 print?
  1. SQL> select 1/12 from dual;  
  2.   
  3.       1/12  
  4. ----------  
  5. .083333333               --这里的值和DENSITY一样的，计算公式为1/NUM_DISTINCT。  

 

1、假如在上面创建了一张表，里面包含1200个人，如何才能确定其中有多少人的生日是在12月份。

[sql]  view plain  copy
 
 print?
  1. SQL> select count(*) from AUDIENCE where month_no=12;  
  2.   
  3. 执行计划  
  4. ----------------------------------------------------------  
  5. Plan hash value: 3337892515  
  6.   
  7. -------------------------------------------------------------------------------  
  8. | Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |  
  9. -------------------------------------------------------------------------------  
  10. |   0 | SELECT STATEMENT   |          |     1 |     3 |     3   (0)| 00:00:01 |  
  11. |   1 |  SORT AGGREGATE    |          |     1 |     3 |            |          |  
  12. |*  2 |   TABLE ACCESS FULL| AUDIENCE |   100 |   300 |     3   (0)| 00:00:01 |  
  13. -------------------------------------------------------------------------------  
  14.   
  15. Predicate Information (identified by operation id):  
  16. ---------------------------------------------------  
  17.   
  18.    2 - filter("MONTH_NO"=12)  

可以看见CBO计算出1200里面，12月份生日的人是100人（在ID=2行的rows）。这和我们通常所理解的是一样的，我们知道月份只有12个，在1200人中在某一个月出生的人，算概率也是100人（CBO也是这样做得）。

计算方法为：DENSITY * NUM_ROWS = 1 / 12 * 1200 = 100。

 

2、现在假设有10%的人不记得自己的生日了，那么CBO会怎么计算呐？

[sql]  view plain  copy
 
 print?
  1. SQL> drop table audience purge;  
  2.   
  3. 表已删除。  
  4.   
  5. SQL> create table audience as  
  6.   2  select  
  7.   3    rownum        id,  
  8.   4    trunc(dbms_random.value(1,13))  month_no  
  9.   5  from  
  10.   6    all_objects  
  11.   7  where  
  12.   8    rownum <= 1200;  
  13.   
  14. 表已创建。  
  15.   
  16. SQL> update    
  17.   2    audience  
  18.   3  set  month_no = null  
  19.   4  where  mod(id,10) = 0;         --10%的用户不记得自己的生日。  
  20.   
  21. 已更新120行。  
  22.   
  23. SQL> commit;  
  24.   
  25. 提交完成。  
  26.   
  27. SQL> begin  
  28.   2    dbms_stats.gather_table_stats(  
  29.   3      user,  
  30.   4      'audience',  
  31.   5      cascade => true,  
  32.   6      estimate_percent => null,  
  33.   7      method_opt => 'for all columns size 1'  
  34.   8    );  
  35.   9  end;  
  36.  10  /  
  37.   
  38. PL/SQL 过程已成功完成。  
  39.   
  40. SQL> select t.TABLE_NAME, t.NUM_ROWS, t.BLOCKS, t.SAMPLE_SIZE from user_tables t;  
  41.   
  42. TABLE_NAME   NUM_ROWS     BLOCKS SAMPLE_SIZE  
  43. ---------- ---------- ---------- -----------  
  44. AUDIENCE         1200          5        1200  
  45.   
  46. SQL> select s.table_name,  
  47.   2         s.column_name,  
  48.   3         s.num_distinct,  
  49.   4         s.low_value,  
  50.   5         s.high_value,  
  51.   6         s.density,  
  52.   7         s.num_nulls,  
  53.   8         s.sample_size,  
  54.   9         s.avg_col_len  
  55.  10    from user_tab_col_statistics s;  
  56.   
  57. TABLE_NAME COLUMN_NAM NUM_DISTINCT LOW_VALUE  HIGH_VALUE    DENSITY  NUM_NULLS SAMPLE_SIZE AVG_COL_LEN  
  58. ---------- ---------- ------------ ---------- ---------- ---------- ---------- ----------- -----------  
  59. AUDIENCE   MONTH_NO             12 C102       C10D       .083333333        120        1080           3   --这里可以看见，NUM_NULLS的值确实为120。  
  60. AUDIENCE   ID                 1200 C102       C20D       .000833333          0        1200           4  
[sql]  view plain  copy
 
 print?
  1. SQL> select count(*) from AUDIENCE where month_no=12;  
  2.   
  3. 执行计划  
  4. ----------------------------------------------------------  
  5. Plan hash value: 3337892515  
  6.   
  7. -------------------------------------------------------------------------------  
  8. | Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |  
  9. -------------------------------------------------------------------------------  
  10. |   0 | SELECT STATEMENT   |          |     1 |     3 |     3   (0)| 00:00:01 |  
  11. |   1 |  SORT AGGREGATE    |          |     1 |     3 |            |          |  
  12. |*  2 |   TABLE ACCESS FULL| AUDIENCE |    90 |   270 |     3   (0)| 00:00:01 |  
  13. -------------------------------------------------------------------------------  
  14.   
  15. Predicate Information (identified by operation id):  
  16. ---------------------------------------------------  
  17.   
  18.    2 - filter("MONTH_NO"=12)  

调整后的选择率：DENSITY * ((NUM_ROWS-NUM_NULLS)/NUM_ROWS) = 1 / 12 * ((1200 - 120) / 1200) = 0.075。

返回的记录数（ROWS）：调整后的选择率 * NUM_ROWS = 0.075 * 1200 = 90行。

或者我们可以换一种方法思考，通过前面可以很容易的知道12分月有100人生日（其中这里就包含了不记得生日的人）。然后1200人中有10%的人不记得自己的生日，也就是120，那么12月份不记得自己生日的人就平摊到10个人，100-10=90。

3、现在假如我们想知道在6、7、8月份生日的人有多少呐？

[sql]  view plain  copy
 
 print?
  1. SQL> select count(*) from AUDIENCE where month_no in(6,7,8);  
  2.   
  3. 执行计划  
  4. ----------------------------------------------------------  
  5. Plan hash value: 3337892515  
  6.   
  7. -------------------------------------------------------------------------------  
  8. | Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |  
  9. -------------------------------------------------------------------------------  
  10. |   0 | SELECT STATEMENT   |          |     1 |     3 |     3   (0)| 00:00:01 |  
  11. |   1 |  SORT AGGREGATE    |          |     1 |     3 |            |          |  
  12. |*  2 |   TABLE ACCESS FULL| AUDIENCE |   270 |   810 |     3   (0)| 00:00:01 |  
  13. -------------------------------------------------------------------------------  
  14.   
  15. Predicate Information (identified by operation id):  
  16. ---------------------------------------------------  
  17.   
  18.    2 - filter("MONTH_NO"=6 OR "MONTH_NO"=7 OR "MONTH_NO"=8)  

6、7、8月份的选择率：6月份选择率 + 7月份选择率 + 8月份选择率 = 0.075 * 3 = 0.225

返回的记录数（ROWS）：6、7、8月份的选择率 * NUM_ROWS = 0.225 * 1200 = 270行。

4、下面来一个更复杂一点的，我们想知道不在6、7、8月份生日的人有多少呐？

[sql]  view plain  copy
 
 print?
  1. SQL> select count(*) from AUDIENCE where month_no not in(6,7,8);  
  2.   
  3. 执行计划  
  4. ----------------------------------------------------------  
  5. Plan hash value: 3337892515  
  6.   
  7. -------------------------------------------------------------------------------  
  8. | Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |  
  9. -------------------------------------------------------------------------------  
  10. |   0 | SELECT STATEMENT   |          |     1 |     3 |     3   (0)| 00:00:01 |  
  11. |   1 |  SORT AGGREGATE    |          |     1 |     3 |            |          |  
  12. |*  2 |   TABLE ACCESS FULL| AUDIENCE |   674 |  2022 |     3   (0)| 00:00:01 |  
  13. -------------------------------------------------------------------------------  
  14.   
  15. Predicate Information (identified by operation id):  
  16. ---------------------------------------------------  
  17.   
  18.    2 - filter("MONTH_NO"<>6 AND "MONTH_NO"<>7 AND "MONTH_NO"<>8)  

选择率：1 - 6、7、8月份的选择率 = 1 - 0.075 * 3

返回记录数：(1-0.075*3)*1200 = 930。

month_no in{specific list} 的基数 + month_no not in{specific list} 的基数 = NUM_ROWS，这里计算出来是相等的，但是在数据库中看见的却不想等，需要注意！

 

5、现在我们求8月份以后出生的人，不包含8月份。

[sql]  view plain  copy
 
 print?
  1. SQL> select count(*) from AUDIENCE where month_no>8;  
  2.   
  3. 执行计划  
  4. ----------------------------------------------------------  
  5. Plan hash value: 3337892515  
  6.   
  7. -------------------------------------------------------------------------------  
  8. | Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |  
  9. -------------------------------------------------------------------------------  
  10. |   0 | SELECT STATEMENT   |          |     1 |     3 |     3   (0)| 00:00:01 |  
  11. |   1 |  SORT AGGREGATE    |          |     1 |     3 |            |          |  
  12. |*  2 |   TABLE ACCESS FULL| AUDIENCE |   393 |  1179 |     3   (0)| 00:00:01 |  
  13. -------------------------------------------------------------------------------  
  14.   
  15. Predicate Information (identified by operation id):  
  16. ---------------------------------------------------  
  17.   
  18.    2 - filter("MONTH_NO">8)  

选择率：((HIGH_VALUE - LIMIT) / (HIGH_VALUE - LOW_VALUE)) * ((NUM_ROWS - NUM_NULLS) / NUM_ROWS)

返回的记录数：选择率 * NUM_ROWS = ((HIGH_VALUE - LIMIT) / (HIGH_VALUE - LOW_VALUE)) * ((NUM_ROWS - NUM_NULLS) / NUM_ROWS) * NUM_ROWS = round(((12-8)/(12-1))*((1200-120)/1200)*1200) = 393。


本文转自 张冲andy 博客园博客，原文链接：http://www.cnblogs.com/andy6/p/5765187.html   ，如需转载请自行联系原作者

文章标签：
SQL
关系型数据库
Oracle
关键词：
Oracle数据库一键上云执行计划
Oracle数据库一键上云计算
Oracle数据库一键上云单表
技术小美
目录
相关文章
ZPY0821
|
8月前
|
SQL Oracle 关系型数据库
Oracle查询优化-计算字符在字符串中出现的次数
【2月更文挑战第3天】【2月更文挑战第7篇】只接上SQL
ZPY0821
151 0
Zh12138
|
8月前
|
SQL Oracle 关系型数据库
Oracle之日期计算相关函数
Oracle之日期计算相关函数
Zh12138
89 0
何雨晨
|
3月前
|
SQL Oracle 关系型数据库
Oracle SQL：了解执行计划和性能调优
Oracle SQL：了解执行计划和性能调优
何雨晨
86 1
1941623231718325
|
8月前
|
监控 Oracle 关系型数据库
Oracle 12c的Adaptive执行计划：数据的“聪明导航员”
【4月更文挑战第19天】Oracle 12c的Adaptive执行计划是数据库查询的智能优化工具，能根据实际运行情况动态调整执行策略。它像一个聪明的导航系统，不仅生成初始执行计划，还能实时监控并适应统计信息和资源变化，例如自动切换索引或调整并行度。此外，它支持自适应连接和统计信息收集，提升处理复杂查询和变化数据环境的能力。数据管理员应充分利用这一特性来优化查询性能和用户体验。
1941623231718325
95 1
Zh12138
|
8月前
|
SQL Oracle 关系型数据库
Oracle之有哪些日期计算函数？
Oracle之有哪些日期计算函数？
Zh12138
155 0
前方一片光明
|
SQL Oracle 关系型数据库
ORACLE——一条SQL计算同比、环比
ORACLE——一条SQL计算同比、环比
前方一片光明
914 0
夏木~
|
SQL Oracle 关系型数据库
Oracle中计算除法 ，解决除数为零报错问题
Oracle中计算除法 ，解决除数为零报错问题
夏木~
360 0
myysophia
|
监控 Oracle 关系型数据库
Oracle 计算时间差
Oracle 计算时间差
myysophia
98 0
姚远OracleACE
|
SQL 缓存 监控
Oracle中如何生成有用的SQL 执行计划（译）
漫画戴夫·艾伦（Dave Allen）曾经讲过一个古老的笑话，一个旅行者问路人去某个城镇的路，路人只是说：“如果我是你，我就不会从这里开始。”
姚远OracleACE
174 0
姚远OracleACE
|
SQL 存储 缓存
获取和解读Oracle中SQL的执行计划（译文）
生成和显示 SQL 语句的执行计划是大多数 DBA、SQL 开发人员和性能专家的常见任务，因为它提供了 SQL 语句执行性能相关的信息。执行计划显示执行 SQL 语句的详细步骤，这些步骤表示为一组使用和生成行的数据库运算符。运算符的顺序和实现由查询优化器根据查询转换和物理优化技术来决定。
姚远OracleACE
334 0

热门文章

最新文章

  • 1
    数据库数据恢复—ORACLE常见故障的数据恢复方案
  • 2
    Oracle数据恢复—Oracle数据库文件有坏快损坏的数据恢复案例
  • 3
    Oracle 从 DMP 文件中恢复指定表的步骤
  • 4
    从ORACLE源进行批量数据迁移到GBase8a参考示例
  • 5
    服务器数据恢复—华为S5300存储Oracle数据库恢复案例
  • 6
    ORACLE 9i归档之ARCn: Media recovery disabled
  • 7
    企业的建库脚本（oracle10g）
  • 8
    Oracle提供两种使用嵌套表
  • 9
    oracle 分页sql 分段查数据和分段求和 sql语句 和java代码
  • 10
    ORACLE 11g 一次ORA-27125 Linux-x86_64 Error
  • 1
    解决Toad for Oracle显示乱中文码问题
    108
  • 2
    【服务器】python通过JDBC连接到位于Linux远程服务器上的Oracle数据库
    112
  • 3
    零基础入门 Oracle数据库：轻松上手
    226
  • 4
    java操作多数据源将oracle数据同步达梦数据库
    318
  • 5
    SuSE linux server 11通过SAP来安装oracle11g
    123
  • 6
    oracle11g SAP测试机归档日志暴增排查（二）
    355
  • 7
    oracle11g SAP测试机归档日志暴增排查（一）
    89
  • 8
    修改oracle11g的awr快照参数
    57
  • 9
    发现oracle10gSYSAUX空间没有了进行处理
    52
  • 10
    在centos7虚拟机上安装docker oracle11g
    304

    • 相关课程

    更多
  • 如何在 PolarDB-X 中优化慢 SQL
  • IMCI列存索引介绍
  • 数据库及SQL/MySQL基础

    • 相关电子书

    更多
  • PostgresChina2018_樊文凯_ORACLE数据库和应用异构迁移最佳实践
  • PostgresChina2018_王帅_从Oracle到PostgreSQL的数据迁移
  • Oracle云上最佳实践

    • 相关实验场景

    更多
  • 使用SQL语句管理索引
  • SQL进阶之约束、索引

    • 推荐镜像

    更多
  • oracle
  • mysql
  • postgresql
    • 下一篇
    阿里云无影云电脑免费试用，最长可试用3个月