Is there any logical reason of having different tablespace for indexes?

Question

Hi Can some let me know why we created different table space for Index and data.

Answer 1

It is a widespread belief that keeping indexes and tables in separate tablespaces improves performance. This is now considered a myth by many respectable experts (see this Ask Tom thread - search for "myth"), but is still a common practice because old habits die hard!

Third party edit

Extract from asktom: "Index Tablespace" from 2001 for Oracle version 8.1.6 the question

• Is it still a good idea to keep indexes in their own tablespace?
• Does this inhance performance or is it more of a recovery issue?
• Does the answer differ from one platform to another?

First part of the Reply

Yes, no, maybe.

The idea, born in the 1980s when systems were tiny and user counts were in the single 
digits, was that you separated indexes from data into separate tablespaces on different 
disks.

In that fashion, you positioned the head of the disk in the index tablespace and the head 
of the disk in the data tablespace and that would be better then seeking 2 times on the 
same disk.

Drives back then were really slow at seeking and typically measured in the 10's to 100's 
of megabytes (if you were lucky)


Today, with logical volumes, raid, NN gigabyte (nn is rapidly becoming NNN gigabytes) 
drives, hundreds/thousands of concurrent users, thousands of tables, 10's of thousands of 
indexes - this sort of "optimization" is sort of impossible.

What you strive for today is to be able to manage things, to spread IO out evenly 
avoiding hot spots.

Since I believe all things should be in locally managed tablespaces with UNIFORM extent 
sizes, I would say that yes, indexes would be in a different tablespace from the data but 
only because they are a different SIZE then the data.  My table with 50 columns and an 
average row size of 4k might belong in a tablespace that has 5meg extents whereas the 
index on a single number column might belong in a tablespace with 512k or 1m extents.

I tend to keep my indexes separate from the data but for the above sizing reason.  The 
tablespaces frequently end up on the same exact mount points.  You strive for even io 
across your disks and you may end up with indexes and data on the same devices. 

Answer 2

It makes a sense in 80s, when there were not to many users and the databases size was not too big. At that time it was usefull to store indexes and tables in the different physical volumes.

Now there are the logical volumes, raid and so on and it is not necessary to store the indexes and tables in different tablespaces.

But all tablespaces must be locally managed with uniform extends size. From this point of view the indexes must be stored in different tablespace as the table with the 50 columns could be stored in the tablespace with 5Mb exteds size, when the tablespace for indexes will be enought 512Kb extended size.