Description:
A parts hierarchy, particularly the case of parts in a kit with the kit/assembly having multiple levels. Another example is parts succession, where older parts are replaced by newer ones.
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) );
Summary:
This Post explained what are the steps should follow to create Performance issue in hierarchical query level
Queries?
Do drop a note by writing us at contact@staging.doyensys.com or use the comment section below to ask your questions.
