SELECT t3.h_city
,t3.h_a_code
,t3.c_a_code
,t3.b_id
,COUNT(DISTINCT t1.userid)
FROM
tmp_bam_ochat_order_m_xzw t1
INNER JOIN tmp_bam_ochat_cactive_m_xzw t2
ON t1.userid = t2.userid
AND t2.s_month = 201108
INNER join tmp_bam_ochat_user_m_xzw t3
ON t1.userid = t3.userid
AND t3.u_status < 30
AND t3.j_date <= TO_DATE('2011-08-31','yyyy-mm-dd')
AND t3.s_month = 201108
WHERE t1.s_month =201108
GROUP By t3.h_city,t3.h_a_code,t3.c_a_code,t3.b_id
t1,t2,t3表都有建索引USERID,而且也都有分区,分区字段是s_month,t1,t2数据量是几十万,t3数据量是6百多万。
但发现跑起来很慢,执行计划出现笛卡尔积,内容如下:
--------------------------------------------------------------------------------------------------------------------------
| Id | Operation