千家信息网

请输入关键字词

热门搜索排行

最新搜索排行

导航： 首页 > 数据库 >

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

发表于：2025-01-22 作者：千家信息网编辑
千家信息网最后更新 2025年01月22日，1、直方图介绍你可以为列统计直方图。这些直方图为列数据的分布情况提供准确的估算。当列数据倾斜时，直方图提供更为优化的选择性估算,为数据分布不均匀的情况提供最优的执行计划。 Oracle Databas
千家信息网最后更新 2025年01月22日微课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安全错误 数据库的锁怎样保障安全 数据库插入一条空白命令怎么写 云空间服务器在哪里 网络安全宣传周新闻标题 战地5服务器会关闭吗 服务器可以主动发送数据吗 网络安全应考哪些证书 重庆秀创软件开发工作室 洋浦网络安全会议 软件开发用户需求规格说明书 服务器监控管理说明书 mysql数据库文件导入 丰台区网络营销软件开发质量服务 服务器操作系统是网络的核心吗 哈利波特各区不同服务器互通吗 cpas使用的数据库 网络技术特殊人才 gbase数据库官网 虚拟网络安全心得 数据库控制台增加数据 UC网络安全 以经济学的角度分析网络安全 软件工程属于计算机网络技术吗 计算机网络技术与实训试卷 苹果手机wifi提示网络安全 数据库载备 内部服务器出错是啥意思 国家电网网络安全实验室 软件开发必须要英语四级吗 网络安全的意义和作用 政府网络安全认证

相关文章

0