Pickleball spielen

You may have noticed that we introduced Invisible Indexes as an 11g New Feature. Their main benefit is that we can test whether performance differs if we would drop an index without actually dropping it. This is particular useful after an Exadata Migration because we expect that some conventional indexes migrated are now obsolete and may be substituted by Storage Indexes. This is the scenario:

SQL> select segment_name,bytes/1024/1024 as mb from user_segments;

SEGMENT_NAME                           MB
------------------------------ ----------
SALES                                 304
SALES_ID_IDX                        79.75

This moderate sized table sales with a B*tree index on the column ID was moved to Exadata. We want to check whether it is a good idea to drop that index. Making it invisible therefore:

SQL> alter index SALES_ID_IDX invisible;

Index altered.

SQL> set timing on
SQL> select * from sales where id=4711;

        ID       FLAG PRODUCT                   CHANNEL_ID    CUST_ID AMOUNT_SOLD ORDER_DAT SHIP_DATE
---------- ---------- ------------------------- ---------- ---------- ----------- --------- ---------
      4711       4712 Oracle Enterprise Edition          1        711        5000 10-NOV-96 11-NOV-96

Elapsed: 00:00:00.19

Keep in mind that we may need multiple statements accessing the ID column in order to make the Cells aware that it is beneficial to maintain a Storage Index for that column. If after a while performance is fine for those statements, we may consider the index as obsolete. Should DML hit the table while the index is invisible, it is still maintained, so that we can make it visible again without any effort, should the index turn out to be necessary:

SQL> update sales set id=4711 where id=4710;

1 row updated.

SQL> commit;

Commit complete.

Let’s compare runtime of Smart Scan with Storage Index with Conventional Index Access:

SQL> select * from sales where id=4711;

        ID       FLAG PRODUCT                   CHANNEL_ID    CUST_ID AMOUNT_SOLD ORDER_DAT SHIP_DATE
---------- ---------- ------------------------- ---------- ---------- ----------- --------- ---------
      4711       4711 Oracle Enterprise Edition          0        710        5000 09-NOV-96 10-NOV-96
      4711       4712 Oracle Enterprise Edition          1        711        5000 10-NOV-96 11-NOV-96

Elapsed: 00:00:00.22
SQL> alter index sales_id_idx visible;

Index altered.

Elapsed: 00:00:00.02
SQL> select * from sales where id=4711;

        ID       FLAG PRODUCT                   CHANNEL_ID    CUST_ID AMOUNT_SOLD ORDER_DAT SHIP_DATE
---------- ---------- ------------------------- ---------- ---------- ----------- --------- ---------
      4711       4711 Oracle Enterprise Edition          0        710        5000 09-NOV-96 10-NOV-96
      4711       4712 Oracle Enterprise Edition          1        711        5000 10-NOV-96 11-NOV-96

Elapsed: 00:00:00.01

In this case, the Conventional Index is superior – so we keep it.

Conclusion: With making indexes invisible, we can easily check whether indexes are useful without having to drop (and in case recreate) them actually. While this may be of interest for „ordinary“ Oracle Databases already, it is particular a useful feature for Exadata where we expect some conventional indexes to become obsolete after a migration.