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) );