How
do you decide which collection type to use?
Choosing
Between Nested Tables and Associative Arrays
Both nested tables
and associative arrays (formerly known as Index-by tables) use similar
subscript notation, but they have different characteristics when it comes to
persistence and ease of parameter passing.
Nested tables can be stored in a database column, but associative arrays cannot. Nested tables can simplify SQL operations where you would normally join a single-column table with a larger table.
Associative Arrays are
appropriate for relatively small lookup tables where the collection can be
constructed in memory each time a procedure is called or a package is
initialized. We cannot store them in DB. They are good for collecting
information whose volume is unknown beforehand, because there is no fixed limit
on their size. Their index values are more flexible, because Associative Array
subscripts can be negative, can be non-sequential, and can use string values
instead of numbers.
Choosing
Between Nested Tables and Varray’s
Varray is a good choice
when:
•
The number of
elements is known in advance.
•
The elements are
usually all accessed in a sequence.
When stored in the
database, Varray keep their ordering and subscripts.
You must update or
retrieve all elements of the Varray at the same time, which is most appropriate
when performing some operation on all the elements at once. But you might find
it impractical to store and retrieve large numbers of elements this way.
Nested tables are a
good choice when:
•
The index values
are not consecutive.
•
There is no set
number of index values. However, a maximum limit is imposed.
•
You need to delete
or update some elements, but not all the elements at once.
•
You would usually
create a separate lookup table, with multiple entries for each row of the main
table, and access it through join queries.
Nested tables can
be sparse: you can delete arbitrary elements, rather than just removing an item
from the end.
Nested table data
is stored in a separate store table, a system-generated database table
associated with the nested table. The database joins the tables for you when
you access the nested table. This makes nested tables suitable for queries and
updates that only affect some elements of the collection.
You cannot rely on
the order and subscripts of a nested table remaining stable as the nested table
is stored in and retrieved from the database, because the order and subscripts
are not preserved in the database.
RELATED TOPICS:
Different types of COLLECTIONS in ORACLE
Examples of COLLECTIONS and COLLECTION Methods
Get involved and leave your Comments in the Box Below. The more people get involved, the more we all benefit. So, leave your thoughts before you leave the page.
0 Comments