У меня есть процедура хранения, которая использует FULL OUTER JOIN две таблицы по идентификатору для получения некоторого значения, а затем LEFT JOIN другие таблицы для получения дополнительной информации.
Проблема в том, что я думаю, что оптимизатор сначала не использует FULL OUTER JOIN, поэтому он не может получить идентификатор для LEFT JOIN других таблиц, но он не показывает никаких ошибок, вместо этого он получает все значения NULL для этой «дополнительной информации».
Если я вставлю значение FULL OUTER JOIN в таблицу GTT, затем использую эту таблицу GTT в LEFT JOIN с этими другими таблицами, тогда она вернет всю правильную информацию.
Я снова пытаюсь использовать подсказку /*+ORDERED */ => NULL. Используйте также информацию /*+ LEADING(t1 t2) */ => NULL.
Я использую CTE для вложения FULL OUTER JOIN, затем также использую CTE для LEFT JOIN => NULL информации.
Я перехожу на базу данных 12c и запускаю процедуру, подсказка не нужна => правильная информация.
Так что же мне теперь делать? Использовать GTT и разделить один запрос на два?
Это пример для моего случая, только для описания, потому что с этим запросом он получает правильную информацию, но в моей процедуре с реальной таблицей он получает NULL информацию
WITH t1 AS
(
SELECT 1 AS id, 2 AS value FROM dual UNION ALL
SELECT 2 AS id, 3 AS value FROM dual UNION ALL
SELECT 5 AS id, 9 AS value FROM dual
)
, t2 AS
(
SELECT 1 AS id, 8 AS value FROM dual UNION ALL
SELECT 3 AS id, 2 AS value FROM dual UNION ALL
SELECT 5 AS id, 1 AS value FROM dual
)
, t3 AS
(
SELECT 1 AS id, 'ab1' AS name FROM dual UNION ALL
SELECT 2 AS id, 'sb2' AS name FROM dual UNION ALL
SELECT 3 AS id, 'tb3' AS name FROM dual UNION ALL
SELECT 5 AS id, 'cs5' AS name FROM dual
)
SELECT /*+ORDERED */
NVL(t1.id, t2.id) AS id,
t3.name, --In real case, this column return all NULL value
NVL(t1.value, 0) AS value1,
NVL(t2.value, 0) AS value2
FROM t1
FULL OUTER JOIN t2
ON t1.id = t2.id
LEFT JOIN t3
ON t3.id = NVL(t1.id, t2.id)
;
.
.
.
Для получения дополнительной/подробной информации, это реальный запрос, я копирую из своей процедуры и устанавливаю значение параметра для запуска и получаю NULL information
WITH ton_dk AS
(
SELECT sc.ma_dvi, sc.kho, sc.nhom, sc.ma_vt,
SUM(sc.l_t) AS l_t
FROM vt_sc sc
INNER JOIN
(
SELECT ma_dvi, kho, nhom, ma_vt, nuoc, model, dv, cl, dai, rong, cao,
MAX(ngay_ht) AS ngay_ht
FROM vt_sc
WHERE
ma_dvi IN (SELECT ma_dvi FROM temp_ma_dvi WHERE ma_ct = '1')
AND kho IN (SELECT c1 FROM temp_7)
AND nhom LIKE '%'
AND ma_vt LIKE '%'
AND ngay_ht <= 20180101
GROUP BY ma_dvi, kho, nhom, ma_vt, nuoc, model, dv, cl, dai, rong, cao
) m
ON sc.ma_dvi = m.ma_dvi
AND sc.kho = m.kho AND sc.nhom = m.nhom
AND sc.ma_vt = m.ma_vt AND sc.nuoc = m.nuoc
AND sc.model = m.model AND sc.dv = m.dv AND sc.cl = m.cl
AND sc.dai = m.dai AND sc.rong = m.rong AND sc.cao = m.cao
AND sc.ngay_ht = m.ngay_ht
GROUP BY sc.ma_dvi, sc.kho, sc.nhom, sc.ma_vt
HAVING SUM(sc.l_t) <> 0
)
, ton_ck AS
(
SELECT sc.ma_dvi, sc.kho, sc.nhom, sc.ma_vt,
SUM(sc.l_t) AS l_t
FROM vt_sc sc
INNER JOIN
(
SELECT ma_dvi, kho, nhom, ma_vt, nuoc, model, dv, cl, dai, rong, cao,
MAX(ngay_ht) AS ngay_ht
FROM vt_sc
WHERE
ma_dvi IN (SELECT ma_dvi FROM temp_ma_dvi WHERE ma_ct = '1')
AND kho IN (SELECT c1 FROM temp_7)
AND nhom LIKE '%'
AND ma_vt LIKE '%'
AND ngay_ht <= 20181130
GROUP BY ma_dvi, kho, nhom, ma_vt, nuoc, model, dv, cl, dai, rong, cao
) m
ON sc.ma_dvi = m.ma_dvi
AND sc.kho = m.kho AND sc.nhom = m.nhom
AND sc.ma_vt = m.ma_vt AND sc.nuoc = m.nuoc
AND sc.model = m.model AND sc.dv = m.dv AND sc.cl = m.cl
AND sc.dai = m.dai AND sc.rong = m.rong AND sc.cao = m.cao
AND sc.ngay_ht = m.ngay_ht
GROUP BY sc.ma_dvi, sc.kho, sc.nhom, sc.ma_vt
HAVING SUM(sc.l_t) <> 0
)
SELECT /*+ORDERED */
NVL(d.ma_dvi, c.ma_dvi) AS ma_dvi,
NVL(d.ma_vt, c.ma_vt) AS ma_vt,
m.ten AS ten_vt,
m.dvi,
m.du_tru,
NVL(d.kho, c.kho) AS kho,
k.ten AS ten_kho,
k.ma_tk AS tk_kho,
k.dvi_ql AS dvi_ql,
NVL(d.l_t, 0) AS l_ton_dk,
NVL(c.l_t, 0) AS l_ton_ck
FROM ton_dk d
FULL OUTER JOIN ton_ck c
ON d.ma_dvi = c.ma_dvi
AND d.kho = c.kho AND d.nhom = c.nhom
AND d.ma_vt = c.ma_vt
LEFT JOIN vt_ma_vt m
ON NVL(d.nhom, c.nhom) = m.nhom
AND NVL(d.ma_vt, c.ma_vt) = m.ma
AND m.ma_dvi = NVL(d.ma_dvi, c.ma_dvi)
LEFT JOIN vt_ma_kho k
ON NVL(d.kho, c.kho) = k.ma
AND k.ma_dvi = NVL(d.ma_dvi, c.ma_dvi)
ORDER BY 1, 5, 8;
Результат:
ma_dvi ma_vt ten_vt dvi du_tru kho ten_kho tk_kho dvi_ql l_ton_dk l_ton_ck
010 V.ON61. 10-09V 0 161.5
010 13.01.01.121 07-1.3 0 1
010 19.03.022 07-3.3 0 16
010 V.DNP15.1 05-032(KD) 0 7
Объясните план:
Plan hash value: 1068421260
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 309 | | 10587 (1)| 00:02:08 |
| 1 | SORT ORDER BY | | 1 | 309 | | 10587 (1)| 00:02:08 |
| 2 | NESTED LOOPS OUTER | | 1 | 309 | | 10586 (1)| 00:02:08 |
| 3 | NESTED LOOPS OUTER | | 1 | 229 | | 10585 (1)| 00:02:08 |
| 4 | VIEW | VW_FOJ_0 | 1 | 143 | | 10583 (1)| 00:02:07 |
|* 5 | HASH JOIN FULL OUTER | | 1 | 182 | | 10583 (1)| 00:02:07 |
| 6 | VIEW | | 1 | 91 | | 5200 (1)| 00:01:03 |
|* 7 | FILTER | | | | | | |
| 8 | HASH GROUP BY | | 1 | 149 | | 5200 (1)| 00:01:03 |
|* 9 | HASH JOIN | | 4 | 596 | | 5199 (1)| 00:01:03 |
| 10 | VIEW | | 19105 | 1417K| | 2631 (1)| 00:00:32 |
| 11 | HASH GROUP BY | | 19105 | 10M| 11M| 2631 (1)| 00:00:32 |
|* 12 | HASH JOIN | | 19105 | 10M| | 248 (1)| 00:00:03 |
| 13 | TABLE ACCESS FULL | TEMP_7 | 353 | 173K| | 2 (0)| 00:00:01 |
| 14 | NESTED LOOPS | | 19105 | 1641K| | 246 (1)| 00:00:03 |
|* 15 | TABLE ACCESS FULL| TEMP_MA_DVI | 1 | 19 | | 2 (0)| 00:00:01 |
|* 16 | INDEX RANGE SCAN | VT_SC_P | 19105 | 1287K| | 244 (1)| 00:00:03 |
| 17 | TABLE ACCESS FULL | VT_SC | 743K| 51M| | 2562 (2)| 00:00:31 |
| 18 | VIEW | | 1 | 91 | | 5383 (1)| 00:01:05 |
|* 19 | FILTER | | | | | | |
| 20 | HASH GROUP BY | | 1 | 149 | | 5383 (1)| 00:01:05 |
|* 21 | HASH JOIN | | 5 | 745 | | 5382 (1)| 00:01:05 |
| 22 | VIEW | | 20568 | 1526K| | 2813 (1)| 00:00:34 |
| 23 | HASH GROUP BY | | 20568 | 11M| 12M| 2813 (1)| 00:00:34 |
|* 24 | HASH JOIN | | 20568 | 11M| | 248 (1)| 00:00:03 |
| 25 | TABLE ACCESS FULL | TEMP_7 | 353 | 173K| | 2 (0)| 00:00:01 |
| 26 | NESTED LOOPS | | 20568 | 1767K| | 246 (1)| 00:00:03 |
|* 27 | TABLE ACCESS FULL| TEMP_MA_DVI | 1 | 19 | | 2 (0)| 00:00:01 |
|* 28 | INDEX RANGE SCAN | VT_SC_P | 20568 | 1385K| | 244 (1)| 00:00:03 |
| 29 | TABLE ACCESS FULL | VT_SC | 743K| 51M| | 2562 (2)| 00:00:31 |
| 30 | TABLE ACCESS BY INDEX ROWID| VT_MA_VT | 1 | 86 | | 2 (0)| 00:00:01 |
|* 31 | INDEX UNIQUE SCAN | VT_MA_VT_P | 1 | | | 1 (0)| 00:00:01 |
| 32 | TABLE ACCESS BY INDEX ROWID | VT_MA_KHO | 1 | 80 | | 1 (0)| 00:00:01 |
|* 33 | INDEX UNIQUE SCAN | VT_MA_KHO_P | 1 | | | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("D"."MA_DVI"="C"."MA_DVI" AND "D"."KHO"="C"."KHO" AND "D"."NHOM"="C"."NHOM" AND
"D"."MA_VT"="C"."MA_VT")
7 - filter(SUM("SC"."L_T")<>0)
9 - access("SC"."MA_DVI"="M"."MA_DVI" AND "SC"."KHO"="M"."KHO" AND "SC"."NHOM"="M"."NHOM"
AND "SC"."MA_VT"="M"."MA_VT" AND "SC"."NUOC"="M"."NUOC" AND "SC"."MODEL"="M"."MODEL" AND
"SC"."DV"="M"."DV" AND "SC"."CL"="M"."CL" AND "SC"."DAI"="M"."DAI" AND "SC"."RONG"="M"."RONG"
AND "SC"."CAO"="M"."CAO" AND "SC"."NGAY_HT"="M"."NGAY_HT")
12 - access("C1"=SYS_OP_C2C("KHO"))
15 - filter("MA_CT"='1')
16 - access("MA_DVI"="MA_DVI" AND "NGAY_HT"<=20180101)
filter("NGAY_HT"<=20180101 AND "NHOM" LIKE '%' AND "MA_VT" LIKE '%')
19 - filter(SUM("SC"."L_T")<>0)
21 - access("SC"."MA_DVI"="M"."MA_DVI" AND "SC"."KHO"="M"."KHO" AND "SC"."NHOM"="M"."NHOM"
AND "SC"."MA_VT"="M"."MA_VT" AND "SC"."NUOC"="M"."NUOC" AND "SC"."MODEL"="M"."MODEL" AND
"SC"."DV"="M"."DV" AND "SC"."CL"="M"."CL" AND "SC"."DAI"="M"."DAI" AND "SC"."RONG"="M"."RONG"
AND "SC"."CAO"="M"."CAO" AND "SC"."NGAY_HT"="M"."NGAY_HT")
24 - access("C1"=SYS_OP_C2C("KHO"))
27 - filter("MA_CT"='1')
28 - access("MA_DVI"="MA_DVI" AND "NGAY_HT"<=20181130)
filter("NGAY_HT"<=20181130 AND "NHOM" LIKE '%' AND "MA_VT" LIKE '%')
31 - access("M"."MA_DVI"(+)="D"."MA_DVI" AND "M"."NHOM"(+)="D"."NHOM" AND
"M"."MA"(+)="D"."MA_VT")
33 - access("K"."MA_DVI"(+)="from$_subquery$_015"."QCSJ_C000000001500000" AND
"K"."MA"(+)="from$_subquery$_015"."QCSJ_C000000001500002")
Note
-----
- dynamic sampling used for this statement (level=2)
nameбыл NOT NULL в результате. - person Pham X. Bach schedule 30.11.2018