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.

Oracle 12c enhanced syntax for row-limiting (A.K.A Top-N queries)

Introduction

Prior to Oracle 12c we had to use the ROWNUM pseudo column in order to implement Top-N reporting. Following are few simple examples of Top-N queries:

• Query the top 3 employees with the highest salaries
• Query the first 2 employees who have joined the company

Using the ROWNUM pseudo column had worked well, however it had major disadvantages:

• It's not a straight-forward syntax, especially if you need to implement query paging such as query the second block of the top 3 employees with the highest salaries.
• It allows us specifying the Top-N rows but it doesn't allows specifying the Top-N percent (for example, query the top 25% percent of employees with the highest salaries.

In Oracle 12c, SQL SELECT syntax has been enhanced in order to address the 2 disadvantages with the ROWNUM pseudo column listed above. You can specify the number of rows or percentage of rows to return with the FETCH NEXT keywords. You can use the OFFSET keyword to specify that the returned rows begin with a row after the first row of the full result set. In the case of a negative OFFSET, the OFFSET value is treated as 0.

Demonstration

For the demonstration I 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,
  7     CONSTRAINT id_pk PRIMARY KEY (id)
  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.

In my first example I will query the first 2 employees who have joined the company:

SQL>   select *
  2    from EMP
  3    ORDER BY hire_date
  4    FETCH FIRST 2 ROWS ONLY;
        ID NAME                     SALARY HIRE_DATE
---------- -------------------- ---------- ---------
         4 DAN                       15000 02-JAN-15
         9 NICK                       7500 04-FEB-15

Now, let's demonstrate a query paging via the OFFSET clause. In this example, I will query the second block of the first 2 employees who have joined the company:

SQL>   select *
  2    from EMP
  3    order by hire_date
  4    offset 2 rows fetch next 2 ROWS ONLY;
        ID NAME                     SALARY HIRE_DATE
---------- -------------------- ---------- ---------
         5 RACHEL                    19000 20-FEB-15
         7 TIM                       15000 16-MAR-15

Now, I will query the top 3 employees with the with the highest salaries:

SQL>   select *
  2    from EMP
  3    order by salary DESC
  4    fetch first 3 rows only;
        ID NAME                     SALARY HIRE_DATE
---------- -------------------- ---------- ---------
         6 BRAD                      20000 25-JUN-15
         5 RACHEL                    19000 20-FEB-15
         4 DAN                       15000 02-JAN-15

In my sample table there are 2 employees with the salary of 15000 : DAN and TIM, but because I used the "ONLY" clause it limited the output to be exactly 3 rows. By replacing the "ONLY" clause with the "WITH TIES" clause, it will diplay all the top-3 employees even if there is more than one employee with the 3rd highest salary , as follows:

SQL>   select *
  2    from EMP
  3    order by salary DESC
  4    fetch first 3 rows with ties;
        ID NAME                     SALARY HIRE_DATE
---------- -------------------- ---------- ---------
         6 BRAD                      20000 25-JUN-15
         5 RACHEL                    19000 20-FEB-15
         4 DAN                       15000 02-JAN-15
         7 TIM                       15000 16-MAR-15

In my last example I will demonstrate how to query the Top-N percent using the PERCENT clause. The following query retrieves the top 40% of employees with the lowest salary:

SQL>   select *
  2    from EMP
  3    order by salary
  4    fetch first 40 percent rows only;
        ID NAME                     SALARY HIRE_DATE
---------- -------------------- ---------- ---------
        10 ERIK                       6000 09-JUL-15
         9 NICK                       7500 04-FEB-15
         1 DAVID                      8500 07-APR-15
         8 KELLY                      9000 28-APR-15

Additional Notes

• If you specify a negative OFFSET number, OFFSET is treated as 0. If you specify NULL or a number greater than or equal to the number of rows that are returned by the query, 0 rows are returned.​
• The FIRST or NEXT keywords can be used interchangeably. They are provided for semantic clarity.
• The ROW keyword and the ROWS keyword can be used interchangeably. They are provided for semantic clarity.

Summary

The new enhanced syntax for row-limiting is one of my favorite new features of Oracle 12c. It provides an extremely straight-forward syntax for implementing a Top-N and query paging instead of using less convenient syntax via the ROWNUM pseudo column as we used to do prior to Oracle 12c.