title: Improving performance: Very Slow Oracle SQL Join
body: <p>I am a newbie in SQL querying and I am spending 3hrs to get the whole result of joining 2 queries.
  I have focused on using left joins and avoided using subqueries on the select statement after researching. However it is still extremely slow. I have no close friends who know sql enough to explain whats wrong or what I approach I should take.
  I am also new here so if this question is not allowed please inform me and I will remove it immediately.</p>
  
  <p>This is the structure of the query...
  The first query will get the member details.
  The second query will get the transaction details.
  The relationship is, 
  one product has many sub-plans which has many members.
  One product also has many transactions which is made on a per product basis.
  I am required to show all transactions and duplicate each line for each member.
  I joined the queries using the product primary key.
  Prior to joining, I have tested both individual queries and they turned out fine. Only 1-2 secs and I get the result.
  But joining the two, I end up with 3 hrs of waiting.</p>
  
  <pre><code>SELECT
  MPPFF.N_DX,
  MPPFF.PM_A_P,
  MPPFF.FEE1,
  MPPFF.FEE2,
  MPPFF.FEE3,
  MPPFF.FEE4,
  MPPFF.FEE11,
  MPPFF.FEE12,
  MPPFF.FEE5,
  MPPFF.N_NO,
  MPPFF.SETN_DX,
  MPPFF.PRIME_NO,
  MPPFF.SECN_NO,
  MPPFF.COMM_A,
  MPPFF.TYX_NO,
  MPPFF.P_NAME,
  MPPFF.B_BFX,
  MPPFF.B_FM,
  MPPFF.B_TO,
  MPPFF.BB_NAME_P,
  MPPFF.BB_NAME_S,
  MPPFF.REVERSE_BFX,
  MPPFF.TYX_REF_NO,
  MPPFF.BB_NO_AX,
  MPPFF.BB_NAME_AX,
  MPPFF.DXC,
  MPPFF.ST,
  MPPFF.DAY,
  MPPFF.CE_D_PRODUCT,
  MPPFF.CE_H,
  MPPFF.AS_C_E,
  MPPFF.BCH,
  MPPFF.RCPY_NO,
  MPPFF.RE_BFX,
  MPPFF.A_END,
  MPPFF.PLACE,
  MPPFF.MEMB_DX,
  MPPFF.MBR_NO,
  MPPFF.MBR_TR_BFX,
  MPPFF.CE_D_TERM_CE,
  MPPFF.MEMBER_AS,
  MPPFF.C_USER,
  MPPFF.C_BFX,
  MPPFF.U_USER,
  MPPFF.U_BFX
  
  FROM (
          SELECT
          FF.N_DX,
          FF.PM_A_P,
          FF.FEE1,
          FF.FEE2,
          FF.FEE3,
          FF.FEE4,
          FF.FEE11,
          FF.FEE12,
          FF.FEE5,
          FF.N_NO,
          FF.SETN_DX,
          FF.PRIME_NO,
          FF.SECN_NO,
          FF.COMM_A,
          FF.TYX_NO,
          FF.P_NAME,
          FF.B_BFX,
          FF.B_FM,
          FF.B_TO,
          FF.BB_NAME_P,
          FF.BB_NAME_S,
          FF.REVERSE_BFX,
          FF.TYX_REF_NO,
          FF.BB_NO_AX,
          FF.BB_NAME_AX,
          FF.DXC,
          FF.ST,
          FF.DAY,
          FF.CE_D_PRODUCT,
          FF.CE_H,
          FF.AS_C_E,
          FF.RCPY_NO,
          FF.RE_BFX,
          FF.A_END,
          FF.BCH,
          MPP.MBR_NO,
          MPP.MBR_TR_BFX,
          MPP.CE_D_TERM_CE,
          MPP.C_USER,
          MPP.C_BFX,
          MPP.U_USER,
          MPP.U_BFX,
          MPP.PLACE,
          MPP.MEMBER_AS,
          MPP.TYX_DX,
          MPP.AS_DX,
          MPP.PRODUCT,
          MPP.POPL_DX,
          MPP.MEMB_DX,
          FF.TYX_DX
  
          FROM (
                  SELECT
                  MBR.MEMB_DX,
                  MBR.MBR_NO,
                  MBR.MBR_TR_BFX,
                  MBR.CE_D_TERM_CE,
                  MBR.C_USER,
                  MBR.C_BFX,
                  MBR.U_USER,
                  MBR.U_BFX,
                  MPP.PLACE,
                  MPP.MEMBER_AS,
                  MPP.TYX_DX,
                  MPP.AS_DX,
                  MPP.PRODUCT,
                  MPP.POPL_DX
  
                  FROM (
                          SELECT
                          MPP.PLACE,
                          MPP.MEMBER_AS,
                          MPP.TYX_DX,
                          MPP.AS_DX,
                          MPP.PRODUCT,
                          MPP.POPL_DX,
                          MMP.MEMB_DX
  
                          FROM(
                                  SELECT
                                  MPP.PLACE,
                                  MPP.TYX_AS_DXC MEMBER_AS,
                                  MPP.TYX_DX,
                                  MPP.AS_DX,
                                  MPP.POPL_DX,
                                  RPT.PRODUCT
  
                                  FROM
                                  TABLE1 MPP
  
                                  LEFT JOIN (
                                          SELECT
                                          SUBSTR(CE_D_PRODUCT,9) PRODUCT,
                                          AS_DX
                                          FROM
                                          TABLE6 RPT,
                                          TABLE7 PP
                                          WHERE
                                          PP.PRTY_DX = RPT.PRTY_DX
                                  ) RPT
                                  ON  MPP.AS_DX = RPT.AS_DX
  
                          ) MPP
  
                          LEFT JOIN (
                                  SELECT
                                  POPL_DX,
                                  MEMB_DX
                                  FROM
                                  TABLE4
                          )MMP
                          ON MPP.POPL_DX=MMP.POPL_DX
  
                  ) MPP,
                  (
                          SELECT
                          MBR.MEMB_DX,
                          MBR.MBR_NO,
                          MBR.TERM_BFX MBR_TR_BFX,
                          MBR.CE_D_TERM_CE,
                          MBR.C_USER,
                          MBR.C_BFX,
                          MBR.U_USER,
                          MBR.U_BFX
  
                          FROM
                          TABLE8 MBR
                  ) MBR
                  WHERE
                  MPP.MEMB_DX = MBR.MEMB_DX
          ) MPP
          INNER JOIN
          (
                  SELECT
                  FF.N_DX,
                  ROUND(CB.FEE5 * FF.RATE,2) PM_A_P,
                  CB.FEE1,
                  CB.FEE2,
                  CB.FEE3,
                  CB.FEE4,
                  CB.FEE11,
                  CB.FEE12,
                  CB.FEE5,
                  FF.N_NO,
                  FF.SETN_DX,
                  FF.PRIME_NO,
                  FF.SECN_NO,
                  FF.COMM_A,
                  FF.TYX_NO,
                  FF.P_NAME_1||', '||FF.P_NAME_2||' '||FF.P_NAME_3 P_NAME,
                  FF.B_BFX,
                  FF.B_FM,
                  FF.B_TO,
                  FF.BB_NAME_1_P||', '||FF.BB_NAME_2_P BB_NAME_P,
                  FF.BB_NAME_1_S||', '||FF.BB_NAME_2_S BB_NAME_S,
                  CB.REVERSE_BFX,
                  FF.TYX_REF_NO,
                  FF.BB_NO_AX,
                  FF.BB_NAME_1_AX||' '|| FF.BB_NAME_2_AX BB_NAME_AX,
                  CASE 
                          WHEN FF.CE_D_ST IN ('A', 'B', 'C') THEN 'AC'
                          WHEN FF.DAY &gt;1 THEN 'NEW'
                          ELSE 'AB'
                  END DXC,
                  FF.CE_D_ST ST,
                  FF.DAY,
                  FF.CE_D_PRODUCT,
                  FF.CE_D_COMP CE_H,
                  FF.AS_C AS_C_E,
                  FF.RCPY_NO,
                  FF.RE_BFX,
                  ROUND(CB.A_S,2) A_END,
                  FF.TYX_DX,
                  MP.BCH
  
                  FROM
                  TABLE2 CB,
                  TABLE3 FF
  
                  LEFT JOIN (
                          SELECT
                          SUBSTR(CE_D_BCH_O,13) BCH,
                          TYX_DX
                          FROM
                          TABLE5 MP
                  )MP
                  ON MP.TYX_DX = FF.TYX_DX
  
                  WHERE
                  FF.SETN_DX = CB.SETN_DX AND
                  EXTRACT( YEAR FROM FF.EFF_BFX) &gt;=2013
          ) FF    
          ON MPP.TYX_DX = FF.TYX_DX
  
  )MPPFF
  ;
  </code></pre>