You can cluster data using the following methods:

• Clustering based on one or more columns of the table on which attribute clustering is defined.

• Clustering based on one or more columns that are joined with the table on which attribute clustering is defined. Clustering based on joined columns is called join attribute clustering. The tables should be connected through a primary key-foreign key relationship but foreign keys do not have to be enforced.

  Because star queries typically qualify dimension hierarchies, it can be beneficial if fact tables are clustered based on columns (attributes) of one or more dimension tables. With join attribute clustering, you can join one or more dimension tables with a fact table and then cluster the fact table data by dimension hierarchy columns. To cluster a fact table on columns from one or more dimension tables, the join to the dimension tables must be on a primary or unique key of the dimension tables. Join attribute clustering in the context of star queries is also known as hierarchical clustering because the table data is clustered by dimension hierarchies, each made up of an ordered list of hierarchical columns (for example, the nation, state, and city columns forming a location hierarchy).

  Note: In contrast with Oracle Table Clusters, join attribute clustered tables do not store data from a group of tables in the same database blocks. For example, consider an attribute clustered table sales joined with a dimension table products. The sales table will only contain rows from the sales table, but the ordering of the rows will be based on the values of columns joined from products table. The appropriate join will be executed during data movement, direct path insert and CTAS operations.

Attribute clustering is a user-defined table directive that provides data clustering on one or more columns in a table. The directives can be specified when the table is created or modified.

Oracle Database provides the following types of attribute clustering:

• Attribute Clustering with Linear Ordering

• Attribute Clustering with Interleaved Ordering

Regardless of the type of attribute clustering used, you can either cluster data based on a single table or by joining multiple tables (join attribute clustering).

An example of how a clustered table looks is illustrated in Figure 14-1. Assume you have a table sales with columns (category, country). The table on the left is clustered using linear ordering, and the table on the right is clustered using interleaved ordering. Observe that, in the interleaved-ordered table, there are contiguous regions on disk that contain data with a given category and country.

The following are some considerations when defining an attribute clustered table:

• Use attribute clustering in combination with zone maps to facilitate zone pruning and its associated I/O reduction.

• Consider large tables that are frequently queried with predicates on medium to low cardinality columns.

• Consider fact tables that are frequently queried by dimensional hierarchies.

• For a partitioned table, consider including columns that correlate with partition keys (to facilitate zone map partition pruning).

• For linear ordering, list columns in prefix-to-suffix order.

• Group together columns that form a dimensional hierarchy. This constitutes a column group. Within each column group, list columns in order of coarsest to finest granularity.

• If there are more than four dimension tables, include the dimensions that are most commonly specified with filters. Limit the number of dimensions to two or three for better clustering effect.

• Consider using attribute clustering instead of indexes on low to medium cardinality columns.

• If the primary key of a dimension table is composed of dimension hierarchy values (for example, the primary key is made up of year, quarter, month, day values), make the corresponding foreign key as clustering column instead of dimension hierarchy.

• Eliminates storage costs associated with using indexes

• Enables the accessing of clustered regions rather than performing random I/O or full table scans when used in conjunction with zone maps

• Provides I/O reduction when used in conjunction with any of the following:

  • Oracle Exadata Storage Indexes

  • Oracle In-memory min/max pruning

  • Zone maps

  Attribute clustering provides data clustering based on the attributes that are used as filter predicates. Because both Exadata Storage Indexes and Oracle In-memory min/max pruning track the minimum and maximum values of columns stored in each physical region, clustering reduces the I/O required to access data.

  I/O pruning using zone maps can significantly reduce I/O costs and CPU cost of table scans and index scans.

• Enables clustering of fact tables based on dimension columns in star schemas

  Techniques such as traditional table clusters do not provide for ordering by columns of other tables. 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.

• Improves data compression ratios and in this way indirectly improves table scan costs

  Compression can be improved because, with clustering, there is a high probability that clustered columns with the same values are close to each other on disk, hence the database can more easily compress them.

• Minimizes table lookup and single block I/O operations for index range scan operations when the attribute clustering is on the index selection criteria.

• Enables I/O reduction in OLTP applications for queries that qualify a prefix in and use attribute clustering with linear order

• Enables I/O reduction on a subset of the clustering columns for attribute clustering with interleaved ordering

  If table data is ordered on multiple columns, as in an index-organized table, then a query must specify a prefix of the columns to gain I/O savings. In contrast, a BY INTERLEAVED table permits queries to benefit from I/O pruning when they specify columns from multiple tables in a non-prefix order.

Attribute clustering information is part of the table metadata. You can define attribute clustering for a table either when table is first created or subsequently, by altering the table definition.

Use the CLUSTERING clause of the CREATE TABLE statement to define attribute clustering for a table. The type of attribute clustering is specified by including BY LINEAR ORDER or BY INTERLEAVED ORDER.

If attribute clustering was not defined when the table was created, you can modify the table definition and add clustering. Use the ALTER TABLE ... ADD CLUSTERING statement to define attribute clustering for an existing table.

