INVISIABLE Columns and MULTIPLE Indexes on Same Set of Columns in Oracle 12C

As you know Oracle 11g introduced Virtual Column and Invisible indexes. Now Oracle 12c introduced new enhancements in this area.

1. Invisible Column
2. Multiple Indexes on the same set of Columns

Invisible Column:
In Oracle 12c you can able to add a column to table in the invisible fashion. The invisible column will not show up in DESCRIBE statement and also column won’t appear in queries for ex: - SELECT * query
You can add an INVISIABLE COLUMN or you can modify an existing COLUMN as INVISIBLE.

SQL> CREATE TABLE test1 (a number, b varchar2 (10), c date);
Table created.

SQL> ALTER TABLE test ADD( d number INVISIBLE );
Table altered.

SQL> DESC test
Name Null? Type
--------------------------
A NUMBER
B VARCHAR2(10)
C DATE

As you see the column “D” is not visible. Let’s insert the data into table.

SQL> INSERT INTO test VALUES (1,'Test1',sysdate,100);
1 row created.

SQL> commit;
Commit complete.

SQL> SELECT * FROM test;
A   B       C
--- ------ ---------- 
1 Test1 10-OCT-13

We are able to insert the data into INVISIBLE column, but not able to see the data using “SELECT *” query. Let’s try to query the data referring the columns explicitly in SELECT statement.

SQL> SELECT a,b,c,d from test;
A  B        C                 D
-- ------- ------------ --------
1  Test1  10-OCT-13 100

Now we are able to see the data from INVISIBLE column

NOTE: - In order to INSERT or SELECT data in INVISIBLE column, you must explicitly use the INVISIBLE column name

Let’s change the column to be visible and query the data.

SQL> ALTER TABLE test modify d visible;
Table altered.

SQL> SELECT * from test;
A B         C                 D
-- ------- -----------  ----------
1  Test1  10-OCT-13 100

Multiple Indexes on the same set of Columns
Prior Oracle12c, you cannot able to create another index on same column name or set of columns which already have index. In Oracle 12c, you can able to create multiple indexes the same set of columns as long as some characteristic is different and qualifying characteristics are:

· B-tree versus bitmap
· Different partitioning strategies
· Unique versus nonunique

Creating multiple indexes on the same set of columns enables transparent and seamless application migrations without the need to drop an existing index and re-create it with different attributes.

SQL> CREATE INDEX test_index1 ON TEST(a,b);
Index created.

SQL> CREATE BITMAP INDEX test_index2 ON TEST(a,b);
CREATE BITMAP INDEX test_index2 ON TEST(a,b)
*
ERROR at line 1:
ORA-01408: such column list already indexed

Here I am trying to create a BITMAP index, but it is failed. Note that one type of index is usable/visible at any given point of time.

SQL> CREATE BITMAP INDEX test_index2 ON TEST(a,b) INVISIBLE;
Index created.

Now index created successfully.

SQL> ALTER INDEX test_index2 VISIABLE;
ALTER INDEX test_index2 VISIABLE;
*
ERROR at line 1:
ORA-14147: There is an existing VISIBLE index defined on the same set of
columns.

First we need to make sure that the existing index is INVISIBLE then only you can able to make the other index visiable

SQL> ALTER INDEX test_index1 INVISIBLE;
Index altered.

SQL> ALTER INDEX test_index2 VISIBLE;
Index altered.

Regards,
Satishbabu Gunukula, Oracle ACE
http://www.oracleracexpert.com