What is more efficient: arrays or single columns values? – oracle

In the last post on this topic it turned out that using an array as a column type needs more space than using a column per value in PostgreSQL. Now I’ll do the same test case in Oracle.

As with the PostgreSQL test case I’ll create two test tables in Oracle: One using 10 columns and one just using one column of type varray:

SQL> create table single_values ( a number, b number, c number, d number, e number, f number, g number, h number, i number, j number );

Table created.

SQL> create type typ_num_arr as varray(10) of number;
  2  /

Type created.

SQL> create table arr_values ( a typ_num_arr );

Table created.

The same logic will be used to populate the tables:

set timi on
truncate table single_values;
declare
  ln number := 1000;
begin
  for i in 0..1e6
  loop
    insert into single_values (a,b,c,d,e,f,g,h,i,j)
           values (ln,ln,ln,ln,ln,ln,ln,ln,ln,ln);
  end loop;
end;
/
commit;

Table truncated.

Elapsed: 00:00:00.07

PL/SQL procedure successfully completed.

Elapsed: 00:00:29.56

Commit complete.

Elapsed: 00:00:00.00
SQL> 

For the table using the varray:

set timi on
truncate table arr_values;
declare
  ln number := 1000;
  lt typ_num_arr := typ_num_arr(ln,ln,ln,ln,ln,ln,ln,ln,ln,ln);
begin
  for i in 0..1e6
  loop
    insert into arr_values (a)
           values (lt); 
  end loop;
end;
/
commit;

Table truncated.

Elapsed: 00:00:00.01

PL/SQL procedure successfully completed.

Elapsed: 00:00:33.05

Commit complete.

Elapsed: 00:00:00.01

Now, what about the space the two tables have allocated:

SQL> select sum(bytes)/1024/1024 MB from dba_extents where SEGMENT_NAME = 'SINGLE_VALUES';

	MB
----------
	38

Elapsed: 00:00:00.23
SQL> select sum(bytes)/1024/1024 MB from dba_extents where SEGMENT_NAME = 'ARR_VALUES';

	MB
----------
	47

Elapsed: 00:00:00.20
SQL> 

The same behavior as in PostgreSQL. The table with the varray needs more space than the table with the columns per value.