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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 |
--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; |