我正在进行从 Oracle 到 SQL 数据仓库 Azure 的仓库迁移,并遇到了此查询的问题。
来自 Oracle 的原始查询 - 它返回1872520
rows.
SELECT
*
FROM
STG_REV_APPORTION_CSC_NO t1,
STG_SEP_VL t2,
STG_SEP_VL t3
WHERE
t3.BUSINESS_DATE(+) = t1.BUSINESS_DATE
AND t3.CSC_APP_NO(+) = t1.CSC_APP_NO
AND t3.JOURNEY_NO(+) = t1.JOURNEY_NO
AND t3.PURSE_TXN_CTR(+) = t1.PURSE_TXN_CTR
AND t2.BUSINESS_DATE(+) = t1.BUSINESS_DATE
AND t2.CSC_APP_NO(+) = t1.CSC_APP_NO
AND t2.JOURNEY_NO(+) = t1.JOURNEY_NO
AND
(
t2.TRIP_NO(+) + 1
)
= t1.TRIP_NO
AND
(
t2.MSG_TYPE_CD(+) = 13070
AND t3.MSG_TYPE_CD(+) = 4357
);
从中获取线索文档 http://docs.oracle.com/cd/B19306_01/server.102/b14200/queries006.htm,我尝试将查询重写为 ANSI:
SELECT COUNT(*)
FROM STG_REV_APPORTION_CSC_NO t1
RIGHT OUTER JOIN STG_SEP_VL t3 ON t3.BUSINESS_DATE = t1.BUSINESS_DATE
AND t3.CSC_APP_NO = t1.CSC_APP_NO
AND t3.JOURNEY_NO = t1.JOURNEY_NO
AND t3.PURSE_TXN_CTR = t1.PURSE_TXN_CTR
RIGHT OUTER JOIN STG_SEP_VL t2 ON t2.BUSINESS_DATE = t1.BUSINESS_DATE
AND t2.CSC_APP_NO = t1.CSC_APP_NO
AND t2.JOURNEY_NO = t1.JOURNEY_NO
AND (t2.TRIP_NO + 1) = t1.TRIP_NO
WHERE t2.MSG_TYPE_CD = 13070 AND t3.MSG_TYPE_CD = 4357
它返回零行。 ANSI 版本应该适用于 oracle 实例 - 它也返回零行。
然后我尝试使用 Toad 上的重构选项将 plus join 转换为 ANSI。我得到以下内容
SELECT *
FROM STG_SEP_VL T2
RIGHT OUTER JOIN STG_REV_APPORTION_CSC_NO T1
ON (T2.BUSINESS_DATE = T1.BUSINESS_DATE)
AND (T2.CSC_APP_NO = T1.CSC_APP_NO)
AND (T2.JOURNEY_NO = T1.JOURNEY_NO)
RIGHT OUTER JOIN STG_SEP_VL T3
ON (T3.PURSE_TXN_CTR = T1.PURSE_TXN_CTR)
AND (T3.BUSINESS_DATE = T1.BUSINESS_DATE)
AND (T3.CSC_APP_NO = T1.CSC_APP_NO)
AND (T3.JOURNEY_NO = T1.JOURNEY_NO)
WHERE ( ( (T2.TRIP_NO /*(+)*/
) + 1) = T1.TRIP_NO)
AND ( ( (T2.MSG_TYPE_CD /*(+)*/
) = 13070) AND ( (T3.MSG_TYPE_CD /*(+)*/
) = 4357));
现在,该查询应该在 Oracle 上运行并返回相同数量的行,然后才能在 SQL Server 上运行它。但事实并非如此——它返回零行。
I looked at the explain plan for both of these queries. Here is how (+) join plan looks like:
Here is how ANSI version of this query looks like:
我错过了什么吗?