Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> comp.databases.theory -> Re: Materialised Path Heirarchy

Re: Materialised Path Heirarchy

From: David Portas <REMOVE_BEFORE_REPLYING_dportas_at_acm.org>
Date: Thu, 27 Mar 2003 11:58:12 -0000
Message-ID: <#tnI$fF9CHA.2216@TK2MSFTNGP12.phx.gbl>

  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
>
>
Received on Thu Mar 27 2003 - 05:58:12 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US