Concurrency level of VARRAY vs Nested tables

Question

I'd like to know what would have a better concurrency level, VARRAY or nested tables. Is it really true that VARRAY only need one input/output operation ot load the collection?

It's for a reservations systems.

Regards,

Answer 1

Is it really true that VARRAY only need one input/output operation ot load the collection?

Define a VARRAY and a collection type:

CREATE TYPE test_varray AS VARRAY(3) OF NUMBER;
CREATE TYPE test_collection AS TABLE OF NUMBER;

Then you can create similar tables for each of those data types:

CREATE TABLE test1 (
  id    NUMBER PRIMARY KEY,
  array test_varray
);

CREATE TABLE test2 (
  id    NUMBER PRIMARY KEY,
  array test_collection
) NESTED TABLE array STORE AS test2__array;

You can INSERT into those tables as a single statement:

INSERT INTO test1 ( id, array ) VALUES ( 1, test_varray( 1.1, 1.2, 1.3 ) );
INSERT INTO test2 ( id, array ) VALUES ( 1, test_collection( 1.1, 1.2, 1.3 ) );

You can SELECT from those tables as a single statement:

SELECT id, column_value FROM test1 CROSS JOIN TABLE( array );
SELECT id, column_value FROM test2 CROSS JOIN TABLE( array );

Which both output:

ID | COLUMN_VALUE
-: | -----------:
 1 |          1.1
 1 |          1.2
 1 |          1.3

db<>fiddle here

So from a point of INSERT/SELECT operations they are functionally similar. The Oracle Documentation - Understanding PL/SQL Collection Types explains some of the differences mainly:

• VARRAY has a bounded number of elements and is always dense.
• Collections (Nested Tables) can have an unbounded number of elements and could be sparse.