Re: Why Varying Arrasy and Nested Tables?

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