Write a query to return parent location base on level (p2)

Question

At the my previous question, I can solve how to fetch the location hierarchy. But It seems I have another issue when try to integrate with the system

so I have 2 tables like

location (~20k records)

id	Name	is_active	level1	level2	level3	level4
1	A	true	A	null	null	null
2	A>B	true	A	B	null	null
3	A>B>C	true	A	B	C	null
4	A>B>C>D	true	A	B	C	D
5	X	true	X	null	null	null

log (~5m records)

id	Name	location_id
1	log 001	3
2	log 002	5

So the goal is I would like to fetch the location hierarchy which has log only. The problem is when I try to join with the log table, It has a performance issue. The query seems to take 10+ minutes

Here is how my query looks like demo I'm trying this query

SELECT *
FROM   location
START WITH id IN (SELECT DISTINCT location_id FROM log_response)
CONNECT BY PRIOR name
           = name || '>' || COALESCE(PRIOR level4, PRIOR level3, PRIOR level2)
ORDER BY NAME

or

WITH cte(location_id) AS (
     SELECT DISTINCT location_id FROM log_response
)
SELECT *
FROM   location
START WITH id IN (SELECT location_id FROM cte)
CONNECT BY PRIOR name
           = name || '>' || COALESCE(PRIOR level4, PRIOR level3, PRIOR level2)
ORDER BY NAME

p/s The expectation of the query on the above example should return the location have id in (1,2,3,5)

id	Name	is_active	level1	level2	level3	level4
1	A	true	A	null	null	null
2	A>B	true	A	B	null	null
3	A>B>C	true	A	B	C	null
5	X	true	X	null	null	null

Thank you very much!

Answer 1

You can use EXISTS (rather than IN and DISTINCT):

SELECT *
FROM   location l
START WITH EXISTS(SELECT 1 FROM log WHERE location_id = l.id)
CONNECT BY name = SUBSTR(PRIOR name, 1, INSTR(PRIOR name, '>', -1) - 1)

Which, for the sample data:

CREATE TABLE location (id, Name, is_active, level1, level2, level3, level4) AS
SELECT 1, 'A',       'true', 'A', null, null, null FROM DUAL UNION ALL
SELECT 2, 'A>B',     'true', 'A', 'B',  null, null FROM DUAL UNION ALL
SELECT 3, 'A>B>C',   'true', 'A', 'B',  'C',  null FROM DUAL UNION ALL
SELECT 4, 'A>B>C>D', 'true', 'A', 'B',  'C',  'D'  FROM DUAL UNION ALL
SELECT 5, 'X',       'true', 'X', null, null, null FROM DUAL;

CREATE TABLE log (id, Name, location_id) AS
SELECT 1, 'log 001', 3 FROM DUAL UNION ALL
SELECT 2, 'log 002', 5 FROM DUAL;

Outputs:

ID	NAME	IS_ACTIVE	LEVEL1	LEVEL2	LEVEL3	LEVEL4
3	A>B>C	true	A	B	C	null
2	A>B	true	A	B	null	null
1	A	true	A	null	null	null
5	X	true	X	null	null	null

Or:

SELECT DISTINCT *
FROM   location l
START WITH EXISTS(SELECT 1 FROM log WHERE location_id = l.id)
CONNECT BY 
       PRIOR level1 = level1
AND    (  (   level2 IS NULL AND level3 IS NULL AND level4 IS NULL
          AND PRIOR level2 IS NOT NULL AND PRIOR level3 IS NULL AND PRIOR level4 IS NULL
          )
          OR (   PRIOR level2 = level2
             AND ( (   level3 IS NULL AND level4 IS NULL
                   AND PRIOR level3 IS NOT NULL AND PRIOR level4 IS NULL
                   )
                 OR (   PRIOR level3 = level3
                    AND level4 IS NULL AND PRIOR level4 IS NOT NULL
                    )
                 )
              )
      )

fiddle