什么时候CBO会选择全表扫描

什么时候CBO会选择全表扫描
1.创建两个表t1,t2，并分别在id列创建索引，注意id列取值的不同方式。插入数据后对表做表分析
create table t1 as 
select trunc((rownum-1)/100) id,
       rpad(rownum,100) tpad 
  from dba_source 
 where rownum<=10000;


create index t1_index1 on t1(id);


zo_leave@orcl> exec dbms_stats.gather_table_stats('SYS','t1',method_opt=>'FOR AL
L COLUMNS SIZE 1', CASCADE=>TRUE);


create table t2 as
select mod(rownum,100) id,
       rpad(rownum,100) tpad
  from dba_source
 where rownum<=10000;     


create index t2_index1 on t2(id); 


zo_leave@orcl> EXEC DBMS_STATS.GATHER_TABLE_STATS('SYS','T2',METHOD_OPT=>'FOR AL
L COLUMNS SIZE 1',CASCADE=>TRUE);


PL/SQL 过程已成功完成。  


zo_leave@orcl> SELECT COUNT(*) FROM T1 WHERE ID = '1';


  COUNT(*)
----------
       100


已用时间:  00: 00: 00.00
zo_leave@orcl> SELECT COUNT(*) FROM T2 WHERE ID = '1';


  COUNT(*)
----------
       100


已用时间:  00: 00: 00.01


可以看到在t1,t2表中ID为1的值都是100条，那么执行计划是否应该一致，而且返回值为1%，这个查询是否一定会走索引呢
look
t1的执行计划
zo_leave@orcl> select * from t1 where id = '1';


已选择100行。


已用时间:  00: 00: 00.00


执行计划
----------------------------------------------------------
Plan hash value: 3353991022


--------------------------------------------------------------------------------
---------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| T
ime     |
--------------------------------------------------------------------------------
---------
|   0 | SELECT STATEMENT            |           |   100 | 10300 |     3   (0)| 0
0:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1        |   100 | 10300 |     3   (0)| 0
0:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_INDEX1 |   100 |       |     1   (0)| 0
0:00:01 |
--------------------------------------------------------------------------------
---------


Predicate Information (identified by operation id):
---------------------------------------------------


   2 - access("ID"=1)




统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         19  consistent gets
          0  physical reads
          0  redo size
      12177  bytes sent via SQL*Net to client
        482  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        100  rows processed
很明显走了索引，执行计划也给出了准确的返回结果行
再看t2


zo_leave@orcl> select * from t2 where id = '1';


已选择100行。


已用时间:  00: 00: 00.00


执行计划
----------------------------------------------------------
Plan hash value: 1513984157


--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   100 | 10300 |    43   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T2   |   100 | 10300 |    43   (0)| 00:00:01 |
--------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   1 - filter("ID"=1)




统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        163  consistent gets
          0  physical reads
          0  redo size
      11855  bytes sent via SQL*Net to client
        482  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        100  rows processed
恩？t2居然不走索引，他选择了全表扫描，而且预计返回值也是正确的，和t1相比
这是为什么呢？


我们来查询下表t1,t2的数据行和数据块统计信息
select table_name,num_rows,blocks from user_tables where table_name = 'T1';     
zo_leave@orcl> select table_name,num_rows,blocks from user_tables where table_na
me = 'T1';


TABLE_NAME                       NUM_ROWS     BLOCKS
------------------------------ ---------- ----------
T1                                  10000        152    


zo_leave@orcl> select table_name,num_rows,blocks from user_tables where table_na
me = 'T2';


TABLE_NAME                       NUM_ROWS     BLOCKS
------------------------------ ---------- ----------
T2                                  10000        152


已用时间:  00: 00: 00.03
块数和数据行也一致，到底是为什么呢？


让我们回到对t1,t2表的插入id数据的这个过程      
t1表：trunc((rownum-1)/100) id
zo_leave@orcl> declare
  2  a number;
  3  begin
  4    for i in 1..10 loop
  5      select trunc((i-1)/100)  into a from dual;
  6      dbms_output.put_line(a);
  7      end loop;
  8      end ;
  9  /
0
0
0
0
0
0
0
0
0
0


PL/SQL 过程已成功完成。


已用时间:  00: 00: 00.00


t2表：mod(rownum,100) id
zo_leave@orcl> declare
  2  a number;
  3  begin
  4    for i in 1..10 loop
  5      select mod(i,100)  into a from dual;
  6      dbms_output.put_line(a);
  7      end loop;
  8      end ;
  9  /
1
2
3
4
5
6
7
8
9
10


PL/SQL 过程已成功完成。


已用时间:  00: 00: 00.01
可以很明显的看到，t1表示100个0,100个1,100个2。。。存放数据，一个是0-99顺序存放一次，再从头开始，0-99顺序存放一次
我们在查询select * from tablename where id = '1'的时候
t1表只需要访问几个数据块就能取得查询所需的100行数据，而t2的数据行在物理上是零散存储的，对它的查询几乎需要读取大部
分的数据块来获取同样的100行数据。优化器计算出使用索引来读取每一个数据块的时间可能比直接用全表扫描，然后直接舍弃不
需要的数据花掉的时间还长

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26477854/viewspace-1072535/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26477854/viewspace-1072535/