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
Subscribe to:
Post Comments (Atom)
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.
ReplyDeleteContact 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/
I appreciate you and hopping for some more informative posts. happy new year 2016 new year 2016 happy new year 2016 images feliz año nuevo 2016 imagenes de año nuevo 2016 feliz año 2016 happy new year images 2016 new year 2016 images new year images 2016 bonne année 2016
ReplyDeleteGreat and Useful Article.
ReplyDeleteJava Online Training
Java Course Online
Java EE course
Java Course in Chennai
Java Training in Chennai
Java Training Institutes in Chennai
Java Interview Questions
Java Interview Questions
easter 2016 pictures
ReplyDeleteeaster 2016 decorations
easter 2016 wishes
easter games
easter eggs images
easter eggs Pictures
شركة نقل عفش بجدة
ReplyDeleteشركة نقل عفش بالقصيم
شركة نقل عفش بتبوك
شركة نقل عفش بحفر الباطن
وبالنسبة الى الخدمات الخاصة بتنظيف خزانات المياه في الطائف فإننا نعتبر من ضمن شركات نظافة خزانات بالطائف التي لها خبرة طويلة في مسألة تنظيف خزانات المياه بالطائف وتعتبر شركتنا من اقوى شركات تنظيف خزانات بالطائف التي تقدم للعملاء خدمات متميزة في نظافة الخزانات من الأتربة والأوساخ ولهذا تعتبر شركتنا افضل شركة تنظيف خزانات بالطائف وافضل من كل الشركات الموجودة بالطائف ولهذا السبب فان شركة تنظيف خزانات المياه بالطائف تعتبر هي الشركة الأولى التي تقدم خدمات ممتازة وتراعي العميل في الأول والأخير
ReplyDelete