• 博客访问: 7349022
  • 博文数量: 1016
  • 用 户 组: 普通用户
  • 注册时间: 2009-10-07 13:14
个人简介

MySQL DBA NoSQL DEVOPS

文章分类

全部博文(1016)

分类: Linux操作系统

2010-11-19 18:48:04

在Oracle中可以创建组合索引,即同时包含两个或两个以上列的索引组合索引的使用存在着一定的局限,只有在谓词中出现全部索引列时才能使用效率最高的index unique scan, 否则谓词中必须包含前导列,否则会走Index full scan或者FTS。
SQL> create index idx_test on yangtest (object_type,object_name);
索引已创建。
SQL> exec dbms_stats.gather_table_stats(user,'YANGTEST',cascade=>true);
PL/SQL 过程已成功完成。
已用时间:  00: 00: 20.78
SQL> select object_type,count(*) from yangtest group by object_type order by 2;

OBJECT_TYPE           COUNT(*)
------------------- ---------- 
EDITION                      1
RULE                         1
MATERIALIZED VIEW            1 
SCHEDULE                     2 
WINDOW GROUP                 4
DIRECTORY                    5 
UNDEFINED                    6 
LOB PARTITION                7 
RESOURCE PLAN                7 
CONTEXT                      7 
WINDOW                       9
CLUSTER                     10
JOB                         11 
EVALUATION CONTEXT          11 
INDEXTYPE                   11
JOB CLASS                   13
CONSUMER GROUP              14
RULE SET                    17
PROGRAM                     18
QUEUE                       33
OPERATOR                    57
XML SCHEMA                  91
TABLE PARTITION            108 
INDEX PARTITION            128
PROCEDURE                  131
LIBRARY                    179
TYPE BODY                  224
SEQUENCE                   227 
FUNCTION                   296
JAVA DATA                  324
TRIGGER                    482
LOB                        760 
JAVA RESOURCE              833 
PACKAGE BODY              1206
PACKAGE                   1267
TABLE                     2543 
TYPE                      2616
INDEX                     3194 
VIEW                      4749 
JAVA CLASS               22103 
SYNONYM                  26670 

已选择41行。
已用时间:  00: 00: 00.09
1、当使用基于规则的优化器(RBO)时,只有当组合索引的前导列出现在SQL语句的where子句中时,才会使用到该索引;
SQL> set autot trace
SQL> select /*+ rule */ * from yangtest where object_type='JOB';
已选择11行。
已用时间:  00: 00: 00.07
执行计划
----------------------------------------------------------
Plan hash value: 2067289980 
------------------------------------------------ 
| Id  | Operation                   | Name     | 
------------------------------------------------ 
|   0 | SELECT STATEMENT            |          |
|   1 |  TABLE ACCESS BY INDEX ROWID| YANGTEST |
|*  2 |   INDEX RANGE SCAN          | IDX_TEST |
------------------------------------------------ 
Predicate Information (identified by operation id):
--------------------------------------------------- 
   2 - access("OBJECT_TYPE"='JOB')
Note       
-----      
   - rule based optimizer used (consider using cbo)
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets 
         13  consistent gets
          0  physical reads 
          0  redo size   
       2310  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory) 
          0  sorts (disk) 
         11  rows processed 
-- 而让CBO自己选择时,却选择了TFS,从信息统计里面可以看出consistent gets 是前者的100倍。CBO 也不一定很聪明。
SQL> select * from yangtest where object_type='JOB';
已选择11行。
已用时间:  00: 00: 00.03
执行计划
---------------------------------------------------------- 
Plan hash value: 911235955 
------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     | 
------------------------------------------------------------------------------ 
|   0 | SELECT STATEMENT  |          |  1668 |   164K|   275   (1)| 00:00:04 | 
|*  1 |  TABLE ACCESS FULL| YANGTEST |  1668 |   164K|   275   (1)| 00:00:04 | 
------------------------------------------------------------------------------
Predicate Information (identified by operation id):  
---------------------------------------------------  
   1 - filter("OBJECT_TYPE"='JOB')  
