create index探微

Question

create index探微index有利于提高检索效率,
当检索涉及到表的多列时,情况是怎样的呢?假设有 员工表:ta2(employee_id,dept_id,dept_name,employee_name,x1,x2,x3,x4,x5)对ta2 分别用两种方法建索引:
1---建两个索引: create index i1 on ta2(dept_name)  和 create index i2 on ta2(employee_name) 
2---建一个索引: create index i1 on ta2(dept_name,employee_name)
a---在两种情况下,执行下面检索:
select * from ta2 
where dept_name>:n1 and employee_name>:n2 and dept_name<:n3 and employee_name<:n4比较两种情况下的检索效率,哪个高?为什么?数据库会不会根本就是 先处理一个限制条件,再处理下一个,如果是这样one_by_one的方式,看来是第1种索引更有效了?  
(有时候俺会怀疑 第2种情况的创建索引 可能等同于 执行了第1种情况的两条命令)数据库懂得将 where dept_name>:n1 and employee_name>:n2中的dept_name,emplyee_name合并考虑成(dept_name,emplyee_name)再与第2种索引比较吗?将where语句的次序打乱又会怎样?
where dept_name>:n1 and dept_name<:n3 and employee_name<:n4 and employee_name>:n2 
又如果 employee_name只出现一次, 数据库怎样处理呢?
where dept_name>:n1 and dept_name<:n3 and employee_name<:n4 数据库采用怎样的策略 决定 何时或如何 将限制条件合并起来 以利用索引提高效率呢?b---在两种情况下,执行下面检索:
select * from ta2 where dept_name>'n1' and dept_name<'n3';
是否第2种情况的索引对此检索没有帮助呢?

Answer 1

哦，这个问题问的不错。第一: 单一索引是否高效，主要看索引列的distinct值的分布情况。第二:就是关于HINTS. 你可以把两种测试都作完，选一个效率高的，
然后强制使用HINTS就可以了.

Answer 2

lee兄,你太言简意赅,能否详细些?
对hints俺不太明白呀,对distinct值也是俺平时偏重写程序,对数据库的细节方面还请多指教

Answer 3

假设有 员工表:ta2(employee_id,dept_id,dept_name,employee_name,x1,x2,x3,x4,x5)对ta2 分别用两种方法建索引:
1---建两个索引: create index i1 on ta2(dept_name)  和 create index i2 on ta2(employee_name) 
2---建一个索引: create index i1 on ta2(dept_name,employee_name)
a---在两种情况下,执行下面检索:
select * from ta2 
where dept_name>:n1 and employee_name>:n2 and dept_name<:n3 and employee_name<:n4比较两种情况下的检索效率,哪个高?为什么?
答:第二种检索效率高!因为第二种dept_name是第一索引,where时,先定位第二索引再在第一索引范围内检索第二索引!对于where 的条件写的前后顺序对执行顺序的影响,请大家继续计论!
我以前看书,好像记得是,如:
where 1 = 2 and 2 = 3
是先判断 2 = 3 的!我对此也比较空白,请高手指定!

Answer 4

根据我使用的经验。对所有查询条件建复合索引效率很好，对所有查询条件分别建索引没有什么效果。查询条件的次序对索引没有影响。但是如果某个条件使用了 < 、>、<> 或者 like 之类的 ，
该字段应该放在复合索引中的最后一项。

Answer 5

查询条件的次序对索引没有影响。但是如果某个条件使用了 < 、>、<> 或者 like 之类的 ，
该字段应该放在复合索引中的最后一项。我赞同!因为ORACLE本身会对where进行优化处理!真正要解决效率问题,除了优化你ORACLE系统的性能外,优化你本身的SELECT部份也很重要!真的!同一个环境下同一条重杂的查询,往往人家写的要用几秒来处理,而我写出来的不到一秒钟就搞掂!

Answer 6

简单说说HINTS.   select * from  tablename 
   -------------------------- 
   假如tablename表有聚簇索引，会使用这个索引;否则全表扫描.   select * from tablename where col1=1
   ------------------------------------
   col1列如果是主键，执行 INDEX UNIQUE SCAN;
   否则执行 INDEX FAST FULL SCAN.   如果返回的是多个ROWID,则执行INDEX RANGE SCAN.
   还有其他的HINTS: all_rows,first_rows等等.   因此测试SQL时，可以这么些:   select /*+ ALL_ROWS INDEX_FFS(s)  */ 
    e.*, s.effective_date from employee e, sal_history s 
    where e.emp_seq=s.emp_seq and Hiredate =:H    当然也可以这么写:  
    
    select /*+ FIRST_ROWS INDEX_FFS(s)  */ 
     e.*, s.effective_date from employee e, sal_history s 
     where e.emp_seq=s.emp_seq and Hiredate =:H    
   在有些情况下，使用全表扫描会比使用索引效率高。这种情况
   只能具体情况具体分析.

Answer 7

不过仔细想,似乎也不太可能找到一个适合各种情况的策略.
所以数据库就让大家用hints来自己决定.俺过去没用过hints,它是oracle独有的吗?
其他数据库应该有类似的东西,但一定与oracle不同吧?

Answer 8

一般情况下CBO会择优选择最好的查询路径，但是有时候它不一定会
 选择最优算法，在这种情况下，只能自己一步步试了. 还有，表和索引要定期更新统计信息:
   analyze table tablename compute statistics 9i引入了跳跃式索引，这个我没仔细研究过. 查询优化是个很大的话题，有条件的话可以看看<<Oracle performace tuning 101>>
 这本书.