increase performance of parent child query

Question

i have a table with 4 million of record that contain parent_id and child_id and person table with flag field in oracle i need plsql code with for loop and one person_id for input that get all relation of that person the input id will be one of the id in persons table that have '1' value in flag field i wrote this plsql code but its too slow would you please help to improve the performance?

FOR rec1 IN (SELECT p.ID
             FROM PERSONS p
             WHERE p.CHANGE_FLAG = '1')
LOOP
  INSERT INTO TEST_CYCLE (person_id)
  WITH cte (id) AS (
    SELECT r.PARENT_PERSON_ID
    FROM PERSON_FAMILY r
    WHERE rec1.id IN (r.CHILD_PERSON_ID, r.PARENT_PERSON_ID)
  UNION ALL
    SELECT CASE
           WHEN c.id = t.PARENT_PERSON_ID
           THEN t.CHILD_PERSON_ID
           ELSE t.PARENT_PERSON_ID
           END
    FROM   PERSON_FAMILY  t
           JOIN cte c
           ON c.id IN (t.CHILD_PERSON_ID, t.PARENT_PERSON_ID)
  )
  CYCLE id SET is_cycle TO '1' DEFAULT   0
  SELECT c.id
  FROM   cte c;
END LOOP;

Please show the query execution plan and describe what you consider "slow". Recursive query is not fast anyway — astentx, Sep 21 '21 at 06:37

score 1 · Answer 1 · answered Sep 21 '21 at 08:59

Don't use a FOR loop and do it all in a single query:

INSERT INTO TEST_CYCLE (person_id)
WITH cte (pid, id) AS (
  SELECT p.id,
         r.PARENT_PERSON_ID
  FROM   PERSON_FAMILY r
         INNER JOIN PERSONS p
         ON p.id IN (r.CHILD_PERSON_ID, r.PARENT_PERSON_ID)
  WHERE  p.CHANGE_FLAG = '1'
UNION ALL
  SELECT c.pid,
         CASE
         WHEN c.id = t.PARENT_PERSON_ID
         THEN t.CHILD_PERSON_ID
         ELSE t.PARENT_PERSON_ID
         END
  FROM   PERSON_FAMILY  t
         JOIN cte c
         ON c.id IN (t.CHILD_PERSON_ID, t.PARENT_PERSON_ID)
)
CYCLE pid, id SET is_cycle TO '1' DEFAULT   0
SELECT c.id
FROM   cte c;

increase performance of parent child query

1 Answers1