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

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)

log (~5m records)

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)

Thank you very much!

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:

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