Learning is not a spectator sport

I posted a video a couple of days ago about the use of the APPEND_VALUES hint

TL;DR: Using this hint as a means of achieving direct path load even when doing array-based inserts as opposed to INSERT-SELECT.

Because direct path are often used to load data more quickly than conventional means, my focus was very much on the performance aspect, and to my disappointment, after the video goes through various scenarios, I concluded that (on my machine) I rarely saw any real performance benefit, or at least, not a benefit significant enough to outweigh the potential downsides (table locks etc).

Thus my parting words in the video were “I can’t really see the benefit, but if you have any examples, please ping me“.

A day after the video was published, I got a simple one-line comment from @fafling: “APPEND_VALUES is useful to trigger basic compression in a table load”, and this was a light bulb moment. Of course, we often use direct load (and “alter table move”) to achieve basic compression. We are not getting a performance boost on load – if anything we are paying a performance tax on load in order to get performance benefits later when we query.

Here’s a simple demo showing that since APPEND_VALUES triggers a direct load operation, we also can use it for compressing the loaded data.

SQL> CREATE TABLE t1 AS SELECT * FROM dba_objects WHERE 1 = 2;

Table created.

SQL>
SQL> declare
  2  s1 timestamp; s2 timestamp;
  3  type tab is table of t1%rowtype;
  4  t tab;
  5
  6  begin
  7
  8  select t1.*
  9  bulk collect into t
 10  from dba_objects t1,
 11  (select level lvl from dual connect by level < 15) order by owner, object_type, object_name;
 12  s1 := systimestamp;
 13  forall i in 1..t.count             -- CONVENTIONAL LOAD
 14  insert into t1
 15  values t(i);
 16  commit;
 17  s2 := systimestamp;
 18  dbms_output.put_line(s2-s1);
 19
 20  end;
 21
 22  /
+000000000 00:00:02.040000000

PL/SQL procedure successfully completed.

SQL>
SQL> exec dbms_stats.gather_table_stats('','T1');

PL/SQL procedure successfully completed.

SQL> select blocks from user_tables where table_name = 'T1';

    BLOCKS
----------
     27437


SQL> declare
  2  s1 timestamp; s2 timestamp;
  3  type tab is table of t1%rowtype;
  4  t tab;
  5
  6  begin
  7
  8  select t1.*
  9  bulk collect into t
 10  from dba_objects t1,
 11  (select level lvl from dual connect by level < 15) order by owner, object_type, object_name;
 12
 13  s1 := systimestamp;
 14  forall i in 1..t.count
 15  insert /*+ append_values */ into t1   -- DIRECT LOAD
 16  values t(i);
 17  commit;
 18  s2 := systimestamp;
 19  dbms_output.put_line(s2-s1);
 20  end;
 21
 22  /
+000000000 00:00:02.706000000

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats('','T1');

PL/SQL procedure successfully completed.

SQL> select blocks from user_tables where table_name = 'T1';

    BLOCKS
----------
      3021

You can see the APPEND_VALUES load ran slower than the conventional load, but we also got a big compression gain by using the hint.

Since then I’ve had also some “to and fro” with Oracle ACE Monika Lewandowska , where (on her machine) she does see a benefit from using APPEND_VALUES, so this reinforces the importance of always testing performance scenarios under the conditions you expect to use them.

But this is why it is important to put your content out there in the wild for the community. It is not just about pushing out information to the community, it is also about you learning and getting information from the community, to give you a broader knowledge on topics.