Re: Hierachical structures - an overview

The major problem with connect-by is that it doesn't seem to differentiate between nodes and edges of the graph, which is very confusing for the end user. For a graph being a tree the matter seems simple, as we can implicitly associate a node with the adjacent edge (although, there is an confusing ambiguity of 2 choices here as well). The second problem is that it is difficult to cost connect-by access method. Basically if you sees the execution plan with connect-by, then you can ignore the cost:-)

I'm awaiting SQL Server Yukon release (I assume there is free developers version:-) to get my hands on "the latest and greatest" "recursive with" method...

"David Morse" <davem405_at_comcast.net> wrote in message news:RKzPb.115674$I06.806127_at_attbi_s01...
> HI,
>
> You can use FirstSQL/J ORDBMS and access the data with CONNECT BY to get
at
> hierarchical data with recursive queries.
>
> See http://www.firstsql.com/connectby.shtml for complete details.
>
> Dave M.
> "Mike MacSween" <mike.macsween.nospam_at_btinternet.com> wrote in message
> news:3ff7e63a$0$52881$5a6aecb4_at_news.aaisp.net.uk...
> > I have an app I need a hierachical structure for. There seem to be 3
ways
> of
> > implementing this, as far as I can see:
> >
> > 1. Adjacency list.
> > Pros - intuitive and relatively simple
> > Cons - not easily accesible via standard SQL, needs recusive queries to
> > crawl up or down the structure. I'll be doing this in Jet or perhaps MS
> SQL
> > Server, so Oracle's Connect by is out.
> >
> > 2. Nested Sets a la Joe Celko's BOM.
> > Pros - easy to access the structure via standard SQL
> > Cons - expensive/complex when the structure changes frequently.
> >
> > 3. Materialised Paths.
> > Pros - easy to access the structure via standSQL, the hierachy is
obvious
> > and stored in a single field.
> > Cons - None? The value in the 'path' field doesn't _appear_ to be
atomic,
> > that might well be a debatable point.
> >
> > 1a. Adjacency list + as per:
> > http://fungus.teststation.com/~jon/treehandling/TreeHandling.htm
> > Not sure about this. It claims to give the path upwards. It looks like
it
> > merely gives the ancestors, which isn't the same thing.
> >
> > Any other techniques?
> >
> > Any other pros and cons?
> >
> > Yours, Mike MacSween
> >
> >
> >
>
>
Received on Wed Jan 21 2004 - 20:15:38 CET