千家信息网

请输入关键字词

热门搜索排行

最新搜索排行

导航： 首页 > 数据库 >

微课sql优化(7)、统计信息收集(5)-关于直方图

发表于：2025-11-11 作者：千家信息网编辑
千家信息网最后更新 2025年11月11日，1、直方图介绍你可以为列统计直方图。这些直方图为列数据的分布情况提供准确的估算。当列数据倾斜时，直方图提供更为优化的选择性估算,为数据分布不均匀的情况提供最优的执行计划。 Oracle Databas
千家信息网最后更新 2025年11月11日微课sql优化(7)、统计信息收集(5)-关于直方图

1、直方图介绍

你可以为列统计直方图。这些直方图为列数据的分布情况提供准确的估算。当列数据倾斜时，直方图提供更为优化的选择性估算,为数据分布不均匀的情况提供最优的执行计划。 Oracle Database为提供2种类别的列统计信息直方图：
  • Height-Balanced Histograms
  • Frequency Histograms
数据库存储直方图信息*TAB_COL_STATISTICS视图(用户和DBA)。列值范围：HEIGHTBALANCED, FREQUENCY, or NONE。 2、Height-Balanced Histograms 在height-balanced histogram中，列值被划分为桶，使得每个桶包含大致相同数量的行。直方图显示端点在值范围内的位置。 考虑一个my_col值为1到100之间的列，以及一个10个桶的直方图。如果数据my_col均匀分布，则直方图看起来与图13-1类似，其中数字是端点值。例如，第七个桶具有值在60到70之间的行。 图13-1具有均匀分布的高度平衡直方图
每个桶中的行数为总行数的10％。在这个均匀分布的例子中，40％的行的值在60到100之间。 如果数据不均匀分布，则直方图可能如图13-2所示。在这种情况下，大多数行的列的值为5。只有10％的行的值在60到100之间。 图13-2具有非均匀分布的高度平衡直方图 您可以使用USER_TAB_HISTOGRAMS表格查看高度平衡的直方图，如示例13-1所示。
  1. BEGIN
  2. DBMS_STATS . GATHER_table_STATS (
  3. OWNNAME = > 'OE' ,
  4. TABNAME = > 'INVENTORIES' ,
  5. METHOD_OPT = > 'FOR COLUMNS SIZE 10 quantity_on_hand' ) ;
  6. END ;
  7. /

  9. SELECT COLUMN_NAME , NUM_DISTINCT , NUM_BUCKETS , HISTOGRAM
  10. FROM USER_TAB_COL_STATISTICS
  11. WHERE TABLE_NAME = 'INVENTORIES' AND COLUMN_NAME = 'QUANTITY_ON_HAND' ;

  13. COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM
  14. ------------------------------ ------------ ----------- ---------------
  15. QUANTITY_ON_HAND 237 10 HEIGHT BALANCED

  17. SELECT ENDPOINT_NUMBER , ENDPOINT_VALUE
  18. FROM USER_TAB_HISTOGRAMS
  19. WHERE TABLE_NAME = 'INVENTORIES' AND COLUMN_NAME = 'QUANTITY_ON_HAND'
  20. ORDER BY ENDPOINT_NUMBER ;

  22. ENDPOINT_NUMBER ENDPOINT_VALUE
  23. --------------- --------------
  24. 0 0
  25. 1 27
  26. 2 42
  27. 3 57
  28. 4 74
  29. 5 98
  30. 6 123
  31. 7 149
  32. 8 175
  33. 9 202
  34. 10 353
在示例13-1查询输出中，一行（1-10）对应于直方图中的每个桶。Oracle数据库向该直方图添加了特殊的第0个数据桶，因为第1个数据桶（27）中的值不是quantity_on_hand列的最小值。第0个桶的最小值为0 quantity_on_hand。

3、 frequency histogra m

在 frequency histogram中，列的每个值对应于直方图的单个桶。每个桶包含此单个值的出现次数。例如，假设36行包含列的值1 warehouse_id。端点值1具有端点号36。 数据库在以下条件下自动创建频率直方图，而不是高度平衡的直方图：
  • 不同值的数量小于或等于指定的直方图桶数（最多254个）。
  • 每个列值重复一次。
