Invisible Indexes
With Version 11g Oracle introduced invisible Indexes and I had several discussions with customers why it makes sense to create Indexes which are not used but maintained – because that’s what an invisible index is.
Let’s have a closer look at the creation of indexes and why an invisible index might fit.
CREATE INDEX DEMO.IDX_NAME ON DEMO.PERSONEN (NACHNAME, VORNAME) LOGGING INVISIBLE;
Wow I’ve created an index and that one is not visible:
What happens if I do try to query some data from the table “personen” like the following?
Unfortunately even though I’ve created a brand new index it will not be used (but still maintained will changing dat).
To use that index we must make it visible:
SQL> ALTER INDEX IDX_NAME VISIBLE;
And – oh wonder – the index will be used!
So why should one want to create an index which cannot be used by the optimizer?
Because you want to test new indexes before they go into production. So by setting them invisible the optimizer will not use them under normal circumstances. But for your session you can use the following command to check if the index is useful or not:
SQL> ALTER SESSION SET optimizer_use_invisible_indexes=TRUE;
And think the other way round: You probably have some few or maybe hundreds of indexes in your database where you are quite sure that they are needless so consuming space and slowing down insert, updates and deletes. So why don’t you drop the indexes; because you are not 100% sure. And that’s where invisible indexes come into play: you can simple made them invisible for the optimizer for some weeks or months and if, after that period of time no one is complaining about performance you can drop them.
Another example might be that you need an index only for one specific report or batch run but for the daily business this index shouldn’t be used because there’s another index which is more suitable. Again make the index invisible and visible only for the specific run.
But until Oracle 11g there was still one limitation: you cannot have two indexes (no matter if visible or not) on the same set of columns. But is that really a limitation? Yes, because you might want to test a B*Tree Index versus a Bitmapped Index. With Oracle 11g you first had to drop the B*Tree Index before you can create the Bitmapped Index. Starting with Oracle 12c it is possible to have both indexes available but only one visible at a time. That’s again a nice example for using invisible indexes.
Invisible Columns
Okay, now we know that invisible indexes might be useful for some special situations – with my favorite to use them before dropping indexes. But why should you want to create invisible columns which are newly introduced with Oracle 12c? It looks like the opposite of virtual columns.
Virtual Columns
Let’s first look at virtual columns, which were introduced to my knowledge in Oracle 10g. It is possible to define additional columns for a table which are derived from other columns of that same table. So for example having a virtual column fullname which is in upper case the concatenation of firstname and surname:
CREATE TABLE customers( custid NUMBER(10) GENERATED BY DEFAULT AS IDENTITY, gender VARCHAR2(5 CHAR), firstname VARCHAR2(50 CHAR), lastname VARCHAR2(50 CHAR), fullname VARCHAR2(102 CHAR) GENERATED ALWAYS AS (UPPER(lastname||', '||firstname)), birthday DATE );
In this case no space (except the definition) is allocated for the column fullname but you can easily query your data including that column using SELECT * FROM customers.
If neccessary you can create indexes based on virtual columns (including primary keys) or partition tables on them. So it’s a very flexible way of adding information to a table without using additional space.
But why do you do it the other way around? Defining a column, which stores data but the data is not visible for a query.
CREATE TABLE addresses ( addid NUMBER(10) GENERATED BY DEFAULT AS IDENTITY, custid NUMBER(10), street VARCHAR2(50 CHAR), city VARCHAR2(50 CHAR), zipcode VARCHAR2(8 CHAR), valid_from DATE INVISIBLE DEFAULT SYSDATE, valid_to DATE INVISIBLE );
In this case two additional columns were added to the table addresses which are indicating from what time until when this is a valid customer address. Per default the address is valid entirely but if he change house you create a new row with the new address.
In this example you can see that neither column valid_from nor valid_to is shown but that customer has two addresses.
But now it is possible to specify to correct address for any given time like with the following query:
This example is very common for CRM systems where you need to know how long data is valid or if you need some historical information. Oracle introduced temporal validity and data archiving with Oracle 12c which are both based on the invisible columns.
In one of my next blogs I will have a closer look in those two new features.
Have fun testing invisible columns!