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

PACKT Publishing offers 50% off on eBooks and Videos

Hi Everyone,

PACKT Publishing offers 50% off on all eBooks and Videos until Oct 21^th 2013 Midnight. It is a limited time offer and take an advantage of this Columbus Day offer to buy your favorite books for half the price.

USE PROMO CODE: COL50

Here is the PACKT PUBLISHING Link

http://www.packtpub.com/?utm_source=Columbus+Day&utm_medium=Link&utm_campaign=Columbus+Day

Regards,

Satishbabu Gunukula

http://www.oracleracexpert.com

SAP HANA Training & Certification (C_HANATEC_1) Experience

I was excited to see how HANA in-memory database works and finally I have attended SAP HANA Training i.e. HA100, HA200 courses from SAP America Education.

The HA100 – SAP HANA – Introduction (2-day course)
This course provides an overview on the capabilities of SAP HANA.

I felt that they would have covered some more technical details in this course. From this course you will get a chance to exercise feel and look of HANA Studio and levels of Modeling. I don’t recommend this course, unless until your company offering free training. Now days there is plenty of documentation/ Free Videos/Webinar available, I would recommend going over those to get the overview of SAP HANA.

Here are some good links 

Introduction to Software Development on SAP HANA
https://open.sap.com/videos/36?module_item_id=84

SAP HANA Academy
http://www.saphana.com/community/hana-academy

The HA200 – SAP HANA – Installation & Operations (5-day course, previously it is a 3 day course)
This course covers some important tasks of the daily work of an SAP HANA Administrator. It also provides details about troubleshooting and assuming the high-availability of SAP HANA System.

From this course you can expect some level of technical information about Architecture, administration, operations, backup & recovery and monitoring addressing primarily technology consultants and administrators.

Note that the SAP HANA Student Guides/Course materials are not enough to prepare for SAP HANA Certification. I would highly recommend to go over below documentation links as it has very detailed, in-depth technical information.

Here are some good links

Help on SAP HANA
http://help.sap.com/hana - it has lot of useful guides

SAP HANA Administration Guides
http://help.sap.com/hana/SAP_HANA_Administration_Guide_en.pdf

SAP HANA Security Guide
http://help.sap.com/hana/SAP_HANA_Security_Guide_en.pdf

SAP HANA Server Installation Guide
http://help.sap.com/hana/SAP_HANA_Server_Installation_Guide_en.pdf

Most of the SAP courses offer both Instructor Led in Physical and Virtual Classroom courses, I always prefer Instructor Led Physical as you will have direct interaction with Instructor.

Certification

I know Certification is very important part and most of us want to complete the SAP HANA Certification as it has some demand in the market. If you are seriously planning to go for certification, then don’t expect that you will get questions from SAP HANA Training Guides/Books of HA100, HA200 like other Vendors guides.

First decide which path you want to go

1. SAP Certified Application Associate - SAP HANA 1.0
2. SAP Certified Technology Associate - SAP HANA 1.0
...etc

Check below link for SAP HANA Certification Paths
http://scn.sap.com/community/hana-in-memory/blog/2013/01/10/sap-hana-certification-pathways

I would highly recommend reading the documentation from above links and getting some hands on practice before you go for SAP HANA Certification.

SAP Offering free 30 day SAP Hana Trail, and it is very useful.
http://scn.sap.com/docs/DOC-39531

Few vendor’s offers SAP HANA Systems on Cloud and you can use their services for Practice
https://aws.amazon.com/marketplace/pp/B009KA3CRY/ref=mkt_ste_sap_slns_SAPHana1

I have used the above documentation and hands on experience and to complete SAP Certified Technology Associate - SAP HANA 1.0(C_HANATEC_1) certification.

Questions

If this is not your fist certification with SAP then you might already know that you will get the questions from list of “Topics”. From each topic some percentage of questions will be asked, you should be able to see that information in above links.

For C_HANATECH_1 Certification, the no of questions will be 80, you must get 60% to pass the Exam and the duration is 180 min (3 hrs). From my experience this is really good amount of time to complete the exam and also to go-over flagged questions. Note that PASS percentage (60%) will NOT be decided by each topic, it will be on overall exam.

Don’t expect that you will get straight questions from book or documentation. I got many questions addressing practical experience and real-life scenarios.

1. System Security and Authorization- There is no straight questions from Security, most of the questions that I got from this section are “scenario” based. They will explain “scenario” and “Error Message” you need to correct the security issue. Some hands on experience needed in this section.

2. Installation – In this topic don’t expect that you will get the questions from HANA Couse Material. If you go through the documentation that I provided then you will be in a very comfortable position.

3. System Architecture Design - If you don’t have much hands on experience then you must concentrate on this Topic. I felt that it is easy to clear 100% questions from this section without hands on experience.

4. System Architecture Implementation – For non-experienced users this is the next easy topic. I feel that the documentation links that I provided are good enough.

5. Data Provisioning – In this section you will see 8%-12% of questions, but related to “Data Services” (Install, Admin, Architecture, Modeling, Monitoring...etc.) itself I have received around 20-25 questions. Don’t neglect “Data Services”.

I hope this is useful to you. “Best of luck for your HANA Certification…!”

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