|
Оптимизация запроса
#39940606
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
|
|
|
|
Есть запрос:
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.
select distinct AG2.AGNNAME SNAGENT, -- SNAGENT --работодатель
AG.AGNABBR FIO, -- FIO
AG.PENSION_NBR PENSION_NBR, -- СНИЛС
CT.CODE SCLNPSPFMTYPNAME, -- Вид исполнения
NVL(CP.NAME, PD.PSDEP_NAME) sEMPPOST, -- должность
ID.NAME sDEPTNAME, -- подразделение
CL.RN nRnCLNPSPFM, -- rn исполнения
HS.SCHEDULE, -- график работы
HS.RATEACC, -- количество ставок
HS.SLCOSTS, -- состав затрат
-- greatest(HS.do_act_from, dDATEBEG) DATEBEG,
-- least(nvl( HS.do_act_to, dDATEEND ), dDATEEND) DATEEND ,
nvl(substr(DICACCS.ACC_NUMBER, 18, 1), '0') VFO -- Источник финансирования
from SELECTLIST SEL,
CLNPSPFM CL,
CLNPSPFMTYPES CT, --Виды должностного исполнения
CLNPERSONS PS,
CLNPSPFMHS HS,
AGNLIST AG2,
AGNLIST AG,
CLNPOSTS CP, --должности
CLNPSDEP PD, --Штатные должности
INS_DEPARTMENT ID,
SLCOMPCHARGES CH,
SLPAYS SL,
SLPAYGRND SPG,
SLCALCULAT SC,
SLCOSTS, -- расчет -- SLCALCTYPE SLCT,--вид расчета
DICACCS
where /*SEL.IDENT = nRN
and CL.RN = SEL.DOCUMENT
and CL.COMPANY = nCOMPANY
and */CL.PERSRN = PS.RN
and CL.clnpspfmtypes = CT.rn
and CT.CODE in ('Основной',
'Совмещение',
'Совмести-во внешнее',
'Совмест-во внешнее',
'Внешсовмещение',
'Внутсовместитель',
'Совмест-во внутр',
'Работающий на времен',
'Работающий на постоя')
and PS.PERS_AGENT = AG.RN
and PS.OWNER_AGENT = AG2.RN
AND CL.POSTRN = CP.RN(+) --должности
AND CL.PSDEPRN = PD.RN(+) --Штатные должности
AND CL.DEPTRN = ID.RN(+)
and CL.RN = HS.PRN(+)
and HS.DO_ACT_FROM =
(select max(DO_ACT_FROM)
from CLNPSPFMHS HS1
where HS1.PRN = CL.RN --and HS1.DO_ACT_FROM <= sysdate
and HS1.DO_ACT_FROM <=
last_DAY(to_date('01.' || 2 || '.' ||
2020,
'DD.MM.YYYY')))
-- and SL.CLNPSPFM(+) = CL.RN
and SL.SLCOMPCHARGES = CH.RN
and SL.SLPAYGRND = SPG.RN
and SL.SLCALCULAT = SC.RN --and SL.YEAR between nYearFrom and nYearTo -- and SL.MONTH between nMONTHFrom and nMONTHTo
and SL.YEAR = 2020
and SL.MONTH = 2
and CH.COMPCH_TYPE = 10
and SLCOSTS.ACCOUNT_DEBIT = DICACCS.rn
order by AG.PENSION_NBR,
CT.CODE,
nvl(substr(DICACCS.ACC_NUMBER, 18, 1), '0') --,--nvl(substr(DICACCS.ACC_NUMBER,18,1),'0')
Его план:
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.
Plan Hash Value : 633313662
-----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 329 | 921 | 00:00:12 |
| 1 | SORT UNIQUE | | 1 | 329 | 920 | 00:00:12 |
| 2 | NESTED LOOPS | | 1 | 329 | 919 | 00:00:12 |
| 3 | NESTED LOOPS | | 1 | 329 | 919 | 00:00:12 |
| 4 | MERGE JOIN CARTESIAN | | 1 | 320 | 918 | 00:00:12 |
| 5 | NESTED LOOPS | | 1 | 307 | 22 | 00:00:01 |
| 6 | NESTED LOOPS | | 1 | 307 | 22 | 00:00:01 |
| 7 | MERGE JOIN CARTESIAN | | 1 | 274 | 21 | 00:00:01 |
| 8 | NESTED LOOPS | | 1 | 269 | 20 | 00:00:01 |
| 9 | NESTED LOOPS | | 1 | 269 | 20 | 00:00:01 |
| 10 | NESTED LOOPS | | 1 | 241 | 18 | 00:00:01 |
| 11 | NESTED LOOPS | | 1 | 216 | 17 | 00:00:01 |
| 12 | NESTED LOOPS | | 1 | 182 | 16 | 00:00:01 |
| 13 | NESTED LOOPS OUTER | | 1 | 165 | 15 | 00:00:01 |
| 14 | NESTED LOOPS OUTER | | 1 | 136 | 14 | 00:00:01 |
| 15 | NESTED LOOPS OUTER | | 1 | 101 | 13 | 00:00:01 |
| * 16 | HASH JOIN | | 1 | 59 | 13 | 00:00:01 |
| 17 | MERGE JOIN CARTESIAN | | 1 | 18 | 4 | 00:00:01 |
| 18 | INDEX FULL SCAN | C_SELECTLIST_PK | 1 | | 1 | 00:00:01 |
| 19 | BUFFER SORT | | 6 | 108 | 3 | 00:00:01 |
| * 20 | TABLE ACCESS FULL | CLNPSPFMTYPES | 6 | 108 | 3 | 00:00:01 |
| 21 | TABLE ACCESS FULL | CLNPSPFM | 1493 | 61213 | 9 | 00:00:01 |
| 22 | TABLE ACCESS BY INDEX ROWID | CLNPSDEP | 1 | 42 | 0 | 00:00:01 |
| * 23 | INDEX UNIQUE SCAN | C_CLNPSDEP_PK | 1 | | 0 | 00:00:01 |
| 24 | TABLE ACCESS BY INDEX ROWID | CLNPOSTS | 1 | 35 | 1 | 00:00:01 |
| * 25 | INDEX UNIQUE SCAN | C_CLNPOSTS_RN | 1 | | 0 | 00:00:01 |
| 26 | TABLE ACCESS BY INDEX ROWID | INS_DEPARTMENT | 1 | 29 | 1 | 00:00:01 |
| * 27 | INDEX UNIQUE SCAN | INS_DEPT_RN | 1 | | 0 | 00:00:01 |
| 28 | TABLE ACCESS BY INDEX ROWID | CLNPERSONS | 1 | 17 | 1 | 00:00:01 |
| * 29 | INDEX UNIQUE SCAN | C_CLNPERSONS_PK | 1 | | 0 | 00:00:01 |
| 30 | TABLE ACCESS BY INDEX ROWID | AGNLIST | 1 | 34 | 1 | 00:00:01 |
| * 31 | INDEX UNIQUE SCAN | C_AGNLIST_RN_PK | 1 | | 0 | 00:00:01 |
| 32 | TABLE ACCESS BY INDEX ROWID | AGNLIST | 1 | 25 | 1 | 00:00:01 |
| * 33 | INDEX UNIQUE SCAN | C_AGNLIST_RN_PK | 1 | | 0 | 00:00:01 |
| * 34 | INDEX RANGE SCAN | I_CLNPSPFMHS_FROM_TO | 1 | | 1 | 00:00:01 |
| 35 | SORT AGGREGATE | | 1 | 14 | | |
| 36 | FIRST ROW | | 1 | 14 | 2 | 00:00:01 |
| * 37 | INDEX RANGE SCAN (MIN/MAX) | I_CLNPSPFMHS_FROM_TO | 1 | 14 | 2 | 00:00:01 |
| 38 | TABLE ACCESS BY INDEX ROWID | CLNPSPFMHS | 1 | 28 | 2 | 00:00:01 |
| 39 | BUFFER SORT | | 27 | 135 | 19 | 00:00:01 |
| * 40 | INDEX FULL SCAN | I_SLCOSTS_FK_ACC_DEBIT | 27 | 135 | 1 | 00:00:01 |
| * 41 | INDEX UNIQUE SCAN | C_DICACCS_RN_PK | 1 | | 0 | 00:00:01 |
| 42 | TABLE ACCESS BY INDEX ROWID | DICACCS | 1 | 33 | 1 | 00:00:01 |
| 43 | BUFFER SORT | | 42 | 546 | 917 | 00:00:12 |
| 44 | TABLE ACCESS BY INDEX ROWID | SLPAYS | 42 | 546 | 896 | 00:00:11 |
| * 45 | INDEX SKIP SCAN | I_SLPAYS_PERS_PERIOD | 42 | | 887 | 00:00:11 |
| * 46 | INDEX UNIQUE SCAN | C_SLCOMPCHARGES_PK | 1 | | 0 | 00:00:01 |
| * 47 | TABLE ACCESS BY INDEX ROWID | SLCOMPCHARGES | 1 | 9 | 1 | 00:00:01 |
-----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 16 - access("CL"."CLNPSPFMTYPES"="CT"."RN")
* 20 - filter("CT"."CODE"='Внешсовмещение' OR "CT"."CODE"='Внутсовместитель' OR "CT"."CODE"='Основной' OR "CT"."CODE"='Работающий на времен' OR "CT"."CODE"='Работающий на постоя' OR
"CT"."CODE"='Совмест-во внешнее' OR "CT"."CODE"='Совмест-во внутр' OR "CT"."CODE"='Совмести-во внешнее' OR "CT"."CODE"='Совмещение')
* 23 - access("CL"."PSDEPRN"="PD"."RN"(+))
* 25 - access("CL"."POSTRN"="CP"."RN"(+))
* 27 - access("CL"."DEPTRN"="ID"."RN"(+))
* 29 - access("CL"."PERSRN"="PS"."RN")
* 31 - access("PS"."OWNER_AGENT"="AG2"."RN")
* 33 - access("PS"."PERS_AGENT"="AG"."RN")
* 34 - access("CL"."RN"="HS"."PRN" AND "HS"."DO_ACT_FROM"= (SELECT MAX("DO_ACT_FROM") FROM "CLNPSPFMHS" "HS1" WHERE "HS1"."DO_ACT_FROM"<=TO_DATE(' 2020-02-29 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"HS1"."PRN"=:B1))
* 37 - access("HS1"."PRN"=:B1 AND "HS1"."DO_ACT_FROM"<=TO_DATE(' 2020-02-29 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
* 40 - filter("SLCOSTS"."ACCOUNT_DEBIT" IS NOT NULL)
* 41 - access("SLCOSTS"."ACCOUNT_DEBIT"="DICACCS"."RN")
* 45 - access("SL"."YEAR"=2020 AND "SL"."MONTH"=2)
* 45 - filter("SL"."YEAR"=2020 AND "SL"."MONTH"=2)
* 46 - access("SL"."SLCOMPCHARGES"="CH"."RN")
* 47 - filter("CH"."COMPCH_TYPE"=10)
Работает 1-10 минут. Надо ускорить. Помогите. |
|
|
