Re: Fetching from a hierarchical data structure?

Maury Markowitz wrote:

> 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.

This looks to me like a data warehouse (data mart) problem. Maybe you can improve architecture first.

A few words on typical DW architecture: The well known star schema consists of a large fact table (as in your application) and some lookup tables, one for each (hierarchical) dimension. Since lookup tables are having a different representation of hierarchies - one column for each level - you are coding your SQL with some joins (most likely faster than treewalks through hierarchies). See books on data warehousing, e.g. by Kimball, or the Oracle documentation (Oracle8i Data Warehousing Guide) for a more detailed explanation.

Oracle8i especially supports this kind of SQL queries with bitmap indexes and star_query_optimization hints. Received on Mon Apr 09 2001 - 00:54:01 CDT