直方图对执行计划的影响

最新推荐文章于 2022-03-30 15:49:24 发布

csdm93728

最新推荐文章于 2022-03-30 15:49:24 发布

阅读量88

点赞数

SQL> create table test_for_col as select rownum a,rownum b from dual connect by level<=20000 ;
Table created.
SQL> update test_for_col set a=20000 where a between 10 and 20000 ;
19991 rows updated.
SQL> commit;
Commit complete.
SQL> create index idx_test on test_for_col(a);
Index created.
SQL> analyze table test_for_col compute statistics;
Table analyzed.
SQL> analyze index idx_test compute statistics ;
Index analyzed.
SQL> set autotrace trace exp ;
SQL> set linesize 150 ;
SQL> select * from test_for_col where a= 1 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 3390667667

--------------------------------------------------------------------------------------------
| Id | Operation                   | Name         | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              | 2000 | 12000 |     8   (0)| 00:00:01 |
|   1 | TABLE ACCESS BY INDEX ROWID| TEST_FOR_COL | 2000 | 12000 |     8   (0)| 00:00:01 |
|* 2 |   INDEX RANGE SCAN          | IDX_TEST     | 2000 |       |     4   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("A"=1)
SQL> select * from test_for_col where a= 20000 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 3390667667

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("A"=20000)

SQL> select a,count(*) from test_for_col group by a order by a ;

         A   COUNT(*)
---------- ----------
         1          1
         2          1
         3          1
         4          1
         5          1
         6          1
         7          1
         8          1
         9          1
     20000      19991

10 rows selected.

可以看出数据极度倾斜，并且在访问的时候给出了错误的执行计划。

现在修改分析命令构造直方图

SQL> analyze table test_for_col compute statistics for all indexed columns ;

Table analyzed.

这句命令的意思是统计这个表所有索引列的信息。并构造直方图

SQL> select num_rows, blocks, empty_blocks, avg_space, chain_cnt, avg_row_len
FROM DBA_TABLES
where wner = 'SYS'
AND TABLE_NAME = UPPER('test_for_col') 2 3 4 ;

NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN
---------- ---------- ------------ ---------- ---------- -----------
20000 39 0 1651 0 10

SQL> select num_distinct, low_value, high_value, density, num_buckets, last_analyzed, sample_size
from dba_tab_columns
where table_name = UPPER('test_for_col') ; 2 3

NUM_DISTINCT LOW_VALUE            HIGH_VALUE       DENSITY NUM_BUCKETS LAST_ANALYZE SAMPLE_SIZE
------------ -------------------- ------------------------ ----------- ------------ -----------
          10 C102                 C303             .000025          10 13-OCT-09          20000
       20000 C102                 C303              .00005           1 13-OCT-09          20000

NUM_DISTINCT   该列不同值的数量
NUM_BUCKETS    柱状图的数量
SAMPLE_SIZE    采样的数量可以使用SAMPLE子句来指定采样的百分比或者行数

重复刚才的实验

SQL> select * from test_for_col where a= 1 ;

Execution Plan
----------------------------------------------------------
Plan hash value: 3390667667

--------------------------------------------------------------------------------------------
| Id | Operation                   | Name         | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |     1 |     6 |     2   (0)| 00:00:01 |
|   1 | TABLE ACCESS BY INDEX ROWID| TEST_FOR_COL |     1 |     6 |     2   (0)| 00:00:01 |
|* 2 |   INDEX RANGE SCAN          | IDX_TEST     |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A"=1)

SQL> select * from test_for_col where a= 20000 ;

Execution Plan
----------------------------------------------------------
Plan hash value: 170577590

----------------------------------------------------------------------------------
| Id | Operation         | Name         | Rows | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |              | 19991 |   117K|    11 (10)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST_FOR_COL | 19991 |   117K|    11 (10)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("A"=20000)


看见已经给出了正确的执行计划