避免笛卡尔积现象方式一

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

 

### 如何在SQL或编程中避免和解决笛卡尔积问题 #### 笛卡尔积的概念与影响 笛卡尔积是指两个集合的所有可能组合形成的产物。在SQL查询中,如果未正确指定联接条件,就会生成笛卡尔积,即每一条记录与其他表中的所有记录形成配对[^1]。这种现象不仅会导致查询效率低下,还可能返回不正确的数据结果。 #### 避免笛卡尔积的方法 ##### 明确指定 JOIN 条件 在 SQL 查询中,使用显式的 `JOIN` 语句并定义明确的联接条件是防止笛卡尔积的关键。例如,假设我们有两个表 `employees` 和 `departments`,可以通过以下方式避免笛卡尔积: ```sql SELECT e.name, d.department_name FROM employees AS e INNER JOIN departments AS d ON e.department_id = d.id; ``` 这里通过 `ON` 子句明确了两张表之间的关联关系,从而避免笛卡尔积的发生。 ##### 使用高效的连接类型 不同的连接类型适用于不同场景。例如,`INNER JOIN` 返回交集部分;`LEFT JOIN` 或 `RIGHT JOIN` 则保留左/右表的所有记录。选择适合需求的连接类型有助于减少不必要的数据膨胀[^3]。 ##### 创建索引优化性能 对于大规模数据集,创建适当的索引可以显著加快查询速度,并减少因全表扫描导致的笛卡尔积风险。例如,在经常用于联接的列上建立索引: ```sql CREATE INDEX idx_department_id ON employees(department_id); ``` ##### 替代笛卡尔积的方式——自然连接 自然连接是一种更为高效的选择,因为它仅基于共享列进行匹配,而非生成全部组合。这种方式减少了冗余数据量,提高了查询效率[^4]。例如: ```sql SELECT * FROM employees NATURAL JOIN departments; ``` #### 解决已经发生的笛卡尔积问题 一旦发现查询产生笛卡尔积,需仔细检查以下几个方面: 1. **确认是否有遗漏的 JOIN 条件** 如果查询中缺失了必要的联接条件,应补充这些条件以限制结果集范围。例如: ```sql SELECT * FROM employees CROSS JOIN departments; -- 此处为笛卡尔积 ``` 修改为: ```sql SELECT * FROM employees INNER JOIN departments ON employees.department_id = departments.id; ``` 2. **调整子查询逻辑** 若笛卡尔积来源于复杂的子查询结构,则应对子查询增加过滤条件或重新设计其架构[^3]。例如: ```sql SELECT * FROM (SELECT id FROM employees) emp, (SELECT id FROM departments) dept; ``` 调整为: ```sql SELECT * FROM ( SELECT id FROM employees WHERE status = 'active' ) emp INNER JOIN ( SELECT id FROM departments WHERE location = 'USA' ) dept ON emp.department_id = dept.id; ``` 3. **分步拆解复杂查询** 将大查询分解成若干个小查询逐步完成,这样不仅可以简化调试过程,还能有效规避笛卡尔积的风险。例如: ```sql WITH active_employees AS ( SELECT id, name FROM employees WHERE status = 'active' ), usa_departments AS ( SELECT id, department_name FROM departments WHERE location = 'USA' ) SELECT ae.name, ud.department_name FROM active_employees ae INNER JOIN usa_departments ud ON ae.department_id = ud.id; ``` #### 编程环境中处理笛卡尔积 在 Python 等编程语言中同样需要注意避免笛卡尔积。Pandas 提供了多种合并方法,默认情况下不会自动产生笛卡尔积。例如: ```python import pandas as pd # 定义两个 DataFrame df1 = pd.DataFrame({'key': ['A', 'B'], 'data1': [1, 2]}) df2 = pd.DataFrame({'key': ['A', 'C'], 'data2': [3, 4]}) # 合并时指定 key 字段可避免笛卡尔积 merged_df = pd.merge(df1, df2, on='key') print(merged_df) ``` 如果不小心忽略了关键字参数,则可能出现意外的结果。因此始终要确保传入合理的联接依据[^3]。 ---
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值