Shulou(Shulou.com)06/01 Report--

1. Histogram introduction you can count histograms for columns. These histograms provide an accurate estimate of the distribution of column data. When the column data is skewed, the histogram provides a more optimized selectivity estimation and an optimal execution plan for the case of uneven data distribution. Oracle Database provides two categories of column statistics histograms: the Height-Balanced HistogramsFrequency Histograms database stores histogram information * TAB_COL_STATISTICS views (users and DBA). Column values range: HEIGHTBALANCED, FREQUENCY, or NONE. 2. Height-Balanced Histograms in height-balanced histogram, column values are divided into buckets so that each bucket contains roughly the same number of rows. The histogram shows the location of the endpoint within the range of values. Consider a column with a my_ value between 1 and 100, as well as a histogram of 10 buckets. If the data my_col is evenly distributed, the histogram looks similar to figure 13-1, where the number is the endpoint value. For example, the seventh bucket has rows with values between 60 and 70. Figure 13-1 highly balanced histogram with uniform distribution

The number of rows in each bucket is 10% of the total. In this uniformly distributed example, 40% of the rows have values between 60 and 100. If the data is unevenly distributed, the histogram may look like figure 13-2. In this case, the column of most rows has a value of 5. Only 10% of rows have values between 60 and 100. Figure 13-2 A highly balanced histogram with non-uniform distribution you can view the highly balanced histogram using the USER_TAB_HISTOGRAMS table, as shown in example 13-1. BEGINDBMS_STATS. GATHER_table_STATS (OWNNAME = > 'OE', TABNAME = >' INVENTORIES', METHOD_OPT = > 'FOR COLUMNS SIZE 10 quantity_on_hand'); END; /

SELECT COLUMN_NAME, NUM_DISTINCT, NUM_BUCKETS, HISTOGRAMFROM USER_TAB_COL_STATISTICSWHERE TABLE_NAME = 'INVENTORIES' AND COLUMN_NAME =' QUANTITY_ON_HAND'

COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM---QUANTITY_ON_HAND 237 10 HEIGHT BALANCED

SELECT ENDPOINT_NUMBER, ENDPOINT_VALUEFROM USER_TAB_HISTOGRAMSWHERE TABLE_NAME = 'INVENTORIES' AND COLUMN_NAME =' QUANTITY_ON_HAND'ORDER BY ENDPOINT_NUMBER

ENDPOINT_NUMBER ENDPOINT_VALUE--001 272 423 574 745986 1237 1498 1759 20210 in example 13-1 query output, one row (1-10) corresponds to each bucket in the histogram. The Oracle database adds a special bucket 0 to the histogram because the value in the first bucket (27) is not the minimum value of the quantity_on_hand column. The minimum value of the 0th barrel is 0 quantity_on_hand. 3. Frequency histogram in frequency histogram, each value of the column corresponds to a single bucket of the histogram. Each bucket contains the number of occurrences of this single value. For example, suppose 36 rows contain a column value of 1 warehouse_id. The endpoint value 1 has the endpoint number 36. The database automatically creates a frequency histogram instead of a highly balanced histogram under the following conditions: the number of different values is less than or equal to the specified number of histogram buckets (up to 254). Each column value is repeated once. You can use the USER_TAB_HISTOGRAMS view to view the frequency histogram, as shown in example 13-2. BEGINDBMS_STATS. GATHER_TABLE_STATS (OWNNAME = > 'OE', TABNAME = >' INVENTORIES', METHOD_OPT = > 'FOR COLUMNS SIZE 20 warehouse_id'); END; /

SELECT COLUMN_NAME, NUM_DISTINCT, NUM_BUCKETS, HISTOGRAMFROM USER_TAB_COL_STATISTICSWHERE TABLE_NAME = 'INVENTORIES' AND COLUMN_NAME =' WAREHOUSE_ID'

COLUMN_NAME NUM_DISTINCT NUM_BUCKETS HISTOGRAM---WAREHOUSE_ID 9 9 FREQUENCY

