Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Запрос в группировкой в join  [new]
Shtock
Member

Откуда: СПб
Сообщений: 3049
 		Господа, есть табличка party, activity и табличка party_activities (для реализации m:n на party_activity). В одной из вьюх надо вывести по party ряд колонок с её activity.

Сейчас я сделал так:

select party_id,
       nvl(gra_l3a.life_ass_code, 'N') L3A_LIFE_ASSURANCE,
       nvl(gra_l3a.inv_b_code, 'N') L3A_INVESTMENT_BONDS,
       nvl(gra_l3a.isas_code, 'N') L3A_ISAS,
       nvl(gra_l3a.ut_code, 'N') L3A_UNIT_TRUSTS,
       nvl(gra_l3a.it_code, 'N') L3A_INVESTMENT_TRUSTS,
       nvl(gra_l3a.rp_code, 'N') L3A_RETIREMENT_PLANNING,
       nvl(gra_l3a.di_code, 'N') L3A_DIRECT_INVESTMENTS,
       nvl(gra_l3a.moi_code, 'N') L3A_MANAGEMENT_OF_INVESTMENTS,
       nvl(gra_l3a.cf_code, 'N') L3A_CORPORATE_FINANCE,
       nvl(gra_l3a.tei_code, 'N') L3A_TAX_EFFICIENT_INVESTMENTS,
       nvl(gra_l3a.te_code, 'N') L3A_TRADED_ENDOWMENTS,
       nvl(gra_l3a.pp_code, 'N') L3A_PERSONAL_PENSIONS,
       nvl(gra_l3a.gp_code, 'N') L3A_GROUP_PENSIONS,
       nvl(gra_l3a.rm_code, 'N') L3A_RESIDENTIAL_MORTGAGES,
       nvl(gra_l3a.cm_code, 'N') L3A_COMMERCIAL_MORTGAGES,
       nvl(gra_l3a.gpr_code, 'N') L3A_GROUP_PROTECTION,
       nvl(gra_l3a.ppr_code, 'N') L3A_PERSONAL_PROTECTION,
       nvl(gra_l3a.lgc_code, 'N') L3A_LONGTERM_CARE,
       nvl(gra_l3a.ann_code, 'N') L3A_ANNUITIES,
       nvl(gra_l3a.eis_code, 'N') L3A_EIS,
       nvl(gra_l3a.ethi_code, 'N') L3A_ETHICAL_INVESTMENTS,
       nvl(gra_l3a.ofs_code, 'N') L3A_OFFSHORE_INVESTMENTS,
       nvl(gra_l3a.pri_code, 'N') L3A_PROPERTY_INVESTMENTS,
       nvl(gra_l3a.vct_code, 'N') L3A_VCT,
       nvl(gra_l3a.sipp_code, 'N') L3A_SIPP,
       nvl(gra_l3a.eqr_code, 'N') L3A_EQUITY_RELEASE,
       nvl(gra_l3a.strp_code, 'N') L3A_STRUCTURED_PRODUCTS

