为什么此查询会导致Oracle中的笛卡尔合并联接 [英] Why would this query cause a Merge Cartesian Join in Oracle

问题描述

我有一个最近需要修改的查询.

I have a query that was recently required to be modified.

这是原始的

SELECT RTRIM (position) AS "POSITION",
   .  // Other fields
   .
   .
   FROM schema.table x WHERE hours > 0 
    AND pay = 'RGW'
    AND NOT EXISTS( SELECT position FROM schema.table2 y where  y.position = x.position )

这是新版本

SELECT RTRIM (position) AS "POSITION",
   .  // Other fields
   .
   .
   FROM schema.table x WHERE hours > 0 
    AND pay = 'RGW'
    AND NOT EXISTS( SELECT position FROM  schema.table2 y where y.date = get_fiscal_year_start_date (SYSDATE) AND y.position = x.position )

UDF get_fiscal_year_start_date()返回date参数的会计年度开始日期.第一个查询运行良好，但是第二个查询创建了合并笛卡尔连接.我查看了表上的索引，发现位置和日期都被索引了.我对您stackoverflow的问题是，为什么添加y.date = get_fiscal_year_start_date (SYSDATE)会导致Oracle 10g中的合并笛卡尔联接.

The UDF get_fiscal_year_start_date() returns the fiscal year start date of the date parameter. The first query runs fine, but the second creates a merge Cartesian join. I looked at the indexes on the tables and found that position and date were both indexed. My question for you stackoverflow is why would the addition of y.date = get_fiscal_year_start_date (SYSDATE) cause a merge cartesian join in Oracle 10g.

推荐答案

问题是，Oracle不知道get_fiscal_year_start_date (SYSDATE)返回一个结果.因此，假设它将生成很多行.

The problem is, Oracle doesn't know that get_fiscal_year_start_date (SYSDATE) returns one single result. So it's assuming that it will generate lots of rows.

很显然，我没有测试工具，但是此版本的查询应消除合并笛卡尔联接.

Obviously I don't have a test harness to hand, but this version of your query ought to banish the merge cartesian join.

SELECT RTRIM (position) AS "POSITION", 
.  // Other fields 
. 
. 
FROM schema.table x 
     , ( select get_fiscal_year_start_date (SYSDATE) as fiscal_year 
         from dual ) fy
WHERE hours > 0  
AND pay = 'RGW' 
AND NOT EXISTS( SELECT position 
                FROM  schema.table2 y 
                where y.date = fy.fiscal_year
                AND y.position = x.position ) 

Oracle知道DUAL只有一行，因此子查询将返回一个值.

Oracle knows that DUAL has a single row, and hence that the sub-query will return one value.

这篇关于为什么此查询会导致Oracle中的笛卡尔合并联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！