Performing clustering may be expensive because it involves reorganization of the table and clustering data during DML operations. Oracle Database does not enforce the clustering of data on conventional DML, conventional insert, update, and merge.

Clustering can be performed in two ways. The first is to automatically perform clustering for certain DML operations on the table. This is done by defining, as part of the table metadata, the operations for which clustering is triggered. The second is to explicitly specify that clustering must be performed as described in "Using Hints to Control Attribute Clustering for DML Operations" and "Overriding Table-level Settings for Attribute Clustering During DDL Operations". In this case, you can perform clustering for a table even if its metadata definition does not include clustering.

As part of the table definition, you can specify that attribute clustering must be performed when the following operations are triggered:

• Direct-path insert operations

  Set the ON LOAD option to YES to specify that attribute clustering must be performed during direct-path insert operations.

• Data movement operations

  Set the ON DATA MOVEMENT option to YES to specify clustering must be performed during data movement operations. This includes online table redefinition and the following partition operations: MOVE, MERGE, SPLIT, and COALESCE.

The ON LOAD and ON DATA MOVEMENT options can be included in a CREATE TABLE or ALTER TABLE statement. If neither YES ON LOAD nor YES ON DATA MOVEMENT is specified, then clustering is not enforced automatically.

It will serve only as metadata defining natural clustering of the table that may be used later for zone map creation. In this case, it is up to the user to enforce clustering during loads.

To define attribute clustering for a table, you must have the CREATE or ALTER privilege on the table. Additionally, for join attribute clustering, you must also have the SELECT or READ privilege on the joined table or tables.

CREATE TABLE sales (
  prod_id        NUMBER(6) NOT NULL,
  cust_id        NUMBER NOT NULL,
  time_id        DATE NOT NULL,
  channel_id     CHAR(1) NOT NULL,
  promo_id       NUMBER(6) NOT NULL,
  quantity_sold  NUMBER(3) NOT NULL,
  amount_sold    NUMBER(10,2) NOT NULL
)
CLUSTERING
  BY LINEAR ORDER (cust_id, prod_id);

CREATE TABLE my_sales (
  prod_id        NUMBER(6) NOT NULL,
  cust_id        NUMBER NOT NULL,
  time_id        DATE NOT NULL,
  channel_id     CHAR(1) NOT NULL,
  promo_id       NUMBER(6) NOT NULL,
  quantity_sold  NUMBER(3) NOT NULL,
  amount_sold    NUMBER(10,2) NOT NULL
)
CLUSTERING
  my_sales JOIN products ON (my_sales.prod_id = products.prod_id)
  BY LINEAR ORDER (cust_id, prod_category, prod_subcategory);

CREATE TABLE sales (
  prod_id        NUMBER(6) NOT NULL,
  cust_id        NUMBER NOT NULL,
  time_id        DATE NOT NULL,
  channel_id     CHAR(1) NOT NULL,
  promo_id       NUMBER(6) NOT NULL,
  quantity_sold  NUMBER(3) NOT NULL,
  amount_sold    NUMBER(10,2) NOT NULL
)
CLUSTERING
  BY INTERLEAVED ORDER (time_id, prod_id);

CREATE TABLE sales (
  prod_id        NUMBER(6) NOT NULL,
  cust_id        NUMBER NOT NULL,
  time_id        DATE NOT NULL,
  channel_id     CHAR(1) NOT NULL,
  promo_id       NUMBER(6) NOT NULL,
  quantity_sold  NUMBER(3) NOT NULL,
  amount_sold    NUMBER(10,2) NOT NULL
)
CLUSTERING
  sales JOIN products ON (sales.prod_id = products.prod_id)
  BY INTERLEAVED ORDER ((time_id), (prod_category, prod_subcategory));

ALTER TABLE sales 
ADD CLUSTERING sales JOIN customers ON (sales.cust_id = customers.cust_id)
                     JOIN products ON (sales.prod_id = products.prod_id)
    BY INTERLEAVED ORDER ((prod_category, prod_subcategory),
                          (country_id, cust_state_province, cust_city))
    YES ON LOAD YES ON DATA MOVEMENT
    WITHOUT MATERLALIZED ZONEMAP;

ALTER TABLE sales MOVE PARTITION sales_1995 UPDATE INDEXES ALLOW CLUSTERING;

ALTER TABLE sales MODIFY CLUSTERING YES ON DATA MOVEMENT;

ALTER TABLE sales MODIFY CLUSTERING WITH MATERIALIZED ZONEMAP;

ALTER TABLE sales MODIFY CLUSTERING WITHOUT MATERIALIZED ZONEMAP;

ALTER TABLE sales DROP CLUSTERING;

INSERT /*+ APPEND NO_CLUSTERING */ INTO sales SELECT * FROM external_sales;

ALTER TABLE sales MOVE PARTITION sales_2010 UPDATE INDEXES ALLOW CLUSTERING;