我有一个使用
FULL OUTER JOIN
按id创建两个表以获取一些值,然后
LEFT JOIN
其他表格以获取更多信息。
问题是,我认为优化器不使用
完全外接
首先,这样它就无法获取身份证
左连接
其他表,但它没有显示任何错误,而是为这些“更多信息”获取所有空值。
如果我插入
完全外接
到GTT表,然后使用该GTT表
左连接
与其他表一起返回所有正确信息。
我试着使用暗示
/*+ORDERED */
=>又是空信息。使用
/*+ LEADING(t1 t2) */
=>也有空信息。
我用CTE筑巢
完全外接
,然后使用CTE
左连接
=>也有空信息。
我切换到数据库12c并运行程序,无需提示=>正确的信息。
那我现在该怎么办?使用GTT,将一个查询拆分为两个?
这是我的例子,仅用于描述,因为通过这个查询
正确信息
但在我用真桌子做的手术中
空信息
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)
