The primary benefit of attribute-clustered tables is I/O reduction, which can significantly reduce the I/O cost and CPU cost of table scans. I/O reduction occurs either with zones or by reducing physical I/O through closer physical proximity on disk for the clustered values.
An attribute-clustered table has the following advantages:
- You can cluster fact tables based on dimension columns in star schemas.In star schemas, most queries qualify dimension tables and not fact tables, so clustering by fact table columns is not effective. Oracle Database supports clustering on columns in dimension tables.
- I/O reduction can occur in several different scenarios:
- When used with Oracle Exadata Storage Indexes, Oracle In-Memory min/max pruning, or zone maps
- In OLTP applications for queries that qualify a prefix and use attribute clustering with linear order
- On a subset of the clustering columns for
BY INTERLEAVED ORDERclustering
- Attribute clustering can improve data compression, and in this way indirectly improve table scan costs.When the same values are close to each other on disk, the database can more easily compress them.
- Oracle Database does not incur the storage and maintenance cost of an index.
Leave a Reply