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
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) )