物化内连接视图:

with t1 as

(select /*+ materialize */
a.user_name, a.invest_id
from base_data_login_info@agent a
where a.str_day <= '20160304' and a.str_day >= '20160301'
and a.channel_id in (select channel_rlat from tb_user_channel a, tb_channel_info b where a.channel_id = b.channel_id and a.user_id = 5002)
and a.platform = a.platform)
select count(distinct user_name) ,count(distinct invest_id) from t1;

总结：1>对于连接condition column,in条件column ,数据分布高度不平衡的列,

count(distinct colmn1)需要放在最外层。

2> with t1 as /*+ materialize */ 对数据不平衡比较有效。

ITPub博客

sql tunning1 物化内连接视图: