PostgreSQL has the CLUSTER command to group rows physically on disk. By grouping on information where "neighboring" rows (for lack of a better term) are often accessed together, performance improves since fewer disk blocks need to be read in a given query. Does Oracle have anything similar? Would it even help performance on a large table that is almost never updated if there is such an option?

  • In Oracle they're called Index Organized Tables and they're very rarely used.
    – Philᵀᴹ
    Commented Sep 20, 2013 at 0:07

3 Answers 3


Similar concept in Oracle is called Index-Organized Tables (IOT).

Difference with PostgreSQL CLUSTER is that in PostgreSQL CLUSTER command reorganizes table once and later table still grows as it wants to. In Oracle IOT keeps its structure as ordered by index.

Unlike Phil I'm seeing IOTs now and then. The most often they are used when you need to retrieve many (think hundreds) rows by index.

  • But importantly only by the primary key, not by other indexed column. If you have a meaningful primary key then you might be selecting by range, but for a synthetic primary key the benefits would really be incidental (eg. because you want all of the rows entered on a particular day, and they naturally cluster by PK if it was generated by a sequence). Commented Sep 25, 2013 at 9:16
  • Of course, you can turn the primary key of the table into a unique key, and the unique key you want to "organize" on into the primary key. Any child tables will have to point their foreign keys to the unique key columns explicitly. But it's still not possible to organize on a non-unique index. Commented Sep 25, 2013 at 9:27

Oracle has index organized tables, b-tree clusters and hash clusters. The cluster implementation in Oracle support one or more tables stored in a same cluster. See AskTom for more details. Which is best for you depends on how you define "neighboring rows" and how do you access the data.

I have seen hash cluster to make significant improvements to performance, but it can be rarely used because the amount of keys must be specified when the cluster is created.

  • Thank you. On this question, I regret that I can only choose one answer.
    – jpmc26
    Commented Sep 21, 2013 at 22:41
  • There are also indexed clusters which are a little more practical since the number of keys is unlimited (though you need a rough estimate of the number of entries per key). Commented Sep 23, 2013 at 14:22
  • Yes, Oracle has indexed cluster. I used the term "b-tree cluster", which is not the official term.
    – Jussi
    Commented Sep 26, 2013 at 6:22

The most similar Oracle functionality would be to recreate the table with the rows being ordered.

This could be performed online using DBMS_REDEFINITON, but you can more simply:

Create table tmp_source_table
select * from source_table;

Truncate table source_table;

Insert /*+ append */ into source_table
select *
from   tmp_source_table
order by column1, column2 ... etc.

It would improve the efficiency of index range-based access methods where the table is accessed via an index on the ordered columns, unless or until the table gets disorganised by more rows being inserted. Updates would not affect this so much as Oracle does not normally move rows just because they are updated, as PostgreSQL does.

Index-organised tables, clusters, and partitioning all provide more permanent solutions for enforcing physical clustering of rows.

  1. IOTs organise by the primary key values only, which is of limited use as you generally don't access a range of rows via a meaningless primary key.
  2. Clusters organise by range, or simply co-locate identical values (hash clusters), and can do so for multiple tables. Hash clusters provide faster lookup than indexes -- typically one logical read -- and optimise single row lookups as well as ranges.
  3. Partitioning physically colocates rows based on a range of partition key column values, on sets of values for the key columns, of on the hash value of key columns, or by reference to a column in another table.

Another associated technique is the use of Materialized Views, which can offer different physical clustering of rows to the original table via the use of the above techniques, with query rewrite being employed to choose the table with the most useful clustering.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.