微課sql最佳化(7)、統計資訊收集(5)-關於直方圖

orastar發表於2020-03-03
SQL直方圖

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

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31442014/viewspace-2678257/，如需轉載，請註明出處，否則將追究法律責任。

相關文章