Oracle 12c New Features For Developers (Part 3)

Having covered top-n queries in Oracle 12c New Features part 1 and invisible columns in part 2, let's move on to the next new feature: multiple indexes on the same column(s).

Multiple Indexes

One Oracle 12c new feature is to allow the same column or columns to have more than one index defined although only one index can be visible at any one time.

You might be wondering what is the point of that. Well, there are a couple of uses: you might want to test what the effect on your application(s) would be of dropping a particular index; or changing the type of index from B-tree to butmap or vice versa; or you might have different applications that would benefit from different types of index. For example oltp during the day using b-tree indexes and over-night reporting or batch processing using bitmap indexes.

Being able to define multiple indexes on the same column(s) enables you to do this without having to drop an existing index or mark it as unusable and later rebuild it.

Let's have a look at a few examples to show how this wroks.

We have a teble

emp(
emp_id NUMBER
emp_name VARCHAR2(50)
salary NUMBER
commission NUMBER)

Usually, we'd have a primary key constaint and therefore a unique index on emp_id, so let's add it.

ALTER TABLE emp ADD (CONSTRAINT emp_pk PRIMARY KEY(emp_id));

That creates a unique index emp_pk on emp(emp_id). So far so good.

Before Oracle 12c we wouldn't be able to create another (non unique) index on emp_id - attempting to do so by running a statemnt like this:

CREATE INDEX emp_ix_2 ON emp (emp_id);

would generate the error "ORA-01408: such column list already indexed".

The same thing happens in 12c UNLESS we add the word "invisible" to the end of the statement.

CREATE INDEX emp_ix_2 ON emp (emp_id) INVISIBLE;

Now if we query user_indexes:

SELECT index_name
     , status
     , visibility
     , column_name
FROM user_indexes
JOIN user_ind_columns
USING (table_name, index_name)
WHERE table_name='EMP';

This is the result:

INDEX_NAME    STATUS   VISIBILITY COLUMN_NAME
------------ -------- ---------- -----------
EMP_IX_2      VALID    INVISIBLE    EMP_ID
EMP_PK        VALID    VISIBLE      EMP_ID

As you can see, we now have two indexes on emp.emp_id.

Note that additional indexes have to be of a different type to the original index and only one can be visible at a time. We could, therefore, create a 3rd index on emp.emp_id as long as it were a bitmap index.

Having created multiple indexs on the same column you can enable or disable them using the ALTER INDEX command.

ALTER INDEX emp_ix_1 INVISIBLE;

ALTER INDEX emp_ix_2 VISIBLE;

However if you attempt to make more than one index visible at the same time this would generate the error "ORA-14147: There is an existing VISIBLE index defined on the same set of columns."

Identity Columns

Another Oracle 12c new feature in SQL is the ANSI standard IDENTITY column which is effectively a short cut to declaring and using a sequence. There are three basic options for using Identity columns - the next sequence value can be used by default, by default on null, or alwys.

Let's look at some examples.

SQL> CREATE TABLE test (col1 NUMBER GENERATED BY DEFAULT AS IDENTITY);

Table created

SQL> INSERT INTO test VALUES (DEFAULT);

1 row created.

SQL> INSERT INTO test VALUES (999);

1 row created.

SQL> SELECT * FROM test;

COL1
--------------
1
999

SQL>

Note that we have to use the keyword DEFAULT otherwise thE following error is generated "ORA-01400: cannot insert NULL into ("HR"."TEST"."COL1")".

The next example demonstrates the use of DEFAULT ON NULL.

SQL> CREATE TABLE test2 (col1 NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY);

Table created

SQL> INSERT INTO test2 VALUES (DEFAULT);

1 row created.

SQL> INSERT INTO test2 VALUES (NULL);

1 row created.

SQL> INSERT INTO test2 VALUES (999);

1 row created.

SQL> SELECT * FROM test2;

COL1
--------------
1
2
999

SQL>


Oracle Tips & Tricks to SKYROCKET Your Career!

If you're not already a subscriber to Oracle Tips and Tricks, you're  missing out on a myriad of tips and techniques to help you become a better, faster, smarter developer. Subscribe now and ignite your career.


The difference is that we can use the keyword NULL and the column will be populated for us from the sequence.

The next example demonstrates the use of GENERATED ALWAYS AS IDENTITY.

SQL> CREATE TABLE test3 (col1 NUMBER GENERATED ALWAYS AS IDENTITY);

Table created

SQL> INSERT INTO test3 VALUES (999);

INSERT INTO test3 VALUES (999);

*
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column

SQL> INSERT INTO test3 VALUES (DEFAULT);

1 row created.

SQL> INSERT INTO test3 VALUES (NULL);

1 row created.

SQL> SELECT * FROM test3;

COL1
--------------
1
2

SQL>

As the previous example shows, we cannot provide a value for a column that is defined as GENERATED ALWAYS.

As with sequences, identity columns can be defined with a starting value, an increment and how many values to cache.

SQL> CREATE TABLE test3 (col1 NUMBER GENERATED ALWAYS AS IDENTITY (START WITH 101 INCREMENT BY 7));


One of the best ways to enhance your Oracle expertise quickly amd easily is to take a fromal Oracle training course.

We partner with Gogo training to provide high quality, online, self-paced training courses taught by industry experts to help you gain the kowledge and skills you need to advance your career.

Go to GogoTraining to see the course available and enter "ASKTHEORACLE" as the coupon code when you check out to get a 5% discount.

Also check out the Oracle, SQL and PL/SQL tutorials on our site. If you can't find what you're looking for try the search facility and if you still can't find the answer to your question, ask us.

Return to part 1 of Oracle 12c new features for developers