APPEND_VALUES Hint in Oracle Database 11g Release 2

Advertisements

We have been able take advantage of the performance benefits of direct-path inserts in "INSERT ... SELECT" operations for a long time using the APPEND hint.

INSERT /*+ APPEND */ INTO dest_tab SELECT * FROM source_tab;

The APPEND_VALUES hint in Oracle 11g Release 2 now allows us to take advantage of direct-path inserts when insert statements include a VALUES clause. Typically we would only want to do this when the insert statement is part of bulk operation using the FORALL statement. We will use the following table to demonstrate the effect of the hint.

CREATE TABLE forall_test (
  id           NUMBER(10),
  code         VARCHAR2(10),
  description  VARCHAR2(50)
);

ALTER TABLE forall_test ADD (CONSTRAINT forall_test_pk PRIMARY KEY (id));

ALTER TABLE forall_test ADD (CONSTRAINT forall_test_uk UNIQUE (code));

The following code populates the base table then deletes half of the rows before performing each test. This is because during a regular (conventional-path) insert, Oracle tries to use up any free space currently allocated to the table, including space left from previous delete operations. In contrast direct-path inserts ignore existing free space and append the data to the end of the table. After preparing the base table we time how long it takes to perform conventional-path insert as part of the FORALL statement. Next, we repeat the same test, but this time use a the APPEND_VALUES hint to give us direct-path inserts.

SET SERVEROUTPUT ON
DECLARE
  TYPE t_forall_test_tab IS TABLE OF forall_test%ROWTYPE;

  l_tab    t_forall_test_tab := t_forall_test_tab();
  l_start  NUMBER;
  l_size   NUMBER            := 1000000;

  PROCEDURE prepare_table AS
  BEGIN
    EXECUTE IMMEDIATE 'TRUNCATE TABLE forall_test';

    INSERT /*+ APPEND */ INTO forall_test
    SELECT level, TO_CHAR(level), 'Description: ' || TO_CHAR(level)
    FROM   dual
    CONNECT BY level <= l_size;
    COMMIT;

    DELETE FROM forall_test WHERE MOD(id, 2) = 0;
    COMMIT;
  END prepare_table;
BEGIN
  -- Populate collection.
  FOR i IN 1 .. (l_size/2) LOOP
    l_tab.extend;
    l_tab(l_tab.last).id          := i*2;
    l_tab(l_tab.last).code        := TO_CHAR(i*2);
    l_tab(l_tab.last).description := 'Description: ' || TO_CHAR(i*2);
  END LOOP;

  prepare_table;

  -- ----------------------------------------------------------------
  -- Test 1: Time bulk inserts.  
  l_start := DBMS_UTILITY.get_time;

  FORALL i IN l_tab.first .. l_tab.last
    INSERT INTO forall_test VALUES l_tab(i);

  DBMS_OUTPUT.put_line('Bulk Inserts : ' || 
                       (DBMS_UTILITY.get_time - l_start));
  -- ----------------------------------------------------------------

  ROLLBACK;

  prepare_table;

  -- ----------------------------------------------------------------
  -- Test 2: Time bulk inserts using the APPEND_VALUES hint.  
  l_start := DBMS_UTILITY.get_time;

  FORALL i IN l_tab.first .. l_tab.last
    INSERT /*+ APPEND_VALUES */ INTO forall_test VALUES l_tab(i);

  DBMS_OUTPUT.put_line('Bulk Inserts /*+ APPEND_VALUES */ : ' || 
                       (DBMS_UTILITY.get_time - l_start));
  -- ----------------------------------------------------------------

  ROLLBACK;

END;
/
Bulk Inserts : 394
Bulk Inserts /*+ APPEND_VALUES */ : 267

PL/SQL procedure successfully completed.

SQL>

We can see that the APPEND_VALUES hint gives us better performance by allowing us to use direct-path inserts within the FORALL statement. Remember there are factors other than performance to consider before deciding to use direct-path inserts