Why is my query slow when replacing the column I join on?

Noobanalyst415 0
Viewed 28

Hi this is more of a general question when I run the query below my query takes about 1.5hours to run, however when I switch from joining "ACC_ID" to "ULT_ID" then my query takes 5+ hours. I know without the source information it may be hard to diagnose, but I am wondering if there's something basic I'm missing.

The amount of unique values for "ACC_ID" and "ULT_ID" are around the same

Original Query

SELECT  DISTINCT
  op."Acc_ID"
, op."Oppty_ID"
, op."Prod1op"
, op."Prod2op"
, CASE WHEN ac1."Acc_ID" IS NOT NULL AND  ac2."Acc_ID" IS NOT NULL AND ac3."Acc_ID" IS NOT NULL
       THEN  'Match @ ALL Levels'
       WHEN ac1."Acc_ID" IS NOT NULL AND  ac2."Acc_ID" IS NOT NULL 
       THEN  'Match @ ACC_ID, Prod1 Levels'
       WHEN ac1."Acc_ID" IS NOT NULL
       THEN 'Match @ ACC_ID Level'
       ELSE 'No Match @ ACC_ID Level' END CF       
FROM Oppty op
LEFT JOIN Acc ac1 ON op."Acc_ID" = ac1."Acc_ID"
LEFT JOIN Acc ac2 ON op."Acc_ID" = ac2."Acc_ID"
                   AND ac2."Prod1acc" = op."Prod1op"
LEFT JOIN Acc ac3   ON op."Acc_ID" = ac3."Acc_ID"
                   AND ac3."Prod1acc" = op."Prod1op"
                   AND ac3."Prod2acc" = op."Prod2op" 
  ORDER BY op."Acc_ID", op."Prod1op"

Query which takes much longer

SELECT  DISTINCT
  op."ULT_ID"
, op."Oppty_ID"
, op."Prod1op"
, op."Prod2op"
, CASE WHEN ac1."ULT_ID" IS NOT NULL AND  ac2."ULT_ID" IS NOT NULL AND ac3."ULT_ID" IS NOT NULL
       THEN  'Match @ ALL Levels'
       WHEN ac1."ULT_ID" IS NOT NULL AND  ac2."ULT_ID" IS NOT NULL 
       THEN  'Match @ ULT_ID, Prod1 Levels'
       WHEN ac1."ULT_ID" IS NOT NULL
       THEN 'Match @ ULT_ID Level'
       ELSE 'No Match @ ULT_ID Level' END CF       
FROM Oppty op
LEFT JOIN Acc ac1 ON op."ULT_ID" = ac1."ULT_ID"
LEFT JOIN Acc ac2 ON op."ULT_ID" = ac2."ULT_ID"
                   AND ac2."Prod1acc" = op."Prod1op"
LEFT JOIN Acc ac3   ON op."ULT_ID" = ac3."ULT_ID"
                   AND ac3."Prod1acc" = op."Prod1op"
                   AND ac3."Prod2acc" = op."Prod2op" 
  ORDER BY op."ULT_ID", op."Prod1op"
left-join performance oracle sql
1 Answers

It basically depends on how the index is set up, but the distribution of the data is also an important indicator.
Perhaps it is the case below.
Case 1. The op.Acc_ID is indexed and op.ULT_ID is not indexed.
Case 2. op.Acc_ID has higher cardinality.

Daniel Choi0
Related