Creating a hierical tree structure from strings

Recently, I have been helping a company in redesigning their schema, and one issue was that they stored hieracal trees in varchar2(4000) column, one full branch per row.

Data was if this kind:

/Oracle/8/1/5/Microsoft
/Oracle/8/1/5/VMS
/Oracle/8/1/5/0/1/VMS

etc.

Their main problem was that searching in this was really ugly, as they had to full table/index scan a lot, when searching using like '%%'

I came up with this solution to convert this into a small table that can be queried using connect by instead:


CREATE GLOBAL TEMPORARY TABLE XXX_TREE_TEMP
( ID NUMBER NOT NULL
, node_name VARCHAR2(256)
, parent_branch VARCHAR2(4000)
)
ON COMMIT DELETE ROWS;
INSERT INTO XXX_TREE_TEMP
SELECT ROWNUM
, b.node
, b.parent_branch
FROM (
WITH p AS (SELECT the_full_node
FROM big_table
WHERE the_full_node IS NOT NULL
GROUP BY the_full_node)
SELECT DISTINCT
t.node
, SUBSTR(p.the_full_node, 1, INSTR(p.the_full_node,'/',1,t.lvl+1)-1) AS parent_branch
, t.lvl
FROM P, TABLE(Str2branch(p.the_full_node,'/')) t
ORDER BY t.lvl, t.node
) b
;

INSERT INTO XXX_TREE
SELECT t1.ID AS node_id
, MIN(t2.ID) AS parent_id
, t1.node_name
FROM XXX_TREE_TEMP t1, XXX_TREE_TEMP t2
WHERE t1.parent_branch = DECODE(t2.parent_branch,NULL,'',t2.parent_branch)'/'t2.node_name
GROUP BY t1.ID, t1.node_name
UNION ALL
SELECT t1.ID AS node_id
, NULL AS parent_id
, t1.node_name
FROM XXX_TREE_TEMP t1
WHERE t1.parent_branch IS NULL
GROUP BY t1.ID, t1.node_name
ORDER BY 1, 2
;

This can then be queries like this to find the original full braches:

SELECT ID
, SYS_CONNECT_BY_PATH(a.node_name, '/')'/' navn
FROM XXX_TREE a
CONNECT BY PRIOR ID = parent_id
START WITH parent_id IS NULL;


Limiting to branches with a special string is trivial now, as the string can be indexed ( xxx_tree.node_name ).

When it goes into production, I will update about the success.

I would have liked to not use the temporary table, but my first shot at this failed (nested with statements), and this is not som important, as this is a one shot load.