哪些 DBMS 提供索引组织表？
我目前的了解：
- Oracle 确实提供索引组织表，并且默认为堆组织表。
- 我听说 SQL-Server 仅使用索引组织表，
我对 MySQL、PostgreSQL、Informix 和 DB2 的答案特别感兴趣。
My current knowledge:
- Oracle does offer index-organized tables and defaults to heap-organized.
- I heard that SQL-Server uses only index-organized tables
I am especially interested in answers for MySQL, PostgreSQL, Informix and DB2.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
MySql 具有聚集索引，但对这些索引的控制似乎有限。
MySql聚集索引并看到这个问题< a href="https://stackoverflow.com/questions/1585619/create-a-mysql-primary-key-without-a-clustered-index">这里
DB2有MDC（多维集群），可以通过多种方式有效地索引组织表。我从未使用过它们，但您可能只需拥有一个与标准聚集索引相同的单维 MDC。
Oracle 有点痛苦。上次我检查它的 IOT 实现时，要求您将表创建为 IOT，并且以后无法更改它，当您想要加载没有索引的数据负载以提高速度然后为其建立索引时，这很烦人。
MySql has clustered indexes but there appears to be limited control on these indexes.
MySql clustered indexes and see this question here
DB2 has MDC (multi-dimension cluster) which can effectively index organise the table in several ways. I've never used them but you can probably just have a single dimension MDC which would be the same as a standard clustered index.
Oracle is a bit of a pain. Last time I checked with its IOT implementation requires you create the table as IOT and you can't change it later, which is annoying when you want to load a load of data with no indexes for speed and then index it after.
至于Informix Dynamic Server (IDS) 或Standard Engine (SE)，它们都支持聚集索引和非聚集索引。 IDS 使用 RSAM，SE 使用 C-ISAM B-Tree 索引。对于这两种情况，当创建聚簇索引时，表中的行的物理顺序与索引的顺序相同，但当新行添加到表中时，该行物理上放置在 EOF（堆）处。
As for Informix Dynamic Server (IDS) or Standard Engine (SE), they both support clustered and non-clustered indexes. IDS uses RSAM and SE uses C-ISAM B-Tree indexing. For both, when a cluster index is created, a table's rows are physically ordered in the same order as the index, but as new rows are added to the table, the row is physically placed at EOF (heap).
SQL Server 可以将表数据存储在
堆结构或聚集索引结构中。如果表没有聚集索引，则它被视为堆。有关更多详细信息，请参阅此处 堆结构 和 聚集索引结构SQL Server can store table data in either
Heap StructuresorClustered Index Structures. If a table doesn't have a clustered index then it is considered a heap. For more details see here Heap Structures and Clustered Index StructuresPostgreSQL 提供聚集索引。
命令
cluster将根据索引对数据库表进行物理排序。后续数据修改将忽略此顺序。可以通过发出 cluster来刷新排序。
请参阅 https://www.postgresql.org/docs/10/static /sql-cluster.html
PostgreSQL offers clustered indexes.
The command
cluster <table> <index>will order the database table physically according to the index. Subsequent data modifications will ignore this ordering. The ordering can be refreshed by issuingcluster <table>.See https://www.postgresql.org/docs/10/static/sql-cluster.html