Creating Multiple Indexes on the same column or set of columns

Introduction

Oracle 11g Introduced a nice feature named "Invisible Indexes" which allows us to mark an index as invisible. This index will still be maintained by Oracle during DML operations like a "regular" index but it is being ignored by the optimizer, i.e. execution plans will not use the invisible indexes.

Oracle 12c leverages the "Invisible Indexes" feature by allowing us create multiple indexes on the same column or set of columns, as long as only one index is visible and all the indexes are different, i.e. it is impossible to create 2 B-Tree indexes on the same column, even if one of them is invisible.

Demonstration

For this demonstration, we will create a table of employees and populate it with 10 records:

SQL> CREATE TABLE EMP
  2  (
  3     id          NUMBER,
  4     name        VARCHAR2 (20),
  5     salary      NUMBER,
  6     hire_date   DATE
  8  );
Table created.

SQL> insert into EMP value values (1, 'DAVID', 8500, '07-APR-15');
1 row created.

SQL> insert into EMP value values (2, 'JOHN', 10000, '17-MAR-15');
1 row created.

SQL> insert into EMP value values (3, 'JANE', 13500, '23-DEC-15');
1 row created.

SQL> insert into EMP value values (4, 'DAN', 15000, '02-JAN-15');
1 row created.

SQL> insert into EMP value values (5, 'RACHEL', 19000, '20-FEB-15');
1 row created.

SQL> insert into EMP value values (6, 'BRAD', 20000, '25-JUN-15');
1 row created.

SQL> insert into EMP value values (7, 'TIM', 15000, '16-MAR-15');
1 row created.

SQL> insert into EMP value values (8, 'KELLY', 9000, '28-APR-15');
1 row created.

SQL> insert into EMP value values (9, 'NICK', 7500, '04-FEB-15');
1 row created.

SQL> insert into EMP value values (10, 'ERIK', 6000, '09-JUL-15');
1 row created.

SQL> commit;
Commit complete.

SQL> select * from EMP;
        ID NAME                     SALARY HIRE_DATE
---------- -------------------- ---------- ---------
         1 DAVID                      8500 07-APR-15
         2 JOHN                      10000 17-MAR-15
         3 JANE                      13500 23-DEC-15
         4 DAN                       15000 02-JAN-15
         5 RACHEL                    19000 20-FEB-15
         6 BRAD                      20000 25-JUN-15
         7 TIM                       15000 16-MAR-15
         8 KELLY                      9000 28-APR-15
         9 NICK                       7500 04-FEB-15
        10 ERIK                       6000 09-JUL-15
10 rows selected.

Now, we will create 2 different indexes on the "id" column, the first index will be a visible B-Tree index, and the second index will be an invisible Bitmap index:

SQL> CREATE INDEX BTREE_IDX ON EMP (ID);
Index created.

SQL> CREATE BITMAP INDEX BITMAP_IDX ON EMP (ID);
CREATE BITMAP INDEX BITMAP_IDX ON EMP (ID)
                                       *
ERROR at line 1:
ORA-01408: such column list already indexed


SQL> CREATE BITMAP INDEX BITMAP_IDX ON EMP (ID) INVISIBLE;
Index created.

The error in the second statement is expected, because the B-Tree index is visible and as previously mentioned, only one index can be visible at a time. Using USER_INDEXES dictionary view, it is possible to determine the current status of the indexes:

select index_name, index_type, visibility
  from user_indexes
 where TABLE_NAME = 'EMP'

INDEX_NAME INDEX_TYPE                  VISIBILIT
---------- --------------------------- ---------
BTREE_IDX  NORMAL                      VISIBLE
BITMAP_IDX BITMAP                      INVISIBLE

If we want to test the performance of the SQL statements, or investigate the execution plans after switching between the indexes, it can be done very easily, as follows:

SQL> ALTER INDEX BTREE_IDX INVISIBLE;
Index altered.

SQL> ALTER INDEX BITMAP_IDX VISIBLE;
Index altered.

SQL> SELECT index_name, index_type, visibility
  FROM user_indexes
 WHERE TABLE_NAME = 'EMP';

