12 Attribute Clustering

Attribute Clustering with Linear Ordering

Linear ordering stores the data according to the order of specified columns. This is the default type of clustering. For example, linear ordering on the (prod_id, channel_id) columns of the table SALES sorts the data by prod_id first and then by channel_id. The sorted data is stored on disk with the data for clustered columns being in close proximity.

Linear ordering can be defined on single tables or multiple tables that are connected through a primary key-foreign key relationship.

Use the CLUSTERING ... BY LINEAR ORDER directive to perform attribute clustering based on the order of specified columns.

Attribute clustering based on linear ordering of columns is best used in the following scenarios:

• Queries specify the prefix of columns included in the CLUSTERING clause in a single table

  For example, if queries on sales often specify either a customer ID or a combination of customer ID and product ID, then you could cluster data in the table using the column order cust_id, prod_id.

• Columns used in the CLUSTERING clause have an acceptable level of cardinality

  The potential data reduction that can be obtained in the scenarios described in "Advantages of Attribute-Clustered Tables" increases in direct proportion to the data reduction obtained from a predicate on a column.

Linear clustering combined with zone maps is very effective in I/O reduction.

Attribute Clustering with Interleaved Ordering

Interleaved ordering uses a special multidimensional clustering technique based on Z-order curve fitting. It maps multiple column attribute values (multidimensional data points) to a single one-dimensional value while preserving the multidimensional locality of column values (data points). Interleaved ordering is supported on single tables or multiple tables. Unlike linear ordering, this method does not require the leading columns of the clustering definition to be present to achieve I/O pruning benefits for the scenarios described in "Advantages of Attribute-Clustered Tables".

Columns can be used individually or grouped together into column groups. Each individual column or column group will be used to constitute one of the multidimensional data points in the cluster. Grouped columns are bracketed by '('..')', and must follow the dimensional hierarchy from the coarsest to the finest level of granularity. For example, (product_category, product_subcategory).

Use the CLUSTERING ... BY INTERLEAVED ORDER directive to perform clustering by interleaved ordering.

Interleaved clustering is most beneficial for SQL operations with varying predicates on multiple columns. This is often the case for star queries against a dimensional model, where the query predicates are on dimension tables and the number of predicates vary. Using interleaved join attribute clustering is most common in environments where the fact table is clustered based on columns from the dimension tables. The columns from a dimension table will likely contain a hierarchy, for example, the hierarchy of a product category and sub-category. In this case, clustering of the fact table would occur on dimension columns forming a hierarchy. This is the reason join attribute clustering for star schemas is sometimes referred to as hierarchical clustering. For example, if queries on sales specify columns from different dimensions, then you could cluster data in the sales table according to columns in these dimensions.

Interleaved clustering combined with zone maps is very effective in I/O pruning for star schema queries. In addition, it enables you to provide a very efficient I/O pruning for queries using zone maps, and enhances compression because the same column values are close to each other and can be easily compressed.

Example: Attribute Clustered Table

An example of how a clustered table looks is illustrated in Figure 12-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.

Figure 12-1 Attribute-Clustered Tables

Description of "Figure 12-1 Attribute-Clustered Tables"

Guidelines for Using Attribute Clustering

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.

Examples of Attribute Clustering with Linear Ordering

Example 12-1 and Example 12-2 illustrate linear ordering.

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);

Examples of Attribute Clustering with Interleaved Ordering

Example 12-3 and Example 12-4 illustrate interleaved ordering.

You can also create an attribute clustered table so that queries benefit from pruning with zone maps. "Creating Zone Maps with Attribute Clustering" contains examples of defining zone maps with attribute clustering.

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));

Adding Attribute Clustering to an Existing Table

When you create a table with clustering, it is created with a zone map by default. You can, however, explicitly prevent this by using WITHOUT ZONEMAP. This could be done for several reasons, such as wanting to create a zone map on clustering columns plus additional columns that correlate to clustering columns, or to use specific zone map storage options instead of the defaults.

Use the ALTER TABLE ... ADD CLUSTERING command to add attribute clustering to an existing table that does not currently use attribute clustering.

The following command adds attribute clustering to the SALES fact table. The modified table will use interleaved clustering that is based on the joined dimension tables CUSTOMERS and PRODUCTS.

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;

When you add clustering to a table, the existing data is not clustered. To force the existing data to be clustered, you need to move the content of the table using an ALTER TABLE...MOVE statement. You can do this partition by partition.

The following command clusters data in the sales table:

ALTER TABLE sales MOVE PARTITION sales_1995 UPDATE INDEXES ALLOW CLUSTERING;

For more information about zone maps, see "About Zone Maps".

Modifying Attribute Clustering Definitions

Use the ALTER TABLE ... MODIFY CLUSTERING statement to modify when attribute clustering is triggered for a table. Modifying clustering definitions does not affect the existing table data. The modified definitions are applicable only to future data movement or direct-path insert operations.

The following command modifies the clustering definition of the SALES table and enables clustering during data movement.

ALTER TABLE sales MODIFY CLUSTERING YES ON DATA MOVEMENT;

You can also modify a table definition and create or drop a zone map that is based on the attribute clustering. The following statement modifies the definition of the SALES table and adds a zone map:

ALTER TABLE sales MODIFY CLUSTERING WITH MATERIALIZED ZONEMAP;

Use the following statement to modify the definition of the attribute-clustered table SALES and remove zone maps.

ALTER TABLE sales MODIFY CLUSTERING WITHOUT MATERIALIZED ZONEMAP;

Dropping Attribute Clustering for an Existing Table

If attribute clustering is defined for an existing table, use the ALTER TABLE ... DROP CLUSTERING statement to remove attribute clustering. Dropping a clustering definition does not have any impact on the existing table data.

The following command removes attribute clustering for the SALES table:

ALTER TABLE sales DROP CLUSTERING;

Using Hints to Control Attribute Clustering for DML Operations

You can use hints to enforce the use of clustering or to prevent its use during direct-path insert operations. Use the CLUSTERING hint to enforce clustering for a table and NO_CLUSTERING hint to prevent the use of clustering.

The following command disables attribute clustering while inserting data into the SALES table. This table was created with the YES ON LOAD option.

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

See "Controlling the Use of Zone Maps" for more information about hints.

Overriding Table-level Settings for Attribute Clustering During DDL Operations

You can override the attribute clustering definition during data movement DDL operations such as partition maintenance that creates new data segments (split or merge operations) or moving a table, partition, or subpartition. For example, if a table was defined using the NO ON DATA MOVEMENT option, then you can cluster data for this table during a data movement operation by using the ALTER TABLE ... ALLOW CLUSTERING statement.

The following command allows clustering during data movement for the sales_2010 partition of the SALES tables that was defined using the NO ON DATA MOVEMENT option:

ALTER TABLE sales MOVE PARTITION sales_2010 UPDATE INDEXES ALLOW CLUSTERING;

Similarly, you can disable clustering during data movement for a table that was defined using the YES ON DATA MOVEMENT option by including the DISALLOW CLUSTERING clause in the ALTER TABLE command that is used to move data.

Clustering Table Data During Online Table Redefinition

Online table redefinition enables you to modify the logical or physical structure of a table without significantly affecting its availability. The table is accessible to both queries and DML during much of the redefinition process.

You can redefine a table online and add attribute clustering to a table that did not previously use attribute clustering. The DBMS_REDEFINITION package enables you redefine tables online and add attribute clustering to them.