Re: Why Varying Arrasy and Nested Tables?

We use nested tables in our database design and it has made things a lot easier for us since we have done theis.

Say you have data that you collect over time for comparison or computation.

Table = time_series_values (columns = date_time, value, location_code)

Now lets say you sample Dissolved Oxygen and temperature in a lake at a specific point (Lat Long = location_code). In your sampling, you do those two measurements at different depths. After you are done you have

Depth, DO, Temp
1, 2, 60
2, 2.5,59
etc

Well if you have value in the time_series_values tables setup as a nested table of (depth, Dissolved Oxygen, temp), you have have a more efficient normalized table then if your time_series table was (date_time, depth, DO, Temp, location code = table without nested tables) Why? Well, if your sampling had 20 measurements you would have 1 row with the nested table or 20 rows with out nested tables.

We like nested tables over nested arrays since you set your array size and you can exceed that without recreating the table. Nested tables are variable in length. You can have as many or as few as you want/need.

Bryan Baker

"RK" <rajXesh_at_hotmail.com> wrote in message news:548b9514.0210091638.60056428_at_posting.google.com...
> Russell Wolfe <wolfer_at_peak.org> wrote in message
 news:<d2t8qu46e58nvf4444j389kdtp4veo31ms_at_4ax.com>...
> > On 7 Oct 2002 07:44:16 -0700, rajXesh_at_hotmail.com (RK) wrote:
> >
> > >Russell Wolfe <wolfer_at_peak.org> wrote in message
 news:<64mupugprsd7mq1d6a9ns0bae9u3kada79_at_4ax.com>...
> > >> I've worked with Oracle extensively since version 3 in 1984. In all
> > >> that time Oracle preached the glories of normalized data. I'm a
> > >> believer. The relational model has held up very well for many years,
> > >> and seems very robust to me.
> > >>
> > >> Then comes Oracle 8i and 9i with Nested Tables and Varying Arrays.
> > >> Aren't Varying Arrays-presumably a Good Thing, since Oracle has
> > >> them-the same thing as repeating fields, which Oracle used to condemn
> > >> as a Bad Thing? Didn't we just de-normalize our data? And aren't
> > >> Nested Tables just a more complex type of Varying Array?
> > >>
> > >> Both of these things add significnat complexity, it seems to me, to
 an
> > >> already numbingly complex product. What do we gain with the addition
> > >> of these things?
> > >>
> > >> Can anybody speak to this? Or point me toward a discussion of the
> > >> pros and cons?
> > >>
> > >> tnx
> > >> rww
> > >> Russ Wolfe
> > >> wolfer_at_peak.org
> > >
> > >
> > >
> > >Russ,
> > >
> > >
> > >Nested tables and Varrays are a good thing because they are a new data
> > >type. Most new data types and operators add functionality, flexibility
> > >and readability to the language.
> > >
> > >For e.g. if '+' and '-' are basic operators, '*' and '/' which are a
> > >extension of '+' and '-' are a good thing.
> > >
> > >Similarly NUMBER, CHAR, DATE, VARCHAR2 can be considered the basic
> > >datatypes but %TYPE or %ROWTYPE which are not your basic datatypes are
> > >still very useful
> > >
> > >Coming to Index by tables, Nested tables and Varays, they are
> > >certainly very useful to have in PL/SQL as they offer a new
> > >'Collection' datatype that was not present before.
> > >
> > >
> > ><FROM http://www.utexas.edu/cc/database/datamodeling/rm/rm7.html>
> > >
> > >The goal of normalization is to create a set of relational tables that
> > >are free of redundant data and that can be consistently and correctly
> > >modified. This means that all tables in a relational database should
> > >be in the third normal form (3NF). A relational table is in 3NF if and
> > >only if all non-key columns are (a) mutually independent and (b) fully
> > >dependent upon the primary key.
> > >
> > ></FROM>
> > >
> > >Nested tables and Varays add to the complexity (for now, because it is
> > >a new concept) of programmatically storing and retrieving data.
> > >
> > >
> > >Repeating fields are different from the Varrays. Consider for example
> > >the table structure in this link
> > >http://www.utexas.edu/cc/database/datamodeling/rm/rm7.html
> > >
> > >The 1NF contains repeating fields. The subsequent 3NF for 'supplier
> > >and parts' can be rewritten as
> > >
> > >type parts_rec is object (
> > >part# number
> > >qty number
> > >)
> > >
> > >type parts_table is table of parts_rec
> > >
> > >and the table is
> > >
> > >s# number
> > >parts parts_table
> > >
> > >
> > >As I was typing this, I just realized that we can have duplicate part#
> > >in the parts table. So I have to agree with you in saying that Varrays
> > >and Nested tables can in fact de-normalize the data.
> > >
> > >So what I am saying (for now until someone corrects me) is that
> > >Nested tables and Varays are great in PL/SQL but bad in the database.
> > >
> > >
> > >-- rajXesh
> >
> > rajXesh,
> >
> > I don't entirely agree with your first assertion. A new datatype may
> > add flexibility, but readability? I'm not so sure.
> >
> > I also think Varrays and Nested Tables are more than new datatypes,
> > they're a new way to design databases. Consider a simple
> > master-detail relationship. In 3NF we have a master table, such as
> > PurchaseOrder, and a detail table, like POLineItem. They join on
> > PONumber. With nested tables, I can (I think, I've never done this)
> > nest POLineItem into PurchaseOrder.
> >
> > I'm struggling with the pros and cons of this design choice. Do you
> > have a real-world example of how a Varray or Nested Table has improved
> > a database design?
> >
> > Thanks for the response
> > rww
> > Russ Wolfe
> > wolfer_at_peak.org

>

>

>

>

>

>

>
>