With SQL CASE performance tuning you can dramatically improve execution time of your code with proper use of CASE function. Tuning programming code with CASE function in SQL query in example below is one of the best examples.
Procedure for tuning:
BEGIN
--ALL MO MMS
lc_sql := ' insert into SQL_STATISTIC.MONTHLY_STATISTICS ( '||
' DATUM, ALL_MO_MMS) '||
' ( select /*+ full(m) */ '||
' to_char(to_date(substr(local_incoming_time, 1, 8), ''yyyymmdd''), ''dd.mm.yyyy'') datum, '||
' count(distinct msg_id) value '||
' from SQL_RECORDS.CDR m '||
' where success_indicator = 128 '||
' and (oiwtype = 0 '||
' and diwtype in ( 0, 450, 470 )) '||
' and local_incoming_time like '''||to_char(add_months(sysdate,-1), 'yyyymm')||'%'' '||
' group by to_char(to_date(substr(local_incoming_time, 1, 8), ''yyyymmdd''), ''dd.mm.yyyy'')) ';
execute immediate lc_sql;
commit;
--ALL MT MMS
OPEN cv FOR ' select /*+ full(m) */ '||
' to_char(to_date(substr(local_incoming_time, 1, 8), ''yyyymmdd''), ''dd.mm.yyyy'') datum, '||
' count(distinct msg_id) value '||
' from SQL_RECORDS.CDR m '||
' where success_indicator = 128 '||
' and oiwtype in (0, 451, 710) '||
' and diwtype = 0 '||
' and local_incoming_time like '''||to_char(add_months(sysdate,-1), 'yyyymm')||'%'' '||
' group by to_char(to_date(substr(local_incoming_time, 1, 8), ''yyyymmdd''), ''dd.mm.yyyy'') ';
LOOP
FETCH cv INTO ld_datum, ln_count;
EXIT WHEN cv%NOTFOUND;
UPDATE SQL_STATISTIC.MONTHLY_STATISTICS SET ALL_MT_MMS = ln_count
WHERE datum = ld_datum;
END LOOP;
CLOSE cv;
--ALL MMS DEGRADED
OPEN cv FOR ' select /*+ full(m) */ '||
' to_char(to_date(substr(local_incoming_time, 1, 8), ''yyyymmdd''), ''dd.mm.yyyy'') datum, '||
' count(distinct msg_id) value '||
' from SQL_RECORDS.CDR m '||
' where success_indicator = 128 '||
' and (oiwtype = 0 '||
' and diwtype in ( 0, 450, 470 )) '||
' and (( sender_charging_type=1 and sender_prepaid_status=2 ) or '||
' ( sender_charging_type=99 and sender_prepaid_status=0 ) or '||
' ( sender_charging_type=0 and sender_prepaid_status=0 ) '||
' ) '||
' and local_incoming_time like '''||to_char(add_months(sysdate,-1), 'yyyymm')||'%'' '||
' group by to_char(to_date(substr(local_incoming_time, 1, 8), ''yyyymmdd''), ''dd.mm.yyyy'') ';
LOOP
FETCH cv INTO ld_datum, ln_count;
EXIT WHEN cv%NOTFOUND;
UPDATE SQL_STATISTIC.MONTHLY_STATISTICS SET MMS_DEGRADED = ln_count
WHERE datum = ld_datum;
END LOOP;
CLOSE cv;
UPDATE SQL_STATISTIC.MONTHLY_STATISTICS SET MMS_DEGRADED = 0
WHERE MMS_DEGRADED is null;
commit;
--OTHER_OPERATOR MO MMS
OPEN cv FOR ' select /*+ full(m) */ '||
' to_char(to_date(substr(local_incoming_time, 1, 8), ''yyyymmdd''), ''dd.mm.yyyy'') datum, '||
' count(distinct msg_id) value '||
' from SQL_RECORDS.CDR m '||
' where success_indicator = 128 '||
' and orig_imsi like ''43370029%'' '||
' and (oiwtype = 0 '||
' and diwtype in ( 0, 450, 470 )) '||
' and local_incoming_time like '''||to_char(add_months(sysdate,-1), 'yyyymm')||'%'' '||
' group by to_char(to_date(substr(local_incoming_time, 1, 8), ''yyyymmdd''), ''dd.mm.yyyy'') ';
LOOP
FETCH cv INTO ld_datum, ln_count;
EXIT WHEN cv%NOTFOUND;
UPDATE SQL_STATISTIC.MONTHLY_STATISTICS SET OTHER_OPERATOR_MO_MMS = ln_count
WHERE datum = ld_datum;
END LOOP;
CLOSE cv;
--OTHER_OPERATOR MT MMS - delivery (in submit we do not have DEST_IMSI)
OPEN cv FOR ' select /*+ full(m) */ '||
' to_char(to_date(substr(local_incoming_time, 1, 8), ''yyyymmdd''), ''dd.mm.yyyy'') datum, '||
' count(distinct msg_id) value '||
' from SQL_RECORDS.CDR m '||
' where success_indicator = 134 '||
' and dest_imsi like ''43370029%'' '||
' and oiwtype in (0, 451, 710) '||
' and diwtype = 0 '||
' and local_incoming_time like '''||to_char(add_months(sysdate,-1), 'yyyymm')||'%'' '||
' group by to_char(to_date(substr(local_incoming_time, 1, 8), ''yyyymmdd''), ''dd.mm.yyyy'') ';
LOOP
FETCH cv INTO ld_datum, ln_count;
EXIT WHEN cv%NOTFOUND;
UPDATE SQL_STATISTIC.MONTHLY_STATISTICS SET OTHER_OPERATOR_MT_MMS = ln_count
WHERE datum = ld_datum;
END LOOP;
CLOSE cv;
--OTHER_OPERATOR MMS DEGRADED
OPEN cv FOR ' select /*+ full(m) */ '||
' to_char(to_date(substr(local_incoming_time, 1, 8), ''yyyymmdd''), ''dd.mm.yyyy'') datum, '||
' count(distinct msg_id) value '||
' from SQL_RECORDS.CDR m '||
' where success_indicator = 128 '||
' and orig_imsi like ''43370029%'' '||
' and (oiwtype = 0 '||
' and diwtype in ( 0, 450, 470 )) '||
' and (( sender_charging_type=1 and sender_prepaid_status=2 ) or '||
' ( sender_charging_type=99 and sender_prepaid_status=0 ) or '||
' ( sender_charging_type=0 and sender_prepaid_status=0 ) '||
' ) '||
' and local_incoming_time like '''||to_char(add_months(sysdate,-1), 'yyyymm')||'%'' '||
' group by to_char(to_date(substr(local_incoming_time, 1, 8), ''yyyymmdd''), ''dd.mm.yyyy'') ';
LOOP
FETCH cv INTO ld_datum, ln_count;
EXIT WHEN cv%NOTFOUND;
UPDATE SQL_STATISTIC.MONTHLY_STATISTICS SET OTHER_OPERATOR_DEGRADED = ln_count
WHERE datum = ld_datum;
END LOOP;
CLOSE cv;
UPDATE SQL_STATISTIC.MONTHLY_STATISTICS SET OTHER_OPERATOR_DEGRADED = 0
WHERE OTHER_OPERATOR_DEGRADED is null;
commit;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
Upper procedure is tuned below with SQL CASE tuning example:
--MMS
BEGIN
--ALL MO MMS + ALL MT MMS + ALL MMS DEGRADED + OTHER_OPERATOR MO MMS + OTHER_OPERATOR MT MMS - delivery (in submit we do not have DEST_IMSI) + OTHER_OPERATOR MMS DEGRADED
lc_sql := ' insert into SQL_STATISTIC.MONTHLY_STATISTICS ( '||
' DATUM, ALL_MO_MMS, ALL_MT_MMS, MMS_DEGRADED, '||
' OTHER_OPERATOR_MO_MMS, OTHER_OPERATOR_MT_MMS, OTHER_OPERATOR_DEGRADED)'||
' ( select /*+ full(m) */ '||
' to_char (to_date (substr (local_incoming_time, 1, 8), ''yyyymmdd''),''dd.mm.yyyy'') datum, '||
' count(distinct( case '|| --ALL MO MMS
' when (success_indicator = 128 '||
' and oiwtype = 0 and diwtype in (0, 450, 470)) '||
' then msg_id '||
' end)) all_mo_mms_count, '||
' count(distinct( case '|| --ALL MT MMS
' when (success_indicator = 128 '||
' and oiwtype in (0, 451, 710) and diwtype = 0) '||
' then msg_id '||
' end)) all_mt_mms_count, '||
' count(distinct( case '|| --ALL MMS DEGRADED
' when ((success_indicator = 128 '||
' and oiwtype = 0 and diwtype in (0, 450, 470)) '||
' and ((sender_charging_type = 1 and sender_prepaid_status = 2) '||
' or (sender_charging_type = 99 and sender_prepaid_status = 0) '||
' or (sender_charging_type = 0 and sender_prepaid_status = 0))) '||
' then msg_id '||
' end)) all_degraded_mms_count, '||
' count(distinct( case '|| --OTHER_OPERATOR MO MMS
' when (orig_imsi like ''43370029%'' '||
' and success_indicator = 128 '||
' and (oiwtype = 0 and diwtype in (0, 450, 470))) '||
'then msg_id '||
' end)) OTHER_OPERATOR_mo_mms_count, '||
' count(distinct( case '|| --OTHER_OPERATOR MT MMS - delivery (in submit we do not have DEST_IMSI)
' when (dest_imsi like ''43370029%'' '||
' and success_indicator = 134 '||
' and oiwtype in (0, 451, 710) '||
' and diwtype = 0) '||
' then msg_id '||
' end)) OTHER_OPERATOR_mt_mms_count, '||
' count(distinct( case '|| --OTHER_OPERATOR MMS DEGRADED
' when (orig_imsi like ''43370029%'' '||
' and success_indicator = 128 '||
' and (oiwtype = 0 and diwtype in (0, 450, 470)) '||
' and ( (sender_charging_type = 1 and sender_prepaid_status = 2) '||
' or (sender_charging_type = 99 and sender_prepaid_status = 0) '||
' or (sender_charging_type = 0 and sender_prepaid_status = 0))) '||
' then msg_id '||
' end)) OTHER_OPERATOR_degraded_mms_count '||
' from SQL_RECORDS.CDR m '||
' where local_incoming_time like '''||to_char(add_months(sysdate,-1), 'yyyymm')||'%'' '||
' group by to_char (to_date (substr (local_incoming_time, 1, 8), ''yyyymmdd''),''dd.mm.yyyy'') ) ';
execute immediate lc_sql;
commit;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;