jump to navigation

Separate Indexes From Tables, Some Thoughts Part I (Everything In Its Right Place) April 16, 2008

Posted by Richard Foote in Oracle General, Oracle Indexes, Oracle Myths, Oracle Opinion, Tablespace Management.

Although by no means as common as it once was, there’s still some who believe separating indexes in a different tablespace from their parent tables somehow improves performance.

The theory goes that by having indexes in their own tablespace, we can reduce overall contention issues and thereby improve the overall performance of the database.

Here are some thoughts for consideration for those who might be so inclined …

First, let’s just have a little look at the behaviour of a “typical” large scale index range scan, using an index with a height of say 3.

We first begin by accessing the root block of the index. This is a single block read which for many high accessed indexes would typically be cached and not result in a physical I/O. Next we read an intermediate branch block. This is also a single block read and is also likely to be cached if the index is heavily accessed. Regardless, it’s another index related I/O. Next we finally reach and read the first index leaf block containing the start of the index entries of interest. Again, it’s a single block I/O and again it’s index related.

So far we’ve performed 3 “random”, single block I/Os of index related blocks. If the index were in a separate tablespace, all the action would only be on the index tablespace thus far.

We next read our first table block containing the first row referenced by the first index entry of interest. This yet again is a single block I/O that could potentially be any block within the table. If the table were in a separate tablespace from the index, we would still need to perform a physical I/O (assuming the block isn’t already cached) on a “random” block within the table tablespace. If the table were in the same tablespace as the index, we again need to perform a physical I/O on a random table block. Still no difference thus far.

We next (very likely) reference the same index leaf block to determine the second row of interest. Note this block will almost certainly still be cached as it’s just been accessed. Therefore, if the index were in the same or different tablespace to the table, still no difference as there’s no associated physical I/O.

We then read the second table block of interest via a single block I/O. Unless this index has a very good clustering factor, we’re likely to read a totally different table block that could be any other block within the table. It’s extremely unlikely therefore to be the block that is physically contiguous to the block previously read. Only if the index were very well clustered, could it possibly be the same block as previously read or possibly the next logical block in the table.

However, in all these scenarios, having the table in a separate tablespace still makes no difference at this stage. We either need to perform another physical I/O on the table or we perform just a logical I/O. Even in the extremely unlikely case the next block read is physically contiguous to the previous block read, it would still be contiguous whether the index was separate or not and not be impacted by the index read activity thus far. Again, thus far it makes no real difference having the index in a separate tablespace.

We go back to the same index leaf block to determine the next row of interest and then access the next table block, which for a large randomly distributed table is again likely to be another different block. The point being we’re accessing the index and the table in a sequential fashion, reading the index, then reading the table. Reading the index and then reading the table again.

For small index scans, the index leaf block in question is likely to be the same single leaf block as a leaf block can potentially store hundreds of index entries (depending of course on block size, index row size and where within the index leaf block we logically begin to read the index entries of interest). So for small scans, it’s not going to have any real impact having indexes in a separate tablespace as we’re basically reading a few index related blocks followed by the table related blocks.

The table blocks are likely to be different blocks in a randomly distributed, poorly clustered index or possibly (although more rarely) a small sample of blocks in a well clustered index. However, in either scenario, if if we need to access just the one leaf block, it makes no difference whether the index is in a separate tablespace or not, the I/Os and so-called contention are the same regardless.

In some scenarios, Oracle can perform a prefetch step whereby it orders the index entries based on the rowids to first determine which table blocks need to be accessed, thus preventing the same table block having to be re-read several times. However, again, it makes no difference thus far if the index is in a separate tablespace or not as the I/O requirements are the same regardless.

In larger index range scans however, we might need to visit the next logical index leaf block or indeed subsequently many such index leaf blocks. Note each leaf block contains a pointer (referred to as kdxlenxt in a block dump) so Oracle can directly access the next index leaf block. If our index were in a separate tablespace and making the HUGE assumption that there’s thus far been no other activity in the index tablespace, the disk head may not have moved from where it left off after reading the last leaf block. With the indexes and tables coexisting in the same tablespace, we have very likely moved on from this location with any subsequent table related I/O activity.

Maybe now at last, finally  we have a benefit in having indexes in their own tablespace …

