在oracle中代码如下 select t2.home_county, count(distinct t1.user_id)
from user_t2 t2
inner join vpn_t1 t1 on t1.sum_month = 201212
and t1.inure_date <=
to_date(20121231, 'yyyymmdd')
and t1.expire_date >=
to_date(20121231, 'yyyymm')
and t1.user_id = t2.user_id
inner join team_t3 t3 on t3.change_inure_date <=
to_date(20121231, 'yyyymmdd')
and t3.change_expire_date >
to_date(20121231, 'yyyymmdd')
and t3.inure_time <=
to_date(20121231, 'yyyymmdd') + 1
and t3.expire_time >=
to_date(20121231, 'yyyymmdd')
and t3.team_sub_type = 31
and t3.team_id = t1.vpn_group_id
where t2.sum_month = 201212
and t2.user_status < 30
and join_date <= to_date(20121231, 'yyyymmdd')
group by t2.home_county;
查看执行计划,当与team_t3表连接的时候是merge join cartestian
然后把上面代码中user_t2 建成临时表t2,vpn_t1 建成t1临时表, team_t3建成t3临时表,在查看执行计划就变成了hash join 了。建成临时表后的代码如下所示
select t2.home_county, count(distinct t1.user_id)
from t2
inner join t1 on t1.user_id = t2.user_id
inner join t3 on t3.team_id = t1.vpn_group_id
where 1 = 1
group by t2.home_county;
请问这是什么原理?
from user_t2 t2
inner join vpn_t1 t1 on t1.sum_month = 201212
and t1.inure_date <=
to_date(20121231, 'yyyymmdd')
and t1.expire_date >=
to_date(20121231, 'yyyymm')
and t1.user_id = t2.user_id
inner join team_t3 t3 on t3.change_inure_date <=
to_date(20121231, 'yyyymmdd')
and t3.change_expire_date >
to_date(20121231, 'yyyymmdd')
and t3.inure_time <=
to_date(20121231, 'yyyymmdd') + 1
and t3.expire_time >=
to_date(20121231, 'yyyymmdd')
and t3.team_sub_type = 31
and t3.team_id = t1.vpn_group_id
where t2.sum_month = 201212
and t2.user_status < 30
and join_date <= to_date(20121231, 'yyyymmdd')
group by t2.home_county;
查看执行计划,当与team_t3表连接的时候是merge join cartestian
然后把上面代码中user_t2 建成临时表t2,vpn_t1 建成t1临时表, team_t3建成t3临时表,在查看执行计划就变成了hash join 了。建成临时表后的代码如下所示
select t2.home_county, count(distinct t1.user_id)
from t2
inner join t1 on t1.user_id = t2.user_id
inner join t3 on t3.team_id = t1.vpn_group_id
where 1 = 1
group by t2.home_county;
请问这是什么原理?
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货