统计信息
---------------------------------------------------------- 
        264  recursive calls     
          0  db block gets   
       1050  consistent gets   
          0  physical reads    
          0  redo size        
       2006  bytes sent via SQL*Net to client 
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client 
          0  sorts (memory) 
          0  sorts (disk) 
         11  rows processed
由于使用了组合索引的前导列并且访问了表中的少量记录,Oracle明智地选择了索引扫描。那么,如果我们访问表中的大量数据时,Oracle会选择什么样的访问路径呢?看下面的测试:
SQL> select * from yangtest where object_type='SYNONYM';
已选择26670行。
已用时间:  00: 00: 01.42
执行计划
----------------------------------------------------------
Plan hash value: 911235955
------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |  1668 |   164K|   275   (1)| 00:00:04 | 
|*  1 |  TABLE ACCESS FULL| YANGTEST |  1668 |   164K|   275   (1)| 00:00:04 | 
------------------------------------------------------------------------------ 
Predicate Information (identified by operation id):  
--------------------------------------------------- 
   1 - filter("OBJECT_TYPE"='SYNONYM')
统计信息
---------------------------------------------------------- 
          1  recursive calls 
          0  db block gets  
       2769  consistent gets 
          0  physical reads  
          0  redo size 
    1228701  bytes sent via SQL*Net to client 
      19963  bytes received via SQL*Net from client 
       1779  SQL*Net roundtrips to/from client 
          0  sorts (memory)     
          0  sorts (disk)    
      26670  rows processed 
测试一下是使用RULE 的优化器。
SQL> select /*+ rule */ * from yangtest where object_type='SYNONYM';
已选择26670行。
已用时间:  00: 00: 01.56
执行计划
----------------------------------------------------------                               

                    
Plan hash value: 2067289980 
------------------------------------------------ 
| Id  | Operation                   | Name     | 
------------------------------------------------ 
|   0 | SELECT STATEMENT            |          |
|   1 |  TABLE ACCESS BY INDEX ROWID| YANGTEST |
|*  2 |   INDEX RANGE SCAN          | IDX_TEST |
------------------------------------------------
Predicate Information (identified by operation id):
--------------------------------------------------- 
   2 - access("OBJECT_TYPE"='SYNONYM')
Note       
-----      
   - rule based optimizer used (consider using cbo)
统计信息
----------------------------------------------------------
          1  recursive calls  
          0  db block gets  
      23543  consistent gets --明显比cbo的执行计划的多10倍。
          0  physical reads  
          0  redo size      
    3235078  bytes sent via SQL*Net to client   
      19963  bytes received via SQL*Net from client
       1779  SQL*Net roundtrips to/from client   
          0  sorts (memory)    
          0  sorts (disk)    
      26670  rows processed  
  从以上结果可以看出,在访问大量数据的情况下,使用索引确实会导致更高的执行成本,这从statistics部分的逻辑读取数(consistent gets)就可以看出,使用索引导致的逻辑读取数是不使用索引导致的逻辑读的10倍还多。因此,Oracle明智地选择了全表扫描而不是索引扫描。
下面,让我们来看看where子句中没有索引前导列的情况:
SQL> select * from yangtest where object_name ='EMP';
已用时间:  00: 00: 00.01
执行计划
----------------------------------------------------------
Plan hash value: 4208055961
----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     2 |   202 |    45   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| YANGTEST |     2 |   202 |    45   (0)| 00:00:01 | 
|*  2 |   INDEX SKIP SCAN           | IDX_TEST |     2 |       |    43   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):   
--------------------------------------------------- 
   2 - access("OBJECT_NAME"='EMP') 
       filter("OBJECT_NAME"='EMP') 
统计信息
---------------------------------------------------------- 
          1  recursive calls  
          0  db block gets  
         35  consistent gets 
          1  physical reads 
          0  redo size    
       1337  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client  
          2  SQL*Net roundtrips to/from client  
          0  sorts (memory)            
          0  sorts (disk)     
          1  rows processed  

SQL> select * from yangtest where object_name ='YANGTEST';
未选定行
已用时间:  00: 00: 00.01
执行计划
----------------------------------------------------------
Plan hash value: 4208055961 
---------------------------------------------------------------------------------------- 
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     | 
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     2 |   202 |    45   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| YANGTEST |     2 |   202 |    45   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN           | IDX_TEST |     2 |       |    43   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id): 
--------------------------------------------------- 
   2 - access("OBJECT_NAME"='YANGTEST')  
       filter("OBJECT_NAME"='YANGTEST')  
