3 Replies Latest reply: Feb 7, 2013 4:49 PM by 597673 RSS

    improve performance

    597673
      I have to join (type A , type B) two columns in transaction_t to one_columns(HIST_CURR_LINE_MAP) in HIST_CURR_LINE_MAP to get a different expected percentage.
      I guess because of this condition.
      LEFT OUTER JOIN
      HIST_CURR_LINE_MAP   c
      ON
      a.trans_typeA = c.type
      LEFT OUTER JOIN
      HIST_CURR_LINE_MAP   d
      ON 
      a.trans_typeB = d.type
      my query takes three times longer than before this condition. i did create index for that but still takes longer.
      I am sure my query is not logical. would you please give me a little help. which way is better( using sub-query or union.... or join different way)
      thanks in advance.
      -----------------------------------------------------------------------------------------------------
      | Id  | Operation                       | Name                         | Rows  | Bytes | Cost (%CPU)|
      -----------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT                |                              |  1053 |   135K|   394K  (1)|
      |   1 |  HASH GROUP BY                  |                              |  1053 |   135K|   394K  (1)|
      |   2 |   NESTED LOOPS OUTER            |                              |  2276K|   286M|   394K  (1)|
      |*  3 |    HASH JOIN RIGHT OUTER        |                              |  2276K|   277M|    56  (58)|
      |   4 |     TABLE ACCESS BY INDEX ROWID | PREV_INV |     2 |    26 |     2   (0)|
      |*  5 |      INDEX RANGE SCAN           | HISTORY_INV _PK |     2 |       |     1   (0)|
      |*  6 |     HASH JOIN RIGHT OUTER       |                              |  2276K|   249M|    46  (53)|
      |   7 |      TABLE ACCESS FULL          | CURR_INV     |     2 |    26 |     3   (0)|
      |*  8 |      HASH JOIN RIGHT OUTER      |                              |  2276K|   221M|    36  (48)|
      |   9 |       TABLE ACCESS FULL         | CURR_INV     |     2 |    26 |     3   (0)|
      |  10 |       VIEW                      |                              |  2276K|   193M|    25  (36)|
      |* 11 |        HASH JOIN RIGHT OUTER    |                              |  2276K|   175M|    25  (36)|
      |  12 |         TABLE ACCESS FULL       | HIST_CURR_LINE_MAP  |  5232 | 47088 |     4   (0)|
      |* 13 |         HASH JOIN RIGHT OUTER   |                              | 84839 |  5965K|    13   (8)|
      |  14 |          TABLE ACCESS FULL      | HIST_CURR_LINE_MAP  |  5232 | 47088 |     4   (0)|
      |* 15 |          HASH JOIN RIGHT OUTER  |                              |  3162 |   194K|     9  (12)|
      |* 16 |           INDEX RANGE SCAN      | LINE _MAP _PK      |   123 |  2091 |     1   (0)|
      |  17 |           TABLE ACCESS FULL     | TRANSACTION_T    |  3162 |   142K|     7   (0)|
      
      /****query sample ****/
      select 
      a.TRANSACTION_ID,
       sum ( case when b.line_map = 'H' then a.trans_amt*hist_pct /curr_pct end) as hist_price
      sum ( case when b.line_map = 'C' then a.trans_amt*hist_pct /curr_pct ) as curr_price
      FROM 
      TRANSACTION_T    a.
      LEFT OUTER JOIN 
      LINE _MAP b.
      ON 
      (
      a.trans_date = b.trans_dtl_date 
      )
      LEFT OUTER JOIN
      HIST_CURR_LINE_MAP   c
      ON
      a.trans_typeA = c.type
      LEFT OUTER JOIN
      HIST_CURR_LINE_MAP   d
      ON 
      a.trans_typeB = d.type
      
      LEFT OUTER JOIN
      HISTORY_INV e
      ON
      a.trans_date =e.hist_trans_date and e.hist_rate_grade=d.rate_grade
      LEFT OUTER JOIN
      CURR_INV f
      ON
       a.trans_date =f.hist_trans_date and f.hist_rate_grade=d.rate_grade
      LEFT OUTER JOIN
      HISTORY_INV g
      ON
      a.trans_date =e.hist_trans_date and e.hist_rate_grade=c.rate_grade
      LEFT OUTER JOIN
      CURR_INV h
      ON
       a.trans_date =f.hist_trans_date and f.hist_rate_grade=c.rate_grade
       
      GROUP BY
       a.TRANSACTION_ID
        • 1. Re: improve performance
          riedelme
          Interesting. The costs are extremely low until the HASH JOIN OUTER in step 3 where they become much higher.

          Did you miss any operations line when you cut & pasted the posting.

          Please post the predicate information if you have it.

          Two potential trouble spots are the view in step 10 and the outer joins.

          Checlk the joins to tables using full table scans to see if indexes exist on the join columns. Indexes on these columns may/may not help.
          • 2. Re: improve performance
            Stew Ashton
            You can reduce the number of outer joins by combining the conditions:
            select ...
            FROM 
            TRANSACTION_T a
            LEFT OUTER JOIN LINE_MAP B
              ON A.TRANS_DATE = B.TRANS_DTL_DATE
            LEFT OUTER JOIN HIST_CURR_LINE_MAP C
              ON C.TYPE IN (A.TRANS_TYPEA, A.TRANS_TYPEB)
            LEFT OUTER JOIN HISTORY_INV E
              ON A.TRANS_DATE = E.HIST_TRANS_DATE
              AND E.HIST_RATE_GRADE = C.RATE_GRADE
            LEFT OUTER JOIN CURR_INV F
              ON A.TRANS_DATE = F.HIST_TRANS_DATE
              and f.hist_rate_grade = c.rate_grade 
            GROUP BY
             a.TRANSACTION_ID
            You might not even need outer joins if you combine the conditions...

            You can use expressions like
            CASE C.TYPE
            WHEN A.TRANS_TYPEA THEN ...
            WHEN A.TRANS_TYPEB THEN ...
            END
            to do different calculations depending on which join condition was met.
            • 3. Re: improve performance
              597673
              if i create two different index keys in hist_curr_line_map table, does it help the performance?