ارجو المساعده اذا سمحتم

(select sub_service_id,ss1.e_name,sub_month,subs,unsub_service_id,ss2.e_name,unsub_month,unsubs,(select count(1) from subscribers_1 where sub_date between

to_date('''||p_start_date||''',''dd-mm-rrrr'') and to_date('''||p_end_date||''',''dd-mm-rrrr'') and service_id=sub_service_id) subs_period,(select count(1)
from subscribers_1 where service_id=sub_service_id) total_subs from

(select * from

(select service_id sub_service_id,to_char(sub_date,''mm-rrrr'') sub_month,count(1) subs from subscribers_log_1 where sub_date between to_date
('''||p_start_date||''',''dd-mm-rrrr'') and to_date('''||p_end_date||''',''dd-mm-rrrr'') and service_id in (select id from services where active=1) group by
service_id,to_char(sub_date,''mm-rrrr''))

full outer join

(select service_id unsub_service_id,to_char(unsub_date,''mm-rrrr'') unsub_month,count(1) unsubs from subscribers_log_1 where sub_date between
to_date('''||p_start_date||''',''dd-mm-rrrr'') and to_date('''||p_end_date||''',''dd-mm-rrrr'') and unsub_date between to_date('''||p_start_date||''',''dd-
mm-rrrr'') and to_date('''||p_end_date||''',''dd-mm-rrrr'') and service_id in (select id from services where active=1) group by service_id,to_char
(unsub_date,''mm-rrrr''))

on (sub_service_id=unsub_service_id and sub_month=unsub_month)) left outer join services ss1 on (ss1.id=sub_service_id) left outer join services
ss2 on (ss2.id=unsub_service_id))

union

(select sub_service_id,ss1.e_name,sub_month,subs,unsub_service_id,ss2.e_name,unsub_month,unsubs,(select count(1) from subscribers_1@zainir where
sub_date between to_date('''||p_start_date||''',''dd-mm-rrrr'') and to_date('''||p_end_date||''',''dd-mm-rrrr'') and service_id=sub_service_id) subs_period,
(select count(1) from subscribers_1@zainir where service_id=sub_service_id) total_subs from

(select * from

(select service_id sub_service_id,to_char(sub_date,''mm-rrrr'') sub_month,count(1) subs from subscribers_log_1@zainir where sub_date between
to_date('''||p_start_date||''',''dd-mm-rrrr'') and to_date('''||p_end_date||''',''dd-mm-rrrr'') and service_id in (select id from services@zainir where
active=1) group by service_id,to_char(sub_date,''mm-rrrr''))

full outer join

(select service_id unsub_service_id,to_char(unsub_date,''mm-rrrr'') unsub_month,count(1) unsubs from subscribers_log_1@zainir where sub_date
between to_date('''||p_start_date||''',''dd-mm-rrrr'') and to_date('''||p_end_date||''',''dd-mm-rrrr'') and unsub_date between to_date('''||
p_start_date||''',''dd-mm-rrrr'') and to_date('''||p_end_date||''',''dd-mm-rrrr'') and service_id in (select id from services@zainir where active=1) group
by service_id,to_char(unsub_date,''mm-rrrr''))

on (sub_service_id=unsub_service_id and sub_month=unsub_month)) left outer join services@zainir ss1 on (ss1.id=sub_service_id) left outer join
services@zainir ss2 on (ss2.id=unsub_service_id));

كيف ممكن ازيد الفعاليه وسرعه هذا الكود بنفس النتيجه فقط التغيير لزياده الperformance