However, reading the next index leaf block is again a single block read and most importantly is not necessarily “physically” contiguous to the previous leaf block. Remember, index leaf blocks split as part of their natural growth and the new block allocated is simply the next block available in the index freelist. Therefore the next logical index leaf block in an index structure could physically be virtually anywhere within the extents allocated to the index. When we read the next “logical” index leaf block, it does not necessarily mean it’s the next “physical” block within the index segment. It’s likely just another random, single block I/O.

That being the case, again we have no benefit in the index being in a separate tablespace. In both scenarios, we have to go scanning the disk looking for the physical location of the next index leaf block (again assuming the index leaf block isn’t already cached). This activity needs to be performed whether the index is in it’s own tablespace or not.

When we move back to read the next table block based on the first index entry from the newly accessed index leaf block, again, it’s extremely unlikely the next table block accessed will be the next contiguous block from the previously read table block. So again, we very likely need to go a hunting for the next table block on disk, regardless of it being in a separate tablespace from the index. Again, separating indexes from tables makes no real difference.

So not only do we move between index and table in a sequential manner but the actual blocks read within both the index and the table are likely to be totally random, non contiguous, single block reads.

That being the case, what are the performance benefits of storing indexes and tables separately ? How does storing indexes and tables separately actually reduce contention when most physical I/Os in both index and table segments are effectively random, single block reads ?

Now this example has just been a single index scan, performed by one user on just one index and table. The benefits therefore of separating indexes and tables even in a single user environment are somewhat “dubious”.

However, how many environments only have the one user. Not many. Most environments have lots of users, some with many hundreds, some with many thousands of concurrent users . All these users are potentially performing concurrent I/O operations, not only potentially on these very same tables and indexes but on lots of different tables and lots of different indexes within our table and index tablespaces. Even if index leaf blocks were to be physically contiguous in some cases (such as monotonically increasing indexes where this is more likely), by the time we’ve read the index leaf block, processed and read all the associated table blocks referenced by the index leaf block, the chances of there being no subsequent physical activity in the index tablespace due to another user session is virtually nil. We would still need to re-scan the disk to physically access the next index leaf block (or table block) anyways.

Add to the mix the fact many sites now use SANS, NAS, ASM etc. and what might appear to be one contiguous file could actually be physically split and spread all over the place. The whole notion of what is actually physically contiguous and what isn’t is blurred anyways.

The next time someone suggests separating indexes from table improves performance, you may just want to ask a couple of little questions; why and how ?

However, I’ll next discuss how indeed performance can improve by storing indexes in a separate tablespace. But just like our mad scientist thinking flies with no wings go deaf, I’ll explain how the performance improvement is not actually directly related to the indexes being separate from the tables.

I’ll also discuss how database recoveries are not typically helped by having indexes in a separate tablespace as often suggested.


1. Tom - April 16, 2008

The only case where I can think the separation can be good is if you have 2 disk controllers and 2 disks and you are doing heavy inserts or a lot of conurrent lookups and you want to spread the I/O out. I think just doing it as a rule of thumb is a bad idea, especially with new technologies to replace old ones. I can also waste a lot of effort and time and not really buy you anything except more complexity. I’ve seen databases that had multiple schemas each with about 200+ tables and each table was in it’s own tablespace and each index was in it’s own tablespace. Talk about a management nightmare.

The other funny thing like you said with SAN’s is it is hard to know how it has been carved up. You then have SAN caches and multiple paths to the disk. Throw in ASM and it rebalances hot spots automatically. This usually eliminates any kind of need to try and segment things for physical reasons. It soon becomes just logical at that point. It should make the DBA’s job easier… you would think : )


2. Brian Tkatch - April 16, 2008

When this myth was originally explained to me, it was explained that this way (assuming the TABLESPACEs are each on their own disk) the DB could access the INDEX and TABLE concurrently. That is, instead of:

1: Get block from INDEX TB
2: Get block from DATA TB
3: Get block from INDEX TB

That step 3 would happen simultaneously with step 2, thus a speed increase.

If i read your explanation correctly, you are saying that step 3 will wait for step 2 to finish. Is that really the case?


3. Robert Klemme - April 16, 2008

Brian, the question seems to be, whether it is generally efficient to fetch the next leaf block early. I can think of scenarios where it would not be efficient; this would typically be the case when you need to read the data block to determine whether you want to continue scanning. OTOH this might be optimized (i.e. prefetching only if you know you will continue scanning irrelevant from the DB block read) but I am not sure whether it’s worthwhile. Generally I’d say fetching a block is an expensive operation and you want to be sure that you actually are going to need it before you fetch.

