关于ORACLE索引的一个效率测试

Question

索引测试语句
select user_number,opp_user_number,call_type,start_time,duration,lac_id,cell_id,esn from jf.dr_gsm_793_20081001@jxcb1 where  user_number='15907932556'  union  all
select user_number,opp_user_number,call_type,start_time,duration,lac_id,cell_id,esn from jf.dr_gsm_793_20081002@jxcb1 where  user_number='15907932556';
共91条记录

1.测试位图索引和普通索引的速度 建立位图索引
create bitmap index gsm_20081001_ind  on DR_GSM_793_20081001(user_number);
create bitmap index gsm_20081002_ind  on DR_GSM_793_20081002(user_number); ·耗数7，基数16 建立普通索引
CREATE index gsm_20081001_ind  on DR_GSM_793_20081001(user_number);
CREATE index gsm_20081002_ind  on DR_GSM_793_20081002(user_number); ·耗数40，基数32 －－结论:在没有执行delete,update,insert into的时候bitmap比正常的索引要有效率2.测试普通组合索引 建立普通组合索引
CREATE index gsm_20081001_ind  on DR_GSM_793_20081001(user_id,user_number);
CREATE index gsm_20081002_ind  on DR_GSM_793_20081002(user_id,user_number);
使用user_number查询并没有用到索引
·耗数84282，基数32
使用user_id查询就用到了索引
·耗数40，基数32

select user_number,opp_user_number,call_type,start_time,duration,lac_id,cell_id,esn from srcb.dr_gsm_793_20081001 where  user_Id>'400010489928' AND user_number>'15907932556'  union  all
select user_number,opp_user_number,call_type,start_time,duration,lac_id,cell_id,esn from srcb.dr_gsm_793_20081002 where  user_id>'400010489928' AND user_number>'15907932556';
查询user_id,user_number按顺序是
·耗费55，基数43
查询user_number,user_id就也使用到索引
·耗费55，基数43

增加索引字段
CREATE index gsm_20081001_ind  on DR_GSM_793_20081001(user_id,user_number,esn);
CREATE  index gsm_20081002_ind  on DR_GSM_793_20081002(user_id,user_number,esn);
查询user_id,user_number按顺序是
·耗费59，基数43


换成每个字段单独建立索引
CREATE index gsm_20081001_ind  on DR_GSM_793_20081001(user_id);
CREATE  index gsm_20081002_ind  on DR_GSM_793_20081002(user_id);
CREATE index gsm_20081003_ind  on DR_GSM_793_20081001(user_number);
CREATE  index gsm_20081004_ind  on DR_GSM_793_20081002(user_number);
按user_id,user_number的顺序
·耗费1548，基数43
按user_number,user_Id的顺序
·耗费1548，基数43

增加单独索引
CREATE index gsm_20081005_ind  on DR_GSM_793_20081001(opp_user_number);
CREATE  index gsm_20081006_ind  on DR_GSM_793_20081002(opp_user_number);
查询的时候没有用的opp_user_number
·耗费1548，基数43

查询的时候用到了opp_user_number
select user_number,opp_user_number,call_type,start_time,duration,lac_id,cell_id,esn from srcb.dr_gsm_793_20081001 where user_number>'15907932556' AND user_Id>'400010489928' AND opp_user_number>'15907932556' UNION ALL
select user_number,opp_user_number,call_type,start_time,duration,lac_id,cell_id,esn from srcb.dr_gsm_793_20081002 WHERE user_number>'15907932556' AND user_Id>'400010489928' AND opp_user_number>'15907932556' ;
·耗费1548，基数43
查询的时候去掉了索引
DROP INDEX gsm_20081005_ind;
DROP INDEX gsm_20081006_ind;
查询多字段，但就一个字段有索引
CREATE  index gsm_20081002_ind  on DR_GSM_793_20081002(user_id);
CREATE index gsm_20081001_ind  on DR_GSM_793_20081001(uSER_ID); 
·耗费1548，基数43
--结论: 1.组合索引中如果没有用的索引的第一个列字段，就不会调用索引；
2.如果都用到组合索引中的2个字段，不管顺序如何，速度都是一样的 ；
3.增加组合索引的索引字段查询会变慢；
4.如果where条件中用到了2个列字段，而且这2个都单独建立了索引，其速度是没有给这2个字段建立组合索引的速度快;
5.如果一个字段有做索引但在where条件中没有使用到是不影响速度的；
6.在where条件中如果用到了多字段，但只有一个字段做个索引，其查询的速度是这个字段所在的位置是没有关系的，就算其它的其它的字段没有做索引，其速度都是以做了索引的字段的查询速度为准;
3.测试索引名的长度
CREATE index dr_gsm_793_20081001_index  on DR_GSM_793_20081001(user_number);
CREATE index dr_gsm_793_20081002_index  on DR_GSM_793_20081002(user_number);
·耗数40，基数32  --
－－结论：说明索引名的长度是没有关系的
4.测试没有where条件下的索引作用
 CREATE index dr_gsm_793_20081001_index  on DR_GSM_793_20081001(user_number);
 查询select * from DR_GSM_793_20081001; 
 －－结论：是全表SCAN,并没有用到索引.
