关于索引使用的问题

Question

有一个表中有50多万行数量，有一列中存了三个值，分别是0，1，4，现在查询时，当条件为0时使用全表扫描，为1和4时使用了该列的索引，请大家为我解惑。select t.* from mytable t
where t.vc_flag='0'
已选择36403行，PLSQL中执行时间2.578秒
-------------------------------------------------------------------------------------------
| Id  | Operation         | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                       |   653K|   132M|  4891   (3)| 00:00:59 |
|*  1 |  TABLE ACCESS FULL| MYTABLE               |   653K|   132M|  4891   (3)| 00:00:59 |
-------------------------------------------------------------------------------------------select t.* from mytable t
where t.vc_flag='1'
已选择20行，PLSQL中执行时间0.125秒
------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                       |     1 |   213 |   1   (0)  | 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID | MYTABLE               |     1 |   213 |   1   (0)  | 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS|                       |       |       |            |          |
|*  3 |    BITMAP INDEX SINGLE VALUE | SYS_C00105059         |       |       |            |          |
------------------------------------------------------------------------------------------------------select t.* from mytable t
where t.vc_flag='4'
已选择512599行，PLSQL中执行时间0.125秒
------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                       |   713 |   148K|  98   (0)  | 00:00:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID | MYTABLE               |   713 |   148K|  98   (0)  | 00:00:02 |
|   2 |   BITMAP CONVERSION TO ROWIDS|                       |       |       |            |          |
|*  3 |    BITMAP INDEX SINGLE VALUE | SYS_C00105059         |       |       |            |          |
------------------------------------------------------------------------------------------------------

Answer 1

没遇到过这样的问题
会不会vc_flag的类型有关啊？
用下面这样的试试
select t.* from mytable t
where t.vc_flag=0

Answer 2

vc_flag的类型是varchar2，后边如果用数字，会强制触发to_number方法，索引是无效的，经测试=0时变化不大，执行时间3.531秒，=1时变慢了很多，执行时间达到4.178秒，=4时基本无变化，时间为0.288秒

Answer 3

--用hint试试
--你的vc_flag是 not null吗？如果允许为null的话可能会不走索引
select /*+index(table_name index_name)*/ 
t.* from mytable t
where t.vc_flag=0

Answer 4

想象下极端情况,如果你的列值只有一个0,即使该列建了索引,oracle也不会走索引的.

Answer 5

哦,看来我想的不对.比较奇怪的是,为什么我的bitmap索引总用不上?
create table t_test_bitmap
(id number,
 cnt number);
insert into t_test_bitmap
select rownum ,mod(rownum,5) from dual connect by rownum<500000;
commit; 
CREATE BITMAP INDEX t_test_bitmap1 on t_test_bitmap(cnt);SQL> exec dbms_stats.gather_table_stats(ownname => 'scott',tabname => 't_test_bitmap',estimate_percent => 10,method_opt=> 'for all indexed columns') ;
 
PL/SQL procedure successfully completed
 
SQL> exec dbms_stats.gather_index_stats(ownname => 'scott',indname => 't_test_bitmap1',estimate_percent => '10',degree => '4') ;
 
PL/SQL procedure successfully completed
 
SQL> explain plan for select * from t_test_bitmap t where t.cnt=0;
 
Explained
 
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 241495815
--------------------------------------------------------------------------------
| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |               | 99930 |  1561K|   217   (6)| 00:00:0
|*  1 |  TABLE ACCESS FULL| T_TEST_BITMAP | 99930 |  1561K|   217   (6)| 00:00:0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("T"."CNT"=0)
 
13 rows selected

Answer 6

如果是这样的话，应该不在vc_flag建立索引，好像意义不大，建议考虑下语句是否作调整

Answer 7

把你的表进行分析下，可能你的优化规则是采取的choose的方式，如果在数据字典里面没有你查询对象的统计信息他就会选择RBO，如果有统计信息他就选择CBO

Answer 8

请解释一下什么是优化规则，如何更改优化规则，RBO和CBO分别是什么？

Answer 9

CBO:
Oracle把一个代价引擎(Cost Engine)集成到数据库内核中，用来估计每个执行计划需要的代价，该代价将每个执行计划所耗费的资源进行量化，从而CBO可以根据这个代价选择出最优 的执行计划。一个查询耗费的资源可以被分成3个基本组成部分：I/O代价、CPU代价、network代价。I/O代价是将数据从磁盘读入内存所需的代 价。访问数据包括将数据文件中数据块的内容读入到SGA的数据高速缓存中，在一般情况下，该代价是处理一个查询所需要的最主要代价，所以我们在优化时，一 个基本原则就是降低查询所产生的I/O总次数。CPU代价是处理在内存中数据所需要的代价，如一旦数据被读入内存，则我们在识别出我们需要的数据后，在这 些数据上执行排序(sort)或连接(join)操作，这需要耗费CPU资源。
RBO
在ORACLE7之前，主要是使用基于规则的优化器。ORACLE在基于规则的优化器中采用启发式的方法(Heuristic Approach)或规则(Rules)来生成执行计划。例如，如果一个查询的where条件(where clause)包含一个谓词(predicate，其实就是一个判断条件，如”=”, “>”, ”<”等)，而且该谓词上引用的列上有有效索引，那么优化器将使用索引访问这个表，而不考虑其它因素，如表中数据的多少、表中数据的易变性、索引的 可选择性等。此时数据库中没有关于表与索引数据的统计性描述，如表中有多上行，每行的可选择性等。优化器也不考虑实例参数，如multi block i/o、可用排序内存的大小等，所以优化器有时就选择了次优化的计划作为真正的执行计划，导致系统性能不高。

Answer 10

这是有oracle优化器决定的，10g默认使用CBO，这些内容可以从官方文档找到。如果你想让你的查询走索引，只要在你的查询语句上做一下更改，如下：
select /*+ rule */ t.* from mytable t
where t.vc_flag='0'

Answer 11

几位兄弟让我长了不少知识，特别是YY_MM_DD兄，谢谢了。
最后查到了速度慢的原因，是因为表初始化空间太小，导致记录在插入后磁盘碎片过多，所以查询速度特别慢，将表的初始化空间调整到600m后，速度提高了10倍以上，希望这个教训能让大家了解表的初始化空间对表的速度影响有多大。问题解决，结贴！