Oracle Indexing a column with delimiter

Question

We've a item table with a delimiter that contain its parent's tree

item,Level,parent,parent_tree,cost
a,0,null,a|,0
b,1,a,a|b,10
c,2,b,a|b|c,10
d,2,b,a|b|d,10
e,1,a,a|e,10
f,2,e,a|e|f,10

We currently using

select sum(cost) from items where parent_tree like 'a|%' 

In order to get all the sub-trees costings without complex joining of tables, however, performances of the full-text search has slowed down significantly.

Is there anyway to create a index for delimiter values? such that our trees could increase up to 20 levels

Answer 1

1. This is not a "full-text" search.
2. An index on parent_tree will probably do the job.

3. Adding a root column will be neater and might give the optimizer a better chance to take the right decisions.

   ... where root = 'a'