下面这段代码,哪些地方有优化的空间,请大侠们指教
INSERT INTO ecp_user_type_teltra(times,prov_name,area_name,company_num,user_num,user_vediomeet_num,tel_num,tel_local_time,tel_city_time,message_num,vediomeet_time,vediomeet_num,netfax_time)
SELECT '''||'2012-05-06'||'至'||'2012-06-05'||''',
DECODE(GROUPING(prov_name),1,''hj,合计'',prov_name) prov_name,
DECODE(GROUPING(area_name),1,''hj,合计'',area_name) area_name,
COUNT(DISTINCT t.companyid) company_num,COUNT(DISTINCT t.phonecode) user_num,
COUNT(DISTINCT (CASE WHEN t.vediomeetnum>=1 THEN t.phonecode END)) user_vediomeet_num,
SUM(t.telnum) tel_num,
SUM(t.localtimebymin) tel_local_time,
SUM(t.cttimebymin) tel_city_time,
SUM(t.messagenum) message_num,
SUM(t.vediomeettimebymin) vediomeet_time,
SUM(t.vediomeetnum) vediomeet_num,
SUM(t.netfaxtimebymin) netfax_time
FROM (SELECT b.prov_code||'',''||b.prov_name prov_name,b.area_code||'',''||b.area_name area_name,a.phonecode,a.companyid,a.telnum,a.localtimebymin,a.cttimebymin,a.messagenum,a.vediomeettimebymin,a.vediomeetnum,a.netfaxtimebymin
FROM (SELECT e.*,DECODE(e.userarea,''0732'',''0731'',''0733'',''0731'',e.userarea) area_code FROM ecp_userteltra e WHERE e.optime BETWEEN '''||'2012-05-06'||''' AND '''||'2012-06-05'||''') a,
(SELECT p.col_id prov_code,p.col_name prov_name,c.col_area_code area_code,c.col_name area_name FROM ecp_cw_area p,ecp_cw_area c WHERE p.col_id=c.col_level2_id'||areasql||') b
WHERE b.area_code=a.area_code(+)
) t
GROUP BY ROLLUP(t.prov_name,t.area_name)
INSERT INTO ecp_user_type_teltra(times,prov_name,area_name,company_num,user_num,user_vediomeet_num,tel_num,tel_local_time,tel_city_time,message_num,vediomeet_time,vediomeet_num,netfax_time)
SELECT '''||'2012-05-06'||'至'||'2012-06-05'||''',
DECODE(GROUPING(prov_name),1,''hj,合计'',prov_name) prov_name,
DECODE(GROUPING(area_name),1,''hj,合计'',area_name) area_name,
COUNT(DISTINCT t.companyid) company_num,COUNT(DISTINCT t.phonecode) user_num,
COUNT(DISTINCT (CASE WHEN t.vediomeetnum>=1 THEN t.phonecode END)) user_vediomeet_num,
SUM(t.telnum) tel_num,
SUM(t.localtimebymin) tel_local_time,
SUM(t.cttimebymin) tel_city_time,
SUM(t.messagenum) message_num,
SUM(t.vediomeettimebymin) vediomeet_time,
SUM(t.vediomeetnum) vediomeet_num,
SUM(t.netfaxtimebymin) netfax_time
FROM (SELECT b.prov_code||'',''||b.prov_name prov_name,b.area_code||'',''||b.area_name area_name,a.phonecode,a.companyid,a.telnum,a.localtimebymin,a.cttimebymin,a.messagenum,a.vediomeettimebymin,a.vediomeetnum,a.netfaxtimebymin
FROM (SELECT e.*,DECODE(e.userarea,''0732'',''0731'',''0733'',''0731'',e.userarea) area_code FROM ecp_userteltra e WHERE e.optime BETWEEN '''||'2012-05-06'||''' AND '''||'2012-06-05'||''') a,
(SELECT p.col_id prov_code,p.col_name prov_name,c.col_area_code area_code,c.col_name area_name FROM ecp_cw_area p,ecp_cw_area c WHERE p.col_id=c.col_level2_id'||areasql||') b
WHERE b.area_code=a.area_code(+)
) t
GROUP BY ROLLUP(t.prov_name,t.area_name)
解决方案 »
- ORACLE10G,Oracle Directory Manager连接不上?????????
- ORACLE触发器如何实现一张表中INSERT了新的数据,也往另外一张表中INSERT这些数据?
- 求一解析字段内分解字符转换的函数
- oracle中如何读写磁盘文件?
- 请教关联查询时产生重复的列内容怎么解决
- 多个存储过程或包,可以运行脚本一次更新吗?怎么做?
- JIoracle里怎么显示有几个数据库?MYSQL里是用SHOW DATABASES;
- select a.* from db1an a where db1_1 in
- 关于更改数据记录的日期和时间问题?
- sql语句请教
- 求推荐一本ORACLE的书籍
- 求简单的描述Oracle怎样控制数据访问?
表ecp_userteltra 有7000万的数据,存放26个月的数据.如果根据日期字段建一个索引,是否会提升性能
这个看样子是时间的数据,为什么用字串存储了?