from party p

     
     left outer join
                    (
                      select pa.party_id,
                             pa.outlet_id,
                             max(case when pa.activity_id = 2281 then alc.CODE end) life_ass_code, --l3 life assurance
                             max(case when pa.activity_id = 103 then alc.CODE end) inv_b_code, --l3 investment bonds
                             max(case when pa.activity_id = 109 then alc.CODE end) isas_code, --l3 isas
                             max(case when pa.activity_id = 106 then alc.CODE end) ut_code, --l3 unit trusts
                             max(case when pa.activity_id = 107 then alc.CODE end) it_code, --l3 investment trusts
                             max(case when pa.activity_id = 2289 then alc.CODE end) rp_code, --l3 retirement planing
                             max(case when pa.activity_id = 2287 then alc.CODE end) di_code, --l3 direct investment
                             max(case when pa.activity_id = 2297 then alc.CODE end) moi_code, --l3 management of investment
                             max(case when pa.activity_id = 2284 then alc.CODE end) cf_code, --l3 corporate finance
                             max(case when pa.activity_id = 2291 then alc.CODE end) tei_code, --l3 tax efficient investment
                             max(case when pa.activity_id = 2286 then alc.CODE end) te_code, --l3 traded endowment
                             max(case when pa.activity_id = 102 then alc.CODE end) pp_code, --l3 personal pension
                             max(case when pa.activity_id = 105 then alc.CODE end) gp_code, --l3 group pension
                             max(case when pa.activity_id = 100 then alc.CODE end) rm_code, --l3 residential mortgage
                             max(case when pa.activity_id = 101 then alc.CODE end) cm_code, -- l3 commercial mortgage
                             max(case when pa.activity_id = 108 then alc.CODE end) gpr_code, -- l3 group protection
                             max(case when pa.activity_id = 104 then alc.CODE end) ppr_code, --l3 persional protection
                             max(case when pa.activity_id = 2165 then alc.CODE end) lgc_code, --l3 longterm care
                             max(case when pa.activity_id = 2157 then alc.CODE end) ann_code, --l3 ANNUITIES
                             max(case when pa.activity_id = 2149 then alc.CODE end) eis_code, --l3  EIS
                             max(case when pa.activity_id = 2208 then alc.CODE end) ethi_code, --l3  ethical
                             max(case when pa.activity_id = 48 then alc.CODE end) ofs_code, --l3  offshore
                             max(case when pa.activity_id = 2184 then alc.CODE end) pri_code, --l3  property investment
                             max(case when pa.activity_id = 2193 then alc.CODE end) vct_code, --l3  vct
                             max(case when pa.activity_id = 62 then alc.CODE end) sipp_code, --l3  sipp
                             max(case when pa.activity_id = 61 then alc.CODE end) eqr_code, --l3  equity
                             max(case when pa.activity_id = 2176 then alc.CODE end) strp_code --l3  structured products
                      from party_activity pa
                           inner join activity_level_code alc on alc.id = pa.activity_level_code_id
                      where  pa.activity_id in (2281,103,109,106,107,2289,2287,2297,2284,2291,2286,102,105,100,101,108,104,2295,2293,2282,2280,2279,2296, 2288,2165,2157,2149,2208,48,2184,2193,62,61,2176)
                      group by pa.party_id
                    ) gra_l3a on gra_l3a.party_id = p.id

однако наблюдается замедление ибо идет full scan по огромной party_activity.

В принципе, никто не мешает сделать по Join на каждый тип activity, Но мне кажется что это тоже как-то нехорошо.

Каков мировой опыт относительно подобных запросов?
27 янв 11, 17:17    [10142500]     Ответить | Цитировать Сообщить модератору
 Re: Запрос в группировкой в join  [new]
plan_table
Guest 		Тебе ща мозг будут есть насчет плана! и трассу тоже замути на всякий пожарный
27 янв 11, 17:22    [10142542]     Ответить | Цитировать Сообщить модератору
 Re: Запрос в группировкой в join  [new]
Shtock
Member

Откуда: СПб
Сообщений: 3049
 		не думаю, что кто-то попросит план ибо я спрашиваю не про оптимизацию, а про мировой опыт.
27 янв 11, 17:25    [10142563]     Ответить | Цитировать Сообщить модератору
 Re: Запрос в группировкой в join  [new]
plan_table
Guest
Shtock
не думаю, что кто-то попросит план ибо я спрашиваю не про оптимизацию, а про мировой опыт.



ты не подумав употребил слово фул_скан, вот увидишь ща съедят тебя!
27 янв 11, 17:27    [10142581]     Ответить | Цитировать Сообщить модератору
 Re: Запрос в группировкой в join  [new]
AmKad
Member

Откуда:
Сообщений: 5223
 		Shtock,

