关于oracle数据库中进行查询的时候出现效率特别差的一种情况

下面这段代码执行效率是极慢的（根本没执行完成过）：

select /*+ parallel(16) */
z.small_code,trunc(hs.CREATED_AT),sum(hs.COST_SCORE)
from zfs_hpoint_spend_patched_v hs
 join hpoint h
 on h.hpt_id = hs.hpt_id
join zfs_rhaierpointoperationtype_v z
 on z.small_id = h.hpt_attr1
where hs.created_at >= date '2016-09-01'
and hs.created_at < date '2016-10-01'
and z.small_code in ('020301','020302','020501','020503')
group by trunc(hs.CREATED_AT),z.small_code
order by 1,2

 

 

但是下面这段代码的执行时间又很短（不到2分钟）：

select /*+ parallel(16) */
z.small_code,trunc(hs.CREATED_AT),sum(hs.COST_SCORE)
from zfs_hpoint_spend_patched_v hs
 join hpoint h
 on h.hpt_id = hs.hpt_id and hs.created_at >= date '2016-09-01'
and hs.created_at < date '2016-10-01'
join zfs_rhaierpointoperationtype_v z
 on z.small_id = h.hpt_attr1
group by trunc(hs.CREATED_AT),z.small_code
order by 1,2;

 

仔细看一下，第一段代码就比第二段代码多了一个in条件而已。