大家帮忙看看下面的 sql怎么跑不出来,按说数据量都不是很大的啊,有没有可优化的空间
with tmp_tab as(
select 'CDR_COMP_CALL_$YYYYMMDD' table_name from dual union all
select 'CDR_COMP_SMS_$YYYYMMDD' table_name from dual union all
select 'DW_COMP_ALL_$YYYYMMDD' table_name from dual union all
select 'DIM_CALL_OPPOSITETYPE' table_name from dual union all
select 'DW_BASS_TD_PROV_$YYYYMMDD' table_name from dual union all
select 'DIM_CALL_ROAMTYPE' table_name from dual)
select a.table_name, sum(bytes) / 1024 / 1024
from tmp_tab a
left join user_segments c on regexp_replace(regexp_replace(regexp_replace(regexp_replace(c.segment_name,
'[0-9]{8}',
'$YYYYMMDD'),
'[0-9]{6}',
'$YYYYMM'),
'[0-9]{4}',
'$YYYY'),
'[0-9]{3}',
'$AREACODE') = a.table_name
left join del_his_tab b on replace(replace(replace(b.table_name,
'@YYYYMMDD@',
'$YYYYMMDD'),
'@YYYYMM@',
'$YYYYMM'),
'@AREACODE@',
'$AREACODE') = a.table_name
where (c.segment_type = 'TABLE' or c.segment_type = 'TABLE PARTITION')
group by a.table_name
未命名.JPG (48.91 KB, 下载次数: 0)
2011-4-1 12:27 上传
[本帖最后由 gengzhizhen 于 2011-4-1 12:27 编辑]