Wednesday, October 16, 2013

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

4 comments:

  1. I am Sridevi Koduru, Senior Oracle Apps Trainer at Oracleappstechnical.com With 8 Yrs Exp on Oracle Apps and 13 Yrs IT Exp Providing Online Training on Oracle Apps Technical, Oracle Apps Financial Functional, Oracle Apps SCM, Oracle Apps HRMS, Oracle Financial for Indian Localization, SQL, PL/SQL and D2K. I have Provided Training for 500+ Professionals Most of them are Working in Real Time now.

    Contact for (One to One Personal Online Training) on Oracle Apps Technical, Oracle Apps Financial Functional, Oracle Apps SCM, Oracle Apps HRMS, Oracle Financial for Indian Localization, SQL, PL/SQL and D2K at training@oracleappstechnical.com | sridevikoduru@oracleappstechnical.com | +91-9581017828 | http://www.oracleappstechnical.com

    Linkedin profile - http://in.linkedin.com/pub/sridevi-koduru/8b/76a/9b8/

    ReplyDelete