SELECT ENDPOINT_NUMBER, ENDPOINT_VALUEFROM USER_TAB_HISTOGRAMSWHERE TABLE_NAME = 'INVENTORIES' AND COLUMN_NAME =' WAREHOUSE_ID'ORDER BY ENDPOINT_NUMBER

ENDPOINT_NUMBER ENDPOINT_VALUE--36 1213 2261 3370 4484 5692 6798 7984 81112 9 in example 13-2, the first barrel is warehouse_id1. This value is displayed 36 times in the table, as confirmed by the following query: oe@PROD > SELECT COUNT (*) FROM inventories WHERE warehouse_id = 1; COUNT (*)-365,exercise 4, histogram optimization exercise statistics of the number of closed users, please optimize the following statements select count (1) from ht.c_cons where status='close';SQL > select status, count (1) from ht. C_cons group by status

STATUS COUNT (1)-close 19open 9519creating 462

SQL > create index ht. Idx_c_cons_status on ht. C_cons (status); SQL > col owner for a10col table_name for a20col column_name for a20col data_type for a30col histogram for a20select owner, table_name, column_name, data_type, column_id, num_distinct, histogram, NUM_NULLS, LAST_ANALYZED fromdba_tab_columns where table_name = 'ClearCons' and owner = 'HT'order by column_id; SQL > SQL > SQL > SQL > SQL > 2 34

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

SQL > exec DBMS_STATS. GATHER_TABLE_STATS (ownname = > 'HT', tabname = >' ClearCons', estimate_percent = > 30, method_opt = > 'for columns size 50 status', no_invalidate = > FALSE, degree = > 4, cascade = > TRUE) PL / SQL procedure successfully completed .SQL > col owner for a10SQL > col table_name for a20col column_name for a20col data_type for a30col histogram for a20select owner, table_name, column_name, data_type, column_id, num_distinct, histogram, NUM_NULLS, LAST_ANALYZED fromdba_tab_columns where table_name = 'ClearCONS' and owner =' HT'order by column_id; SQL > SQL > SQL > SQL > 2 34

OWNER TABLE_NAME COLUMN_NAME DATA_TYPE COLUMN_ID NUM_DISTINCT HISTOGRAM NUM_NULLS LAST_ANALYZED -HT C_CONS CONS_NO NUMBER 1 10000 NONE 0 20-AUG-17HT C_CONS CONS_NAME VARCHAR2 2 5057 NONE 0 20-AUG-17HT C_CONS ORG_NAME VARCHAR2 3 12 NONE 0 20-AUG -17HT C_CONS BUILD_DATE DATE 4 10000 NONE 0 20-AUG-17HT C_CONS STATUS VARCHAR2 53 FREQUENCY 0 20-AUG-17SQL > select count (1) from ht. C_cons where status = 'open'; Execution Plan---Plan hash value: 2016425671

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

Predicate Information (identified by operation id):-2-filter ("STATUS" = 'open') Statistics -1 recursive calls0 db block gets28 consistent gets0 physical reads0 redo size527 bytes sent via SQL * Net to client523 bytes received via SQL * Net from client2 SQL * Net roundtrips to / from client0 sorts (memory) 0 sorts (disk) 1 rows processedSQL > SQL > select count (1) from ht. C_cons where status = 'close'; Execution Plan

Plan hash value: 2292286995

-| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- | 0 | SELECT STATEMENT | | 1 | 6 | 1 (0) | 00: 00: 01 | | 1 | SORT AGGREGATE | | 1 | 6 | | * 2 | INDEX RANGE SCAN | IDX_C_CONS | _ STATUS | 24 | 144 | 1 (0) | 00: 00: 01 |-

Predicate Information (identified by operation id):-

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

Statistics---1 recursive calls0 db block gets2 consistent gets0 physical reads0 redo size526 bytes sent via SQL * Net to client523 bytes received via SQL * Net from client2 SQL * Net roundtrips to / from client0 sorts (memory) 0 sorts (disk) 1 rows processed

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.