About Oracle Varrays

My thought was to use varray types and populate them with the various possible values, then use dbms_random.value to generate the index values for the various varrays. The count column was just a truncated call to dbms_random in the range of 1 to 600. All of this was of course placed into a procedure with the ability to give it the number of required values.

Essentially:

create or replace procedure load_random_data(cnt in number) as
define varray types;
define varrays using types;
declare loop interator;
begin
initialize varrays with allowed values;
start loop 1 to cnt times
set dbms_rabdom seed to loop interator;
insert using calls to dbms_random.value(1-n) as the indice for the various varrays;
end loop;
commit;
end;

Varray tables

Varray tables have the benefit of avoiding costly SQL joins, and they can maintain the order of the a varrays items based upon the sequence when they were stored. However, the longer row length of varray tables causes full-table scans to run longer, and the items inside the varrays cannot be indexed. More importantly, varrays cannot be used when the number of repeating items is unknown or very large.

Storing varrays

Relational and object-partitioned index-organized tables (partitioned by range, hash, or list) cannot have VARRAYs stored as LOB types, abstract data types with LOB attributes, or nested tables with LOB types.