Interleaved ordering uses a technique that is similar to a Z-order.

Interleaved ordering enables the database to prune I/O based on any subset of predicates in the clustering columns. Interleaved ordering is useful for dimensional hierarchies in a data warehouse.

rooms

As with attribute-clustered tables with linear ordering, Oracle Database supports interleaved ordering on single or multiple tables that are connected through a primary-foreign key relationship. Columns in tables other than the attribute-clustered table must be linked by foreign key and joined to the attribute-clustered table.

dell emc certification malaysia

Large data warehouses frequently organize data in a star schema. A dimension table uses a parent-child hierarchy and is connected to a fact table by a foreign key. Clustering a fact table by interleaved order enables the database to use a special function to skip values in dimension columns during table scans.

comptia certification malaysia

Example 2-10 Interleaved Ordering Example

Suppose your data warehouse contains a sales fact table and its two dimension tables: customers and products. Most queries have predicates on the customers table hierarchy (cust_state_province, cust_city) and the products hierarchy (prod_category, prod_subcategory). You can use interleaved ordering for the sales table as shown in the partial statement in the following example:

CopyCREATE TABLE sales
(
   prod_id NUMBER NOT NULL
,  cust_id NUMBER NOT NULL
,  amount_sold NUMBER(10,2) ...
)
CLUSTERING sales
   JOIN products ON (sales.prod_id = products.prod_id)
   JOIN customers ON (sales.cust_id = customers.cust_id)
   BY INTERLEAVED ORDER
   (
     (  products.prod_category
     ,  products.prod_subcategory
     ),
     (  customers.cust_state_province
     ,  customers.cust_city
     )
   )
WITH MATERIALIZED ZONEMAP;

Note

The columns specified in the BY INTERLEAVED ORDER clause need not be in actual dimension tables, but they must be connected through a primary-foreign key relationship.

Suppose an application queries the sales, products, and customers tables in a join. The query specifies the customers.prod_category and customers_cust_state_province columns in the predicate as follows:

CopySELECT cust_city, prod_sub_category, SUM(amount_sold)
FROM   sales, products, customers
WHERE  sales.prod_id = products.prod_id 
AND    sales.cust_id = customers.cust_id
AND    customers.prod_category = 'Boys' 
AND    customers.cust_state_province = 'England - Norfolk' 
GROUP BY cust_city, prod_sub_category;

In the preceding query, the prod_category and cust_state_province columns are part of the clustering definition shown in the CREATE TABLE example. During the scan of the sales table, the database can consult the zone map and access only the rowids in this zone.

citrix certification malaysia 2