1 主要SQL语句
SELECT *
FROM (
SELECT row_.*,
rownum rownum_
FROM (
SELECT matnr,
maktx,
lgort,
lgobe,
charg,
labst,
meins,
matkl,
wgbez,
brgew,
mtart,
gewei,
menge AS qty,
holdQty
FROM rtl_inventory_freed ri
WHERE EXISTS
(
SELECT 'x'
FROM rtl_sys_permission_inv pi
WHERE pi.tenantCode = ri.tenantCode
AND pi.lgort = ri.lgort
AND pi.werks = ri.werks
AND pi.userID = :1
)
AND tenantCode = :2
AND werks = :3
AND vkorg = :4
AND vtweg = :5
AND datuv <= :6
AND sortf >= :7
)
row_
)
WHERE rownum_ BETWEEN 1 AND 50
2 查看其执行
出现笛卡尔积现象
2 修改SQL语句,加/*+ ordered */
SELECT *
FROM (
SELECT row_.*,
rownum rownum_
FROM (
SELECT /*+ ordered */ matnr,
maktx,
lgort,
lgobe,
charg,
labst,
meins,
matkl,
wgbez,
brgew,
mtart,
gewei,
menge AS qty,
holdQty
FROM rtl_inventory_freed ri
WHERE EXISTS
(
SELECT 'x'
FROM rtl_sys_permission_inv pi
WHERE pi.tenantCode = ri.tenantCode
AND pi.lgort = ri.lgort
AND pi.werks = ri.werks
AND pi.userID = :1
)
AND tenantCode = :2
AND werks = :3
AND vkorg = :4
AND vtweg = :5
AND datuv <= :6
AND sortf >= :7
)
row_
)
WHERE rownum_ BETWEEN 1 AND 50
2 查看执行计划
注:笛卡尔积现象消失。
总结:
1 子查询容易造成笛卡尔积现象
2 /*+ ordered */ 按在from从句中出现顺序连接表