Oracle 中的索引是聚簇的还是非聚簇的？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6290422/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is an index clustered or unclustered in Oracle?
提问by gino
How can I determine if an Oracle index is clustered or unclustered?
如何确定 Oracle 索引是聚簇的还是非聚簇的？
I've done
我弄完了
select FIELD from TABLE where rownum <100
where FIELDis the field on which is built the index. I have ordered tuples, but the result is wrong because the index is unclustered.
在哪里FIELD建立索引的字段。我已经对元组进行了排序，但结果是错误的，因为索引是非聚集的。
回答by Vincent Malgrat
By default all indexes in Oracle are unclustered. The only clustered indexes in Oracle are the Index-Organized tables (IOT) primary key indexes.
默认情况下，Oracle 中的所有索引都是非聚集的。Oracle 中唯一的聚集索引是索引组织表 (IOT) 主键索引。
You can determine if a table is an IOT by looking at the IOT_TYPEcolumn in the ALL_TABLESview (its primary key could be determined by querying the ALL_CONSTRAINTSand ALL_CONS_COLUMNSviews).
您可以通过查看视图中的IOT_TYPE列来确定表是否为 IOT ALL_TABLES（其主键可以通过查询ALL_CONSTRAINTS和ALL_CONS_COLUMNS视图确定）。
Here are some reasons why your query might return ordered rows:
以下是您的查询可能返回有序行的一些原因：
- Your table is index-organized and
FIELDis the leading part of its primary key. - Your table is heap-organized but the rows are by chance ordered by
FIELD, this happens sometimes on an incrementing identity column.
- 您的表是按索引组织的，并且
FIELD是其主键的前导部分。 - 您的表是堆组织的，但行是偶然排序的
FIELD，这有时发生在递增的标识列上。
Case 2 will return sorted rows only by chance. The order of the inserts is not guaranteed, furthermore Oracle is free to reuse old blocks if some happen to have available space in the future, disrupting the fragile ordering.
情况 2 将仅偶然返回已排序的行。插入的顺序是无法保证的，此外，如果将来某些块碰巧有可用空间，Oracle 可以自由地重用旧块，从而破坏脆弱的排序。
Case 1 will most of the time return ordered rows, however you shouldn't rely on it since the order of the rows returned depends upon the algorithm of the access path which may change in the future (or if you change DB parameter, especially parallelism).
情况 1 大部分时间会返回有序的行，但是您不应该依赖它，因为返回的行的顺序取决于访问路径的算法，该算法将来可能会发生变化（或者如果您更改 DB 参数，尤其是并行度） ）。
In both case if you want ordered rows you should supply an ORDER BY clause:
在这两种情况下，如果您想要有序行，您应该提供 ORDER BY 子句：
SELECT field
FROM (SELECT field
FROM TABLE
ORDER BY field)
WHERE rownum <= 100;
回答by Tony Andrews
There is no concept of a "clustered index" in Oracle as in SQL Server and Sybase. There is an Index-Organized Table, which is similar but not the same.
Oracle 中没有 SQL Server 和 Sybase 中的“聚集索引”概念。有一个Index-Organized Table，它相似但不相同。
回答by Mike Woodhouse
"Clustered" indices, as implemented in Sybase, MS SQL Server and possibly others, where rows are physically stored in the order of the indexed column(s) don't exist as such in Oracle. "Cluster" has a different meaning in Oracle, relating, I believe, to the way blocks and tables are organized.
“聚集”索引，如在 Sybase、MS SQL Server 和可能的其他人中实现的，其中行按索引列的顺序物理存储，在 Oracle 中不存在。“集群”在 Oracle 中有不同的含义，我相信，与块和表的组织方式有关。
Oracle doeshave "Index Organized Tables", which are physically equivalent, but they're used much less frequently because the query optimizer works differently.
Oracle确实有“索引组织表”，它们在物理上是等效的，但它们的使用频率要低得多，因为查询优化器的工作方式不同。
The closest I can get to an answer to the identification question is to try something like this:
我能得到的最接近识别问题的答案是尝试这样的事情：
SELECT IOT_TYPE FROM user_tables
WHERE table_name = '<your table name>'
My 10g instance reports IOTor nullaccordingly.
我的 10g 实例报告IOT或null相应。
回答by Gary Myers
Index Organized Tables have to be organized on the primary key. Where the primary key is a sequence generated value this is often useless or even counter-productive (because simultaneous inserts get into conflict for the same block).
索引组织表必须按主键组织。当主键是序列生成的值时，这通常是无用的，甚至适得其反（因为同时插入会导致同一块的冲突）。
Single table clusters can be used to group data with the same column value in the same database block(s). But they are not ordered.
单表集群可用于对同一数据库块中具有相同列值的数据进行分组。但它们不是有序的。
