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?
Postgresql – Oracle Equivalent of PostgreSQL CLUSTER
oraclepostgresql
Related Question
- PostgreSQL – Storing Historical Data in Database vs On Disk
- postgresql – Debugging Slow Queries on Large Tables
- How should I store a large number of dynamically generated heterogeneous tables
- SQL Server – Oracle 12c (RDS) Questions from a SQL Server DBA
- Oracle – Equivalent Idioms in Google BigQuery
- Postgresql – System configuration to address poor PostgreSQL INSERT performance
- Postgresql – Large bulk insert performance difference PostgreSQL vs MariaDB (InnoDB)
- Postgresql – Is it a good or bad idea to use “ON UPDATE CASCADE ON DELETE CASCADE” for foreign keys? Why does this mechanism exist at all
Best Answer
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.