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 12:15:04 -0000
Message-ID: <uM5QapF9CHA.2308@TK2MSFTNGP10.phx.gbl>


I see. Query should use the delimiter:

select e1.ename from emp e1, emp e2
where e2.path like e1.path || '.%'
and e2.name = 'FORD'

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

"David Portas" <REMOVE_BEFORE_REPLYING_dportas_at_acm.org> wrote in message
news:#tnI$fF9CHA.2216_at_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 - 06:15:04 CST

Original text of this message

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