|
Как избежать материализации
#39493646
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
Ссылка на профиль пользователя:
|
|
|
|
Доброго дня господа !
Есть план запроса, хотелось бы в нем избежать создания временной таблицы
(хочу попробовать будет ли так более быстрее)
Хочу сначала сделать UNION-ALL к результате LEFT агрегирующий запрос
Сейчас наблюдается обратное - отсюда материа-я
Т.е. избежать двойной связи
| 11 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6E07_D924BE0D | 1 | 43 | | 2 (0)| 00:00:01 |
| 19 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6E00_D924BE0D | 1 | 43 | | 2 (0)| 00:00:01 |
Сделать это после операции UNION -ALL
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.
lan hash value: 2771393202
------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3331K| 1026M| | 199K (1)| 00:00:16 |
| 1 | VIEW | AAAA1111 | 3331K| 1026M| | 199K (1)| 00:00:16 |
| 2 | TEMP TABLE TRANSFORMATION | | | | | | |
| 3 | LOAD AS SELECT | SYS_TEMP_0FD9D6E00_D924BE0D | | | | | |
| 4 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 |
|* 5 | HASH JOIN OUTER | | 3331K| 1003M| 2400K| 199K (1)| 00:00:16 |
| 6 | VIEW | | 15185 | 2209K| | 197K (1)| 00:00:16 |
| 7 | UNION-ALL | | | | | | |
|* 8 | FILTER | | | | | | |
| 9 | NESTED LOOPS | | 15030 | 807K| | 1394 (1)| 00:00:01 |
| 10 | VIEW | | 1 | 14 | | 2 (0)| 00:00:01 |
| 11 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6E00_D924BE0D | 1 | 43 | | 2 (0)| 00:00:01 |
| 12 | TABLE ACCESS BY INDEX ROWID BATCHED | T_CFA_STATUSOFCONTR_FACT | 15030 | 601K| | 1392 (1)| 00:00:01 |
|* 13 | INDEX RANGE SCAN | I02_CFA_STATUS_FACT_DATE_FIN | 27054 | | | 52 (0)| 00:00:01 |
|* 14 | FILTER | | | | | | |
|* 15 | FILTER | | | | | | |
|* 16 | HASH JOIN | | 155 | 12400 | | 196K (1)| 00:00:16 |
| 17 | NESTED LOOPS | | 155 | 7285 | | 75 (2)| 00:00:01 |
| 18 | VIEW | | 1 | 14 | | 2 (0)| 00:00:01 |
| 19 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6E00_D924BE0D | 1 | 43 | | 2 (0)| 00:00:01 |
|* 20 | INDEX FAST FULL SCAN | SYS_C00338039 | 155 | 5115 | | 73 (2)| 00:00:01 |
| 21 | TABLE ACCESS BY INDEX ROWID BATCHED| T_CFA_STATUSOFCONTR_FACT | 3821K| 120M| | 196K (1)| 00:00:16 |
|* 22 | INDEX RANGE SCAN | I02_CFA_STATUS_FACT_DATE_FIN | 3821K| | | 7133 (1)| 00:00:01 |
| 23 | VIEW | | 21938 | 3577K| | 1506 (1)| 00:00:01 |
|* 24 | FILTER | | | | | | |
| 25 | SORT GROUP BY | | 21938 | 2806K| 3328K| 1506 (1)| 00:00:01 |
|* 26 | FILTER | | | | | | |
|* 27 | HASH JOIN RIGHT OUTER | | 22674 | 2900K| | 1081 (1)| 00:00:01 |
|* 28 | TABLE ACCESS FULL | SPP_FACT | 29 | 899 | | 14 (0)| 00:00:01 |
|* 29 | HASH JOIN OUTER | | 22674 | 2214K| | 1067 (1)| 00:00:01 |
| 30 | MERGE JOIN CARTESIAN | | 22674 | 1505K| | 69 (0)| 00:00:01 |
| 31 | VIEW | | 1 | 8 | | 2 (0)| 00:00:01 |
| 32 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6E00_D924BE0D | 1 | 43 | | 2 (0)| 00:00:01 |
| 33 | BUFFER SORT | | 22674 | 1328K| | 69 (0)| 00:00:01 |
| 34 | TABLE ACCESS FULL | T_CFA_USERS_FULL | 22674 | 1328K| | 67 (0)| 00:00:01 |
| 35 | TABLE ACCESS FULL | T_CFA_USER_GRADE | 507K| 15M| | 996 (1)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("U"."USER_TAB"(+)=LTRIM("A"."USER_TAB",'P0'))
8 - filter(SYS_AUDIT(1,'ADBUS','V_CFA_STATUSOFCONTR_FACT','S_CFA_STATUSOFCONTR_FACT',3) IS NULL)
13 - access(TRUNC(INTERNAL_FUNCTION("DATE_FININSURANCE"))>=INTERNAL_FUNCTION("P"."DBEG") AND
TRUNC(INTERNAL_FUNCTION("DATE_FININSURANCE"))<=INTERNAL_FUNCTION("P"."DEND"))
14 - filter(SYS_AUDIT(1,'ADBUS','V_CFA_STATUSOFCONTR_FACT','S_CFA_STATUSOFCONTR_FACT',3) IS NULL)
15 - filter(SYS_AUDIT(1,'PF_REPORT','DTS_SCLIST','S_S_SCLIST',3) IS NULL)
16 - access("ID_CONTRACT_RATANET"="ID_CONTRACT_RATANET")
20 - filter("PSTNG_DATE">=INTERNAL_FUNCTION("P"."DBEG") AND "PSTNG_DATE"<=INTERNAL_FUNCTION("P"."DEND"))
22 - access(TRUNC(INTERNAL_FUNCTION("DATE_FININSURANCE"))=TO_DATE(' 9999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
24 - filter(SYS_AUDIT(1,'ADBUS','V_CFA_USERS_FULL','S_CFA_USERS_FULL',3) IS NULL)
26 - filter(SYS_AUDIT(1,'ADBUS','V_CFA_USER_GRADE','S_CFA_USER_GRADE',3) IS NULL AND
SYS_AUDIT(1,'PF_REPORT','SPP_FACT','S_P_FACT',3) IS NULL)
27 - access("DAY"(+)=INTERNAL_FUNCTION("P"."DEND") AND "TAB"(+)=LTRIM("USER_TAB",'0'))
28 - filter("TYPE"(+)='TAB' AND "KIND"(+)='Grade')
29 - access("ID_USER"(+)="ID_USER" AND "USER_LOGIN"(+)="USER_LOGIN" AND
TRUNC(INTERNAL_FUNCTION("GRADE_DATE_BEGIN"(+)))=TRUNC("P"."DEND"-32*"P"."FATE",'fmmm'))
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- 1 Sql Plan Directive used for this statement
Запрос
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.
select
A.DBEG,
A.DEND,
A.FATE,
A.ID_CONTRACT_RATANET,
A.ID_CONTRACT_CARD,
Ltrim(A.USER_TAB,'P0') USER_TAB,
A.DATE_REQUEST REQUEST,
Trunc(A.DATE_FININSURANCE) ACCEPT,
U.USER_NAME USER_NAME,
U.GRADE GRADE
from (
select
/*+ Index(A I02_CFA_STATUS_FACT_DATE_FIN) */
A.ID_CONTRACT_RATANET,
Ltrim(A.USER_TAB,'P0') USER_TAB,
A.DATE_REQUEST,
A.DATE_FININSURANCE,
P.DBEG,
P.DEND,
P.FATE,
Null ID_CONTRACT_CARD,
Cast(Null as Date) CARD_ACTIVE
from ADBUS.V_CFA_STATUSOFCONTR_FACT A
inner join PARAMETER P
on ( Trunc(A.DATE_FININSURANCE) between P.DBEG and P.DEND
)
union all
select
/*+ Index(A I02_CFA_STATUS_FACT_DATE_FIN) */
A.ID_CONTRACT_RATANET,
Ltrim(A.USER_TAB,'P0') USER_TAB,
A.DATE_REQUEST,
C.PSTNG_DATE DATE_FININSURANCE,
P.DBEG,
P.DEND,
P.FATE,
C.ID_CONTRACT_CARD,
C.PSTNG_DATE CARD_ACTIVE
from ADBUS.V_CFA_STATUSOFCONTR_FACT A
inner join PARAMETER P
on ( Trunc(A.DATE_FININSURANCE)=To_Date('31-12-9999','DD-MM-YYYY')
)
inner join PF_REPORT.DTS_SCLIST C
on ( C.ID_CONTRACT_RATANET=A.ID_CONTRACT_RATANET
and C.PSTNG_DATE between P.DBEG and P.DEND
)
) A
left join
(
select /*+ no_merge */
Ltrim(A.USER_TAB,'0') USER_TAB,
Max(A.USER_NAME) USER_NAME,
Nvl(Max(D.VAL),Max(C.GRADE_VALUE) keep(Dense_Rank Last order by A.GRADE_DATE_CHANGED nulls first)) GRADE
from ADBUS.V_CFA_USERS_FULL A
inner join PARAMETER P
on (1=1)
left join ADBUS.V_CFA_USER_GRADE C
on ( C.ID_USER =A.ID_USER
and C.USER_LOGIN=A.USER_LOGIN
and Trunc(C.GRADE_DATE_BEGIN)=Trunc(P.DEND-32*P.FATE,'MM')
)
left join PF_REPORT.SPP_FACT D
on ( D.DAY = P.DEND
and D.TAB = Ltrim(A.USER_TAB,'0')
and D.TYPE='TAB'
and D.KIND='Grade'
)
group by Ltrim(A.USER_TAB,'0')
order by USER_TAB
) U
on ( U.USER_TAB=Ltrim(A.USER_TAB,'P0')
)
Заранее благодарен ! |
|
|