您可以使用USER_TAB_HISTOGRAMS视图查看频率直方图，如示例13-2所示。
  1. BEGIN
  2. DBMS_STATS . GATHER_TABLE_STATS (
  3. OWNNAME = > 'OE' ,
  4. TABNAME = > 'INVENTORIES' ,
  5. METHOD_OPT = > 'FOR COLUMNS SIZE 20 warehouse_id' ) ;
  6. END ;
  7. /

  9. SELECT COLUMN_NAME , NUM_DISTINCT , NUM_BUCKETS , HISTOGRAM
  10. FROM USER_TAB_COL_STATISTICS
  11. WHERE TABLE_NAME = 'INVENTORIES' AND COLUMN_NAME = 'WAREHOUSE_ID' ;

  13. COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM
  14. ------------------------------ ------------ ----------- ---------------
  15. WAREHOUSE_ID 9 9 FREQUENCY

  17. SELECT ENDPOINT_NUMBER , ENDPOINT_VALUE
  18. FROM USER_TAB_HISTOGRAMS
  19. WHERE TABLE_NAME = 'INVENTORIES' AND COLUMN_NAME = 'WAREHOUSE_ID'
  20. ORDER BY ENDPOINT_NUMBER ;

  22. ENDPOINT_NUMBER ENDPOINT_VALUE
  23. --------------- --------------
  24. 36 1
  25. 213 2
  26. 261 3
  27. 370 4
  28. 484 5
  29. 692 6
  30. 798 7
  31. 984 8
  32. 1112 9
在例13-2中，第一个桶为warehouse_id1。该值在表中显示36次，如以下查询所证实： oe@PROD> SELECT COUNT(*) FROM inventories WHERE warehouse_id = 1; COUNT(*) ---------- 36

5、 练习4 、直方图优化练习

