How to improve query performance on left our joins.

Database

How to improve query performance on left our joins.

Hi Dieter,

Can u please help me in optimization of below query.

 INSERT INTO HBARCH.Table1

 SELECT

     RIA.COMPANY_ID

    ,RIA.DOC_NO

    ,RIA.QCI

    ,RIA.RIA_TOUR_CDE

    ,RIA.PNR_LOC

    ,T423.TRAVPLNI

    ,T423.PNR_ITEM_NO

    ,T433.TOUR_CDE

 FROM HBARCH.table1 RIA

  LEFT OUTER JOIN  table3              T423

  ON          RIA.DOC_NO    =  T423.RSVN_TKT_NO    /* NUPI (DOC_NO) Joining with Non index column ) */

   LEFT OUTER JOIN  table4 T433

  ON          T423.TRAVPLNI = T433.TRAVPLNI   /* NUPI INDEX  column JOIN */

  AND         T423.PNR_ITEM_NO = T433.PNR_ITEM_NO  /* NON INDEX COLUMN  JOIN */

  GROUP BY 1,2,3,4,5,6,7,8;

Rewrite the same above query as below two forms.. but did not see the good performance. The CPU impact, CPU skew, IOSKEW is high for these queries.... also it generates the same explain plan for 3 queries. table 3 and table 4 having 150 M records

The number of transactions in the tables

INSERT INTO HBARCH.table1

  SELECT                                              

    RIA.COMPANY_ID                                  

   ,RIA.DOC_NO                                      

   ,RIA.QCI                                         

   ,RIA.RIA_TOUR_CDE                                

   ,RIA.PNR_LOC                                     

   ,B.TRAVPLNI                                   

   ,B.PNR_ITEM_NO                                

   ,T433.TOUR_CDE                                   

FROM HBARCH.table2 RIA

 LEFT OUTER JOIN

(

SELECT   T423.* FROM HBARCH.table1 RIA                                            

INNER JOIN

table3  T423

 ON

 RIA.DOC_NO    =  T423.RSVN_TKT_NO  /* NUPI (DOC_NO) Joining with Non index column ) */

) B

 ON          RIA.DOC_NO    =  B.RSVN_TKT_NO   /* NUPI (DOC_NO) Joining with Non index column ) */

 LEFT OUTER JOIN  table4T433            

 ON          B.TRAVPLNI = T433.TRAVPLNI          

 AND         B.PNR_ITEM_NO = T433.PNR_ITEM_NO     /* NON INDEX COLUMN  JOIN */

 GROUP BY 1,2,3,4,5,6,7,8;

and INSERT INTO HBARCH.table1

  SELECT                                              

    RIA.COMPANY_ID                                  

   ,RIA.DOC_NO                                      

   ,RIA.QCI                                         

   ,RIA.RIA_TOUR_CDE                                

   ,RIA.PNR_LOC                                     

   ,B.TRAVPLNI                                   

   ,B.PNR_ITEM_NO                                

   ,T433.TOUR_CDE                                   

FROM HBARCH.table1 RIA

 LEFT OUTER JOIN

(

SELECT   

TRAVPLNI

,PNR_ITEM_NO

,RSVN_TKT_NO

FROM table3

) B ( TRAVPLNI,PNR_ITEM_NO,RSVN_TKT_NO)

 ON          RIA.DOC_NO    =  B.RSVN_TKT_NO   /* NUPI (DOC_NO) Joining with Non index column ) */

 LEFT OUTER JOIN  table4T433            

 ON          B.TRAVPLNI = T433.TRAVPLNI          

 AND         B.PNR_ITEM_NO = T433.PNR_ITEM_NO     /* NON INDEX COLUMN  JOIN */

 GROUP BY 1,2,3,4,5,6,7,8;

Regards,

Ramaiah