Collections for the DBA
Varrays limited and tricky. Ideal use is permanently bounded list whose aggregate bytesize is less than 4000.
Varrays cannot be extended beyond their initially defined maxsize.
Varray columns can’t be modified using SQL.
Nested table is really a physical child table.
Both parent and child table have hidden columns and hidden constraints.
No inherent order to rows in nested table, unless you add it yourself (object attribute).
Nested table can be index-organized. Nested_table_id should be indexed.
·   In practice, most varrays (per row) total less than 4000 bytes.  This data will be stored RAW inline and makes varrays your best choice for speed and efficiency.  If the data surpasses the 4000 byte mark, or if you specify a LOB storage clause for the varray, Oracle will create a LOB segment and move the data out of line.
·   Varrays cannot be extended beyond their initially defined maxsize.  You will need to drop and recreate the type and table if you miscalculated the maxsize during modeling.
·   Varray columns can't be modified using SQL; you are reduced to using PL/SQL to insert, update or delete varrays.  You can't just replace an element inside the varray either; you must replace the whole array.

·   Physically, nested tables are implemented by Oracle as a child table, with a hidden column (nested_table_id), which is foreign-keyed to a surrogate, hidden column in the parent table (one for every nested table column).  The child table may not be queried or modify independently of the parent.
·   There is no inherent order to how data in nested tables is stored or returned, and foreign keys are not supported.  If you want to impose order, you must add an attribute to the nested table for indexing or sorting.
·   Unlike regular DML, when unnesting Oracle doesn't allow the subquery to return zero or multiple collections.   The subquery to get the row's nested table must bring back only one nested table.
·   If you do create columns using nested tables, index the nested_table_id pseudocolumn.  Since the child table created by the server to store your nested table has the tendency to de-cluster the ordered data you've put in there, it is also a good idea to define the nested tables as index-organized (less physical IO).
.   Oracle docs speak of compressing the leading column in the UK you add to the nested table. In the docs, they show the nested_table_id as the letters A,B,C etc. In reality it is a chock-full RAW(16) column which can really save some space if compressed.
.   So both the parent and child end up with a rather large extra column, even though the parent probably already has a UK/PK. There is also the overhead of the extra UK on the parent table.