Посмотри как следует задавать вопросы по sql чтобы тебя поняли: 1, 2, 3
И обязательно приведи данные через with.
27 янв 11, 17:31    [10142614]     Ответить | Цитировать Сообщить модератору
 Re: Запрос в группировкой в join  [new]
Shtock
Member

Откуда: СПб
Сообщений: 3049
 		Собственно пример запроса с данными:
/*select *
from party_activity pa
where party_id = 122727                                
  */                              
                                
                                
with 
activity_level_code as (
                          select 1 id, 'Rare' code from dual--id - primary key, code - code for activity frequency
                          union all
                          select 2 id, 'Not rare' code from dual
)
,
party as (
          select 1 id , 'Petrov' person_name from dual  --id - primary key, person name - name of person
          union all
          select 2 id, 'Ivanov' person_name from dual 
          union all
          select 3 id, 'Sidorov' person_name  from dual
         ),

party_activity as (
                   select 1 id , 2281 activity_id, 1 activity_level_code_id, 1 party_id  from dual --id - primary ky, activity_id - a link to list of activities, activity_level_code level of frequency, party_id - link to party
                   union all
                   select 2 id , 106 activity_id, 1 activity_level_code_id, 1 party_id from dual
                   union all
                   select 3 id , 2291 activity_id, 1 activity_level_code_id, 1 party_id from dual
                   union all
                   select 4 id , 2282 activity_id, 1 activity_level_code_id, 1 party_id from dual
                   union all
                   select 5 id , 2208 activity_id, 2 activity_level_code_id, 1 party_id from dual
                   union all
                   select 6 id , 2281 activity_id, 2 activity_level_code_id, 2 party_id from dual
                   union all
                   select 7 id , 10666 activity_id, 3 activity_level_code_id, 2 party_id from dual 
                  )         
                  
select p.id party_id,
       p.person_name,
       nvl(gra_l3a.life_ass_code, 'N') L3A_LIFE_ASSURANCE,
       nvl(gra_l3a.inv_b_code, 'N') L3A_INVESTMENT_BONDS,
       nvl(gra_l3a.isas_code, 'N') L3A_ISAS,
       nvl(gra_l3a.ut_code, 'N') L3A_UNIT_TRUSTS,
       nvl(gra_l3a.it_code, 'N') L3A_INVESTMENT_TRUSTS,
       nvl(gra_l3a.rp_code, 'N') L3A_RETIREMENT_PLANNING,
       nvl(gra_l3a.di_code, 'N') L3A_DIRECT_INVESTMENTS,
       nvl(gra_l3a.moi_code, 'N') L3A_MANAGEMENT_OF_INVESTMENTS,
       nvl(gra_l3a.cf_code, 'N') L3A_CORPORATE_FINANCE,
       nvl(gra_l3a.tei_code, 'N') L3A_TAX_EFFICIENT_INVESTMENTS,
       nvl(gra_l3a.te_code, 'N') L3A_TRADED_ENDOWMENTS,
       nvl(gra_l3a.pp_code, 'N') L3A_PERSONAL_PENSIONS,
       nvl(gra_l3a.gp_code, 'N') L3A_GROUP_PENSIONS,
       nvl(gra_l3a.rm_code, 'N') L3A_RESIDENTIAL_MORTGAGES,
       nvl(gra_l3a.cm_code, 'N') L3A_COMMERCIAL_MORTGAGES,
       nvl(gra_l3a.gpr_code, 'N') L3A_GROUP_PROTECTION,
       nvl(gra_l3a.ppr_code, 'N') L3A_PERSONAL_PROTECTION,
       nvl(gra_l3a.lgc_code, 'N') L3A_LONGTERM_CARE,
       nvl(gra_l3a.ann_code, 'N') L3A_ANNUITIES,
       nvl(gra_l3a.eis_code, 'N') L3A_EIS,
       nvl(gra_l3a.ethi_code, 'N') L3A_ETHICAL_INVESTMENTS,
       nvl(gra_l3a.ofs_code, 'N') L3A_OFFSHORE_INVESTMENTS,
       nvl(gra_l3a.pri_code, 'N') L3A_PROPERTY_INVESTMENTS,
       nvl(gra_l3a.vct_code, 'N') L3A_VCT,
       nvl(gra_l3a.sipp_code, 'N') L3A_SIPP,
       nvl(gra_l3a.eqr_code, 'N') L3A_EQUITY_RELEASE,
       nvl(gra_l3a.strp_code, 'N') L3A_STRUCTURED_PRODUCTS
