避免笛卡尔积现象方式一

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从句中出现顺序连接表