有无直方图，性能的差距

本文主要是介绍有无直方图，性能的差距，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

#################################
###有无直方图，性能的差距###
#################################
实验的环境在redhat平台下的11gr2单实例环境
1、在自己定义的用户下创建表及索引
CREATE TABLE customers AS SELECT * FROM sh.customers;
CREATE INDEX customers_country_ix ON customers(country_id);
2、收集表的收集信息
BEGIN
   SYS.DBMS_STATS.gather_table_stats (ownname      =>'TRSEN',
                                      tabname      => 'CUSTOMERS');
END;
/

3、查看一下统计信息(列的密度、列的空值、列的不同值)
SQL> SELECT density, num_nulls, num_distinct
2    FROM user_tab_col_statistics
3   WHERE table_name = 'CUSTOMERS' AND column_name = 'COUNTRY_ID';

   DENSITY NUM_NULLS NUM_DISTINCT
---------- ---------- ------------
.052631579          0           19
4、执行两条语句
SQL> SELECT MAX (cust_income_level)
2    FROM customers
3   WHERE country_id = 52787;
Execution Plan
----------------------------------------------------------
Plan hash value: 296924608
--------------------------------------------------------------------------------
| Id | Operation          | Name      | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 |    26 |   406   (1)| 00:00:05 |
|   1 | SORT AGGREGATE    |           |     1 |    26 |            |          |
|* 2 |   TABLE ACCESS FULL| CUSTOMERS | 2921 | 75946 |   406   (1)| 00:00:05 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("COUNTRY_ID"=52787)

SQL> SELECT MAX (cust_income_level)
2    FROM customers
3   WHERE country_id = 52790;
Execution Plan
----------------------------------------------------------
Plan hash value: 296924608
--------------------------------------------------------------------------------
| Id | Operation          | Name      | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 |    26 |   406   (1)| 00:00:05 |
|   1 | SORT AGGREGATE    |           |     1 |    26 |            |          |
|* 2 |   TABLE ACCESS FULL| CUSTOMERS | 2921 | 75946 |   406   (1)| 00:00:05 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("COUNTRY_ID"=52790)
4、收集索引列直方图信息
     SYS.DBMS_STATS.gather_table_stats (ownname         => 'TRSEN',
3                                        tabname         => 'CUSTOMERS',
4                                        method_opt      => 'FOR ALL INDEXED COLUMNS'
5                                       );
6 END;
7 /
PL/SQL procedure successfully completed.
5、查看统计信息，发现列的密度发生改变了
SQL> SELECT density, num_nulls, num_distinct
2    FROM user_tab_col_statistics
3   WHERE table_name = 'CUSTOMERS' AND column_name = 'COUNTRY_ID';
   DENSITY NUM_NULLS NUM_DISTINCT
---------- ---------- ------------
9.1437E-06          0           19
6、此时查询查看执行计划
SQL> SELECT MAX (cust_income_level)
2    FROM customers
3   WHERE country_id = 52787;
Execution Plan
----------------------------------------------------------
Plan hash value: 479268801
-----------------------------------------------------------------------------------------------------
| Id | Operation                    | Name                 | Rows | Bytes | Co
st (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                      |     1 |    26 |23   (0)| 00:00:01 |
|   1 | SORT AGGREGATE              |                      |     1 |    26 |      |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| CUSTOMERS            |   102 | 2652 |23   (0)| 00:00:01 |
|* 3 |    INDEX RANGE SCAN          | CUSTOMERS_COUNTRY_IX |   102 |       |1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("COUNTRY_ID"=52787)

SQL> SELECT MAX (cust_income_level)
2    FROM customers
3   WHERE country_id = 52790;
--------------------------------------------------------------------------------
| Id | Operation          | Name      | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 |    26 |   406   (1)| 00:00:05 |
|   1 | SORT AGGREGATE    |           |     1 |    26 |            |          |
|* 2 |   TABLE ACCESS FULL| CUSTOMERS | 18230 |   462K|   406   (1)| 00:00:05 |
--------------------------------------------------------------------------------
   2 - filter("COUNTRY_ID"=52790)

7、强制走索引查看执行计划
SQL> SELECT MAX (cust_income_level)
2    FROM customers
3   WHERE country_id = 52790;
Execution Plan
----------------------------------------------------------
Plan hash value: 296924608
--------------------------------------------------------------------------------
| Id | Operation          | Name      | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 |    26 |   406   (1)| 00:00:05 |
|   1 | SORT AGGREGATE    |           |     1 |    26 |            |          |
|* 2 |   TABLE ACCESS FULL| CUSTOMERS | 18230 |   462K|   406   (1)| 00:00:05 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("COUNTRY_ID"=52790)

SQL> SELECT /*+ INDEX(customers) */
2         MAX (cust_income_level)
3    FROM customers
4   WHERE country_id = 52790;
Execution Plan
----------------------------------------------------------
Plan hash value: 479268801
-----------------------------------------------------------------------------------------------------
| Id | Operation                    | Name                 | Rows | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                      |     1 |    26 | 3
872   (1)| 00:00:47 |
|   1 | SORT AGGREGATE              |                      |     1 |    26 |        |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| CUSTOMERS            | 18230 |   462K| 3872   (1)| 00:00:47 |
|* 3 |    INDEX RANGE SCAN          | CUSTOMERS_COUNTRY_IX | 18230 |       |41   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("COUNTRY_ID"=52790)
SQL> SELECT /*+ INDEX(customers) */
2         MAX (cust_income_level)
3    FROM customers
4   WHERE country_id = 52787;
Execution Plan
----------------------------------------------------------
Plan hash value: 479268801
-----------------------------------------------------------------------------------------------------
| Id | Operation                    | Name                 | Rows | Bytes | Co
st (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                      |     1 |    26 |23   (0)| 00:00:01 |
|   1 | SORT AGGREGATE              |                      |     1 |    26 |        |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| CUSTOMERS            |   102 | 2652 |23   (0)| 00:00:01 |
|* 3 |    INDEX RANGE SCAN          | CUSTOMERS_COUNTRY_IX |   102 |       |1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("COUNTRY_ID"=52787)
8、这张表的数据分布及实验结果分析
select country_id,count(country_id) as total from customers group by country_id order by total;

从这张统计表可以看出一些信息有19个country_id值，有30%左右的数据在52790列上，如果我们要搜索一个特定的country_id=52787，优化器也会选择全表扫描

9、清除收集的直方图信息语句
BEGIN
SYS.DBMS_STATS.gather_table_stats ('TRSEN','CUSTOMERS',method_opt=>'FOR ALL INDEXED COLUMNS size 1');
END;
/
10、针对consistent gets分析结果图