Oracle 创建索引和直方图会提高数据库性能吗？

嗨，团队，

我对我面临的情况有疑问。

我有表格让有例子表 “IP”:

表IP有一个列ID，即IP(ID);

以下是价值分布:-

SQL> select count(*),id from IP group by id;

  COUNT(*) ID
---------- --------------------
        25 a
        50 b
        75 c

我想知道如果我在IP(ID) 上创建索引，我一定会受益，我也在考虑创建直方图，这将使我的值 (a，b，c) 受益。

但是，如果我在where子句中为 “id” 传递值，除了在ID列中列出之外，收集直方图将对我有所帮助，或者它可能会超出界限，可能会进入FTS或索引全扫描 ？？？或者它会以任何方式帮助我提高性能？

示例:-

select count(*) from IP where id='h';====> will gathering histogram  help ???

将添加索引definitely提高性能？

否:

create table t as 
  select mod(level, 3) x, lpad('x', 500, 'x') y
  from   dual
  connect by level <= 1000;

set serveroutput off
select /*+ gather_plan_statistics */x, count(*) from t
group  by x;

X   COUNT(*)   
  1        334 
  2        333 
  0        333 

select * 
from   table(dbms_xplan.display_cursor(null, null, 'IOSTATS LAST'));

PLAN_TABLE_OUTPUT                                                                                
SQL_ID  0v787jt578k0y, child number 0                                                            
-------------------------------------                                                            
select /*+ gather_plan_statistics */x, count(*) from t group  by x                               
                                                                                                 
Plan hash value: 674246460                                                                       
                                                                                                 
----------------------------------------------------------------------------------------------   
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |   
----------------------------------------------------------------------------------------------   
|   0 | SELECT STATEMENT   |      |      1 |        |      3 |00:00:00.01 |      84 |      1 |   
|   1 |  HASH GROUP BY     |      |      1 |   1043 |      3 |00:00:00.01 |      84 |      1 |   
|   2 |   TABLE ACCESS FULL| T    |      1 |   1043 |   1000 |00:00:00.01 |      84 |      1 |   
----------------------------------------------------------------------------------------------   
                                                                                                 
Note                                                                                             
-----                                                                                            
   - dynamic statistics used: dynamic sampling (level=2)  

create index i on t (x);

select /*+ gather_plan_statistics */x, count(*) from t
group  by x;

X   COUNT(*)   
  1        334 
  2        333 
  0        333 

select * 
from   table(dbms_xplan.display_cursor(null, null, 'IOSTATS LAST'));

PLAN_TABLE_OUTPUT                                                                       
SQL_ID  0v787jt578k0y, child number 0                                                   
-------------------------------------                                                   
select /*+ gather_plan_statistics */x, count(*) from t group  by x                      
                                                                                        
Plan hash value: 674246460                                                              
                                                                                        
-------------------------------------------------------------------------------------   
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |   
-------------------------------------------------------------------------------------   
|   0 | SELECT STATEMENT   |      |      1 |        |      3 |00:00:00.01 |      84 |   
|   1 |  HASH GROUP BY     |      |      1 |   1043 |      3 |00:00:00.01 |      84 |   
|   2 |   TABLE ACCESS FULL| T    |      1 |   1043 |   1000 |00:00:00.01 |      84 |   
-------------------------------------------------------------------------------------   
                                                                                        
Note                                                                                    
-----                                                                                   
   - dynamic statistics used: dynamic sampling (level=2) 

嗯。我添加了一个索引，但我们仍然有一个全表扫描!

怎么回事？

Oracle数据库从索引中排除所有列为空的行。您正在阅读所有行 (没有where子句)。所以全表扫描是唯一的选择。

但使列不为null和:

alter table t modify x not null;

select /*+ gather_plan_statistics */x, count(*) from t
group  by x;

X   COUNT(*)   
  1        334 
  2        333 
  0        333 

select * 
from   table(dbms_xplan.display_cursor(null, null, 'IOSTATS LAST'));

PLAN_TABLE_OUTPUT                                                                                   
SQL_ID  0v787jt578k0y, child number 0                                                               
-------------------------------------                                                               
select /*+ gather_plan_statistics */x, count(*) from t group  by x                                  
                                                                                                    
Plan hash value: 1339806295                                                                         
                                                                                                    
-------------------------------------------------------------------------------------------------   
| Id  | Operation             | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |   
-------------------------------------------------------------------------------------------------   
|   0 | SELECT STATEMENT      |      |      1 |        |      3 |00:00:00.01 |       5 |      3 |   
|   1 |  HASH GROUP BY        |      |      1 |   1043 |      3 |00:00:00.01 |       5 |      3 |   
|   2 |   INDEX FAST FULL SCAN| I    |      1 |   1043 |   1000 |00:00:00.01 |       5 |      3 |   
-------------------------------------------------------------------------------------------------   
                                                                                                    
Note                                                                                                
-----                                                                                               
   - dynamic statistics used: dynamic sampling (level=2) 

喔!快速的全索引扫描!:)

这比全表扫描要少得多 (请注意缓冲区5与84的值要小得多)。

因此，如果提供索引，索引可能会有所帮助:

-该列被声明为不为空
-索引小于表格

那么直方图呢？

这些没有直接帮助。它们是改进行估计的工具。即优化器认为计划的每个步骤将处理多少数据。

这些估计越接近现实，你就越有可能有一个好的计划。如果数据存在较大的偏斜，直方图会有所帮助。例如，一个人的地址数据库对中国的行将远远多于智利。

-您访问的行越多 (中国)，就越有可能是全表扫描是最好的方法。
-获取的行越少 (智利)，索引范围扫描的可能性就越大。

但是直方图还有很多。我建议在概念指南中阅读它们是如何工作的:

https://docs.oracle.com/en/database/oracle/oracle-database/18/tgsql/histograms.html#GUID-BE10EBFC-FEFC-4530-90DF-1443D9AD9B64

玛丽亚·科尔根关于常见直方图神话的这篇文章:

https://sqlmaria.com/2017/05/09/optimizer-histograms/