Multiple OR EXISTS causing long query to hang - Just one works

Question

Trying to get this query to run. The colons are parameterization next to what would be the input.

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 ) ));

When I run the entire query, it just hangs.

When I run the query from the beginning to right before the first OR EXISTS, the query works (no error, no hanging).

If I take out the first OR EXISTS, and leave the 2nd one in there, that entire query works (no hanging, no errors).

So the issue seems to be with both OR EXISTS together, or perhaps something with the first OR EXISTS.

Any ideas or suggestions?

Thanks.

Presumably, you need parentheses to get the logic that you intend. — Gordon Linoff, Feb 13 '16 at 00:26

RafaelCaballero · Answer 1 · 2016-02-13T00:41:56.973

The subquery:

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)

looks strange because it is not related to the rest of the query.

In the existential you are using a new alias ai which hides the outer ai (both for the same table...so you are using a new "copy" of the table). Thus, the subquery only depends on its own from tables and on the external parameters. This is almost always erroneous. If this existential is true it is true for all the rows of the main from section. Maybe you should omit the internal ai, if you are referring to the original table t_pa_a_item

Could this be the problem?

score 0 · Answer 2 · edited May 23 '17 at 11:45

First, consider using the explicit join over implicit join which essentially moves join keys in WHERE clause to INNER JOIN clause. Oracle 9i introduced ANSI SQL joins. While not a contributor to efficiency or performance, it does aid in readability and maintainability even intent.

Second, consider using LEFT JOIN derived tables over the OR EXISTS subqueries. Additionally, re-think why you are using the OR EXISTS as the return of subquery is optional, so is considered or not, and no columns are being used from them.

Below is a re-write which may require you to update for appropriate keys:

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;

Thank you to all who replied with advice and suggestions. I appreciate it. I wound up needing to add an additional parens that enclosed from ai.kas_proceding = :kas_proceding (before first OR EXISTS) and ending after AND ds.cde_1 = ai.cde_keyed). I also reworked the JOINS. Thanks again! — We5inelgr, Feb 16 '16 at 00:57

Multiple OR EXISTS causing long query to hang - Just one works

2 Answers2