title: Is it better to JOIN using OR in the join condition or to use multiple JOINS
body: <p>I'm trying to flag tickets based on whether they indicate a certain event has occurred. The issue I'm running into is that the particular value(s) that indicate such a condition occurred could be in any 1 of 25 fields on the ticket and my current logic takes quite a while to complete (generally >45min) if it completes at all (our DBAs ocassionally kill it for taking too much CPU or having too high a PJI). Currently I'm using the below to try and flag the tickets:</p>
  
  <pre><code>CREATE VOLATILE TABLE
  --DROP TABLE 
  FCT
  AS ( 
  SELECT
  I_FCT.Ticket_Nbr
  , I_FCT.Plan_Dim_CK
  , I_FCT.S_DATE
  , I_FCT.E_DATE
  
  , CASE
      WHEN I_FCT.Event1_CK = AD.Event_CK THEN '1'
      WHEN I_FCT.Event2_CK = AD.Event_CK THEN '2'
      WHEN I_FCT.Event3_CK = AD.Event_CK THEN '3'
      WHEN I_FCT.Event4_CK = AD.Event_CK THEN '4'
      WHEN I_FCT.Event5_CK = AD.Event_CK THEN '5'
      WHEN I_FCT.Event6_CK = AD.Event_CK THEN '6'
      WHEN I_FCT.Event7_CK = AD.Event_CK THEN '7'
      WHEN I_FCT.Event8_CK = AD.Event_CK THEN '8'
      WHEN I_FCT.Event9_CK = AD.Event_CK THEN '9'
      WHEN I_FCT.Event10_CK = AD.Event_CK THEN '10'
      WHEN I_FCT.Event11_CK = AD.Event_CK THEN '11'
      WHEN I_FCT.Event12_CK = AD.Event_CK THEN '12'
      WHEN I_FCT.Event13_CK = AD.Event_CK THEN '13'
      WHEN I_FCT.Event14_CK = AD.Event_CK THEN '14'
      WHEN I_FCT.Event15_CK = AD.Event_CK THEN '15'
      /*WHEN I_FCT.Event16_CK = AD.Event_CK THEN '16'
      WHEN I_FCT.Event17_CK = AD.Event_CK THEN '17'
      WHEN I_FCT.Event18_CK = AD.Event_CK THEN '18'
      WHEN I_FCT.Event19_CK = AD.Event_CK THEN '19'
      WHEN I_FCT.Event20_CK = AD.Event_CK THEN '20'
      WHEN I_FCT.Event21_CK = AD.Event_CK THEN '21'
      WHEN I_FCT.Event22_CK = AD.Event_CK THEN '22'
      WHEN I_FCT.Event23_CK = AD.Event_CK THEN '23'
      WHEN I_FCT.Event24_CK = AD.Event_CK THEN '24'
      WHEN I_FCT.Event25_CK = AD.Event_CK THEN '25'*/
      ELSE NULL
  END AS AOD_Event
  
  FROM
  I_FCT
  
  LEFT JOIN AOD_CKS AS AD
      ON (AD.Event_CK = I_FCT.Event1_CK
          OR AD.Event_CK = I_FCT.Event2_CK
          OR AD.Event_CK = I_FCT.Event3_CK
          OR AD.Event_CK = I_FCT.Event4_CK
          OR AD.Event_CK = I_FCT.Event5_CK
          OR AD.Event_CK = I_FCT.Event6_CK
          OR AD.Event_CK = I_FCT.Event7_CK
          OR AD.Event_CK = I_FCT.Event8_CK
          OR AD.Event_CK = I_FCT.Event9_CK
          OR AD.Event_CK = I_FCT.Event10_CK
          OR AD.Event_CK = I_FCT.Event11_CK
          OR AD.Event_CK = I_FCT.Event12_CK
          OR AD.Event_CK = I_FCT.Event13_CK
          OR AD.Event_CK = I_FCT.Event14_CK
          OR AD.Event_CK = I_FCT.Event15_CK
          /*OR AD.Event_CK = I_FCT.Event16_CK
          OR AD.Event_CK = I_FCT.Event17_CK
          OR AD.Event_CK = I_FCT.Event18_CK
          OR AD.Event_CK = I_FCT.Event19_CK
          OR AD.Event_CK = I_FCT.Event20_CK
          OR AD.Event_CK = I_FCT.Event21_CK
          OR AD.Event_CK = I_FCT.Event22_CK
          OR AD.Event_CK = I_FCT.Event23_CK
          OR AD.Event_CK = I_FCT.Event24_CK
          OR AD.Event_CK = I_FCT.Event25_CK*/
      )
  )
  WITH DATA PRIMARY INDEX (Ticket_Nbr) 
  ON COMMIT PRESERVE ROWS;
  </code></pre>
  
  <p>So my question is whether it would be better to switch to JOINing once per Event CK (which is a key value by the way &amp; is the index on AOD_CKS)? Or should I go a little less verbose &amp; try splitting up the work (i.e. join once for fields 1-6, another table for 7-13, etc.) &amp; then JOIN those 4 tables together?</p>
  
  <p>Any help is much appreciated!</p>