indexing a "path"

we are managing a folder structure in a table like this

id parentid path

1    null            \1
2    1               \1\2

etc.

a index is created on path and we exceute a "deep" search in a folder and its subfolders via:

where path like '\1\...\%'

the field id is a varchar and always contains 10 characters (sample above is simplified)

so, is it a problem that many values on path start with the same string?

and why doesn't oracle 8.1.7 use the index (statistics are up to date) but performs a table scan - even an optimizer hint does not change this, only switching to optimizer mode "rule" helps (and we really don't want to set or instance to "rule"...)

p.s.

in a query like: path = '\1\...' the index is used Received on Fri Mar 15 2002 - 06:41:41 CST