from party p
     left outer join
                    (
                      select pa.party_id,
                             max(case when pa.activity_id = 2281 then alc.CODE end) life_ass_code, --l3 life assurance
                             max(case when pa.activity_id = 103 then alc.CODE end) inv_b_code, --l3 investment bonds
                             max(case when pa.activity_id = 109 then alc.CODE end) isas_code, --l3 isas
                             max(case when pa.activity_id = 106 then alc.CODE end) ut_code, --l3 unit trusts
                             max(case when pa.activity_id = 107 then alc.CODE end) it_code, --l3 investment trusts
                             max(case when pa.activity_id = 2289 then alc.CODE end) rp_code, --l3 retirement planing
                             max(case when pa.activity_id = 2287 then alc.CODE end) di_code, --l3 direct investment
                             max(case when pa.activity_id = 2297 then alc.CODE end) moi_code, --l3 management of investment
                             max(case when pa.activity_id = 2284 then alc.CODE end) cf_code, --l3 corporate finance
                             max(case when pa.activity_id = 2291 then alc.CODE end) tei_code, --l3 tax efficient investment
                             max(case when pa.activity_id = 2286 then alc.CODE end) te_code, --l3 traded endowment
                             max(case when pa.activity_id = 102 then alc.CODE end) pp_code, --l3 personal pension
                             max(case when pa.activity_id = 105 then alc.CODE end) gp_code, --l3 group pension
                             max(case when pa.activity_id = 100 then alc.CODE end) rm_code, --l3 residential mortgage
                             max(case when pa.activity_id = 101 then alc.CODE end) cm_code, -- l3 commercial mortgage
                             max(case when pa.activity_id = 108 then alc.CODE end) gpr_code, -- l3 group protection
                             max(case when pa.activity_id = 104 then alc.CODE end) ppr_code, --l3 persional protection
                             max(case when pa.activity_id = 2165 then alc.CODE end) lgc_code, --l3 longterm care
                             max(case when pa.activity_id = 2157 then alc.CODE end) ann_code, --l3 ANNUITIES
                             max(case when pa.activity_id = 2149 then alc.CODE end) eis_code, --l3  EIS
                             max(case when pa.activity_id = 2208 then alc.CODE end) ethi_code, --l3  ethical
                             max(case when pa.activity_id = 48 then alc.CODE end) ofs_code, --l3  offshore
                             max(case when pa.activity_id = 2184 then alc.CODE end) pri_code, --l3  property investment
                             max(case when pa.activity_id = 2193 then alc.CODE end) vct_code, --l3  vct
                             max(case when pa.activity_id = 62 then alc.CODE end) sipp_code, --l3  sipp
                             max(case when pa.activity_id = 61 then alc.CODE end) eqr_code, --l3  equity
                             max(case when pa.activity_id = 2176 then alc.CODE end) strp_code --l3  structured products
                      from party_activity pa
                           inner join activity_level_code alc on alc.id = pa.activity_level_code_id
                      where  pa.activity_id in (2281,103,109,106,107,2289,2287,2297,2284,2291,2286,102,105,100,101,108,104,2295,2293,2282,2280,2279,2296, 2288,2165,2157,2149,2208,48,2184,2193,62,61,2176)
                      group by pa.party_id
                    ) gra_l3a on gra_l3a.party_id = p.id



