Indexes In Their Own Tablespace: Recoverability Advantages (Get Back)

Thought I might share some thoughts regarding recoverability issues with regard to having indexes separate and stored in their own tablespace.

I’ve already discussed here how the loss of an index only tablespace would be a catastrophic event, with the database in dire straights until the indexes are recovered. Therefore the faster we can recover from the situation, the faster we can make DML statements work again, the faster we can prevent Full Table Scans from crippling database performance, the faster we can return the database to a functional state again, the better for our users and for our sanity.

One of the advantages of having indexes separate from tables and stored in their own tablespace is that we have a number of different recovery options available to us. Rather than having to perform a full tablespace or data file recovery, we can potentially simply just rebuild all the impacted indexes. Providing the base tables are available and have not been impacted by whatever catastrophic event has befallen the index tablespace, we can rebuild the indexes (in another tablespace if necessary). This will hopefully be a more simplistic, efficiently and most importantly faster method of recovering all our impacted indexes than performing an actual database recovery.

But will it really be more simplistic, efficient and faster ? The recovery advantages with having indexes in their own tablespace are often exaggerated. Let’s first take a look at an example scenario.

Let’s assume we have an index only tablespace that stores all the indexes for our application. Let’s say we have 100G worth of indexes. In a physically separate table only tablespace, let’s say we have a total of 200G worth of table data which is approximately double that of the index tablespace. Generally speaking, it’s common for indexes to not use the same amount of storage as the tables as typically not all columns are indexed. Of course it’s possible for a specific column to be indexed several times and for the index storage to exceed table storage in some cases, but not typically. There may of course be some free space in these tablespaces but let’s assume free space is minimal.

So we have 100G of indexes and 200G of tables.

Let’s also assume there’s on average 2 indexes per table, if only to keep the following arithmetic nice and simple 🙂 Of course some tables may have many more indexes, some may just have the one index and in some rare examples there may be no indexes at all.

Now, it’s important to note that building a new index is actually a very expensive exercise. Oracle has to read all the data blocks in the base table, it has to sort the data in the order of the index entries, it has to create the index segment and write the index data, while generating undo and redo in the process.

Now that’s a lot of work …

However, in this scenario, we need to do this work for each and every index that’s in our stuffed index tablespace. Not only that, but we also need a script that can identify each of our impacted indexes, that generates the necessary index rebuild scripts (to another tablespace if necessary) and that handles any necessary constraint related issues.

In this specific scenario, we have to make Oracle and the database processes basically perform the following amount of work:

• Read approximately 400G of table related data. As we have an average of 2 indexes per table, we have to basically read each and every table an average of 2 times to build their related indexes. That’s 2 x 200G = 400G.
• Sort approximately 100G worth of index related data. Sorting is a really expensive, relatively slow process and we have 100G worth of index data that needs to be sorted.
• Write and create approximately 100G of index related segments

Note we also have to generate Data Dictionary related changes, we have to generate a bunch of undo related changes and we also (although optionally) generate lots and lots of redo.

In short, the database is being absolutely hammered during this whole process and it will take a loooong time to complete.

And this is meant to be the easy, efficient and above all fast method of recovering our indexes ?

So what is the alternate recovery strategy that this method of “simply” rebuilding all indexes is meant to protect us from.

Well, with a damaged tablespace, we basically need to perform a tablespace level recovery, restoring “just” the 100G worth of data files and applying any associated redo logs since our last backup. Depending on our backup and recovery strategy, we may actually reduce the redo logs being applied by applying incremental or cumulative backups as well.

Instead of the database slowly and laboriously having to read, process and write 6 or 7 times the amount of data (in our scenario), we can use the OS to much more efficiently copy across the index related data files.

Instead of having to script the rebuilding of all impacted indexes, literally a couple of RMAN commands will basically automatically completely restore and recover the impacted index tablespace for us.

In the scenario when only a specific data file or mount point within the tablespace has been problematic, the implications of attempting to recover the situation by simply rebuilding the indexes gets worse, much worse.

Firstly, if we are so inclined, we need to identify which indexes have at least one extent within the damaged portion of the index tablespace. We then need to entirely rebuild all these indexes, regardless of how much of the index may actually remain undamaged with other extents in undamaged portions of the index tablespace. This all takes resources, resources, resources and time, time, time.

We can’t just rebuild a part of an index (unless it’s partitioned of course) but we can recover a part of a tablespace. We can simply recover the damaged part of the tablespace, restoring and recovering just the specific data file or files, again potentially with just a few simple RMAN commands.

With small databases with small amounts of data, the time it takes to rebuild all indexes in an application may be acceptable for the business. However, in larger database environments, the extra time and resources required to rebuild large amounts of index data compared to other recovery strategies would be totally and completely unacceptable.

An exercise for those who store indexes in a separate tablespace, in large part because of the recoverability advantages. On a QA system or equivalent copy of your production database environment, go through a real exercise of attempting to recover your indexes by rebuilding them and actually time how long such a recovery process takes. Then repeat the exercise by recovering the database using a conventional database recovery technique and time the differences.

You may just come to the conclusion that rebuilding indexes may not be such a fast and efficient recovery process in many scenarios after all …