إنتقال للمحتوى

  • تسجيل الدخول عبر الفيس بوك تسجيل الدخول عبر تويتر Log In with LinkedIn Log In with Google      تسجيل دخول    
  • إنشاء حساب

صورة
- - - - -

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

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

لا توجد ردود على هذا الموضوع

#1 mohammad badarneh

mohammad badarneh

    عضو

  • الأعضــاء
  • 16 مشاركة
  • البـلـد: Country Flag

تاريخ المشاركة 14 November 2012 - 11:35 AM

(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





موضوعات أخرى ذات صلة بـ ارجو, المساعده, اذا, سمحتم