Re: Materialised Path Heirarchy

1. Yes
2. I don't see the problem. In the example query you give there is no > or < predicate anyway.

To take another example from the article:

select e1.ename from emp e1, emp e2
where e2.path > e1.path and e2.path < e1.path || 'Z' and e2.name = 'FORD'

Alphanumeric sorting isn't a problem here because you are simply comparing relative paths. The problem you describe is relevant only to *sorting* on numeric strings.

--
David Portas
------------
Please reply only to the newsgroup
--

"stu" <smcgouga_at_nospam.co.uk> wrote in message
news:b5umbg$bhm$1$8300dec7_at_news.demon.co.uk...


> http://www.dbazine.com/tropashko4.html

> (about 2 pages down they talk about the materialised path)


>

> 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

>

>