Есть процедурка, работает, ну очень долго. Работает с 3 таблицами, в которых более милиона записей, идет сложная групировка. Подскажите какие есть методы оптимизации процедуры ?

PDQPRIORITY = 100
вот приблизительные размеры таблиц
MTR_DETAILS_1, MTR_CRED_DETAILS ~ по 10 млн. записей
union ~ 1 млн. записей
mtr_par_rating 200 000

по всем полям по которым идет сравнение есть индексы и делается
апдейт статистикс

вот злополучный кусок который все тормозит ...
********************
INSERT INTO USLUG
SELECT rusnam,
gos.name,
start,
end,
CASE
WHEN MPR.MTR_DIRECTION_ID[1,3]='703'
THEN 'Iaea?.naycu(703)'
WHEN MPR.MTR_DIRECTION_ID[1,3]='800'
THEN 'Oneoaa 800'
WHEN MPR.MTR_DIRECTION_ID[1,3]='900'
THEN 'Aoaeioaen 900'
WHEN MPR.MTR_ZONE_ID = 6
AND mntr_flag = 0
THEN 'Nioiaay naycu'
ELSE CASE WHEN MPR.CALL_KIND_ID=1
THEN 'Aaoiiaoeea '
WHEN MPR.CALL_KIND_ID=2
THEN 'Caeac. ?acaiai?u'
END
END USL,
mpr.client_class_id,
COUNT(*) AS COUN,
SUM(CASE WHEN mpr.mtr_direction_id[1,3] = '100' THEN 0 ELSE md.SUMMA END) AS SU,
COUNT(DISTINCT MD.CLIENT_ID) AS ABON,
sum(md.duration+md.add_service_count) as time,
SUM(CASE WHEN mpr.mtr_direction_id[1,3] = '100' THEN 0 ELSE NVL(mc.credit,0) END) AS CRED,
SUM(CASE WHEN mpr.mtr_direction_id[1,3] = '100' THEN 0 ELSE
CASE WHEN mc.credit IS NULL THEN 0 ELSE 1 END END) AS talk_cred,
0, 0, 0, 0
FROM MTR_PAR_RATING MPR, MTR_DETAILS_1 MD,
gos, union, outer MTR_CRED_DETAILS MC

WHERE union.NACH_id=MD.CHARGE_ID
AND gos.TELEPH_STATION_ID=MD.ATS_ID
AND MPR.MTR_PAR_RATING_ID=MD.MTR_PAR_RATING_ID
and mpr.replflag>=0
and md.replflag>=0
AND MPR.UNIT_USE_KIND_ID<>9
AND md.traffic_reg_id = 1
AND DATE(md.event_date) BETWEEN gos.start_date AND gos.finish_date - 1 UNITS DAY
AND MC.CHARGE_ID = MD.CHARGE_ID
and mc.replflag>=0
GROUP BY 1,2,3,4,5,6;
**********************

вот план запроса

