多表连接索引问题

Question

语句:
select a.id,
       a.title,
       a.content,
       a.fname,
       a.fphone,
       a.ftype,
       a.stype,
       a.bh,
       d.name,
       a.result,
       a.re,
       a.cdate,
       a.pdate,
       a.state,
       a.userid
  from n_flow_service a
  left outer join dept d on a.deptid = d.id
 where 1 = 1
 and STATE = 6
 and cdate>to_date('2000-1-1','yyyy-MM-dd')
 and a.stype <>15 and a.stype<>16 and a.stype<>17
 AND BH like '3309%'
 order by cdate descn_flow_service表索引:id(unique),cdate,state,stype,bh,state-stype-bh
dept表索引:id(unique)执行计划:select statement,goal=choose
           sort order by             object name         cost
             hash join outer
                table access full    n_flow_service       21
                   index range scan  state-stype-bh       10
                table access full    dept                  4执行需要时间0.453s当我在语句中再加一个条件 and cdate>to_date('2000-1-1','yyyy-MM-dd')再执行时
执行计划:select statment,goal=choose         object name       cost
           nested loops outer
             table access by index rowid      n_flow_service    6
                index range scan descending    cdate            2
             table access by index rowid       dept             1
                index unique scan              id               
执行时间:0.094s现在想请问:为什么我加入一个and cdate>to_date('2000-1-1','yyyy-MM-dd')后连接方式变了,执行计划也变了.执行时间更是大大的缩短了.搞不懂.
哪位大大给我讲解下这条语句在加那个 cdate>....语句的前后的执行顺序变掉和是否走索引变掉的原因.
在下不胜感谢啊!!!!当我在语句中加如下面一句:

Answer 1

怎么你前面那条sql也有and cdate>to_date('2000-1-1','yyyy-MM-dd')啊？

Answer 2

也就是想问 加and cdate>to_date('2000-1-1','yyyy-MM-dd') 前后执行计划不同的原因

Answer 3

数据量改变了，不加and cdate>to_date('2000-1-1','yyyy-MM-dd')前结果集大，加了后，a.cdate上有索引，正好可以用上。且加了后过滤后结果集小，所以ORACLE根据计算，选择合适的连接方式。

Answer 4

楼上说的对,主要是过滤后结果集发生了改变,导致连接算法发生改变,oracle  会自动选择成本最低的执行计划.

Answer 5

不加之前 那写where后的字段也都是有索引的啊  为什么table access full n_flow_service

Answer 6

不写日期之前没用索引是因为
1， state上如果有索引的话就是这个索引的选择性太差，比如10000条记录只有10个不同值，oracle不会使用这样的索引。
and STATE = 6
2， <> 这种连接符不会使用索引，同样因为这样的条件过滤性太低
and a.stype <>15 and a.stype<>16 and a.stype<>173，这个条件倒是有可能导致使用索引，但要看有没有以BH为前导列的索引存在，也就是说BH上要有索引，并且必须是BH为索引的第一个列。
AND BH like '3309%'

Answer 7

请问这未大哥 你说的第三条是什么意思,什么叫要有BH为前导列的索引存在,并且必须是BH为索引的第一个列?我的BH上已经有索引了,还要有什么条件?能具体解释下么?

Answer 8

state-stype-bh这个索引是这几列cdate,state,stype,bh的联合索引吗？1、很明显，第一种写法导致两个表都是全表扫描（是因为如果上面如果是组合索引，虽然有state,bh列等条件，但索引并不以state,bh等开头，所以索引无法用上，必须分别建索引，才有可能用上。另外，<>条件是不能用上索引的，无论stype上建单独索引或者联合索引），并且还要进行索引扫描，这个成本很高；
2、第二种写用到了索引并且明显通过索引来直接访问rowid，效率最高

Answer 9

晕死 我就是不知道第二种写法为什么会走索引啊.问了半天......哎
第一张human表97万数据 第二张dept表2000左右数据

Answer 10

这么说吧，oracle很智能，它会选择最优的执行计划来完成查询工作，即使同样的sql，在数据量、数据分布等因素的影响下，也可能会产生不同的执行计划。何况不同的sql呢？你的第一个查询结果集偏大，通过索引检索还不如全表扫描快，因此oracle就不走索引了。