加了日期条件后查询结果慢了100倍

--下面这句秒出,不加use_hash也是秒出。

select /*+use_hash(i j d)*/ count(distinct i.F3070220000101_000048002) 户,count(1) 人
from T3070220000101_000048_V1 i , T3070220000101_000042_V1 j ,dim_area_5lvl d
where i.F3070220000101_000048002=j.F3070220000101_000042003
and i.F3070220000101_000048036='否' and j.F3070220000101_000042040='否'
and nvl(j.F3070220000101_000042036,'正常')<>'减少'
and nvl(i.F3070220000101_000048029,'没有变动')='没有变动'
and substr(i.F3070220000101_000048005,1,12)=d.country_id
and d.city_name_s='清远市';

 

--下面这句过了很久很久才有结果，不在为啥，ij表只有一个时间片的记录.

select /*+use_hash(i j d)*/ count(distinct i.F3070220000101_000048002) 户,count(1) 人
from T3070220000101_000048_V1 i , T3070220000101_000042_V1 j ,dim_area_5lvl d
where i.F3070220000101_000048002=j.F3070220000101_000042003
and i.F3070220000101_000048036='否' and j.F3070220000101_000042040='否'
and nvl(j.F3070220000101_000042036,'正常')<>'减少'
and nvl(i.F3070220000101_000048029,'没有变动')='没有变动'
and substr(i.F3070220000101_000048005,1,12)=d.country_id
and i.F3070220000101_000048001=20190827             -----多加的条件
and j.F3070220000101_000042001=20190827             -----多加的条件
and d.city_name_s='清远市';

转载于:https://www.cnblogs.com/jiangqingfeng/p/11422935.html