Performance Issue – Hierarchical query LEVEL

Sample Query with Performance Issue:
SELECT    progress_date, LEVEL slno
               FROM 
                (SELECT
   PROGRESS_ID,progress_date, MAX (cnt) cnt 
                 
        FROM (SELECT   sdp.PROGRESS_ID, progress_date,
                 
                     
 (SELECT meaning
                 
                     
    FROM smpib_table
                 
                     
   WHERE lookup_id =
                 
                     
            activity_lookup_id)
                 
                     
                     
       activity,
                 
                     
 COUNT (activity_lookup_id) cnt
                 
                  FROM table1 sdp,
                 
                     
 Table2 shp
                 
                 WHERE
sdp.progress_id = shp.progress_id
                 
                   AND
progress_date
                 
                     
    BETWEEN :p42_from_date_filter
                 
                     
        AND :p42_to_date_filter
                 
              GROUP BY
 sdp.PROGRESS_ID,progress_date, activity_lookup_id)
                 
    GROUP BY  PROGRESS_ID,progress_date)
             GROUP BY
 progress_date, LEVEL
         CONNECT BY  cnt>= LEVEL
Solution:
CREATE TYPE t_tf_row AS OBJECT (
  id           NUMBER,
  progress_date  date
);
CREATE TYPE t_tf_tab IS TABLE OF t_tf_row;
CREATE OR REPLACE FUNCTION get_tab_tf (p_from_date IN
date,p_to_date date) RETURN t_tf_tab AS
  l_tab  t_tf_tab := t_tf_tab();
BEGIN
for rec in (SELECT    PROGRESS_ID,progress_date, MAX
(cnt) cnt
                 
        FROM (SELECT   sdp.PROGRESS_ID, progress_date,
                 
                     
 (SELECT meaning
                 
                     
    FROM smpib_lookups
                 
                     
   WHERE lookup_id =
                 
                     
            activity_lookup_id)
                 
                     
                     
       activity,
                 
                     
 COUNT (activity_lookup_id) cnt
                 
                  FROM table1 sdp,
                 
                       Table2
shp
                 
                 WHERE
sdp.progress_id = shp.progress_id
                 
                   AND
progress_date
                 
                     
    BETWEEN p_from_date
                 
                     
        AND p_to_date
                 
              GROUP BY
 sdp.PROGRESS_ID,progress_date, activity_lookup_id)
                 
    GROUP BY  PROGRESS_ID,progress_date)
                 
    Loop
                 
    FOR in_rec IN 1 .. rec.cnt
                 
    loop
                 
    l_tab.extend;
                 
    l_tab(l_tab.last) := t_tf_row(in_rec,rec.progress_date);
                 
    end loop;
                 
    end loop;
     RETURN l_tab;
END;
Replace the query as given below:
SELECT id slno,progress_date from
table(get_tab_tf(:from_date_filter,:to_date_filter) );

  • October 15, 2014 | 21 views