Invisible Indexes & Exadata

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.

About these ads

, ,

  1. #1 by liyan on November 12, 2014 - 19:39

    can you share dropping index strategy? such as what kind of index can be dropped on Exadata, Primary index, unique index, etc
    thanks

  2. #2 by Uwe Hesse on November 15, 2014 - 14:37

    The strategy is quite simple: You drop the indexes that do not benefit your statements. The hard part is to actually find those indexes :-)

  3. #3 by liyan on November 17, 2014 - 21:05

    we are testing to drop any index besides unique ones
    will share what we found out

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 2,975 other followers

%d bloggers like this: