1 Reply Latest reply: Apr 6, 2012 9:06 AM by AlanWms RSS

    Oracle Query tuning

    kolipaka
      Can any one simplify this query, as this is taking more time to execute. This is query is basically pulling data for report.


      ************************************************
      SELECT diff. set of columns
      FROM gr gr1
      LEFT JOIN gro gro1
      ON gr1.key = gro1.key
      LEFT JOIN g1
      ON gro1.key = g1.key
      LEFT JOIN o o1
      ON gro1.key = o1.key
      LEFT JOIN
      (SELECT s.key,
      MIN(s.sdate) AS minStart,
      MAX(s.edate) AS maxEnd
      FROM
      (SELECT ofs1.key,
      gp1.sdt AS start_date,
      gp1.edt AS end_date
      FROM
      (SELECT gro1.key,
      o1.key,
      o1.pokey,
      level
      FROM offset_e o1,
      gro gro1
      WHERE o1.key = gro1.key
      START WITH gro1.key =0
      CONNECT BY nocycle prior o1.key = o1.pokey
      ) ofs1
      LEFT OUTER JOIN gi gi1
      ON ofs1.key = gi1.key
      LEFT OUTER JOIN gp gp1
      ON gi1.key = gp1.key
      UNION ALL
      SELECT gro2.key,
      gp2.sdt AS start_date,
      gp2.edt AS end_date
      FROM gro2 gro3
      INNER JOIN gp2 gp3
      ON gro3.key = gp3.key
      ) s
      GROUP BY s.key
      ) m ON gr.key = m.key
      WHERE gr.key IN
      (SELECT gr2.key
      FROM gr2
      LEFT JOIN gr12
      ON gr2.key = gr12.key
      LEFT JOIN guar_e g2
      ON gr12.key = g2.key
      LEFT JOIN offset o2
      ON gr12.ey = o2.offset_key
      )
      ORDER BY gr.rpt_key,
      gro1.seq_num

      *********************************************************


      --------------------------------------------------------------------------------
      | Id | Operation | Name |
      --------------------------------------------------------------------------------
      | 0 | SELECT STATEMENT | |
      | 1 | SORT ORDER BY | |
      | 2 | HASH JOIN OUTER | |
      | 3 | HASH JOIN RIGHT OUTER | |
      | 4 | TABLE ACCESS FULL | G |
      | 5 | HASH JOIN RIGHT OUTER | |
      | 6 | TABLE ACCESS FULL | O |
      | 7 | HASH JOIN RIGHT OUTER | |
      | 8 | TABLE ACCESS FULL | GRO |
      | 9 | HASH JOIN RIGHT SEMI | |
      | 10 | VIEW | VW_NSO_1 |
      | 11 | HASH JOIN RIGHT OUTER | |
      | 12 | TABLE ACCESS FULL | O |
      | 13 | HASH JOIN OUTER | |
      | 14 | INDEX FAST FULL SCAN | PK_GR |
      | 15 | TABLE ACCESS FULL | GRO |
      | 16 | TABLE ACCESS FULL | GR |
      | 17 | VIEW | |
      | 18 | HASH GROUP BY | |
      | 19 | VIEW | |
      | 20 | UNION-ALL | |
      | 21 | HASH JOIN RIGHT OUTER | |
      | 22 | TABLE ACCESS FULL | G |
      | 23 | HASH JOIN RIGHT OUTER | |
      | 24 | TABLE ACCESS FULL | G |
      | 25 | VIEW | |
      | 26 | CONNECT BY NO FILTERING WITH START-WITH| |
      | 27 | HASH JOIN | |
      | 28 | TABLE ACCESS FULL | O |
      | 29 | TABLE ACCESS FULL | GRO |
      | 30 | HASH JOIN | |
      | 31 | TABLE ACCESS FULL | GRO |
      | 32 | TABLE ACCESS FULL | GP |
      --------------------------------------------------------------------------------
        • 1. Re: Oracle Query tuning
          AlanWms
          It helps to format your code so it is easier to read:
          SELECT {col1, col2, ....}
          FROM   gr gr1
                 left join gro gro1
                   ON gr1.key = gro1.key
                 left join g1
                   ON gro1.key = g1.key
                 left join o o1
                   ON gro1.key = o1.key
                 left join (SELECT s.key,
                                   MIN(s.sdate) AS minstart,
                                   MAX(s.edate) AS maxend
                            FROM   (SELECT ofs1.key,
                                           gp1.sdt AS start_date,
                                           gp1.edt AS end_date
                                    FROM   (SELECT gro1.key,
                                                   o1.key,
                                                   o1.pokey,
                                                   LEVEL
                                            FROM   offset_e o1,
                                                   gro gro1
                                            WHERE  o1.key = gro1.key
                                            START WITH gro1.key = 0
                                            CONNECT BY nocycle PRIOR o1.key = o1.pokey) ofs1
                                           left outer join gi gi1
                                             ON ofs1.key = gi1.key
                                           left outer join gp gp1
                                             ON gi1.key = gp1.key
                                    UNION ALL
                                    SELECT gro2.key,
                                           gp2.sdt AS start_date,
                                           gp2.edt AS end_date
                                    FROM   gro2 gro3
                                           inner join gp2 gp3
                                             ON gro3.key = gp3.key) s
                            GROUP  BY s.key) m
                   ON gr.key = m.key
          WHERE  gr.key IN (SELECT gr2.key
                            FROM   gr2
                                   left join gr12
                                     ON gr2.key = gr12.key
                                   left join guar_e g2
                                     ON gr12.key = g2.key
                                   left join offset o2
                                     ON gr12.ey = o2.offset_key)
          ORDER  BY gr.rpt_key,
                    gro1.seq_num 
          Edited by: AlanWms on Apr 6, 2012 10:05 AM