OracleDBPro - Pini Dibask Blog: March 2016

Wednesday, March 9, 2016

Oracle 12c Improved Column Addition

Introduction

In my "Oracle 12c Improved Defaults" article, I review the enhancements of column defaults in Oracle 12c.
"Oracle 12c Improved Column Addition" is a continuation article because it covers performance improvements for adding a new column with a default value to an existing table.

Before Oracle 11g

Prior to Oracle 11g, adding a new column with a default value to an existing table, requires updating all the rows in the table which is a very long and high consuming operation, especially if the table is big.

Oracle 11g

In Oracle 11g a new feature named "Metadata-only value" was introduced. This means that upon every new column addition with a default value, Oracle will not update every row in a table, but rather make a small metadata-only change in the data dictionary. As a result, every time that a user queries the table, Oracle will obtain the default value from the data dictionary. This feaure allows us to add a new column with a default value within milliseconds instead of waiting for a long time and generating lots of redo and undo which may impact the entire database.

In the following example, a table named "TEST" is being created and populated with 100,000 rows.
Afterwards, we will test the time it takes to add a new column with a default value:

SQL> CREATE TABLE TEST (ID NUMBER);
Table created.

SQL>BEGIN
        FOR i in 1 .. 100000 LOOP
            EXECUTE IMMEDIATE 'INSERT INTO test VALUES (:i)' USING i;
        END LOOP;
    END;
    /
PL/SQL PROCEDURE successfully completed.

SQL> COMMIT;
Commit complete.

SQL>set timing ON

SQL> ALTER TABLE test ADD string1 VARCHAR2(20) DEFAULT 'DUMMY TEXT' NOT NULL;
Table altered.
Elapsed: 00:00:00.10

As you can see in the above demonstration, the column addition operation completed instantly.
However, this feature works only if the column was added with the NOT NULL constraint.
Let us see what will happen if we will try to add a nullable column with a default value (i.e. a column that doesn't have the NOT NULL constraint):

SQL> ALTER TABLE test ADD string2 VARCHAR2(20) DEFAULT 'DUMMY TEXT';
Table altered.
Elapsed: 00:01:08.17

Now, the operation took more than 1 minute! This table is only populated with 100K rows. Imagine how much time it would take if a table contains hundreds of millions of rows ...

Oracle 12c

In Oracle 12c, Oracle took the "metadata-only value" feature one step further.
Now, this feature is supports nullable column, as per the below demonstration:

SQL> ALTER TABLE test ADD string VARCHAR2(20) DEFAULT 'DUMMY TEXT';
Table altered.
Elapsed: 00:00:00.06

As you can see, in Oracle 12c adding a column with a default value is an instant operation, regardless of the the nullable attribute.

Thursday, March 3, 2016