create index的问题

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  
   
 Q:1）比较两种情况下的检索效率,哪个高?为什么?  
   
   2）数据库会不会根本就是先处理一个限制条件,再处理下一个,如果是这样one_by_one的方式,看来是第1种索引更有效了?      
   
   3） 数据库懂得将   where   dept_name>:n1   and   employee_name>:n2中的dept_name,emplyee_name合并考虑成(dept_name, emplyee_name)再与第2种索引比较吗?  
   
   4） 将where语句的次序打乱又会怎样?  
  where   dept_name>:n1   and   dept_name<:n3   and   employee_name<:n4   and   employee_name>:n2    
   5） 又如果   employee_name只出现一次,   数据库怎样处理呢?  
  where   dept_name>:n1   and   dept_name<:n3   and   employee_name<:n4    
   
   6） 数据库采用怎样的策略   决定   何时或如何   将限制条件合并起来   以利用索引提高效率呢?  
   
 问题： b---在两种情况下,执行下面检索:  
  select   *   from   ta2   where   dept_name>'n1'   and   dept_name<'n3';  
  是否第2种情况的索引对此检索没有帮助呢?

 

1 楼leecooper0918（爱一个人好难）回复于 2003-12-18 21:11:25 得分 0

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

4 楼sunluo（听风的歌）回复于 2003-12-18 23:11:42 得分 10

假设有   员工表: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   的!  

5 楼iwantsay（吵闹）回复于 2003-12-19 01:29:14 得分 40

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

6 楼sunluo（听风的歌）回复于 2003-12-19 09:35:14 得分 0

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

7 楼leecooper0918（爱一个人好难）回复于 2003-12-19 11:28:02 得分 50

简单说说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    
   
         
        在有些情况下，使用全表扫描会比使用索引效率高。这种情况  
        只能具体情况具体分析.  
   

9 楼xili（）回复于 2003-12-19 12:24:40 得分 0

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

10 楼leecooper0918（爱一个人好难）回复于 2003-12-19 14:32:22 得分 0

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