I am sure, Richard will come up with a much better analysis. 🙂


4. Tom - April 16, 2008


We are having a healthy debate here at work. My manager used to be a DBA and is still kind of hands on believes in always separating indexes and tables.

This was the co-workers response when I pointed them to your blog post.

I believe the author has made many assumptions regarding reading the data and indexes and I disagree with some of his logic. He only looked at the simple leaf blocks and really did not discuss fragmentation or heavy high IO from multiple tables/indexes sharing the same space.”

Would you say that this person has a valid argument? Curious to see what your response is as I know many who believe that separating is always a good idea. I even used to believe this until I did some tests to see for myself but it seems to be an accepted rule of thumb in the Oracle DBA community to this day. Just like I hear that certain old-school DBAs still use buffer cache hit-ratios.


5. jarneil - April 16, 2008

Woah there Tom:

“Throw in ASM and it rebalances hot spots automatically.”

The only thing ASM rebalances is to ensure each disk in a disk group is filled to the same capacity. It does not rebalance based on any I/O metric, certainly not yet:



6. Tom - April 16, 2008

Jarneil… Good Catch!

Right after some heavy I/O activity I would see some RBAL processes kicking off and it was just a bad assumption : ).


7. Nigel Thomas - April 17, 2008


Your coworker has missed most of Richard’s points. First, the activity for a single session is sequential – so there’s no particular benefit there in splitting tables and indexes. Second: because of multi-user activity, the data access is essentially random – so in Brian’s example (post 2) the disc head is highly unlikely to be right where you need it for the “next” read of either index or table. Finally (and Richard maybe didn’t emphasis this enough) of course you DO want to split your physical IOs overall (across all your sessions, to keep up with the required read/write rates) so you do want to spread your segments across as many physical devices as necessary. The easiest way to do that is by following the SAME approach – ie striping each segment (therefore each datafile) over many physical discs.

A tablespace is a primarily a logical concept, though it is mapped to the O/S level datafiles. The benefits of separate tablespaces are:
– you can define different block sizes (but see Richard’s earlier posts)
– you can define different standard sized extents (fragmentation is only a problem if you mix up extent sizes – which used to be the default behaviour with PCTINCREASE)
– you get to do tablespace transport – so decide if you want the indexes to be included
– you can make a tablespace read-only
– in VLDBs of course you have to make your unit of management (tablespace and datafile) small enough to cope with eg for backup and recovery, so you will tend to have many more tablespaces

My (25 years) experience says that in 99.9 cases out of 100 striping everything will do a better job of spreading the physical IO statistically than trying to map individual tables and indexes to separate spindles. Applications tend to have a relatively small number of hot segments, within which there may be just a few hot extents or blocks (eg the last block in a ID index; the currently filling blocks in a table). This is why striping beats “designed placement”. And it’s a whole lot easier too.

PS I bet when your coworker uses separate tablespaces for index and tables, that he stripes the underlying datafiles anyway. That’s what gives him the multi-user performance… not the T/S split itself.

Regards Nigel


8. Richard Foote - April 17, 2008

Hi Tom

Thanks for the comments, it’s pleasing to know I’ve succeeded in generating some water-cooler discussions. There’s little point in simply preaching to the converted after all 😉

I intentionally focused initially on a nice, simple little example as it’s really important to get a reasonable understanding on what’s going on at a basic level.

If the “mechanics” of a typical large individual query executed by a single user is not going to be improved by separating indexes from tables, then the same will apply to the mechanics of all the other typical queries concurrently running by a whole bunch of users in an environment. None of the individual queries are going to benefit directly from indexes being separate from their tables.

The issue your boss mentions touches on what I’m going to cover in Part II. If there can be no contention within an individual query, then we need only concern ourselves with contention between different sessions and different queries. As your boss correctly notes, most environments have lots of users concurrently accessing lots of different blocks from the same tables and the same indexes and lots of blocks from lots of different tables and indexes.

Basically lot’s of sessions contending for access to different database blocks, from both tables and indexes, “in the same space”. Separating indexes from tables is actually a very very poor technique to reduce such contention. Because although an index access by one user won’t now interfere with a table access by another user, it does nothing per se to reduce contention of a table access by one user from a table access by other user (or an index access from one user from an index access by another user) within the same tablespace. Because you still have all tables “sharing the same space”.

