oracle 归档数据查询（分区查询）

查询201212月份数据记录的SQL如下：

SELECT COUNT(DISTINCT(c.caller_nbr)) AS emp_use_count  FROM tab_pub_calllog c WHERE TO_CHAR(c.end_time,'yyyymm')='201212'

等了2个小时还没结果...

找数据库维护人员解决，给出如下查询SQL语句

SELECT COUNT(DISTINCT(emp_use_count)) FROM ( 
SELECT DISTINCT(c.caller_nbr) AS emp_use_count  FROM tab_pub_calllog_his PARTITION(p_20121201) c 
UNION ALL 
SELECT DISTINCT(c.caller_nbr) AS emp_use_count  FROM tab_pub_calllog_his PARTITION(p_20121202) c 
UNION ALL 
SELECT DISTINCT(c.caller_nbr) AS emp_use_count  FROM tab_pub_calllog_his PARTITION(p_20121203) c 
UNION ALL 
SELECT DISTINCT(c.caller_nbr) AS emp_use_count  FROM tab_pub_calllog_his PARTITION(p_20121204) c 
UNION ALL 
SELECT DISTINCT(c.caller_nbr) AS emp_use_count  FROM tab_pub_calllog_his PARTITION(p_20121205) c 
UNION ALL 
SELECT DISTINCT(c.caller_nbr) AS emp_use_count  FROM tab_pub_calllog_his PARTITION(p_20121206) c 
UNION ALL 
SELECT DISTINCT(c.caller_nbr) AS emp_use_count  FROM tab_pub_calllog_his PARTITION(p_20121207) c 
UNION ALL 
SELECT DISTINCT(c.caller_nbr) AS emp_use_count  FROM tab_pub_calllog_his PARTITION(p_20121208) c 
UNION ALL 
SELECT DISTINCT(c.caller_nbr) AS emp_use_count  FROM tab_pub_calllog_his PARTITION(p_20121209) c 
UNION ALL 
SELECT DISTINCT(c.caller_nbr) AS emp_use_count  FROM tab_pub_calllog_his PARTITION(p_20121210) c 
UNION ALL 
SELECT DISTINCT(c.caller_nbr) AS emp_use_count  FROM tab_pub_calllog_his PARTITION(p_20121211) c 
UNION ALL 
SELECT DISTINCT(c.caller_nbr) AS emp_use_count  FROM tab_pub_calllog_his PARTITION(p_20121212) c 
UNION ALL 
SELECT DISTINCT(c.caller_nbr) AS emp_use_count  FROM tab_pub_calllog_his PARTITION(p_20121213) c 
UNION ALL 
SELECT DISTINCT(c.caller_nbr) AS emp_use_count  FROM tab_pub_calllog_his PARTITION(p_20121214) c 
UNION ALL 
SELECT DISTINCT(c.caller_nbr) AS emp_use_count  FROM tab_pub_calllog_his PARTITION(p_20121215) c 
UNION ALL 
SELECT DISTINCT(c.caller_nbr) AS emp_use_count  FROM tab_pub_calllog_his PARTITION(p_20121216) c 
UNION ALL 
SELECT DISTINCT(c.caller_nbr) AS emp_use_count  FROM tab_pub_calllog_his PARTITION(p_20121217) c 
UNION ALL 
SELECT DISTINCT(c.caller_nbr) AS emp_use_count  FROM tab_pub_calllog_his PARTITION(p_20121218) c 
UNION ALL 
SELECT DISTINCT(c.caller_nbr) AS emp_use_count  FROM tab_pub_calllog_his PARTITION(p_20121219) c 
UNION ALL 
SELECT DISTINCT(c.caller_nbr) AS emp_use_count  FROM tab_pub_calllog_his PARTITION(p_20121220) c 
UNION ALL 
SELECT DISTINCT(c.caller_nbr) AS emp_use_count  FROM tab_pub_calllog_his PARTITION(p_20121221) c 
UNION ALL 
SELECT DISTINCT(c.caller_nbr) AS emp_use_count  FROM tab_pub_calllog_his PARTITION(p_20121222) c 
UNION ALL 
SELECT DISTINCT(c.caller_nbr) AS emp_use_count  FROM tab_pub_calllog_his PARTITION(p_20121223) c 
UNION ALL 
SELECT DISTINCT(c.caller_nbr) AS emp_use_count  FROM tab_pub_calllog_his PARTITION(p_20121224) c 
UNION ALL 
SELECT DISTINCT(c.caller_nbr) AS emp_use_count  FROM tab_pub_calllog_his PARTITION(p_20121225) c 
UNION ALL 
SELECT DISTINCT(c.caller_nbr) AS emp_use_count  FROM tab_pub_calllog_his PARTITION(p_20121226) c 
UNION ALL 
SELECT DISTINCT(c.caller_nbr) AS emp_use_count  FROM tab_pub_calllog_his PARTITION(p_20121227) c 
UNION ALL 
SELECT DISTINCT(c.caller_nbr) AS emp_use_count  FROM tab_pub_calllog_his PARTITION(p_20121228) c 
UNION ALL 
SELECT DISTINCT(c.caller_nbr) AS emp_use_count  FROM tab_pub_calllog_his PARTITION(p_20121229) c 
UNION ALL 
SELECT DISTINCT(c.caller_nbr) AS emp_use_count  FROM tab_pub_calllog_his PARTITION(p_20121230) c 
UNION ALL 
SELECT DISTINCT(c.caller_nbr) AS emp_use_count  FROM tab_pub_calllog_his PARTITION(p_20121231) c )

执行不到6秒，结果就出来了，真是够强悍的！