Ramana Cheemakurthy, Principal Database Administrator
Have you ever wished that you could create an index that is visible to only your session, so you can test its effect without disturbing other users or before releasing it to production? Perhaps you wanted the index vanished for a few days to see if the query is really slow. Maybe you just wanted to use the index for particular job or reporting purposes.
Someone listened to us or went through the same pain and now thanks to Oracle, we don't have to worry about it. Oracle has introduced a nice feature called "Invisible indexes" starting from Oracle 11g release 1, which allows us to play around with hiding indexes from applications or users. Oracle has made our lives easier by giving us the ability to make an index invisible or visible with the following simple commands:
ALTER INDEX index_name INVISIBLE;
ALTER INDEX index_name VISIBLE;
You might be wondering how we know which indexes are visible, and which are not; you can query the DBA_INDEXES or USER_INDEXES dictionary views. Now these views have a new column named VISIBILITY that indicates the status of the index visibility. To make life easier, there is a new database initialization parameter named OPTIMIZER_USE_INVISIBLE_INDEXES, which determines whether all invisible indexes are visible to the optimizer/user or not for the entire database. If you want to set this parameter for a particular session, you can use the following simple command in your session, which makes all Invisible indexes available.
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE;
Unless there is a hint specified, an invisible index is not seen by the optimizer; thus, it won't be used for any execution plans. Isn't it cool? Several times as a DBA, I'd suggest to developers that they create an index, if I feel that query would perform better. But, this gives us a unique opportunity to test the effects of new indexes before completely moving it into a production.
Additionally, invisible indexes can be used to test the removal of an index without dropping it right away, thus enabling a grace period for testing in production environments.
Furthermore, Oracle 12c allows us to create multiple indexes on the same columns, providing only one index visible at a time. Before 12c, if you had an index on column {col1} or columns {col1, col2}, you couldn't create another index on the same column or set of columns in the same order. But in 12c, you can have multiple indexes on the same column or set of columns as long as the index type is different. However, only one type of index is usable/visible at a given time.
Invisible columns:
As with invisible indexes, have you ever wanted to hide a column? Previously, we used to create a view to hide the some of the columns (required information), or to apply some sort of security conditions by providing access on limited columns. With the same concept as invisible indexes, now we have a new feature starting from Oracle 12c to make a column invisible.
In 12c R1, you can now have an invisible column in a table. When a column is defined as invisible, the column won't appear in generic queries, unless the column is explicitly referred to in the SQL statement or condition, or DESCRIBED in the table definition. It is pretty easy to add or modify a column to be invisible and vice versa.
CREATE TABLE tbl_invisible_column (
coluna1 NUMBER,
coluna2 NUMBER,
coluna3 NUMBER INVISIBLE,
coluna4 NUMBER );
SQL> desc tbl_invisible_column
Name Null? Type
---------------------------------------- -------- ----------------------------
COLUNA1
NUMBER
COLUNA2
NUMBER
COLUNA4 NUMBER
So we can't see invisible columns in a simple "SELECT * from Table" query, but they will be displayed if explicitly referenced in the SELECT column list. It might have some use when you want to make changes to a table without disrupting existing applications (by adding an invisible column, you're not going to stuff anything up), and then make sure anything new that does need the column explicitly references it.
In the insert statement, all the columns including the invisible columns must be included in the insert statement otherwise it will return error as shown below.
SQL> INSERT INTO tbl_invisible_column VALUES (101,102,103,104); INSERT INTO tbl_invisible_column VALUES (101,102,103,104)
* ERROR at line 1:
ORA-00913: too many values SQL> INSERT INTO tbl_invisible_column (coluna1,coluna2,coluna3,coluna4) VALUES (100,200,300,400);
1 row created.
SQL> INSERT INTO tbl_invisible_column VALUES (101,102,104);
1 row created.
Prior to Oracle 12c, adding a column could break an application or procedures if the column dependencies are not revised. I personally believe that the Invisible column is a great way to add a column to a table without breaking the code. As a DBA, I love these two features, as I can deploy the changes to production or test them without impacting the production application.
For assistance with JD Edward, please email us at inquiries@GetGSI.com.