Re: Fetching from a hierarchical data structure?

"Maury Markowitz" <maury_at_remove_this.sympatico.ca.invalid> wrote in message news:CEoz6.37661$XV.9494838_at_news3.rdc1.on.home.com...
> I'm one of the authors of an order processing system, and I've run into
 a
> problem I'm hoping some of you could help with. Sorry for the x-post.
>
> Our customers have multiple divisions, each with many buildings (often
> shared) and multiple organizations and billing entities within each. In
> order to ship to them they have to tell us one of the shipping accounts
 and
> one of the billing accounts. So as you might expect, we have an order
> "object" that has a key to one of each of these account types, as well as
> pointers to other relevant info and status bits and such.
>
> The customers need to be able to do searches at any level of the
> hierarchy. In many cases they want to know every order for everything in
 the
> compnay, but in other cases they want to know all the items that were
> shipped to any of the shipping docks in a particular building. That's why
> they're hierarchical, so they can select "Buffalo" and see everything that
> went to any of their buildings there.
>
> The problem that I'm having is that the resulting select statements are
> huge - larger than Sybase can handle. Why are they so huge? Well since the
> order objects themselves are keyed to particular leaves, if the user
 enters
> a selection in the middle of the hierarchy we have to find the leaves from
> that point down, and then do a search with a huge OR statement for all of
> the leaves. Consider this simple case...
>
> shipping billing
>
> A 1
> / \ / \
> B C 2 3
>
> If the user wants to see everything in B and 3 that's fine...
>
> SELECT * FROM orders WHERE shipping_id='B' and billing_id='3'
>
> But consider what happens when they select A and 1...
>
> SELECT * FROM orders WHERE (shipping_id='B' and billing_id='3') or
> (shipping_id='B' and billing_id='2') or (shipping_id='C' and
 billing_id='3')
> or (shipping_id='C' and billing_id='2')
>
> That might not look so bad, but consider the case when there's 50 items
 in
> each tree and they pick items in the middle.
>
> A consultant wrote some Java code to avoid the resulting gigantic select
> statement by breaking down the query into chunks of ten billing/shipping
> pairs. This means lots of little select statements though, all on a
 network
> round-trip. At the end we have a set of order objects, but then we have to
> go back and select the unique addresses (and such) for them, which means
> another huge series of selects, fetching the details for each one of the
> basic records.
>
> Is there some way we can do this walk entirely in SQL? Instead of
 fetching
> all the leaves and then constructing a huge select, is there some way the
> SQL itself can walk the tree? Each account has a pointer to it's parent
 (or
> null).
>
> Any suggestions?
>
> Maury
>
>

Look up CONNECT BY in your SQL reference manual. It has been designed to do treewalks. Warning: this is an Oracle specific feature.

Hth,

Sybrand Bakker, Oracle DBA Received on Fri Apr 06 2001 - 14:31:34 CDT