insert into "lbadmin".uslug (rus_id,name,start,end,usl,class,talk,summa,abon,time,credit,talk_cred,free_talk,free_user,free_time,free_summa) select ? ,x2.name ,? ,? ,CASE WHEN (x0.mtr_direction_id [1,3] = '703' ) THEN '...........(703)' WHEN (x0.mtr_direction_id [1,3] = '800' ) THEN '...... 800' WHEN (x0.mtr_direction_id [1,3] = '900' ) THEN '......... 900' WHEN ((x0.mtr_zone_id = 6 ) AND (x0.mntr_flag = 0 ) ) THEN '....... .....' ELSE CASE WHEN (x0.call_kind_id = 1 ) THEN '.......... ' WHEN (x0.call_kind_id = 2 ) THEN '...... .........' END END ,x0.client_class_id ,count(*) ,sum(CASE WHEN (x0.mtr_direction_id [1,3] = '100' ) THEN 0 ELSE x1.summa END ) ,count(distinct x1.client_id ) ,sum((x1.duration + x1.add_service_count ) ) ,sum(CASE WHEN (x0.mtr_direction_id [1,3] = '100' ) THEN 0 ELSE NVL (x4.credit ,0 ) END ) ,sum(CASE WHEN (x0.mtr_direction_id [1,3] = '100' ) THEN 0 ELSE CASE WHEN (x4.credit IS NULL ) THEN 0 ELSE 1 END END ) ,0 ,0 ,0 ,0 from "lbadmin".mtr_par_rating x0 ,"lbadmin".mtr_details_1 x1 ,"lbadmin".gos x2 ,"lbadmin".union x3 ,outer("lbadmin".mtr_cred_details x4 ) where ((((((((((x3.nach_id = x1.charge_id ) AND (x2.teleph_station_id = x1.ats_id ) ) AND (x0.mtr_par_rating_id = x1.mtr_par_rating_id ) ) AND (x0.replflag >= 0 ) ) AND (x1.replflag >= 0 ) ) AND (x0.unit_use_kind_id != 9 ) ) AND (x1.traffic_reg_id = '1' ) ) AND ((DATE (x1.event_date ) >= x2.start_date ) AND (DATE (x1.event_date ) <= (x2.finish_date - 1 UNITS day ) ) ) ) AND (x4.charge_id = x1.charge_id ) ) AND (x4.replflag >= 0 ) ) group by 1 ,x2.name ,3 ,4 ,5 ,x0.client_class_id

QUERY:
------


Estimated Cost: 2
Estimated # of Rows Returned: 11


Temporary Files Required For: Group By

1) lbadmin.mpr: SEQUENTIAL SCAN

Filters: (lbadmin.mpr.replflag >= 0 AND lbadmin.mpr.unit_use_kind_id != 9 )

2) lbadmin.md: INDEX PATH

Filters: (lbadmin.md.replflag >= 0 AND lbadmin.md.traffic_reg_id = '1' )

(1) Index Keys: mtr_par_rating_id charge_id summa client_id
Lower Index Filter: lbadmin.md.mtr_par_rating_id = lbadmin.mpr.mtr_par_rating_id
NESTED LOOP JOIN

3) lbadmin.gos: INDEX PATH

Filters: DATE (lbadmin.md.event_date ) <= lbadmin.gos.finish_date - 1 UNITS day

(1) Index Keys: teleph_station_id start_date (Serial, fragments: ALL)
Lower Index Filter: lbadmin.gos.teleph_station_id = lbadmin.md.ats_id
Upper Index Filter: lbadmin.gos.start_date <= DATE (lbadmin.md.event_date )
NESTED LOOP JOIN

4) lbadmin.union: INDEX PATH

(1) Index Keys: nach_id (Key-Only) (Serial, fragments: ALL)
Lower Index Filter: lbadmin.union.nach_id = lbadmin.md.charge_id
NESTED LOOP JOIN

5) lbadmin.mc: INDEX PATH

Filters: lbadmin.mc.replflag >= 0

(1) Index Keys: charge_id credit
Lower Index Filter: lbadmin.mc.charge_id = lbadmin.md.charge_id
NESTED LOOP JOIN

в итоге возвращается 53 записи
процедура работает окколо 4-х часов

2 Julian

Действительно без COUNT(DISTINCT...) и COUNT(...) запрос отработал намного быстрее ... В связи с этим вопрос по пункту 2.1 - внутри цикла делать SELECT только для MC а как с остальными таблицами? на каком этапе делать группировку ?
Я сейчас сделал выборку в тмп таблицу всех записей по связке
union.NACH_id=MD.CHARGE_ID и всех необходимых полей из MD
Теперь планирую сделать Foreach c выборкой необходимых полей из других таблиц в переменные и вставкой в тмп1 таблицу - все это внутри одного Foreach.
Затем из полученной таблицы получить данные с группировкой.
Поправьте если что не так