oracle 索引有不同的表空间是否有任何逻辑原因？

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

提问by P Sharma

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

嗨，可以让我知道为什么我们为索引和数据创建了不同的表空间。

回答by Tony Andrews

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

摘自 asktom: "Index Tablespace"from 2001 for Oracle version 8.1.6 问题

• 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. 

回答by demas

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.

这在80年代是有道理的，当时用户不多，数据库规模也不大。当时将索引和表存储在不同的物理卷中很有用。

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

现在有逻辑卷、raid 等，不需要将索引和表存储在不同的表空间中。

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.

但是所有表空间都必须在本地管理，具有统一的扩展大小。从这个角度来看，索引必须存储在不同的表空间中，因为具有 50 列的表可以存储在 5Mb 扩展大小的表空间中，当索引表空间足够 512Kb 扩展大小时。

回答by yamyth

• Performance. It should be analyzed from case to case. I think that keeping all toghether in one tablespace becomes another myth too! It should be enough spindles, enough luns and take care of queuing in operating system. if someone thinks that making one tablespace is enough and is the same like many tablespaces without taking in consideration all other factors, means again another myth. It depends!
• High Avalilability. using separate tablespaces can improve high availability of the system in case that some file corrution, files system corruption, block corruption. If the problem occures only at index tablespace there is achance to do the recovery online and our application still beeing available to the customer. see also: http://richardfoote.wordpress.com/2008/05/02/indexes-in-their-own-tablespace-recoverability-advantages-get-back/
• using separate tablespaces for indexes, data, blobs, clobs, eventually some individual tables can be important for the manageability and costs. We can use our storage system to store our blobs, clobs, eventually archive to a different layer of storage with different quality of service

• 表现。应该具体情况具体分析。我认为将所有内容放在一个表空间中也成为另一个神话！应该有足够的spindles，足够的luns，并注意操作系统中的排队。如果有人认为创建一个表空间就足够了，并且与许多表空间一样，而不考虑所有其他因素，那么又是一个神话。这取决于！
• 高可用性。在某些文件损坏、文件系统损坏、块损坏的情况下，使用单独的表空间可以提高系统的高可用性。如果问题仅发生在索引表空间，则有机会在线进行恢复，并且我们的应用程序仍然可供客户使用。另见：http: //richardfoote.wordpress.com/2008/05/02/indexes-in-their-own-tablespace-recoverability-advantages-get-back/
• 为索引、数据、blob、clob 使用单独的表空间，最终一些单独的表对于可管理性和成本可能很重要。我们可以使用我们的存储系统来存储我们的 blob、clob，最终以不同的服务质量存档到不同的存储层