5.建立索引的名称必须是字母开头，而不是数字。
＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
这样分析后，我给每个表都建立了
CREATE bitmap INDEX index1_20081222 ON dr_gsm_793_20081222(USER_NUMBER);
CREATE bitmap INDEX index2_20081222 ON dr_gsm_793_20081222(opp_USER_NUMBER);
CREATE bitmap INDEX index3_20081222 ON dr_gsm_793_20081222(esn);
CREATE bitmap INDEX index4_20081222 ON dr_gsm_793_20081222(netcall_flag);但是我发现在select * from user_ind_columns
INDEX_NAME TABLE_NAME COLUMN_NAME COLUMN_POSITION COLUMN_LENGTH CHAR_LENGTH DESCEND
INDEX1_20081201 DR_GSM_793_20081201 USER_NUMBER 1 15 15 ASC
INDEX2_20081201 DR_GSM_793_20081201 OPP_USER_NUMBER 1 30 30 ASC
INDEX3_20081201 DR_GSM_793_20081201 ESN 1 25 25 ASC
INDEX4_20081201 DR_GSM_793_20081201 NETCALL_FLAG 1 22 0 ASC这个netcall_flag的索引是没有用的，基本没有用到，在user_ind_columns中的char_length是0，是不是索引有问题？
还有这个select * from DR_GSM_793_20081201 where user_number>0也没有用到索引（这个0在user_number中不存在）
但是select * from DR_GSM_793_20081201 where user_number>15907932556就存在（这个15907932556在user_number中不存在）有高手来分析下不，大家讨论下呢

Answer 1

6.测试索引的字段的类型对索引的影响
   表中的user_number是varchar2(15)的类型
   在CREATE bitmap INDEX index1_20081201 ON dr_gsm_793_20081201(USER_NUMBER); 的情况下
   查询select * from dr_gsm_793_20081201 where user_number=15907932556是没有用到索引
但用select * from dr_gsm_793_20081201 where user_number='15907932556'来查询就用到了索引。   在CREATE INDEX index1_20081201 ON dr_gsm_793_20081201(user_number); 的情况下
查询select * from dr_gsm_793_20081201 where user_number=15907932556是没有用到索引
但用select * from dr_gsm_793_20081201 where user_number='15907932556'来查询就用到了索引。我猜想这个索引是不是生效是要在查询的时候使用对应的数据类型？

Answer 2

查询select * from dr_gsm_793_20081201 where user_number=15907932556是没有用到索引 
但用select * from dr_gsm_793_20081201 where user_number='15907932556'来查询就用到了索引。 
是因为ORACLE对15907932556做了转换，user_number上的索引就失效了~

Answer 3

同样的道理，你有一个日期型字段，并且在上面建立了索引，但是你这样查询的话，也是不会用到索引的
select * from table where to_char(rq,'yyyy-mm-dd')='2008-10-01'

Answer 4

在上面我建了个
CREATE bitmap INDEX index4_20081222 ON dr_gsm_793_20081222(netcall_flag);  这个netcall_flag是个number(2)的数据类型，但是
select * from dr_gsm_793_20081222 where netcall_flag=11;却没有用到索引，这个怎么回事呢，这个11和数字31都在数据库中有数据，但好像select * from dr_gsm_793_20081222 where netcall_flag=31; 可以用到索引

Answer 5

我觉的应该不会存在这种问题啊，11和31都是数字啊。你1楼说的很有道理啊，如果是varchar2的话，要用上索引就必顺是字符串,lz对索引测试很详细哦，看了下基本是这么一回事，不过有时候后用>号时最好用>=号，之于为什么我也不太明白。有时候索引用不上时对表进行分析后就能用上了。分析表语句用analyze table table_name compute statistics;

Answer 6

1.select * from dr_gsm_793_20081222 where netcall_flag=11;没有用到索引   EXPLAIN如下:    SELECT STATEMENT, GOAL = ALL_ROWS 44469 2490044 348606160
       TABLE ACCESS FULL SRCB DR_GSM_793_20081222 44469 2490044 3486061602.select NETCALL_FLAG from srcb.dr_gsm_793_20081222 GROUP BY NETCALL_FLAG;有用到索引，且数据如下:
    NETCALL_FLAG
    0
    11
    31
    42EXPLAIN如下:
SELECT STATEMENT, GOAL = ALL_ROWS 406 4 12
 SORT GROUP BY NOSORT 406 4 12
  BITMAP CONVERSION TO ROWIDS 406 9960174 29880522
   BITMAP INDEX FULL SCAN SRCB INDEX4_20081222
有没有达人能解释下，这个还真是搞不懂了

Answer 7

select netcall_flag from dr_gsm_793_20081222 where netcall_flag=11,这样呢

Answer 8

bitmap 索引是针对表中数据字段值量重复性较大的字段建立的，例如你一个表中有个性别的字段，只有男，女，未知三个值，在其上建立一个bitmap索引，查起来比普通的索引很快；
而你的表中字段是个主键，重复概率无 或者很小，那样建立一般的b-tree索引，也就是普通索引，检索的效率会很快；
建议你做的测试的时候对每个你所需要where的字段都做测试，之后再定夺具体用什么索引，具体问题要具体对待，还要分不同时段来测试，以便排除os的资源的因素；
另外建立索引时，系统也是要占用资源的，如磁盘空间，io等；索引太大了，也影响效率；

Answer 9

select netcall_flag from dr_gsm_793_20081222 where netcall_flag=11
这样就用到了索引。我这个库里一张表就是700W的记录，不做DML,只是一个数据仓库，而且没有唯一的值