PARTY_ID	PERSON_NAME	L3A_LIFE_ASSURANCE	L3A_INVESTMENT_BONDS	L3A_ISAS	L3A_UNIT_TRUSTS	L3A_INVESTMENT_TRUSTS	L3A_RETIREMENT_PLANNING	L3A_DIRECT_INVESTMENTS	L3A_MANAGEMENT_OF_INVESTMENTS	L3A_CORPORATE_FINANCE	L3A_TAX_EFFICIENT_INVESTMENTS	L3A_TRADED_ENDOWMENTS	L3A_PERSONAL_PENSIONS	L3A_GROUP_PENSIONS	L3A_RESIDENTIAL_MORTGAGES	L3A_COMMERCIAL_MORTGAGES	L3A_GROUP_PROTECTION	L3A_PERSONAL_PROTECTION	L3A_LONGTERM_CARE	L3A_ANNUITIES	L3A_EIS	L3A_ETHICAL_INVESTMENTS	L3A_OFFSHORE_INVESTMENTS	L3A_PROPERTY_INVESTMENTS	L3A_VCT	L3A_SIPP	L3A_EQUITY_RELEASE	L3A_STRUCTURED_PRODUCTS
1	Petrov	Rare	N	N	Rare	N	N	N	N	N	Rare	N	N	N	N	N	N	N	N	N	N	Not rare	N	N	N	N	N	N
2	Ivanov	Not rare	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N
3	Sidorov	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N	N



Собственно вопрос правильно ли я с точки зрения концепта получил нужный мне результат или лучше кучу left outer join для party_activity сделать.
27 янв 11, 18:10    [10142969]     Ответить | Цитировать Сообщить модератору
 Re: Запрос в группировкой в join  [new]
-2-
Member

Откуда:
Сообщений: 15330
Shtock
про мировой опыт.
мировой опыт надо было узнавать на этапе проектирования EAV.
27 янв 11, 18:12    [10142985]     Ответить | Цитировать Сообщить модератору
 Re: Запрос в группировкой в join  [new]
Shtock
Member

Откуда: СПб
Сообщений: 3049
 		Очень признателен господину -2- за его прорицательность. А раскажите-ка, мне, дорогой, как Вы будете хранить активности субъекта когда у одного их может быть 2, у одного 102, а у 3-го вообще нет. На каждый вид из имеющихся у нас в системе активностей (а их порядка 3000 в справочнике сейчас) поле в Party заводить? если можете по-делу сказать - пожалуйста. Нет - Просто трёп в Вашем распоряжении.
27 янв 11, 18:33    [10143105]     Ответить | Цитировать Сообщить модератору
 Re: Запрос в группировкой в join  [new]
100500
Member

Откуда:
Сообщений: 124
 		Shtock,

Претендовать на оглашение мирового опыта мне мешает моя неповторимая скромность :-), но думаю следующее:

Если использование этой VIEW предполагает выборки по небольшому множеству party_id, то вариант с join на каждый тип активности будет предпочтительней. Возможно, он будет не хуже и при выборке всех party_id (надо замерять). В любом случае, подзапрос с group by всегда ( ну или в обозримом будущем, пока новых query transformations не придумают ) будет тянуть все party_id.

Или посмотреть в сторону pivot table, если Oracle 11g+.
27 янв 11, 18:53    [10143197]     Ответить | Цитировать Сообщить модератору
 Re: Запрос в группировкой в join  [new]
Shtock
Member

Откуда: СПб
Сообщений: 3049
 		Oracle 10 версии :(
27 янв 11, 18:58    [10143221]     Ответить | Цитировать Сообщить модератору
 Re: Запрос в группировкой в join  [new]
-2-
Member

Откуда:
Сообщений: 15330
Shtock
если можете по-делу сказать - пожалуйста
Указал ключевые три буквы, чтобы вы смогли сами воспользоваться мировым гуглопытом и взглянуть на проблему более структурно, а не со стороны приведенного запроса, где оптимизатор, в отличие от вас, сообразил выбрать оптимальный "full scan по огромной party_activity".
27 янв 11, 20:52    [10143616]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить