多个 OR 存在导致长查询挂起 - 只有一个有效

Question

试图让这个查询运行。 冒号是输入旁边的参数化。

SELECT DISTINCT pa.kas_pa, pa.auth_num FROM t_pa_path pa, t_pa_a_item ai 
WHERE (pa.kas_receipt = :kas_receipt OR 
       pa.ID_receipt_KEYED = :id_receipt_keyed OR 
       pa.kas_receipt IN (SELECT kas_rcp_purged FROM t_receipt_link_xref WHERE kas_receipt = :kas_receipt AND cde_status = 'Z') ) 
AND pa.auth_num <> :auth_num 
AND pa.kas_pa = ai.kas_pa 
AND pa.kas_payer_one = :kas_payer_one 
AND pa.ind_special = :ind_special 
AND pa.ind_emerg = :ind_emerg 
AND pa.the_status IN ('A','B','C','D','E') 
AND pa.kas_prov_loc IN (SELECT kas_prov_loc FROM t_pr_identifier WHERE RTRIM(id_other_cust) = :id_prov_keyed) 
AND :cde_keyed IN (ai.cde_keyed, ai.cde_2_keyed, ai.cde_3_keyed, ai.cde_4_keyed) 
AND ai.cde_keyed IN (' ',:cde_keyed) 
AND ai.cde_2_keyed IN (' ',:cde_keyed) 
AND ai.cde_3_keyed IN (' ',:cde_keyed) 
AND ai.cde_4_keyed IN (' ',:cde_keyed) 
AND ai.the_STATUS IN ('A','C','E','G','W') 
AND ai.kas_proceding = :kas_proceding
OR EXISTS (SELECT 1 FROM t_pa_a_item ai, t_dup_svc ds JOIN t_prog p ON ds.cde_proceding_2=rtrim(p.cde_proc)   
  WHERE ds.cde_tax_1 = :cde_tax_1 
  AND ds.cde_proceding_1 = :cde_proceding_1 
  AND ds.cde_1 = :cde_1 
  AND ds.cde_tax_2 = ai.cde_tax 
  AND p.kas_proceding = ai.kas_proceding 
  AND ds.cde_2 = ai.cde_keyed)
OR EXISTS (SELECT 1 FROM t_pa_a_item ai, t_dup_svc ds JOIN t_prog p ON ds.cde_proceding_1=rtrim(p.cde_proc) 
  WHERE ds.cde_tax_2 = :cde_tax_1 
  AND ds.cde_proceding_2 = :cde_proceding_1 
  AND ds.cde_2 = :cde_1 
  AND ds.cde_tax_1 = ai.cde_tax 
  AND p.kas_proceding = ai.kas_proceding 
  AND ds.cde_1 = ai.cde_keyed) 
  AND (  ai.dte_pa_req_eff = :dte_pa_req_eff OR 
      ((:dte_pa_req_eff BETWEEN ai.DTE_PA_REQ_EFF AND ai.DTE_PA_REQ_END ) OR 
      ( :dte_pa_req_end BETWEEN ai.DTE_PA_REQ_EFF AND ai.DTE_PA_REQ_END ) OR 
      ( :dte_pa_req_eff < ai.DTE_PA_REQ_EFF AND :dte_pa_req_end > ai.DTE_PA_REQ_END ) ));

当我运行整个查询时，它只是挂起。

当我在第一个 OR EXISTS 之前从头到右运行查询时，查询有效(没有错误，没有挂起(。

如果我取出第一个 OR 存在，并将第二个留在那里，整个查询都可以工作(没有挂起，没有错误(。

因此，问题似乎与两个 OR 一起存在，或者可能与第一个 OR 存在有关。

有什么想法或建议吗？

谢谢。

Answer 1

子查询：

OR EXISTS (SELECT 1 
     FROM t_pa_a_item ai, t_dup_svc ds 
           JOIN t_prog p ON ds.cde_proceding_2=rtrim(p.cde_proc)   
 WHERE ds.cde_tax_1 = :cde_tax_1 
       AND ds.cde_proceding_1 = :cde_proceding_1 
       AND ds.cde_1 = :cde_1 
       AND ds.cde_tax_2 = ai.cde_tax 
       AND p.kas_proceding = ai.kas_proceding 
       AND ds.cde_2 = ai.cde_keyed)

看起来很奇怪，因为它与查询的其余部分无关。

在存在中，您正在使用一个新的别名 ai，它隐藏了外部 ai(两者都用于同一个表......所以你正在使用表格的新"副本"(。因此，子查询仅依赖于其自己的from表和外部参数。这几乎总是错误的。如果此存在性为真，则对于主from部分的所有行都是正确的。也许你应该省略内部 ai，如果你指的是原始表t_pa_a_item

这可能是问题所在吗？

Answer 2

首先，考虑使用显式连接而不是隐式连接，这实质上是将子句中的连接键移动到INNER JOIN子句WHERE。Oracle 9i引入了ANSI SQL联接。虽然它不是效率或性能的贡献者，但它确实有助于可读性和可维护性，甚至是意图。

其次，考虑对OR EXISTS子查询使用派生LEFT JOIN表。此外，请重新考虑为什么要使用 OR EXISTS，因为子查询的返回是可选的，因此是否考虑，并且没有使用其中的列。

下面是重写，可能需要您更新适当的密钥：

SELECT DISTINCT pa.kas_pa, pa.auth_num 
FROM t_pa_path pa
INNER JOIN t_pa_a_item ai 
      ON pa.kas_pa = ai.kas_pa     
LEFT JOIN (
  SELECT ai.kas_pa
  FROM t_pa_a_item ai
  INNER JOIN t_dup_svc ds              -- EDIT FOR PROPER JOIN KEYS 
        ON ds.cde_tax_2 = ai.cde_tax 
        AND ds.cde_2 = ai.cde_keyed
  INNER JOIN t_prog p                  -- EDIT FOR PROPER JOIN KEYS 
        ON ds.cde_proceding_2 = rtrim(p.cde_proc) 
        ON p.kas_proceding = ai.kas_proceding 
  WHERE ds.cde_tax_1 = :cde_tax_1 
  AND ds.cde_proceding_1 = :cde_proceding_1 
  AND ds.cde_1 = :cde_1 
  ) as derivedTable1    
ON ai.kas_pa = derivedTable1.kas_pa    -- EDIT FOR PROPER JOIN KEYS 
LEFT JOIN (
  SELECT ai.kas_pa 
  FROM t_pa_a_item ai 
  INNER JOIN t_dup_svc ds              -- EDIT FOR PROPER JOIN KEYS 
        ON ds.cde_tax_1 = ai.cde_tax 
        AND ds.cde_1 = ai.cde_keyed
  INNER JOIN t_prog p                  -- EDIT FOR PROPER JOIN KEYS 
        ON ds.cde_proceding_1=rtrim(p.cde_proc) 
        ON p.kas_proceding = ai.kas_proceding 
  WHERE ds.cde_tax_2 = :cde_tax_1 
  AND ds.cde_proceding_2 = :cde_proceding_1 
  AND ds.cde_2 = :cde_1 
  AND (  ai.dte_pa_req_eff = :dte_pa_req_eff OR 
      ((:dte_pa_req_eff BETWEEN ai.DTE_PA_REQ_EFF AND ai.DTE_PA_REQ_END ) OR 
      ( :dte_pa_req_end BETWEEN ai.DTE_PA_REQ_EFF AND ai.DTE_PA_REQ_END ) OR 
      ( :dte_pa_req_eff < ai.DTE_PA_REQ_EFF AND :dte_pa_req_end > ai.DTE_PA_REQ_END )) )
  ) AS derivedTable2
ON ai.kas_pa = derivedTable2.kas_pa    -- EDIT FOR PROPER JOIN KEYS
WHERE (pa.kas_receipt = :kas_receipt OR 
       pa.ID_receipt_KEYED = :id_receipt_keyed OR 
       pa.kas_receipt IN (SELECT kas_rcp_purged 
                          FROM t_receipt_link_xref 
                          WHERE kas_receipt = :kas_receipt AND cde_status = 'Z')) 
AND pa.auth_num <> :auth_num 
AND pa.kas_payer_one = :kas_payer_one 
AND pa.ind_special = :ind_special 
AND pa.ind_emerg = :ind_emerg 
AND pa.the_status IN ('A','B','C','D','E') 
AND pa.kas_prov_loc IN (SELECT kas_prov_loc 
                        FROM t_pr_identifier 
                        WHERE RTRIM(id_other_cust) = :id_prov_keyed) 
AND :cde_keyed IN (ai.cde_keyed, ai.cde_2_keyed, ai.cde_3_keyed, ai.cde_4_keyed) 
AND ai.cde_keyed IN (' ',:cde_keyed) 
AND ai.cde_2_keyed IN (' ',:cde_keyed) 
AND ai.cde_3_keyed IN (' ',:cde_keyed) 
AND ai.cde_4_keyed IN (' ',:cde_keyed) 
AND ai.the_STATUS IN ('A','C','E','G','W') 
AND ai.kas_proceding = :kas_proceding;