Also, in my example, just note how many index blocks were read vs. the number of table blocks. There were potentially many hundreds more table blocks accessed because typically index blocks only contribute a relatively small percentage of overall I/Os.

Let’s say you currently have a 4 disk set to store your tables and a separate 4 disk set to store your indexes, to reduce contention of course and something your boss would recommend. And yes, indexes no longer cause contention for table accesses as result because indexes and table no longer “share the same space”.

However, all we’ve achieved is the separating of segments which typically have few blocks accessed (the indexes) from those segments that have a much higher number of individual blocks accessed (the tables).

The table disk set will typically be much “hotter” and potentially have contention issues while all the users battle to get physical access to all the table related blocks while the index disk set is relatively cold as most users don’t need to physically access anywhere as many index blocks.

Take a look at a statspack report and you’ll notice a far greater proportion (say 75%) of overall physical I/Os are table related while a much smaller proportion (say 25%) of your physical I/Os are index related.

The whole idea of managing disk contention is to even out the load among all your disk resources. Attempting to do so by separating indexes from tables does a pretty average job of it.

If one had only evenly distributed and striped both the tables and indexes among the 8 disks, you would have a much more even distribution of physical accesses across all the 8 disks thereby potentially reducing overall disk contention.

Putting indexes in their own tablespace may actually end up hurting, not improving overall database performance. I’ll expand on this in my next post …


9. Richard Foote - April 17, 2008

Hi Brian

Yes, that really is the case 🙂


10. Richard Foote - April 17, 2008

Hi Nigel

Exactly !!

I’ll expand on a lot of what you’ve said in my next post when people often see a perceived benefit after separating indexes. However the so-called benefits have actually nothing to do with the indexes themselves being separated.


11. Tom - April 17, 2008


I took a look and all the datafiles for this tablespace are on one mount point. I’ll check witht he SAN admin to see how the carve it up. People citing rules of thumb frustrate me. They offer me no proof except to say, well I have always done it and it has always worked. It’s just the status quo. In one of the tables, it had longs in it, and this person wanted the table and it’s indexes in separate tablespaces. Talk about all the work of exporting the table and having to reimport it. I just did it anyway to shut the coworker up but I disagreed. : )


Looking forward to your next post. I agree with everything you said.


12. Nigel Thomas - April 18, 2008

Shutting up a coworker can be a great use of your time – particularly if as a result you’ve been able to gather the incontrovertable evidence that he’s WRONG, and he has WASTED YOUR TIME and he is SO going to have to BUY THE NEXT BEER 🙂


13. Richard Foote - April 19, 2008

Just to add to comments #2 by Brian and #3 by Robert, it would become extremely complex if a single process had to somehow manage and coordinate simultaneous or parallel lines of logical processes.

I guess that’s why we have parallel processing and that’s why we need multiple “slave” processes to run off and do the “simulataneous” work for the master process.

However, even with parallel processing, a single process still performs steps A, B and C in a serial manner for the portion of the partitioned table it manages.


14. Richard Foote - April 19, 2008

Hi Nigel

A free cappuccino, won from a fellow co-worker in a friendly wager, taste’s oh so sweet 😉


15. блога на явор » Blog Archive » Индекси и таблици: Не питай старо, ами… провери - May 12, 2008

[…] Separate Indexes From Tables, Some Thoughts Part I (Everything In Its Right Place) […]


16. Joe Bloggs - June 10, 2008

What does all this hold for table partioning ?
Is that redundant now too??


17. Richard Foote - June 17, 2008

Hi Joe

No partitioning most definitely is not redundant. As an obvious example, if I have 10 years worth of data but I only need to read all the data from one specific year, the options without partitioning are either to read 1/10 of data via an index (possibly too expensive an option) or read the entire table (meaning reading 9/10s of the data unnecessarily).

Partition pruning means reading just the 1/10 of data as effeciently as possible.

This discussion and partitioning are entirely different discussions …


18. Mushtaq - July 22, 2010

Dear Sir,

please help me out.

” i want the scriot for removing eror “MAx Extent Reached” so that the script should automatically add datafile to the tablespace or increase Extent.

With regards,
Mushtaq Ahmad


Richard Foote - July 22, 2010

Hi Mushtaq

If your error is due to reaching the max extents of the segment, then increasing the tablespace size is not going to do any good or solve the problem. You run out of extents for the segment, not space in the tablespace.

The fix is really to start using locally managed tablespaces …


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 )

Connecting to %s

%d bloggers like this: