Non-Unique Indexes and Direct-Path Inserts (What In The World)

The OTN Database Forum has had some really good threads lately and something that came up was the question of indexes and Direct-Inserts which I thought might be worth a mention here.

I’ve previously discussed the differences between Unique and Non-Unique Indexes and my general preference for using Unique Indexes where possible and appropriate:

https://richardfoote.wordpress.com/2007/12/18/differences-between-unique-and-non-unique-indexes-part-i/

https://richardfoote.wordpress.com/2007/12/21/differences-between-unique-and-non-unique-indexes-part-ii/

https://richardfoote.wordpress.com/2007/12/30/differences-between-unique-and-non-unique-indexes-part-iii/

A Direct-Path Insert is a special mechanism used by Oracle to more quickly and efficiently insert data into a table. Rather than utilising the conventional method of using the table freelists or ASSM bitmaps to find an available free block that’s subsequently loaded into the buffer cache and processed, Oracle instead builds the necessary blocks in session memory and writes them directly to disk, “appending” them above the table High Water Mark (HWM) and hence by-passing the buffer cache entirely.

When inserting a large amount of data, a Direct-Path Insert can hence be substantially faster and has the added flexibility of being a NOLOGGING operation if required.

However, if you have a Primary Key (or Unique Key) policed via a Non-Unique index, then Oracle will automatically disable Direct-Path inserts behind the scene. This restriction has only been lifted since 11g.

A simple demo.

First, using a 10.2.0.3 database, create a table and populate it with a few rows:

SQL> CREATE TABLE ZIGGY (id NUMBER, text VARCHAR2(20));

Table created.

SQL> INSERT INTO ziggy SELECT rownum , ‘Ziggy’ FROM dual CONNECT BY LEVEL <= 10000;

10000 rows created.

SQL> COMMIT;

Commit complete.

Next, let’s add a PK that’s policed by a Non-Unique Index:

SQL> ALTER TABLE ziggy ADD CONSTRAINT ziggy_pk PRIMARY KEY(id) USING INDEX(CREATE INDEX ziggy_pk ON ziggy(id));

Table altered.

Now, let’s see how many blocks below the HWM we have allocated to the table:

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>’ZIGGY’, estimate_percent=>null, cascade=> true, method_opt=>’FOR ALL COLUMNS SIZE 1′);

PL/SQL procedure successfully completed.

SQL> SELECT blocks, empty_blocks FROM dba_tables WHERE table_name = ‘ZIGGY’;

BLOCKS EMPTY_BLOCKS
------ ------------
    25            0

Let’s see how many physical direct write operations we have currently performed:

SQL> SELECT n.name, s.value FROM v$mystat s, v$statname n WHERE s.statistic# = n.statistic# AND n.name = ‘physical writes direct’;

NAME                   VALUE
---------------------- -----
physical writes direct    21

Let’s now attempt to perform a Direct-Path Insert operation:

SQL> INSERT /*+ APPEND */ INTO ziggy SELECT 10001, ‘NEW’ FROM dual;

1 row created.

SQL> COMMIT;

Commit complete.

But have we actually performed any physical writes direct operations ?

SQL> SELECT n.name, s.value FROM v$mystat s, v$statname n WHERE s.statistic# = n.statistic# AND n.name = ‘physical writes direct’;

NAME                   VALUE
---------------------- -----
physical writes direct    21

NO !! The number of such operations has not changed. Has the HWM been incremented ?

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>’ZIGGY’, estimate_percent=>null, cascade=> true, method_opt=>’FOR ALL COLUMNS SIZE 1′);

PL/SQL procedure successfully completed.

SQL> SELECT blocks, empty_blocks FROM dba_tables WHERE table_name = ‘ZIGGY’;

BLOCKS EMPTY_BLOCKS
------ ------------
    25            0 

NO !! The number of blocks has also remained the same. Clearly then, the Direct-Path Insert has not actually worked and Oracle has simply performed a conventional insert operation instead.

Let’s see if the situation changes if we replace the index policing the PK with a Unique Index instead …

SQL> ALTER TABLE ziggy DROP PRIMARY KEY;

Table altered.

SQL> DROP INDEX ZIGGY_PK;

Index dropped.

SQL> ALTER TABLE ziggy ADD CONSTRAINT ziggy_pk PRIMARY KEY(id);

Table altered.

OK, let’s see the current number of physical writes direct operations:

SQL> SELECT n.name, s.value FROM v$mystat s, v$statname n WHERE s.statistic# = n.statistic# AND n.name = ‘physical writes direct’;

NAME                   VALUE
---------------------- -----
physical writes direct    41 

Let’s try another Direct-Path Insert …

SQL> INSERT /*+ APPEND */ INTO ziggy SELECT 10002, ‘NEW’ FROM dual;

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT n.name, s.value FROM v$mystat s, v$statname n WHERE s.statistic# = n.statistic# AND n.name = ‘physical writes direct’;

NAME                   VALUE
---------------------- -----
physical writes direct    42 

And the number of physical writes direct has now increased. What about the HWM ?

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>’ZIGGY’, estimate_percent=>null, cascade=> true, method_opt=>’FOR ALL COLUMNS SIZE 1′);

PL/SQL procedure successfully completed.

SQL> SELECT blocks, empty_blocks FROM dba_tables WHERE table_name = ‘ZIGGY’;

BLOCKS EMPTY_BLOCKS
------ ------------
    26            0 

Yep, that’s gone up by one as well. Let’s just repeat the process to be sure …

SQL> SELECT n.name, s.value FROM v$mystat s, v$statname n WHERE s.statistic# = n.statistic# AND n.name = ‘physical writes direct’;

NAME                   VALUE
---------------------- -----
physical writes direct    42 

SQL> INSERT /*+ APPEND */ INTO ziggy SELECT 10003, ‘NEW’ FROM dual;

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT n.name, s.value FROM v$mystat s, v$statname n WHERE s.statistic# = n.statistic# AND n.name = ‘physical writes direct’;

NAME                   VALUE
---------------------- -----
physical writes direct    43 

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>’ZIGGY’, estimate_percent=>null, cascade=> true, method_opt=>’FOR ALL COLUMNS SIZE 1′);

PL/SQL procedure successfully completed.

SQL> SELECT blocks, empty_blocks FROM dba_tables WHERE table_name = ‘ZIGGY’;

BLOCKS EMPTY_BLOCKS
------ ------------
    27            0 

Yes indeed, Direct-Path Inserts are definitely now occurring now we have a Unique Index policing our PK constraint.

Same thing now with a Non-Unique Index policing our PK again but this time on an 11g database …

SQL> CREATE TABLE ZIGGY (id NUMBER, text VARCHAR2(20));

Table created.

SQL> INSERT INTO ziggy SELECT rownum , ‘Ziggy’ FROM dual CONNECT BY LEVEL <= 10000;

10000 rows created.

SQL> COMMIT;

Commit complete.

SQL> ALTER TABLE ziggy ADD CONSTRAINT ziggy_pk PRIMARY KEY(id) USING INDEX(CREATE INDEX ziggy_pk ON ziggy(id));

Table altered.

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>’ZIGGY’, estimate_percent=>null, cascade=> true, method_opt=>’FOR ALL COLUMNS SIZE 1′);

PL/SQL procedure successfully completed.

SQL> select blocks, empty_blocks from dba_tables where table_name = ‘ZIGGY’;

BLOCKS EMPTY_BLOCKS
------ ------------
    25            0 

SQL> SELECT n.name, s.value FROM v$mystat s, v$statname n WHERE s.statistic# = n.statistic# AND n.name = ‘physical writes direct’;

NAME                   VALUE
---------------------- -----
physical writes direct    23 

SQL> INSERT /*+ APPEND */ INTO ziggy SELECT 10001, ‘NEW’ FROM dual;

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT n.name, s.value FROM v$mystat s, v$statname n WHERE s.statistic# = n.statistic# AND n.name = ‘physical writes direct’;

NAME                   VALUE
---------------------- -----
physical writes direct    24 

Now, even though we have a Non-Unique index, the number of physical writes direct operations has indeed gone up by one.

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>’ZIGGY’, estimate_percent=>null, cascade=> true, method_opt=>’FOR ALL COLUMNS SIZE 1′);

PL/SQL procedure successfully completed.

SQL> select blocks, empty_blocks from dba_tables where table_name = ‘ZIGGY’;

BLOCKS EMPTY_BLOCKS
------ ------------
    26            0 

And indeed, the HWM has increased as well showing that indeed a Direct-Path Insert works as expected in 11g even if our PK constraint is policed by a Non-Unique Index. 

Yet another example of a difference between a Unique and a Non-Unique index which might be worth some consideration.