Oracle中的索引是集群还是非集群？ [英] Is an index clustered or unclustered in Oracle?

问题描述

如何判断Oracle索引是集群还是非集群？

How can I determine if an Oracle index is clustered or unclustered?

我已经完成

select FIELD from TABLE where rownum <100

其中 FIELD 是构建索引的字段。我已经订购了元组，但结果是错误的，因为索引是非聚集的。

where FIELD is the field on which is built the index. I have ordered tuples, but the result is wrong because the index is unclustered.

推荐答案

默认情况下，Oracle中的所有索引都是非聚集的。 Oracle中唯一的聚簇索引是索引组织表（IOT）主键索引。

By default all indexes in Oracle are unclustered. The only clustered indexes in Oracle are the Index-Organized tables (IOT) primary key indexes.

您可以通过查看<$ c来确定表是否是IOT $ c> IOT_TYPE ALL_TABLES 视图中的列（其主键可以通过查询 ALL_CONSTRAINTS 和 ALL_CONS_COLUMNS views）。

You can determine if a table is an IOT by looking at the IOT_TYPE column in the ALL_TABLES view (its primary key could be determined by querying the ALL_CONSTRAINTS and ALL_CONS_COLUMNS views).

以下是您的查询可能返回有序行的一些原因：

Here are some reasons why your query might return ordered rows:

1. 您的表是索引组织的， FIELD 是其主键的主要部分。


2. 您的表是堆组织的，但行是按 FIELD 排序的，有时会在递增的标识列上发生。

1. Your table is index-organized and FIELD is the leading part of its primary key.
2. Your table is heap-organized but the rows are by chance ordered by FIELD, this happens sometimes on an incrementing identity column.

案例2只会偶然返回已排序的行。无法保证插入的顺序，此外，Oracle可以自由地重用旧块，如果将来碰巧有可用空间，则会破坏脆弱的排序。

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.

案例1大部分时间都会返回有序行，但是您不应该依赖它，因为返回的行的顺序取决于访问路径的算法，该算法将来可能会更改（或者如果更改数据库参数，尤其是并行性）。

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

如果你想要有序行，你应该提供一个ORDER BY子句：

In both case if you want ordered rows you should supply an ORDER BY clause:

SELECT field 
  FROM (SELECT field 
          FROM TABLE 
         ORDER BY field) 
 WHERE rownum <= 100;

这篇关于Oracle中的索引是集群还是非集群？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！