Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
In Oracle they're called Index Organized Tables and they're very rarely used. –  FreshPhilOfSO Sep 20 '13 at 0:07
add comment

3 Answers

up vote 4 down vote accepted

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.

share|improve this answer
    
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). –  David Aldridge Sep 25 '13 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. –  Colin 't Hart Sep 25 '13 at 9:27
add comment

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.

share|improve this answer
    
Thank you. On this question, I regret that I can only choose one answer. –  jpmc26 Sep 21 '13 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). –  Vincent Malgrat Sep 23 '13 at 14:22
    
Yes, Oracle has indexed cluster. I used the term "b-tree cluster", which is not the official term. –  Jussi Sep 26 '13 at 6:22
add comment

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
nologging
as
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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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