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

specialco 2008-12-26 10:09:34
索引测试语句
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中不存在）

有高手来分析下不，大家讨论下呢
...全文
384 13 打赏 收藏 转发到动态 举报
写回复
用AI写文章
13 条回复
切换为时间正序
请发表友善的回复…
发表回复
cnham 2009-03-03
  • 打赏
  • 举报
 回复
mark
specialco 2008-12-29
  • 打赏
  • 举报
 回复
select netcall_flag from dr_gsm_793_20081222 where netcall_flag=11
这样就用到了索引。

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

ab5669 2008-12-28
  • 打赏
  • 举报
 回复
mark
JuanRiquelme 2008-12-28
  • 打赏
  • 举报
 回复
select netcall_flag from dr_gsm_793_20081222 where netcall_flag=11,这样呢
icss_zhen 2008-12-27
  • 打赏
  • 举报
 回复
[Quote=引用 6 楼 specialco 的回复:]
在上面我建了个
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; 可以用到索引
[/Quote]我觉的应该不会存在这种问题啊，11和31都是数字啊。你1楼说的很有道理啊，如果是varchar2的话，要用上索引就必顺是字符串,lz对索引测试很详细哦，看了下基本是这么一回事，不过有时候后用>号时最好用>=号，之于为什么我也不太明白。有时候索引用不上时对表进行分析后就能用上了。分析表语句用analyze table table_name compute statistics;
specialco 2008-12-27
  • 打赏
  • 举报
 回复
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 348606160

2.select NETCALL_FLAG from srcb.dr_gsm_793_20081222 GROUP BY NETCALL_FLAG;有用到索引，且数据如下:
NETCALL_FLAG
0
11
31
42

EXPLAIN如下:
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
有没有达人能解释下，这个还真是搞不懂了

zhangyong369 2008-12-26
  • 打赏
  • 举报
 回复
你哦题目么
yf520gn 2008-12-26
  • 打赏
  • 举报
 回复
同样的道理，你有一个日期型字段，并且在上面建立了索引，但是你这样查询的话，也是不会用到索引的
select * from table where to_char(rq,'yyyy-mm-dd')='2008-10-01'
yf520gn 2008-12-26
  • 打赏
  • 举报
 回复
查询select * from dr_gsm_793_20081201 where user_number=15907932556是没有用到索引
但用select * from dr_gsm_793_20081201 where user_number='15907932556'来查询就用到了索引。
是因为ORACLE对15907932556做了转换，user_number上的索引就失效了~
specialco 2008-12-26
  • 打赏
  • 举报
 回复
期待高手就索引的问题做一个详细的说明
specialco 2008-12-26
  • 打赏
  • 举报
 回复
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'来查询就用到了索引。

我猜想这个索引是不是生效是要在查询的时候使用对应的数据类型？
specialco 2008-12-26
  • 打赏
  • 举报
 回复
在上面我建了个
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; 可以用到索引
Oracle入门课程
01、Oracle介绍02、表空间、用户、授权03、DDL数据定义语言04、DML数据操纵语言05、JDBC CRUD操作06、Oracle简单查询07、Oracle伪列08、Oracle聚合函数09、Oracle关联查询、子查询10、Oracle分页11、Oracle单行函数12、Oracle集合运算13、Oracle视图14、Oracle序列15、Oracle索引16、Oracle基本语法17、Oracle数据类型18、Oracle变量19、Oracle运算符20、Oracle条件控制21、Oracle循环22、Oracle循环控制23、Oracle字符串24、Oracle数组25、Oracle存储过程26、Oracle函数27、Oracle游标28、Oracle触发器 
oracle 索引概念、索引相关、sql查询执行计划
文章目录一.索引1.1 索引概念1.2 索引分类1.3 位图索引(企业版oracle)：1.4 B树索引1.5 和分区相关的索引二.索引操作2.1 创建标准索引:2.2 查看表中的索引:2.3 查看索引信息:2.4 分析索引是否存在索引碎片:2.5 重建索引:2.6 删除索引：三.执行计划（explain plan）3.1 执行计划概念3.2 如何查看执行计划3.3 表访问方式 一.索引 1.1 索引概念 索引是与表相关的可选结构，用于提高sql语句执行性能，减少磁盘IO； 索引在逻辑上和物理上都独立于表的
Oracle中的索引
由于索引占用的空间远小于表所占用的实际空间，在系统通过索引进行数据检索时，可先将索引调入内存，通过索引对记录进行定位，大大减少了磁盘/O操作次数，提高检索效率。一般而言，表中的记录数越多，索引带来的效率提高就越明显，所以在数据库系统中，索引是必不可少的数据对象之一。虽然索引对数据库的性能有很大的帮助，但过多、不恰当的索引也可能会产生反作用，导致性能下降。在这种情况下，Oracle会自动为每个索引组织表建立一个基于主键的聚集索引，因为索引组织表本身就是通过主键进行排序的。如果未指定，则使用默认表空间。
oracle 走指定索引,oracle 指定索引
表中如果创建多个索引， 测试发现使用不同索，查询效率有较大差别。 我们希望使用某个更快的索引， 这个时候需要指定索引，以明确告诉oracle使用更快的索引。oracle指定索引语法：/*+index(t ind_name)*/“t”： 为表的别名， 如果查询语句中有表别名，这里必须使用表别名， 如果没有表别名，则直接使用表名“ind_name”： 要使用的索引名称。需要将指定索引的语句写在查询...
Oracle索引——百万级数据测试
百万级数据测试——测试Oracle的索引 一.单一索引 1.首先创建一张表 create table five_million_test ( name varchar2(30), school varchar2(30) ); 2.插入数据 declare begin for i in 1..5000000 loop insert into five_m...
Oracle 高级技术

3,491

社区成员

18,714

社区内容

发帖
与我相关
我的任务
社区描述
Oracle 高级技术相关讨论专区
社区管理员
  • 高级技术社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章