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?
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
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.
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.
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:
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.
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.