千家信息网

请输入关键字词

热门搜索排行

最新搜索排行

导航： 首页 > 数据库 >

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

发表于：2026-11-01 作者：千家信息网编辑
千家信息网最后更新 2026年11月01日，1、直方图介绍你可以为列统计直方图。这些直方图为列数据的分布情况提供准确的估算。当列数据倾斜时，直方图提供更为优化的选择性估算,为数据分布不均匀的情况提供最优的执行计划。 Oracle Databas
千家信息网最后更新 2026年11月01日微课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安全错误 数据库的锁怎样保障安全 百单网互联网科技有限公司怎么样 极限挑战互联网科技 融势互联网科技 互联网 视角下的科技创新 亿人帮互联网科技 互联网科技博客 上海卓越科技 互联网 互联网科技公司介绍 互联网科技创新企业排名 江苏南京365互联网科技 来此购互联网科技有限公司 开一家科技互联网公司需要多少钱 中国互联网公司排名锤子科技 上海科技产业互联网平台 江苏苏星互联网科技有限公司 互联网科技实习生岗位 互联网科技融合创新 北京互联网科技类企业分布 唐山双旺合互联网科技产业园 北京品多多互联网科技有限公司 麻工互联网科技 上海喔噻互联网科技 互联网科技大会案例 互联网 科技馆 互联网科技革命什么开始 互联网公司属于科技吗 互联网科技手抄报图片 唐山双旺合互联网科技产业园 互联网金融金融科技 互联网 科技节活动 微视频大赛

相关文章

0