统计已销户用户数量，请优化以下语句 select count(1) from ht.c_cons where status='close';
  1. SQL > select status , count ( 1 ) from ht . c_cons group by status ;

  3. STATUS COUNT ( 1 )
  4. ------------------------------------------------------------ ----------
  5. close 19
  6. open 9519
  7. creating 462

  9. SQL > create index ht . idx_c_cons_status on ht . c_cons ( status ) ;
  10. SQL > col owner for a10
  11. col table_name for a20
  12. col column_name for a20
  13. col data_type for a30
  14. col histogram for a20
  15. select owner , table_name , column_name , data_type ,
  16. column_id , num_distinct , histogram , NUM_NULLS , LAST_ANALYZED from
  17. dba_tab_columns where table_name = 'C_CONS' and owner = 'HT'
  18. order by column_id ; SQL > SQL > SQL > SQL > SQL > 2 3 4

  20. OWNER TABLE_NAME COLUMN_NAME DATA_TYPE COLUMN_ID NUM_DISTINCT HISTOGRAM NUM_NULLS LAST_ANALYZED
  21. ---------- -------------------- -------------------- ------------------------------ ---------- ------------ -------------------- ---------- ------------------------------
  22. HT C_CONS CONS_NO NUMBER 1 10000 NONE 0 20 - AUG - 17
  23. HT C_CONS CONS_NAME VARCHAR2 2 5057 NONE 0 20 - AUG - 17
  24. HT C_CONS ORG_NAME VARCHAR2 3 12 NONE 0 20 - AUG - 17
  25. HT C_CONS BUILD_DATE DATE 4 10000 NONE 0 20 - AUG - 17
  26. HT C_CONS STATUS VARCHAR2 5 3 NONE 0 20 - AUG - 17

  28. SQL > exec DBMS_STATS . GATHER_TABLE_STATS ( ownname = > 'HT' , tabname = > 'C_CONS' , estimate_percent = > 30 , method_opt = > 'for columns size 50 status' , no_invalidate = > FALSE , degree = > 4 , cascade = > TRUE ) ;
  29. PL / SQL procedure successfully completed .
  30. SQL > col owner for a10
  31. SQL > col table_name for a20
  32. col column_name for a20
  33. col data_type for a30
  34. col histogram for a20
  35. select owner , table_name , column_name , data_type ,
  36. column_id , num_distinct , histogram , NUM_NULLS , LAST_ANALYZED from
  37. dba_tab_columns where table_name = 'C_CONS' and owner = 'HT'
  38. order by column_id ; SQL > SQL > SQL > SQL > 2 3 4

  40. OWNER TABLE_NAME COLUMN_NAME DATA_TYPE COLUMN_ID NUM_DISTINCT HISTOGRAM NUM_NULLS LAST_ANALYZED
  41. ---------- -------------------- -------------------- ------------------------------ ---------- ------------ -------------------- ---------- ------------------------------
  42. HT C_CONS CONS_NO NUMBER 1 10000 NONE 0 20 - AUG - 17
  43. HT C_CONS CONS_NAME VARCHAR2 2 5057 NONE 0 20 - AUG - 17
  44. HT C_CONS ORG_NAME VARCHAR2 3 12 NONE 0 20 - AUG - 17
  45. HT C_CONS BUILD_DATE DATE 4 10000 NONE 0 20 - AUG - 17
  46. HT C_CONS STATUS VARCHAR2 5 3 FREQUENCY 0 20 - AUG - 17
  47. SQL > select count ( 1 ) from ht . c_cons where status = 'open' ;
  48. Execution Plan
  49. ----------------------------------------------------------
  50. Plan hash value : 2016425671

  52. -------------------------------------------------------------------------------------------
  53. | Id | Operation | Name | Rows | Bytes | Cost ( % CPU ) | Time |
  54. -------------------------------------------------------------------------------------------
  55. | 0 | SELECT STATEMENT | | 1 | 6 | 8 ( 0 ) | 00 : 00 : 01 |
  56. | 1 | SORT AGGREGATE | | 1 | 6 | | |
  57. | * 2 | INDEX FAST FULL SCAN | IDX_C_CONS_STATUS | 9639 | 57834 | 8 ( 0 ) | 00 : 00 : 01 |
  58. -------------------------------------------------------------------------------------------

  60. Predicate Information ( identified by operation id ) :
  61. ---------------------------------------------------
  62. 2 - filter ( "STATUS" = 'open' )
  63. Statistics
  64. ----------------------------------------------------------
  65. 1 recursive calls
  66. 0 db block gets
  67. 28 consistent gets
  68. 0 physical reads
  69. 0 redo size
  70. 527 bytes sent via SQL * Net to client
  71. 523 bytes received via SQL * Net from client
  72. 2 SQL * Net roundtrips to / from client
  73. 0 sorts ( memory )
  74. 0 sorts ( disk )
  75. 1 rows processed
  76. SQL >
  77. SQL > select count ( 1 ) from ht . c_cons where status = 'close' ;
  78. Execution Plan
  79. ----------------------------------------------------------

  81. Plan hash value : 2292286995

  83. ---------------------------------------------------------------------------------------
  84. | Id | Operation | Name | Rows | Bytes | Cost ( % CPU ) | Time |
  85. ---------------------------------------------------------------------------------------
  86. | 0 | SELECT STATEMENT | | 1 | 6 | 1 ( 0 ) | 00 : 00 : 01 |
  87. | 1 | SORT AGGREGATE | | 1 | 6 | | |
  88. | * 2 | INDEX RANGE SCAN | IDX_C_CONS_STATUS | 24 | 144 | 1 ( 0 ) | 00 : 00 : 01 |
  89. ---------------------------------------------------------------------------------------

  91. Predicate Information ( identified by operation id ) :
  92. ---------------------------------------------------

  94. 2 - access ( "STATUS" = 'close' )


  97. Statistics
  98. ----------------------------------------------------------
  99. 1 recursive calls
  100. 0 db block gets
  101. 2 consistent gets
  102. 0 physical reads
  103. 0 redo size
  104. 526 bytes sent via SQL * Net to client
  105. 523 bytes received via SQL * Net from client
  106. 2 SQL * Net roundtrips to / from client
  107. 0 sorts ( memory )
  108. 0 sorts ( disk )
  109. 1 rows processed
很赞哦！
直方图 数据 之间 端点 高度 统计 情况 数据库 数量 示例 信息 最小 单个 用户 范围 视图 频率 查询 不同 特殊 数据库的安全要保护哪些东西 数据库安全各自的含义是什么 生产安全数据库录入 数据库的安全性及管理 数据库安全策略包含哪些 海淀数据库安全审计系统 建立农村房屋安全信息数据库 易用的数据库客户端支持安全管理 连接数据库失败ssl安全错误 数据库的锁怎样保障安全 网络安全中心定位 电厂数据服务器 初中校园网络安全活动总结 圳市有信网络技术有限公司 怎么发布服务器 网络安全横向隔离纵向认证 物探研究院做软件开发好不好 网上租服务器贵吗 tcp服务器测评 四川农业大学dns服务器云空间 数据中心网络技术面试题 软件开发的大学课程 最全药品资料数据库2021 web项目连接数据库需要接口吗 windows远程服务器 数据库系统ims 网络安全接入准入系统 c 软件开发有什么用 科室如何构建数据库 列举常见网络安全问题有哪些 服务器备份数据有哪些产品 济南心动网络技术 网易云音乐服务器无法播放 河北软件开发单招 向正科技互联网 ff14数据库 学数据库还是软件编程好 网络安全倡议话 四川套料软件开发商 广东c语言软件开发怎么样

相关文章

0