Доброго дня господа !
Имею запрос (ниже), его архитектура (не менять)
PARAMETER - получение параметров из внешнего источника
PARAMETER_LIST - преобразования параметров списка в табличную форму
Базовый запрос - ADBUS.V_CFA_STATUSOFCONTR_FACT со связью с параметрической таблицей
Использую hint для комбинированных индексов Index_Combine
Вариант 1
PARAMETER -> Преобразование строк посредством CONNECT BY -> Materialize -> Базовый запрос => План оптимазции 1
Вариант 2
PARAMETER -> Преобразование коллекции посредством table() -> Materialize -> Базовый запрос => План оптимазции 2
План оптимазции 1 - быстрый использует доступ типа INDEX RANGE SCAN в обоих индексах
План оптимазции 2 - менее производительный использует доступ типа INDEX FULL SCAN в ОДНОМ из индексов
Почему применение приема коллекций table(), который реализован ДО !!!!!!!! материализации
привело к такому изменению плана
Предполагаю, что мог потерять немного времени (1-2 сек) на разборе коллекции, но все должно было
отфетчится во временную таблицу и базовый план остался бы без изменений
Под базовым планом понимаю это
....
from ADBUS.V_CFA_STATUSOFCONTR_FACT A
inner join PARAMETER_LIST B
on ( Trunc(A.DATE_FININSURANCE) between B.DBEG and B.DEND
and B.KEYS is Null
and B.TYPE in(0,1)
and B.KIND in ('DAY')
or B.LIST=A.ID_CONTRACT_SAP
and Not B.KEYS is Null
and B.TYPE in(0,1)
and B.KIND in ('ONE')
)
.....
Подскажите пожалуйста как исправить (т.е. коллекция + Index_RS) - заранее благодарен !
Запрос
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.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
with PARAMETER as (select /*+ Materialize */
To_Date('01-02-2018','DD-MM-YYYY') DBEG,
To_Date('28-02-2018','DD-MM-YYYY') DEND,
0 TYPE,
2 SHOW,
--'CT446757832333,CTR55663745550,GH7894344515000' LIST,
Null LIST,
Mdsys.SdorIdTab('CT0040','CT0041','CT0042','CT0043','CT0047','CT0049','CT0052','CT0054','CT2011','CT2013','CT2031','CT2033','CT2083','CT2111','CT2113','CT2131','CT2133','CT2161','CT2163','CT2181','CT2183') ATM,
Mdsys.SdorIdTab('CT0044','CT0045','CT0048','CT0053','CT0065','CT0066','CT2021','CT2071','CT2073','CT2121','CT2123','CT2173') POS,
Mdsys.SdorIdTab('CT0057','CTX3') RKO,
'CT0040,CT0041,CT0042,CT0043,CT0047,CT0049,CT0052,CT0054,CT2011,CT2013,CT2031,CT2033,CT2083,CT2111,CT2113,CT2131,CT2133,CT2161,CT2163,CT2181,CT2183' ATM1,
'CT0044,CT0045,CT0048,CT0053,CT0065,CT0066,CT2021,CT2071,CT2073,CT2121,CT2123,CT2173' POS1,
'CT0057,CTX3' RKO1
from DUAL
),
PARAMETER_LIST(LIST,KIND,DBEG,DEND,TYPE,SHOW,KEYS) as
(select /*+ Materialize */
*
from (
select Nvl( RegExp_Substr(LIST,'[^,]+',1,LEVEL),'NOT_SC' ) LIST,
Nvl2(RegExp_Substr(LIST,'[^,]+',1,LEVEL),'ONE','DAY') KIND,
DBEG,
DEND,
TYPE,
SHOW,
LIST KEYS
from PARAMETER
connect by LEVEL<=RegExp_Count(LIST,'','')+2
/*
--Вариант 1
union all
select Nvl(RegExp_Substr(ATM1,'[^,]+',1,LEVEL),'NOT_SC') LIST,
'ATM' KIND,
DBEG,
DEND,
TYPE,
SHOW,
LIST KEYS
from PARAMETER
connect by LEVEL<=RegExp_Count(ATM1,'','')+1
union all
select Nvl(RegExp_Substr(POS1,'[^,]+',1,LEVEL),'NOT_SC') LIST,
'POS' KIND,
DBEG,
DEND,
TYPE,
SHOW,
LIST KEYS
from PARAMETER
connect by LEVEL<=RegExp_Count(POS1,'','')+1
union all
select Nvl(RegExp_Substr(RKO1,'[^,]+',1,LEVEL),'NOT_SC') LIST,
'RKO' KIND,
DBEG,
DEND,
TYPE,
SHOW,
LIST KEYS
from PARAMETER
connect by LEVEL<=RegExp_Count(RKO1,'','')+1
*/
--Вариант 2
union all
select A.*,'ATM' KIND,To_Date('01-02-2018','DD-MM-YYYY') DBEG,To_Date('28-02-2018','DD-MM-YYYY') DEND,0,2,Null
from table(select ATM from PARAMETER) A
union all
select A.*,'POS' KIND,To_Date('01-02-2018','DD-MM-YYYY') DBEG,To_Date('28-02-2018','DD-MM-YYYY') DEND,0,2,Null
from table(select POS from PARAMETER) A
union all
select A.*,'RKO' KIND,To_Date('01-02-2018','DD-MM-YYYY') DBEG,To_Date('28-02-2018','DD-MM-YYYY') DEND,0,2,Null
from table(select RKO from PARAMETER) A
)
)
select /*+
Index_Combine(A I03_CFA_STATUS_FACT_ID_CONTR I02_CFA_STATUS_FACT_DATE_FIN)
*/
A.ID_CONTRACT_RATANET,
A.DATE_REQUEST,
B.SHOW
from ADBUS.V_CFA_STATUSOFCONTR_FACT A
inner join PARAMETER_LIST B
on ( Trunc(A.DATE_FININSURANCE) between B.DBEG and B.DEND
and B.KEYS is Null
and B.TYPE in(0,1)
and B.KIND in ('DAY')
or B.LIST=A.ID_CONTRACT_SAP
and Not B.KEYS is Null
and B.TYPE in(0,1)
and B.KIND in ('ONE')
)
План оптимизации 1
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.
Plan hash value: 1915423001
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 50710 (1)| 00:00:04 |
| 1 | VIEW | SV_AA00 | 1 | 25 | 50710 (1)| 00:00:04 |
| 2 | TEMP TABLE TRANSFORMATION | | | | | |
| 3 | LOAD AS SELECT | SYS_TEMP_0FD9D75ED_FE49FB4E | | | | |
| 4 | VIEW | | 4 | 412 | 8 (0)| 00:00:01 |
| 5 | UNION-ALL | | | | | |
|* 6 | CONNECT BY WITHOUT FILTERING | | | | | |
| 7 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
|* 8 | CONNECT BY WITHOUT FILTERING | | | | | |
| 9 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
|* 10 | CONNECT BY WITHOUT FILTERING | | | | | |
| 11 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
|* 12 | CONNECT BY WITHOUT FILTERING | | | | | |
| 13 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 |
|* 14 | FILTER | | | | | |
| 15 | NESTED LOOPS | | 1 | 141 | 50702 (1)| 00:00:04 |
|* 16 | VIEW | | 4 | 412 | 2 (0)| 00:00:01 |
| 17 | TABLE ACCESS FULL | SYS_TEMP_0FD9D75ED_FE49FB4E | 4 | 412 | 2 (0)| 00:00:01 |
|* 18 | TABLE ACCESS BY INDEX ROWID BATCHED| T_CFA_STATUSOFCONTR_FACT | 1 | 38 | 50702 (1)| 00:00:04 |
| 19 | BITMAP CONVERSION TO ROWIDS | | | | | |
| 20 | BITMAP OR | | | | | |
| 21 | BITMAP CONVERSION FROM ROWIDS | | | | | |
| 22 | SORT ORDER BY | | | | | |
|* 23 | INDEX RANGE SCAN | I02_CFA_STATUS_FACT_DATE_FIN | | | 735 (1)| 00:00:01 |
| 24 | BITMAP CONVERSION FROM ROWIDS | | | | | |
|* 25 | INDEX RANGE SCAN | I03_CFA_STATUS_FACT_ID_CONTR | | | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter(LEVEL<=NULL)
8 - filter(LEVEL<=NULL)
10 - filter(LEVEL<=NULL)
12 - filter(LEVEL<=NULL)
14 - filter(SYS_AUDIT(1,'ADBUS','V_CFA_STATUSOFCONTR_FACT','S_CFA_STATUSOFCONTR_FACT',3) IS NULL)
16 - filter("B"."TYPE"=0)
18 - filter("B"."KEYS" IS NULL AND "B"."KIND"='DAY' AND
"B"."DBEG"<=TRUNC(INTERNAL_FUNCTION("DATE_FININSURANCE")) AND
"B"."DEND">=TRUNC(INTERNAL_FUNCTION("DATE_FININSURANCE")) OR "B"."LIST"="ID_CONTRACT_SAP" AND "B"."KIND"='ONE'
AND "ID_CONTRACT_SAP" IS NOT NULL AND "B"."KEYS" IS NOT NULL)
23 - access("B"."DBEG"<=TRUNC(INTERNAL_FUNCTION("DATE_FININSURANCE")) AND
"B"."DEND">=TRUNC(INTERNAL_FUNCTION("DATE_FININSURANCE")))
25 - access("B"."LIST"="ID_CONTRACT_SAP")
filter("ID_CONTRACT_SAP" IS NOT NULL)
План оптимизации 2
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.
Plan hash value: 2244502267
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 924M| 21G| | 562K (10)| 00:00:44 |
| 1 | VIEW | SV_AA00 | 924M| 21G| | 562K (10)| 00:00:44 |
| 2 | TEMP TABLE TRANSFORMATION | | | | | | |
| 3 | LOAD AS SELECT | SYS_TEMP_0FD9D75C6_FE49FB4E | | | | | |
| 4 | VIEW | | 49081 | 2013K| | 116 (0)| 00:00:01 |
| 5 | UNION-ALL | | | | | | |
|* 6 | CONNECT BY WITHOUT FILTERING | | | | | | |
| 7 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 |
| 8 | COLLECTION ITERATOR CONSTRUCTOR FETCH| | 16360 | 32720 | | 38 (0)| 00:00:01 |
| 9 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 |
| 10 | COLLECTION ITERATOR CONSTRUCTOR FETCH| | 16360 | 32720 | | 38 (0)| 00:00:01 |
| 11 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 |
| 12 | COLLECTION ITERATOR CONSTRUCTOR FETCH| | 16360 | 32720 | | 38 (0)| 00:00:01 |
| 13 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 |
|* 14 | FILTER | | | | | | |
|* 15 | FILTER | | | | | | |
| 16 | CONCATENATION | | | | | | |
| 17 | NESTED LOOPS | | 43958 | 3434K| | 147K (1)| 00:00:12 |
| 18 | NESTED LOOPS | | 49081 | 3434K| | 147K (1)| 00:00:12 |
|* 19 | VIEW | | 49081 | 2013K| | 52 (0)| 00:00:01 |
| 20 | TABLE ACCESS FULL | SYS_TEMP_0FD9D75C6_FE49FB4E | 49081 | 2013K| | 52 (0)| 00:00:01 |
|* 21 | INDEX RANGE SCAN | I03_CFA_STATUS_FACT_ID_CONTR | 1 | | | 2 (0)| 00:00:01 |
| 22 | TABLE ACCESS BY INDEX ROWID | T_CFA_STATUSOFCONTR_FACT | 1 | 38 | | 3 (0)| 00:00:01 |
| 23 | MERGE JOIN | | 923M| 68G| | 414K (13)| 00:00:33 |
| 24 | SORT JOIN | | 7530K| 272M| | 363K (1)| 00:00:29 |
| 25 | TABLE ACCESS BY INDEX ROWID BATCHED| T_CFA_STATUSOFCONTR_FACT | 7530K| 272M| | 363K (1)| 00:00:29 |
| 26 | INDEX FULL SCAN | I02_CFA_STATUS_FACT_DATE_FIN | 7581K| | | 14673 (1)| 00:00:02 |
|* 27 | FILTER | | | | | | |
|* 28 | SORT JOIN | | 49081 | 2013K| 5808K| 399 (1)| 00:00:01 |
|* 29 | VIEW | | 49081 | 2013K| | 52 (0)| 00:00:01 |
| 30 | TABLE ACCESS FULL | SYS_TEMP_0FD9D75C6_FE49FB4E | 49081 | 2013K| | 52 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter(LEVEL<=NULL)
14 - filter(SYS_AUDIT(1,'ADBUS','V_CFA_STATUSOFCONTR_FACT','S_CFA_STATUSOFCONTR_FACT',3) IS NULL)
15 - filter(SYS_AUDIT(1,'ADBUS','V_CFA_STATUSOFCONTR_FACT','S_CFA_STATUSOFCONTR_FACT',3) IS NULL)
19 - filter("B"."KIND"='ONE' AND "B"."KEYS" IS NOT NULL AND "B"."TYPE"=0)
21 - access("B"."LIST"="ID_CONTRACT_SAP")
filter("ID_CONTRACT_SAP" IS NOT NULL)
27 - filter("B"."DEND">=TRUNC(INTERNAL_FUNCTION("DATE_FININSURANCE")) AND (LNNVL("B"."LIST"="ID_CONTRACT_SAP") OR
LNNVL("B"."KIND"='ONE') OR LNNVL("ID_CONTRACT_SAP" IS NOT NULL) OR LNNVL("B"."KEYS" IS NOT NULL)))
28 - access(INTERNAL_FUNCTION("B"."DBEG")<=INTERNAL_FUNCTION(TRUNC(INTERNAL_FUNCTION("DATE_FININSURANCE"))))
filter(INTERNAL_FUNCTION("B"."DBEG")<=INTERNAL_FUNCTION(TRUNC(INTERNAL_FUNCTION("DATE_FININSURANCE"))))
29 - filter("B"."KEYS" IS NULL AND "B"."KIND"='DAY' AND "B"."TYPE"=0)
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- 1 Sql Plan Directive used for this statement
|
