大家好，能否有人告诉我为什么我们要为索引和数据创建不同的表空间呢？
3个回答
阿里云服务器只需要99元/年，新老用户同享，点击查看详情
24
许多人普遍认为将索引和表格保存在不同的表空间中可以提高性能。但现在许多权威专家已经认为这是一个谬论（请参见此Ask Tom线程--搜索"myth"）。尽管如此，它仍然是常见的做法，因为老习惯难改！
第三方编辑
从asktom中提取："Index Tablespace" 问题是 Oracle 8.1.6版本的，发布于2001年
- 现在是否仍然将索引保留在自己的表空间中是个好主意？
- 这是否会增强性能，还是更注重恢复问题？
- 答案是否因平台而异？
回复的第一部分
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.
- Tony Andrews
10
3
- ceth
1
3我不同意存储区大小必须统一这一说法——对于几乎所有应用来说，自动分段大小完全足够。 - David Aldridge
2
- 性能。应该根据实际情况进行分析。我认为将所有内容放在一个表空间中也成为了另一个谬论！应该有足够的磁盘驱动器、足够的逻辑单元，并注意操作系统中的排队。如果有人认为只创建一个表空间就足够了，而不考虑其他因素，那么这又是另一个谬论。这取决于具体情况！
- 高可用性。使用单独的表空间可以提高系统的高可用性，以防出现某些文件损坏、文件系统损坏或块损坏的情况。如果问题仅发生在索引表空间中，则有机会在线恢复，使我们的应用程序仍然对客户可用。详见：http://richardfoote.wordpress.com/2008/05/02/indexes-in-their-own-tablespace-recoverability-advantages-get-back/
- 对于管理和成本控制来说，使用单独的表空间来存储索引、数据、BLOBs、CLOBs，甚至一些特定的表格可能非常重要。我们可以使用存储系统来存储BLOBs、CLOBs，甚至将档案存储到不同的存储层中，以提供不同的服务质量。
- yamyth
网页内容由stack overflow 提供, 点击上面的可以查看英文原文，
foobar_index, foobar_data, foobar_LOB，并在其上添加了这些表空间：SYSAUX、SYSTEM、TEMP、UNDOTBS1。使用这么多的表空间是否存在缺点？ - surfmuggle