Skip navigation.

kimant's blog

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:


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 OR REPLACE TYPE node_parent_type AS OBJECT ( lvl NUMBER, node VARCHAR2(200), PARENT VARCHAR2(200) )