INDEX_NAME INDEX_TYPE                  VISIBILIT
---------- --------------------------- ---------
BTREE_IDX  NORMAL                      INVISIBLE
BITMAP_IDX BITMAP                      VISIBLE

Summary

In this article I have demonstrated a new 12c feature that allows create more than one index on a column or set of columns, assuming the indexes are from different types.

This can be useful when you want to test the impact of different indexes easily without dropping and creating a new index. However, it is important to bear in mind that additional indexes means additional overhead during DML operations. This additional overhead is necessary in order to maintain the indexes, therefore use this feature with cautious

Oracle 12c Improved Defaults

Introduction

Oracle version 12c introduced some very nice and useful enhancements for table column defaults. This includes the following enhancements:

• Identity columns
• Default value when using a sequence
• Default value when NULL is inserted

Identity columns

When identity column is created for a table, it will automatically generate an auto increment value upon every row insertion. It has the following options:

• ALWAYS (default option) - In this option, Oracle forces the usage of the sequence generator. If an insert or update statements reference the identity column, Oracle raises an error.

• BY DEFAULT - This option allows to explicitly assign a value for the column as part of an INSERT or UPDATE statements.

• BY DEFAULT ON NULL - In the previous option (BY DEFAULT), If you will attempt to reference the identity column, and assign it a NULL value, you will encounter an error. Using the BY DEFAULT ON NULL option tells Oracle to use the identity generator when there is an attempt to insert a NULL to the table.

ALWAYS

In the first demonstration, I will create a sample table named "PRODUCTS" with an identity column for the primary key, that uses the ALWAYS option.

CREATE TABLE PRODUCTS
(
   ID     NUMBER GENERATED ALWAYS AS IDENTITY,
   NAME   VARCHAR2 (20),
   CONSTRAINT PK_ID PRIMARY KEY (ID)
);

SQL> insert into PRODUCTS(NAME) values ('Product1');
1 row created.

SQL> insert into PRODUCTS(ID, NAME) values (2, 'Product1');
ORA-32795: cannot insert into a generated always identity column

SQL> insert into PRODUCTS(ID, NAME)  values (NULL, 'Product2');
ORA-32795: cannot insert into a generated always identity column

As you can see in the above demonstration, when trying to reference the identity column, Oracle raises an error.

BY DEFAULT

In the next demonstration, a "BY DEFAULT" clause is used for the identity column, which allows us to explicitly assign a value for the identity column (as long as it is not NULL), as follows:

SQL> DROP TABLE PRODUCTS PURGE;
Table dropped.

CREATE TABLE PRODUCTS
(
   ID     NUMBER GENERATED BY DEFAULT AS IDENTITY,
   NAME   VARCHAR2 (20),
   CONSTRAINT PK_ID PRIMARY KEY (ID)
);
Table created.

SQL> insert into PRODUCTS(NAME) values ('Product1');
1 row created.

SQL> insert into PRODUCTS (ID, NAME) values (2, 'Product1');
1 row created.

SQL> insert into PRODUCTS(ID, NAME)  values (NULL, 'Product2');
ORA-01400: cannot insert NULL into ("SYS"."PRODUCTS"."ID")

BY DEFAULT ON NULL

Now, we will specify the "BY DEFAULT ON NULL" clause, which tells Oracle to use the identity generator even if NULL value is explicitly assigned:

SQL> DROP TABLE PRODUCTS PURGE;
Table dropped.

CREATE TABLE PRODUCTS
(
   ID     NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
   NAME   VARCHAR2 (20),
   CONSTRAINT PK_ID PRIMARY KEY (ID)
);
Table created.

SQL> insert into PRODUCTS(NAME) values ('Product1');
1 row created.

SQL> insert into PRODUCTS (ID, NAME) values (102, 'Product1');
1 row created.

SQL>  insert into PRODUCTS(ID, NAME)  values (NULL, 'Product2');
1 row created.

SQL> select * from PRODUCTS;

        ID NAME
---------- --------------------
         1 Product1
       102 Product1
         2 Product2

When creating an identity column, Oracle "behind the scenes" creates a sequence. This means that all the options that available for sequences (e.g. START WITH , INCREMENT BY, MAX_VALUE, etc.) are available for identify columns as well, as you can see in the following example:

