Materialised Path Heirarchy

I need to set up a hierarchy in a database. I would like to understand the nested set but can not quite get my head round it as quickly as is required. So that aside I would like to ask some questions about the materialised path.

1. Am i correct in saying an index over the path field could be used if I parse the path on the client and return a query with an SQL IN statement? eg select * from .... where path in ('1' 1.1', '1.2', ...)
2. Do queries such as select e1.ename from emp e1, emp e2 where e2.path like e1.path || '%' and e2.name = 'FORD' only work for up to 9 nodes in one level? remembering '8' < '9' > '10' when comparing strings.

The only way I can see round this is to always use x characters to represent each level. e.g '0008' < '0009' < '0010'

But this would require hard coding the max number of nodes in each level
(9999 in this case) which i do not want to do.

comments or pointers?

cheers
stu Received on Thu Mar 27 2003 - 12:11:00 CET