By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE location_master (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>E', 'true', 'A', 'E', NULL, NULL FROM DUAL UNION ALL
SELECT 6, 'X>E>C', 'true', 'A', 'E', 'C', NULL FROM DUAL UNION ALL
SELECT 7, 'X', 'true', 'A', 'E', 'C', NULL FROM DUAL UNION ALL
SELECT 8, 'X>E>C>D', 'true', 'A', 'E', 'C', 'D' FROM DUAL;
8 rows affected
CREATE TABLE log_response (id, Name, location_id) AS
SELECT 1, 'Log 001', 3 FROM DUAL UNION ALL
SELECT 2, 'Log 002', 5 FROM DUAL;
2 rows affected
SELECT *
FROM location_master
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
| 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 |
| 7 | X | true | A | E | C | null |
| 5 | X>E | true | A | E | null | null |
explain plan for
SELECT *
FROM location_master
START WITH id IN (SELECT DISTINCT location_id FROM log_response)
CONNECT BY name = SUBSTR(PRIOR name, 1, INSTR(PRIOR name, '>', -1) - 1)
ORDER BY NAME
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
| PLAN_TABLE_OUTPUT |
|---|
| Plan hash value: 1854579171 |
| ------------------------------------------------------------------------------------------------- |
| | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | |
| ------------------------------------------------------------------------------------------------- |
| | 0 | SELECT STATEMENT | | 4 | 132 | 10 (10)| 00:00:01 | |
| | 1 | SORT ORDER BY | | 4 | 132 | 10 (10)| 00:00:01 | |
| |* 2 | CONNECT BY WITHOUT FILTERING| | | | | | |
| |* 3 | HASH JOIN SEMI | | 2 | 46 | 6 (0)| 00:00:01 | |
| | 4 | TABLE ACCESS FULL | LOCATION_MASTER | 8 | 160 | 3 (0)| 00:00:01 | |
| | 5 | TABLE ACCESS FULL | LOG_RESPONSE | 2 | 6 | 3 (0)| 00:00:01 | |
| | 6 | TABLE ACCESS FULL | LOCATION_MASTER | 8 | 160 | 3 (0)| 00:00:01 | |
| ------------------------------------------------------------------------------------------------- |
| Predicate Information (identified by operation id): |
| --------------------------------------------------- |
| 2 - filter("LOCATION_MASTER"."NAME"=SUBSTR(PRIOR |
| "LOCATION_MASTER"."NAME",1,INSTR(PRIOR "LOCATION_MASTER"."NAME",'>',(-1))-1)) |
| 3 - access("ID"="LOCATION_ID") |
-- SELECT *
-- FROM location_master t
-- WHERE EXISTS(
-- SELECT 1
-- FROM location_master x
-- WHERE x.name LIKE t.name || '%'
-- AND x.id IN (3,5)
-- )
-- ORDER BY t.NAME
ORA-00900: invalid SQL statement
-- SELECT *
-- FROM table_name t
-- WHERE EXISTS(
-- SELECT 1
-- FROM table_name x
-- WHERE ( t.level4 = x.level4 OR t.level4 IS NULL )
-- AND ( t.level3 = x.level3 OR t.level3 IS NULL )
-- AND ( t.level2 = x.level2 OR t.level2 IS NULL )
-- AND t.level1 = x.level1
-- AND x.id = 3
-- )
ORA-00900: invalid SQL statement
-- SELECT *
-- FROM table_name
-- START WITH id = 3
-- CONNECT BY PRIOR name
-- = name || '>' || COALESCE(PRIOR level4, PRIOR level3, PRIOR level2)
ORA-00900: invalid SQL statement
-- SELECT *
-- FROM table_name
-- START WITH id = 3
-- CONNECT BY name = SUBSTR(PRIOR name, 1, INSTR(PRIOR name, '>', -1) - 1)
ORA-00900: invalid SQL statement