本文主要是介绍有无直方图，性能的差距，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
####################################有无直方图，性能的差距###
#################################
实验的环境在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分析结果图
实验分析：列的直方图创建，使得oracle能够在其他值不具备选择性的列中识别出那些有选择性的值。优化能够为有选择性的值选择使用索引，为没有选择性的列值选择全表扫描。即便强制没有选择性的列走索引，但其性能会更差。
这篇关于有无直方图，性能的差距的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