统计信息
---------------------------------------------------------- 
          1  recursive calls   
          0  db block gets     
         27  consistent gets   
          0  physical reads     
          0  redo size         
       1124  bytes sent via SQL*Net to client
        405  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)      
          0  sorts (disk)       
          0  rows processed    

 没有使用前导列,Oracle正确地选择了索引跳跃扫描。我们再来看看如果不使用索引跳跃扫描,该语句的成本:
SQL> select /*+ NO_INDEX(YANGTEST,IDX_TEST)*/ * from yangtest where object_name ='DEPT';
已用时间:  00: 00: 00.01
执行计划
---------------------------------------------------------- 
Plan hash value: 911235955     
------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |     2 |   202 |   275   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| YANGTEST |     2 |   202 |   275   (1)| 00:00:04 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):  
---------------------------------------------------
   1 - filter("OBJECT_NAME"='DEPT') 
统计信息
---------------------------------------------------------- 
          1  recursive calls    
          0  db block gets     
       1011  consistent gets  --是使用索引跳跃扫描的50倍左右
          0  physical reads     
          0  redo size         
       1335  bytes sent via SQL*Net to client 
        416  bytes received via SQL*Net from client 
          2  SQL*Net roundtrips to/from client 
          0  sorts (memory)    
          0  sorts (disk)      
          1  rows processed     

SQL> select * from yangtest where object_name like 'T%';
已选择136行。
已用时间:  00: 00: 00.04
执行计划
----------------------------------------------------------
Plan hash value: 911235955     
------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |   925 | 93425 |   275   (1)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| YANGTEST |   925 | 93425 |   275   (1)| 00:00:04 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id): 
--------------------------------------------------- 
   1 - filter("OBJECT_NAME" LIKE 'T%') 
统计信息
----------------------------------------------------------  
          1  recursive calls   
          0  db block gets     
       1020  consistent gets    
          0  physical reads    
          0  redo size         
       8900  bytes sent via SQL*Net to client 
        515  bytes received via SQL*Net from client 
         11  SQL*Net roundtrips to/from client  
          0  sorts (memory)    
          0  sorts (disk)      
        136  rows processed 
 这次只选择了136条数据,跟表YANGTEST中总的数据量29489条相比,显然只是很小的一部分,但是Oracle还是选择了全表扫描,有1020 个逻辑读。这种情况下,如果我们强制使用索引.结果如下
SQL> select /*+ INDEX(YANGTEST,IDX_TEST)*/ * from yangtest where object_name like 'T%';
已选择136行。
已用时间:  00: 00: 00.06
执行计划
----------------------------------------------------------
Plan hash value: 972231820     
----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |   925 | 93425 |  1084   (1)| 00:00:14 |
|   1 |  TABLE ACCESS BY INDEX ROWID| YANGTEST |   925 | 93425 |  1084   (1)| 00:00:14 |
|*  2 |   INDEX FULL SCAN           | IDX_TEST |   925 |       |   424   (1)| 00:00:06 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id): 
--------------------------------------------------- 
   2 - access("OBJECT_NAME" LIKE 'T%')
       filter("OBJECT_NAME" LIKE 'T%')
统计信息
---------------------------------------------------------- 
          1  recursive calls    
          0  db block gets     
        537  consistent gets   
          0  physical reads    
          0  redo size         
      14700  bytes sent via SQL*Net to client
        515  bytes received via SQL*Net from client
         11  SQL*Net roundtrips to/from client
          0  sorts (memory)    
          0  sorts (disk)      
        136  rows processed    
通过添加提示(hint),我们强制Oracle使用了索引扫描(index full scan),执行了335个逻辑读,比使用全表扫描的时候少了一些。
    由此可见,Oracle优化器有时会做出错误的选择,因为它再“聪明”,也不如我们SQL语句编写人员更清楚表中数据的分布,在这种情况下,通过使用提示(hint),我们可以帮助Oracle优化器作出更好的选择。
阅读(13447) | 评论(0) | 转发(1) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册