SQL> DROP TABLE PRODUCTS PURGE;
Table dropped.

CREATE TABLE PRODUCTS
(
   ID     NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY (START WITH 10 INCREMENT BY 2),
   NAME   VARCHAR2 (20),
   CONSTRAINT PK_ID PRIMARY KEY (ID)
);
Table created.

It is possible to query the [CDB|DBA|ALL|USER]_tab_identity_cols dictionary view to obtain all of the table identity columns, as follows:

SQL> select table_name,column_name,generation_type, identity_options, sequence_name 
     from user_tab_identity_cols;

TABLE_NAME COLUMN_NAME GENERATION IDENTITY_OPTIONS                    SEQUENCE_NAME
---------- ----------- ---------- ----------------------------------- ---------------
PRODUCTS   ID          BY DEFAULT START WITH: 10, INCREMENT BY: 2, MA ISEQ$$_176970
                                  X_VALUE: 99999999999999999999999999
                                  99, MIN_VALUE: 1, CYCLE_FLAG: N, CA
                                  CHE_SIZE: 20, ORDER_FLAG: N

Additional Comments

• Identity  columns are NOT NULL, i.e. you cannot assign a NULL value to identity column
• As previously mentioned, when creating an identity column, Oracle automatically creates a sequence. This means that in order to use this feature, the use must have the CREATE SEQUENCE privilege.

Default value when using a sequence

Another enhacement in the improved defaults which was introduced in Oracle 12c, it the ability to use a sequence as a default value for a column. It can be either CURRVAL or NEXTVAL. The following is a demonstration of this feature:

SQL> CREATE SEQUENCE id_seq;
Sequence created.

CREATE TABLE PRODUCTS
(
   ID     NUMBER DEFAULT ID_SEQ.NEXTVAL,
   NAME   VARCHAR2(20)
);
Table created.

SQL> insert into PRODUCTS (NAME) values ('Product1');
1 row created.

SQL> insert into PRODUCTS (ID, NAME) values (DEFAULT, 'Product2');
1 row created.

SQL>  insert into PRODUCTS (ID,NAME) values (NULL,'Product3');
1 row created.

SQL> select * from PRODUCTS;

        ID NAME
---------- --------------------
         1 Product1
         2 Product2
           Product3

As you can see in the above example, Oracle will use the sequence as a default value if the column is not referenced, or if it is referenced and the value "DEFAULT" is specified. When an explicit NULL value was inserted, the default sequence value is not being used, instead, a NULL was inserted to the table.

Additional Comments

• Creating a table with a default sequence value, requires having a SELECT privilege on the sequence (in addition to the CREATE TABLE privilege)
• Inserting a value to the table with a default sequence requires having a SELECT privilege on the sequence (in addition to the INSERT privilege on that table)

Default value when NULL is inserted

In order to use a default sequence number even when an explicit NULL is being inserted, you can use the ON NULL clause. Let us see a demonstration of this feature:

SQL>  DROP SEQUENCE id_seq;
Sequence dropped.

SQL> DROP TABLE PRODUCTS PURGE;
Table dropped.

SQL> CREATE SEQUENCE id_seq;
Sequence created.

CREATE TABLE PRODUCTS
(
   ID     NUMBER DEFAULT ON NULL ID_SEQ.NEXTVAL,
   NAME   VARCHAR2(20)
);
Table created.

SQL> insert into PRODUCTS (NAME) values ('Product1');
1 row created.

SQL> insert into PRODUCTS (ID, NAME) values (DEFAULT, 'Product2');
1 row created.

SQL> insert into PRODUCTS (ID,NAME) values (NULL,'Product3');
1 row created.

SQL> select * from PRODUCTS;

        ID NAME
---------- --------------------
         1 Product1
         2 Product2
         3 Product3

Summary

In earlier releases (prior to Oracle 12c), there was no straightforward way to specify an auto increment value for a column; in order to solve this challenge, a common solution was creating a sequence and a trigger that inserts the sequence next value upon every insert to the table. Starting with Oracle 12c, there are 2 options that can provide that - using sequence as a default value, and using an identity columns. Both options provide a better performance than the old trigger approach, and both are simpler to use.