Oracle直方图详解(转)
当系统中的某些表存在高度不均匀的数据分布时，使用柱状图能够产生更好的选择性评估，从而产生更加优化的执行计划。柱状图提供一种有效和简捷的方法来呈现数据的分布情况。
下面通过一个具体的例子解释柱状图的使用。
SQL> create table tab (a number, b number);
Table created.
SQL> begin
for i in 1..10000 loop
insert into tab values (i, i);
end loop;
commit;
end;
/
PL/SQL procedure successfully completed.
SQL> update tab set b=5 where b between 6 and 9995;
9990 rows updated.
SQL> commit;
Commit complete.
这样在tab表中，b列有10个不同的值，其中等于的值有9991个。在创建索引之前，无论是查询b=3或者是b=5，都只能是走全表扫描（FULL TABLE SCAN），因为没有别的可以使用的访问路径。
下面我们在b列上创建一个索引。
SQL> create index ix_tab_b on tab(b);
Index created.
SQL> select index_name, table_name, column_name, column_position, column_length
from user_ind_columns
where table_name='TAB';
INDEX_NAME TABLE_NAME COLUMN_NAME COLUMN_POSITION COLUMN_LENGTH
------------------------------ ------------------------------ -------------------- --------------- -------------
IX_TAB_B TAB B 1 22
现在我们分别来看看下面的查询。
SQL> select * from tab where b=3;
1 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 439197569
------------------------------------------------
| Id | Operation | Name |
------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| TAB |
|* 2 | INDEX RANGE SCAN | IX_TAB_B |
------------------------------------------------
Statistics
----------------------------------------------------------
178 recursive calls
0 db block gets
30 consistent gets
5 physical reads
116 redo size
462 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select * from tab where b=5;
9991 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 439197569
------------------------------------------------
| Id | Operation | Name |
------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| TAB |
|* 2 | INDEX RANGE SCAN | IX_TAB_B |
------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1370 consistent gets
16 physical reads
0 redo size
206729 bytes sent via SQL*Net to client
7711 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9991 rows processed
可以看出这里走的都是基于RBO的INDEX RANGE SCAN。
接下来，我们使用计算统计对表进行分析。
SQL> analyze table tab compute statistics;
Table analyzed.
SQL> select num_rows, blocks, empty_blocks, avg_space, chain_cnt, avg_row_len
2 from dba_tables
3 where table_name = 'TAB';
NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN
---------- ---------- ------------ ---------- ---------- -----------
10000 20 4 2080 0 10
SQL> select num_distinct, low_value, high_value, density, num_buckets, last_analyzed, sample_size
from dba_tab_columns
where table_name = 'TAB';
NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY NUM_BUCKETS LAST_ANAL SAMPLE_SIZE
------------ -------------------- -------------------- ---------- ----------- --------- -----------
10000 C102 C302 .0001 1 21-DEC-08 10000
10 C102 C302 .1 1 21-DEC-08 10000
SQL> select table_name, column_name, endpoint_number, endpoint_value
from dba_tab_histograms
where table_name = 'TAB';
TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
------------------------------ -------------------- --------------- --------------
TAB A 0 1
TAB A 1 10000
TAB B 0 1
TAB B 1 10000
再来执行上面的两个查询，观察其执行计划，发现两个查询仍然走的都是INDEX RANGE SCAN，只不过这时的执行计划是基于CBO的。
现在我们创建tab表b列的柱状图统计信息，使得优化器能够知道该列每个值的具体分布情况。
SQL> analyze table tab compute statistics for columns b size 10;
Table analyzed.
SQL> select table_name, column_name, endpoint_number, endpoint_value
from dba_histograms
where table_name = 'TAB';
TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
------------------------------ -------------------- --------------- --------------
TAB B 1 1
TAB B 2 2
TAB B 3 3
TAB B 4 4
TAB B 9995 5
TAB B 9996 9996
TAB B 9997 9997
TAB B 9998 9998
TAB B 9999 9999
TAB B 10000 10000
直方图中的ENDPOINT_VALUE表示列值，ENDPOINT_NUMBER表示累积的行数。比如ENDPOINT_VALUE=2，ENDPOINT_NUMBER=2，因为ENDPOINT_NUMBER是个累积值，实际上2的ENDPOINT_NUMBER应该是2减去上一个值的ENDPOINT_NUMBER，也即是2-1=1。同理，5的ENDPOINT_NUMBER=9995-4=9991。
SQL> select * from tab where b=3;
1 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 439197569
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TAB | 1 | 6 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IX_TAB_B | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
178 recursive calls
0 db block gets
28 consistent gets
0 physical reads
0 redo size
462 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select * from tab where b=5;
9991 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1995730731
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9991 | 59946 | 6 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TAB | 9991 | 59946 | 6 (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
689 consistent gets
0 physical reads
0 redo size
174757 bytes sent via SQL*Net to client
7711 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9991 rows processed
这时可以看出，不同值的分布导致了Oracle优化器选择了不同执行计划。对于b=5的查询来说，全表扫描的一致性读比之前的索引范围扫描要降低很多。可以看出此时的全表扫描比之索引范围扫描更加的合理，优化器正是根据直方图的统计信息做出的正确的判断。
上述的例子描述了一种理想的状况，因为我们为每一个不同的值创建了bucket。在实际的生产系统中，一张表可能包含很多的唯一值，我们不可能为每一个唯一值创建bucket，这样开销将是巨大的。
下面的例子描述了唯一值大于buckets的情况。
SQL> analyze table tab compute statistics for columns b size 8;
Table analyzed.
SQL> select table_name, column_name, endpoint_number, endpoint_value
from dba_histograms
where table_name = 'TAB';
TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
------------------------------ -------------------- --------------- --------------
TAB B 0 1
TAB B 7 5
TAB B 8 10000
ENDPOINT_NUMBER是实际的bucket编号，ENDPOINT_VALUE是根据列值决定的该bucket的endpoint值。上面的输出中，bucket 0存放着b列的低值，为了节省空间没有显示出1-6号的bucket。但是我们能够理解，bucket[1-7]里存放着的endpoint=5，而bucket8里存放endpoint=10000。因此，实际上bucket0里包含了1-5之间的所有值，而bucket8里包含了5-10000之间的所有值，在本例中也就是9996-10000这5个数值。
综上所述，假如数据是均衡的，没有必要使用直方图。如果使用唯一值数量来创建直方图，Oracle为每个值创建一个bucket；但是假如实际的生产系统中，不能够为每一个唯一值分配一个bucket时，Oracle采用合适的算法尽可能将值平均分布到每个bucket中，剩余的值放入到最后的bucket。
下面通过一个具体的例子解释柱状图的使用。
SQL> create table tab (a number, b number);
Table created.
SQL> begin
for i in 1..10000 loop
insert into tab values (i, i);
end loop;
commit;
end;
/
PL/SQL procedure successfully completed.
SQL> update tab set b=5 where b between 6 and 9995;
9990 rows updated.
SQL> commit;
Commit complete.
这样在tab表中，b列有10个不同的值，其中等于的值有9991个。在创建索引之前，无论是查询b=3或者是b=5，都只能是走全表扫描（FULL TABLE SCAN），因为没有别的可以使用的访问路径。
下面我们在b列上创建一个索引。
SQL> create index ix_tab_b on tab(b);
Index created.
SQL> select index_name, table_name, column_name, column_position, column_length
from user_ind_columns
where table_name='TAB';
INDEX_NAME TABLE_NAME COLUMN_NAME COLUMN_POSITION COLUMN_LENGTH
------------------------------ ------------------------------ -------------------- --------------- -------------
IX_TAB_B TAB B 1 22
现在我们分别来看看下面的查询。
SQL> select * from tab where b=3;
1 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 439197569
------------------------------------------------
| Id | Operation | Name |
------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| TAB |
|* 2 | INDEX RANGE SCAN | IX_TAB_B |
------------------------------------------------
Statistics
----------------------------------------------------------
178 recursive calls
0 db block gets
30 consistent gets
5 physical reads
116 redo size
462 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select * from tab where b=5;
9991 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 439197569
------------------------------------------------
| Id | Operation | Name |
------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| TAB |
|* 2 | INDEX RANGE SCAN | IX_TAB_B |
------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1370 consistent gets
16 physical reads
0 redo size
206729 bytes sent via SQL*Net to client
7711 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9991 rows processed
可以看出这里走的都是基于RBO的INDEX RANGE SCAN。
接下来，我们使用计算统计对表进行分析。
SQL> analyze table tab compute statistics;
Table analyzed.
SQL> select num_rows, blocks, empty_blocks, avg_space, chain_cnt, avg_row_len
2 from dba_tables
3 where table_name = 'TAB';
NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN
---------- ---------- ------------ ---------- ---------- -----------
10000 20 4 2080 0 10
SQL> select num_distinct, low_value, high_value, density, num_buckets, last_analyzed, sample_size
from dba_tab_columns
where table_name = 'TAB';
NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY NUM_BUCKETS LAST_ANAL SAMPLE_SIZE
------------ -------------------- -------------------- ---------- ----------- --------- -----------
10000 C102 C302 .0001 1 21-DEC-08 10000
10 C102 C302 .1 1 21-DEC-08 10000
SQL> select table_name, column_name, endpoint_number, endpoint_value
from dba_tab_histograms
where table_name = 'TAB';
TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
------------------------------ -------------------- --------------- --------------
TAB A 0 1
TAB A 1 10000
TAB B 0 1
TAB B 1 10000
再来执行上面的两个查询，观察其执行计划，发现两个查询仍然走的都是INDEX RANGE SCAN，只不过这时的执行计划是基于CBO的。
现在我们创建tab表b列的柱状图统计信息，使得优化器能够知道该列每个值的具体分布情况。
SQL> analyze table tab compute statistics for columns b size 10;
Table analyzed.
SQL> select table_name, column_name, endpoint_number, endpoint_value
from dba_histograms
where table_name = 'TAB';
TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
------------------------------ -------------------- --------------- --------------
TAB B 1 1
TAB B 2 2
TAB B 3 3
TAB B 4 4
TAB B 9995 5
TAB B 9996 9996
TAB B 9997 9997
TAB B 9998 9998
TAB B 9999 9999
TAB B 10000 10000
直方图中的ENDPOINT_VALUE表示列值，ENDPOINT_NUMBER表示累积的行数。比如ENDPOINT_VALUE=2，ENDPOINT_NUMBER=2，因为ENDPOINT_NUMBER是个累积值，实际上2的ENDPOINT_NUMBER应该是2减去上一个值的ENDPOINT_NUMBER，也即是2-1=1。同理，5的ENDPOINT_NUMBER=9995-4=9991。
SQL> select * from tab where b=3;
1 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 439197569
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TAB | 1 | 6 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IX_TAB_B | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
178 recursive calls
0 db block gets
28 consistent gets
0 physical reads
0 redo size
462 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select * from tab where b=5;
9991 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1995730731
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9991 | 59946 | 6 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TAB | 9991 | 59946 | 6 (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
689 consistent gets
0 physical reads
0 redo size
174757 bytes sent via SQL*Net to client
7711 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9991 rows processed
这时可以看出，不同值的分布导致了Oracle优化器选择了不同执行计划。对于b=5的查询来说，全表扫描的一致性读比之前的索引范围扫描要降低很多。可以看出此时的全表扫描比之索引范围扫描更加的合理，优化器正是根据直方图的统计信息做出的正确的判断。
上述的例子描述了一种理想的状况，因为我们为每一个不同的值创建了bucket。在实际的生产系统中，一张表可能包含很多的唯一值，我们不可能为每一个唯一值创建bucket，这样开销将是巨大的。
下面的例子描述了唯一值大于buckets的情况。
SQL> analyze table tab compute statistics for columns b size 8;
Table analyzed.
SQL> select table_name, column_name, endpoint_number, endpoint_value
from dba_histograms
where table_name = 'TAB';
TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
------------------------------ -------------------- --------------- --------------
TAB B 0 1
TAB B 7 5
TAB B 8 10000
ENDPOINT_NUMBER是实际的bucket编号，ENDPOINT_VALUE是根据列值决定的该bucket的endpoint值。上面的输出中，bucket 0存放着b列的低值，为了节省空间没有显示出1-6号的bucket。但是我们能够理解，bucket[1-7]里存放着的endpoint=5，而bucket8里存放endpoint=10000。因此，实际上bucket0里包含了1-5之间的所有值，而bucket8里包含了5-10000之间的所有值，在本例中也就是9996-10000这5个数值。
综上所述，假如数据是均衡的，没有必要使用直方图。如果使用唯一值数量来创建直方图，Oracle为每个值创建一个bucket；但是假如实际的生产系统中，不能够为每一个唯一值分配一个bucket时，Oracle采用合适的算法尽可能将值平均分布到每个bucket中，剩余的值放入到最后的bucket。
