Nested vs.
Varray vs. Associative Collections
Forget
Varray is there ;-)
Nested
More flexible; use in objects, tables and SQL statements
Little more cumbersome to use
Great for joins, IN/NOT IN, and synchronization
Associative
Maps, Hash Tables, Dictionaries, intelligent key PK stores, etc. Bi-directional, random and multiple scans.
Index can be zero, negative or string
Can be created initially sparse| Nested tables |
|
| When you don’t
mind that there is no implicit ordering. When you need to access or
manipulate individual entries in each collection. When you need efficient
parent/child lookups. When you foresee the need to issue SQL statements
against a list. When you have code that currently maintains temporary tables
as staging areas or that does syncronization. |
|
| Story about
re_rate_err or paren-child in alarm system. |
|
| Associative
Arrays |
|
| For everything
else! Bi-directional, random access and multiple scans. Especially good for
the random access inherent with hash tables. |
|
| Story about
resync process! |
|
| Varrays |
|
| Varrays are
useful when you need to maintain ordering of your collection elements.
Varrays are very efficient when you always manipulate the entire collection
as a unit, and that you don't require querying on individual elements of the
collections. Since they can be stored inline, there is no transparent joining
going on, no possibility of inefficient joins because there is no child table
and no unindexed nested_table_id. So they can be “faster”. |