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.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
168.
169.
170.
171.
172.
173.
174.
175.
176.
177.
178.
179.
180.
181.
182.
183.
184.
185.
186.
187.
188.
189.
190.
191.
192.
193.
194.
195.
196.
197.
198.
199.
200.
201.
202.
203.
204.
205.
206.
207.
208.
209.
210.
211.
212.
213.
214.
215.
216.
217.
218.
219.
220.
221.
222.
223.
224.
225.
226.
227.
228.
229.
230.
231.
232.
233.
234.
235.
236.
237.
238.
239.
240.
241.
242.
243.
244.
245.
246.
247.
248.
249.
250.
251.
252.
253.
254.
255.
256.
257.
258.
259.
260.
261.
262.
263.
264.
PLAN_TABLE_OUTPUT
Plan hash value: 3915164698
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5834 | 336K| | 78M (1)| 00:50:57 |
| 1 | SORT GROUP BY | | 5834 | 336K| | 78M (1)| 00:50:57 |
| 2 | VIEW | | 2042M| 112G| | 78M (1)| 00:50:54 |
| 3 | HASH GROUP BY | | 2042M| 327G| 346G| 78M (1)| 00:50:54 |
|* 4 | FILTER | | | | | | |
|* 5 | HASH JOIN RIGHT OUTER | | 2042M| 327G| 7936K| 932K (1)| 00:00:37 |
| 6 | VIEW | | 270K| 4761K| | 129K (1)| 00:00:06 |
| 7 | HASH GROUP BY | | 270K| 13M| 16M| 129K (1)| 00:00:06 |
|* 8 | HASH JOIN | | 270K| 13M| 13M| 126K (1)| 00:00:05 |
| 9 | VIEW | | 270K| 10M| | 4815 (1)| 00:00:01 |
| 10 | HASH GROUP BY | | 270K| 12M| 15M| 4815 (1)| 00:00:01 |
|* 11 | HASH JOIN | | 270K| 12M| | 1636 (1)| 00:00:01 |
|* 12 | TABLE ACCESS FULL | KRS_DAY | 4017 | 92391 | | 22 (0)| 00:00:01 |
| 13 | NESTED LOOPS | | 183K| 4309K| | 1613 (1)| 00:00:01 |
| 14 | NESTED LOOPS | | 183K| 4309K| | 1613 (1)| 00:00:01 |
| 15 | VIEW | VW_NSO_1 | 1 | 6 | | 3 (34)| 00:00:01 |
| 16 | HASH UNIQUE | | 1 | | | 3 (34)| 00:00:01 |
|* 17 | CONNECT BY WITHOUT FILTERING (UNIQUE)| | | | | | |
| 18 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 |
|* 19 | INDEX RANGE SCAN | SK_BY_DATE_DT_IDX | 183K| | | 501 (1)| 00:00:01 |
| 20 | TABLE ACCESS BY INDEX ROWID | SK_BY_DT | 183K| 3232K| | 1610 (1)| 00:00:01 |
| 21 | TABLE ACCESS FULL | TOV | 28M| 351M| | 86903 (1)| 00:00:04 |
|* 22 | HASH JOIN | | 1981K| 291M| 262M| 780K (1)| 00:00:31 |
|* 23 | HASH JOIN | | 1981K| 240M| 29M| 628K (1)| 00:00:25 |
|* 24 | HASH JOIN | | 319K| 26M| 23M| 98930 (1)| 00:00:04 |
| 25 | NESTED LOOPS | | 319K| 19M| | 67033 (1)| 00:00:03 |
|* 26 | TABLE ACCESS FULL | KRS_DAY | 365 | 8395 | | 22 (0)| 00:00:01 |
|* 27 | TABLE ACCESS BY INDEX ROWID BATCHED | DOC | 875 | 36750 | | 213 (0)| 00:00:01 |
|* 28 | INDEX RANGE SCAN | DOC_TRUNC_DT_IDX | 1207 | | | 12 (0)| 00:00:01 |
|* 29 | INDEX FAST FULL SCAN | DOCSUB_IDX_STOR | 12M| 256M| | 10701 (1)| 00:00:01 |
| 30 | TABLE ACCESS FULL | DOCL | 67M| 2643M| | 358K (1)| 00:00:14 |
| 31 | TABLE ACCESS FULL | TOV | 28M| 729M| | 86903 (1)| 00:00:04 |
----------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$EDC04FC4
2 - SEL$B2D3FBAB / from$_subquery$_019@SEL$12
3 - SEL$B2D3FBAB
6 - SEL$CCA5E655 / AA@SEL$6
7 - SEL$CCA5E655
9 - SEL$F938087A / from$_subquery$_016@SEL$10
10 - SEL$F938087A
12 - SEL$F938087A / K@SEL$7
15 - SEL$092D738C / VW_NSO_1@SEL$F938087A
16 - SEL$092D738C
18 - SEL$092D738C / DUAL@SEL$9
19 - SEL$F938087A / S@SEL$8
20 - SEL$F938087A / S@SEL$8
21 - SEL$CCA5E655 / T@SEL$10
26 - SEL$B2D3FBAB / K@SEL$4
27 - SEL$B2D3FBAB / D@SEL$1
28 - SEL$B2D3FBAB / D@SEL$1
29 - SEL$B2D3FBAB / DS@SEL$3
30 - SEL$B2D3FBAB / DL@SEL$1
31 - SEL$B2D3FBAB / T@SEL$2
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
CONNECT_BY_COMBINE_SW(@"SEL$092D738C")
NO_CONNECT_BY_FILTERING(@"SEL$092D738C")
USE_HASH_AGGREGATION(@"SEL$092D738C")
USE_HASH_AGGREGATION(@"SEL$F938087A")
SWAP_JOIN_INPUTS(@"SEL$F938087A" "K"@"SEL$7")
USE_HASH(@"SEL$F938087A" "K"@"SEL$7")
NLJ_BATCHING(@"SEL$F938087A" "S"@"SEL$8")
USE_NL(@"SEL$F938087A" "S"@"SEL$8")
LEADING(@"SEL$F938087A" "VW_NSO_1"@"SEL$F938087A" "S"@"SEL$8" "K"@"SEL$7")
FULL(@"SEL$F938087A" "K"@"SEL$7")
INDEX(@"SEL$F938087A" "S"@"SEL$8" ("SK_BY_DT"."DT"))
NO_ACCESS(@"SEL$F938087A" "VW_NSO_1"@"SEL$F938087A")
USE_HASH_AGGREGATION(@"SEL$CCA5E655")
USE_HASH(@"SEL$CCA5E655" "T"@"SEL$10")
LEADING(@"SEL$CCA5E655" "from$_subquery$_016"@"SEL$10" "T"@"SEL$10")
FULL(@"SEL$CCA5E655" "T"@"SEL$10")
NO_ACCESS(@"SEL$CCA5E655" "from$_subquery$_016"@"SEL$10")
USE_HASH_AGGREGATION(@"SEL$B2D3FBAB")
SWAP_JOIN_INPUTS(@"SEL$B2D3FBAB" "AA"@"SEL$6")
USE_HASH(@"SEL$B2D3FBAB" "AA"@"SEL$6")
USE_HASH(@"SEL$B2D3FBAB" "T"@"SEL$2")
USE_HASH(@"SEL$B2D3FBAB" "DL"@"SEL$1")
USE_HASH(@"SEL$B2D3FBAB" "DS"@"SEL$3")
USE_NL(@"SEL$B2D3FBAB" "D"@"SEL$1")
LEADING(@"SEL$B2D3FBAB" "K"@"SEL$4" "D"@"SEL$1" "DS"@"SEL$3" "DL"@"SEL$1" "T"@"SEL$2" "AA"@"SEL$6")
NO_ACCESS(@"SEL$B2D3FBAB" "AA"@"SEL$6")
FULL(@"SEL$B2D3FBAB" "T"@"SEL$2")
FULL(@"SEL$B2D3FBAB" "DL"@"SEL$1")
INDEX_FFS(@"SEL$B2D3FBAB" "DS"@"SEL$3" ("DOCSUB"."ID" "DOCSUB"."STOR"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$B2D3FBAB" "D"@"SEL$1")
INDEX_RS_ASC(@"SEL$B2D3FBAB" "D"@"SEL$1" "DOC_TRUNC_DT_IDX")
FULL(@"SEL$B2D3FBAB" "K"@"SEL$4")
NO_ACCESS(@"SEL$EDC04FC4" "from$_subquery$_019"@"SEL$12")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
MERGE(@"SEL$1" >"SEL$2")
OUTLINE(@"SEL$58A6D7F6")
OUTLINE(@"SEL$3")
MERGE(@"SEL$58A6D7F6" >"SEL$3")
OUTLINE(@"SEL$9E43CB6E")
OUTLINE(@"SEL$4")
OUTLINE(@"SEL$5")
OUTLINE(@"SEL$8")
OUTLINE(@"SEL$7")
OUTLINE(@"SEL$12")
MERGE(@"SEL$9E43CB6E" >"SEL$4")
OUTLINE(@"SEL$EE94F965")
OUTLINE(@"SEL$6")
ANSI_REARCH(@"SEL$5")
OUTLINE(@"SEL$EB4BAB32")
OUTLINE(@"SEL$10")
OUTLINE(@"SEL$11")
OUTLINE(@"SEL$9")
MERGE(@"SEL$8" >"SEL$7")
OUTLINE(@"SEL$8A3193DA")
OUTLINE(@"SEL$A9DBB1CB")
ANSI_REARCH(@"SEL$12")
OUTLINE_LEAF(@"SEL$EDC04FC4")
MERGE(@"SEL$EE94F965" >"SEL$EB4BAB32")
MERGE(@"SEL$6" >"SEL$EB4BAB32")
OUTLINE_LEAF(@"SEL$B2D3FBAB")
MERGE(@"SEL$10" >"SEL$11")
OUTLINE_LEAF(@"SEL$CCA5E655")
UNNEST(@"SEL$9" UNNEST_INNERJ_DISTINCT_VIEW)
OUTLINE_LEAF(@"SEL$F938087A")
CONNECT_BY_ELIM_DUPS(@"SEL$A9DBB1CB")
OUTLINE_LEAF(@"SEL$092D738C")
ALL_ROWS
OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')
OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')
OPT_PARAM('_px_adaptive_dist_method' 'off')
DB_VERSION('12.2.0.1')
OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter(LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE@!),-1))>=ADD_MONTHS(TRUNC(SYSDATE@!,'fmmm'),-12))
5 - access("AA"."BRID"(+)="T"."BRID")
8 - access("T"."ID"="S1"."TID")
11 - access("S"."DT"="K"."DT")
12 - filter("K"."CURRID"=22)
17 - filter(LEVEL<13)
19 - access("S"."DT"=INTERNAL_FUNCTION("DT"))
22 - access("T"."ID"="DL"."TID")
23 - access("DL"."DID"="D"."ID")
24 - access("DS"."ID"="D"."ID")
26 - filter("K"."CURRID"=22 AND "K"."DT">=ADD_MONTHS(TRUNC(SYSDATE@!,'fmmm'),-12) AND
"K"."DT"<=LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE@!),-1)))
27 - filter("D"."TPK"='N' AND ("D"."STK"='G' OR "D"."STK"='H' OR "D"."STK"='P' OR "D"."STK"='Z'))
28 - access("K"."DT"=TRUNC(INTERNAL_FUNCTION("DT")))
filter(TRUNC(INTERNAL_FUNCTION("DT"))>=ADD_MONTHS(TRUNC(SYSDATE@!,'fmmm'),-12) AND
TRUNC(INTERNAL_FUNCTION("DT"))<=LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE@!),-1)))
29 - filter("DS"."STOR"=0)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=2) "T"."BRID"[NUMBER,22], "S"."SUMM"[NUMBER,22], SUM("DL"."SUMM"/"K"."KRSP")[22]
2 - (rowset=256) "DL"."SUMM"[NUMBER,22], "T"."BRID"[NUMBER,22], "K"."KRSP"[NUMBER,22], "S"."SUMM"[NUMBER,22]
3 - (#keys=25; rowset=256) "T"."BRID"[NUMBER,22], ROWID[ROWID,10], ROWID[ROWID,10], ROWID[ROWID,10], ROWID[ROWID,10],
ROWID[ROWID,10], "K"."KRSP"[NUMBER,22], "K"."CURRID"[NUMBER,22], "K"."DT"[DATE,7], "DS"."STOR"[NUMBER,22],
"DS"."ID"[NUMBER,22], "AA"."BRID"[NUMBER,22], "T"."CENA0"[NUMBER,22], "T"."CURRID"[NUMBER,22], "T"."ID"[NUMBER,22],
"DL"."CENA0"[NUMBER,22], "DL"."SUMM"[NUMBER,22], "DL"."TID"[NUMBER,22], "DL"."DID"[NUMBER,22], "DL"."ID"[NUMBER,22],
"D"."STK"[CHARACTER,4], "D"."TPK"[CHARACTER,4], "D"."CURRID"[NUMBER,22], "D"."DT"[DATE,7], "D"."ID"[NUMBER,22],
COUNT("AA"."SUMM")[22], SUM("AA"."SUMM")[22]
4 - "AA"."BRID"[NUMBER,22], "T"."BRID"[NUMBER,22], "AA"."SUMM"[NUMBER,22], "DL"."TID"[NUMBER,22], "T"."ID"[NUMBER,22],
"D"."ID"[NUMBER,22], "DL"."DID"[NUMBER,22], "DS"."STOR"[NUMBER,22], "DS"."ID"[NUMBER,22], ROWID[ROWID,10],
"K"."DT"[DATE,7], "K"."CURRID"[NUMBER,22], "K"."KRSP"[NUMBER,22], ROWID[ROWID,10], "D"."DT"[DATE,7],
"D"."CURRID"[NUMBER,22], "D"."TPK"[CHARACTER,4], "D"."STK"[CHARACTER,4], ROWID[ROWID,10], ROWID[ROWID,10],
"DL"."ID"[NUMBER,22], "DL"."CENA0"[NUMBER,22], "DL"."SUMM"[NUMBER,22], ROWID[ROWID,10], "T"."CENA0"[NUMBER,22],
"T"."CURRID"[NUMBER,22]
5 - (#keys=1) "AA"."BRID"[NUMBER,22], "T"."BRID"[NUMBER,22], "AA"."SUMM"[NUMBER,22], "DL"."TID"[NUMBER,22],
"T"."ID"[NUMBER,22], "D"."ID"[NUMBER,22], "DL"."DID"[NUMBER,22], "DS"."STOR"[NUMBER,22], "DS"."ID"[NUMBER,22],
ROWID[ROWID,10], "K"."DT"[DATE,7], "K"."CURRID"[NUMBER,22], "K"."KRSP"[NUMBER,22], ROWID[ROWID,10], "D"."DT"[DATE,7],
"D"."CURRID"[NUMBER,22], "D"."TPK"[CHARACTER,4], "D"."STK"[CHARACTER,4], ROWID[ROWID,10], ROWID[ROWID,10],
"DL"."ID"[NUMBER,22], "DL"."CENA0"[NUMBER,22], "DL"."SUMM"[NUMBER,22], ROWID[ROWID,10], "T"."CENA0"[NUMBER,22],
"T"."CURRID"[NUMBER,22]
6 - (rowset=256) "AA"."BRID"[NUMBER,22], "AA"."SUMM"[NUMBER,22]
7 - (#keys=2; rowset=256) "T"."BRID"[NUMBER,22], "from$_subquery$_016"."QCSJ_C000000001600000"[DATE,7],
SUM("from$_subquery$_016"."Q"*"T"."CENA0"/"from$_subquery$_016"."KRSP")[22]
8 - (#keys=1; rowset=256) "from$_subquery$_016"."KRSP"[NUMBER,22],
"from$_subquery$_016"."QCSJ_C000000001600000"[DATE,7], "from$_subquery$_016"."Q"[NUMBER,22], "T"."BRID"[NUMBER,22],
"T"."CENA0"[NUMBER,22]
9 - (rowset=256) "S1"."TID"[NUMBER,22], "from$_subquery$_016"."QCSJ_C000000001600000"[DATE,7],
"from$_subquery$_016"."Q"[NUMBER,22], "from$_subquery$_016"."KRSP"[NUMBER,22]
10 - (#keys=6; rowset=256) "S"."TID"[NUMBER,22], "S"."DT"[DATE,7], ROWID[ROWID,10], "K"."KRSP"[NUMBER,22],
"K"."CURRID"[NUMBER,22], "K"."DT"[DATE,7], SUM("S"."Q")[22]
11 - (#keys=1; rowset=256) "K"."DT"[DATE,7], "S"."DT"[DATE,7], ROWID[ROWID,10], "K"."KRSP"[NUMBER,22],
"K"."CURRID"[NUMBER,22], "S"."Q"[NUMBER,22], "S"."TID"[NUMBER,22]
12 - (rowset=256) ROWID[ROWID,10], "K"."DT"[DATE,7], "K"."CURRID"[NUMBER,22], "K"."KRSP"[NUMBER,22]
13 - (#keys=0) "S"."DT"[DATE,7], "S"."TID"[NUMBER,22], "S"."Q"[NUMBER,22]
14 - (#keys=0) "S".ROWID[ROWID,10], "S"."DT"[DATE,7]
15 - "DT"[DATE,8]
16 - (#keys=1) TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE@!,(-13)+LEVEL)))[8]
17 - LEVEL[4]
19 - "S".ROWID[ROWID,10], "S"."DT"[DATE,7]
20 - "S"."TID"[NUMBER,22], "S"."Q"[NUMBER,22]
21 - (rowset=256) "T"."ID"[NUMBER,22], "T"."CENA0"[NUMBER,22], "T"."BRID"[NUMBER,22]
22 - (#keys=1) "DL"."TID"[NUMBER,22], "T"."ID"[NUMBER,22], "D"."ID"[NUMBER,22], "DL"."DID"[NUMBER,22],
"DS"."STOR"[NUMBER,22], "DS"."ID"[NUMBER,22], ROWID[ROWID,10], "K"."DT"[DATE,7], "K"."CURRID"[NUMBER,22],
"K"."KRSP"[NUMBER,22], ROWID[ROWID,10], "D"."DT"[DATE,7], "D"."CURRID"[NUMBER,22], "D"."TPK"[CHARACTER,4],
"D"."STK"[CHARACTER,4], ROWID[ROWID,10], ROWID[ROWID,10], "DL"."ID"[NUMBER,22], "DL"."CENA0"[NUMBER,22],
"DL"."SUMM"[NUMBER,22], ROWID[ROWID,10], "T"."BRID"[NUMBER,22], "T"."CURRID"[NUMBER,22], "T"."CENA0"[NUMBER,22]
23 - (#keys=1; rowset=256) "D"."ID"[NUMBER,22], "DL"."DID"[NUMBER,22], "DS"."STOR"[NUMBER,22], "DS"."ID"[NUMBER,22],
ROWID[ROWID,10], "K"."DT"[DATE,7], "K"."CURRID"[NUMBER,22], "K"."KRSP"[NUMBER,22], ROWID[ROWID,10], "D"."DT"[DATE,7],
"D"."CURRID"[NUMBER,22], "D"."TPK"[CHARACTER,4], "D"."STK"[CHARACTER,4], ROWID[ROWID,10], ROWID[ROWID,10],
"DL"."ID"[NUMBER,22], "DL"."CENA0"[NUMBER,22], "DL"."TID"[NUMBER,22], "DL"."SUMM"[NUMBER,22]
24 - (#keys=1; rowset=256) "D"."ID"[NUMBER,22], "DS"."ID"[NUMBER,22], ROWID[ROWID,10], "K"."DT"[DATE,7],
"K"."CURRID"[NUMBER,22], "K"."KRSP"[NUMBER,22], ROWID[ROWID,10], "D"."DT"[DATE,7], "D"."CURRID"[NUMBER,22],
"D"."TPK"[CHARACTER,4], "D"."STK"[CHARACTER,4], ROWID[ROWID,10], "DS"."STOR"[NUMBER,22]
25 - (#keys=0) ROWID[ROWID,10], "K"."DT"[DATE,7], "K"."CURRID"[NUMBER,22], "K"."KRSP"[NUMBER,22], ROWID[ROWID,10],
"D"."ID"[NUMBER,22], "D"."DT"[DATE,7], "D"."CURRID"[NUMBER,22], "D"."TPK"[CHARACTER,4], "D"."STK"[CHARACTER,4]
26 - ROWID[ROWID,10], "K"."DT"[DATE,7], "K"."CURRID"[NUMBER,22], "K"."KRSP"[NUMBER,22]
27 - ROWID[ROWID,10], "D"."ID"[NUMBER,22], "D"."DT"[DATE,7], "D"."CURRID"[NUMBER,22], "D"."TPK"[CHARACTER,4],
"D"."STK"[CHARACTER,4]
28 - ROWID[ROWID,10], TRUNC(INTERNAL_FUNCTION("DT"))[DATE,7]
29 - ROWID[ROWID,10], "DS"."ID"[NUMBER,22], "DS"."STOR"[NUMBER,22]
30 - (rowset=256) ROWID[ROWID,10], "DL"."ID"[NUMBER,22], "DL"."DID"[NUMBER,22], "DL"."TID"[NUMBER,22],
"DL"."SUMM"[NUMBER,22], "DL"."CENA0"[NUMBER,22]
31 - (rowset=256) ROWID[ROWID,10], "T"."ID"[NUMBER,22], "T"."CURRID"[NUMBER,22], "T"."CENA0"[NUMBER,22],
"T"."BRID"[NUMBER,22]
Sql Plan Directive information:
-------------------------------
Valid directive ids:
7817342961589794216
9269255978859239658
13659517254222289456
7917300634642988666
8499098861008093164
3197875205861779786
6740147412193993527
3936095245764813869
14885619136507854832
13586474784952263830
17833722588659491645
1891618652614293619
12136247708456137003
5745223894351957094
Used directive ids:
12753152432378949753
12148210053655134229
2202228160552015123
7922324600670031403
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- 4 Sql Plan Directives used for this statement
