关于RBO与CBO的问题

寒鸦

optimizer mode=choose:

表在A列上有索引
select * from table_name where a=** or a=**;
select * from table_name where a in (**,**,**);
select * from table_name where a between ** and **;
以上三条语句均导致全表扫描 table access full

用HINTS强制为RBO

select /*+rule*/ * from table_name where a=** or a=**;
select /*+rule*/ * from table_name where a in (**,**,**);
select /*+rule*/ * from table_name where a between ** and **;
以上三条执行计划均用到在A上的索引

CBO下的用OR，IN，BETWEEN为什么会无视索引呢？

jametong

你分析表了吗? 你是如何分析表的..

做一个10053的trace看看, Oracle是如何选择的..

numenhuang

以Oracle10g为例

Oracle10g优化就是CBO，比如optimizer_mode，在10g只有FIRST_ROWS_N, FIRST_ROWS, ALL_ROWS。FIRST_ROWS 主要追求的是数据返回的时间，ALL_ROWS追求的是系统资源使用的最小化。举例：

CREATE TABLE test1(
    id            NUMBER,
    name      VARCHAR2(10)
) tablespace users;

CRATE INDEX test1_id on test1(id) tablespace indx;

insert 测试数据，结果如下：
SQL> select id,name from test1;

        ID NAME                                                                 
---------- ----------                                                           
         1 01                                                                  
       100 100                                                                  
        10 10                                                                  
         5 05                                                                  
        99 99                                                                  
         9 09                                                                  
        69 69                                                                  
        59 59               


sqlplus>set autotrace on

sqlplus>alter session set optimizer_mode=all_rows;

SQL> select name from  test1 where id between 10 and 50;

NAME                                                                           
----------                                                                     
10                                                                              


Execution Plan
----------------------------------------------------------                     
Plan hash value: 4122059633                                                     
                                                                                
---------------------------------------------------------------------------     
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |     
---------------------------------------------------------------------------     
|   0 | SELECT STATEMENT  |       |     1 |    20 |     3   (0)| 00:00:01 |     
|*  1 |  TABLE ACCESS FULL| TEST1 |     1 |    20 |     3   (0)| 00:00:01 |     
---------------------------------------------------------------------------     
                                                                                
Predicate Information (identified by operation id):                             
---------------------------------------------------                             
                                                                                
   1 - filter("ID">=10 AND "ID"<=50)                                            
                                                                                
Note                                                                           
-----                                                                           
   - dynamic sampling used for this statement                                   


Statistics
----------------------------------------------------------                     
          9  recursive calls                                                   
          0  db block gets                                                      
         18  consistent gets                                                   
          0  physical reads                                                     
          0  redo size                                                         
        511  bytes sent via SQL*Net to client                                   
        468  bytes received via SQL*Net from client                             
          2  SQL*Net roundtrips to/from client                                 
          0  sorts (memory)                                                     
          0  sorts (disk)                                                      
          1  rows processed

后续中

原帖由 寒鸦 于 2006-8-8 14:40 发表
optimizer mode=choose:

表在A列上有索引
select * from table_name where a=** or a=**;
select * from table_name where a in (**,**,**);
select * from table_name where a between ** and **;
以上三 ...

numenhuang

SQL> alter session set optimizer_mode=first_rows;

SQL> select name from  test1 where id between 10 and 50;

NAME                                                                           
----------                                                                     
10                                                                              


Execution Plan
----------------------------------------------------------                     
Plan hash value: 2199759019                                                     
                                                                                
--------------------------------------------------------------------------------
--------                                                                        
                                                                                
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Ti
me     |                                                                        
                                                                                
--------------------------------------------------------------------------------
--------                                                                        
                                                                                
|   0 | SELECT STATEMENT            |          |     1 |    20 |   102   (0)| 00
:00:02 |                                                                        
                                                                                
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST1    |     1 |    20 |   102   (0)| 00
:00:02 |                                                                        
                                                                                
|*  2 |   INDEX RANGE SCAN          | TEST1_ID |     1 |       |     2   (0)| 00
:00:01 |                                                                        
                                                                                
--------------------------------------------------------------------------------
--------                                                                        
                                                                                
                                                                                
Predicate Information (identified by operation id):                             
---------------------------------------------------                             
                                                                                
   2 - access("ID">=10 AND "ID"<=50)                                            
                                                                                
Note                                                                           
-----                                                                           
   - dynamic sampling used for this statement                                   


Statistics
----------------------------------------------------------                     
          7  recursive calls                                                   
          0  db block gets                                                      
         13  consistent gets                                                   
          0  physical reads                                                     
          0  redo size                                                         
        518  bytes sent via SQL*Net to client                                   
        468  bytes received via SQL*Net from client                             
          2  SQL*Net roundtrips to/from client                                 
          0  sorts (memory)                                                     
          0  sorts (disk)                                                      
          1  rows processed

numenhuang

sql>alter session set optimizer_mode=all_rows;

sql>select /*+ index(test1 test1_id) */ id,name from test1 where id between 10 and 50;

NAME                                                                           
----------                                                                     
10                                                                              


Execution Plan
----------------------------------------------------------                     
Plan hash value: 2199759019                                                     
                                                                                
------------------------------------------------                                
| Id  | Operation                   | Name     |                                
------------------------------------------------                                
|   0 | SELECT STATEMENT            |          |                                
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST1    |                                
|*  2 |   INDEX RANGE SCAN          | TEST1_ID |                                
------------------------------------------------                                
                                                                                
Predicate Information (identified by operation id):                             
---------------------------------------------------                             
                                                                                
   2 - access("ID">=10 AND "ID"<=50)                                            
                                                                                
Note                                                                           
-----                                                                           
   - rule based optimizer used (consider using cbo)                             


Statistics
----------------------------------------------------------                     
          1  recursive calls                                                   
          0  db block gets                                                      
          3  consistent gets                                                   
          0  physical reads                                                     
          0  redo size                                                         
        518  bytes sent via SQL*Net to client                                   
        468  bytes received via SQL*Net from client                             
          2  SQL*Net roundtrips to/from client                                 
          0  sorts (memory)                                                     
          0  sorts (disk)                                                      
          1  rows processed

numenhuang

所以即使是CBO，选择不同的goal（ first_rows, all_rows)， 也会走不同的access path。
当然我们也可以通过hints来强制oracle来使用某些规则

另外，在使用CBO的时候，一定要注意表的统计数据的更新，这个数据是CBO的计算基础。如果统计数据不准备或者缺失，在表的数据量变化很大的情况下，会导致性能很大的差异。可以通过dbms_stat定期来更新统计数据。

RBO对于Oracle9i以后的版本逐渐被CBO替代。