A linear ordering scheme for a table divides rows into ranges based on user-specified attributes in a specific order. Oracle Database supports linear ordering on single or multiple tables that are connected through a primary-foreign key relationship.
devops certification training courses malaysia
For example, the sales table divides the cust_id and prod_id columns into ranges, and then clusters these ranges together on disk. When you specify the BY LINEAR ORDER directive for a table, significant I/O reduction can occur when a predicate specifies either the prefix column or all columns in the directive.
Assume that queries of sales often specify either a customer ID or a combination of a customer ID and product ID. You can create an attribute-clustered table so that such queries benefit from I/O reduction:
CopyCREATE TABLE sales
(
prod_id NOT NULL NUMBER
, cust_id NOT NULL NUMBER
, amount_sold NUMBER(10,2) ...
)
CLUSTERING
BY LINEAR ORDER (cust_id, prod_id)
YES ON LOAD YES ON DATA MOVEMENT
WITH MATERIALIZED ZONEMAP;
Queries that qualify both columns cust_id and prod_id, or the prefix cust_id experience I/O reduction. Queries that qualify prod_id only do not experience significant I/O reduction because prod_id is the suffix of the BY LINEAR ORDER clause. The following examples show how the database can reduce I/O during table scans.
ccna certification training courses malaysia
Example 2-8 Specifying Only cust_id
An application issues the following query:
CopySELECT * FROM sales WHERE cust_id = 100;
Because the sales table is a BY LINEAR ORDER cluster, the database must only read the zones that include the cust_id value of 100.
Example 2-9 Specifying prod_id and cust_id
An application issues the following query:
CopySELECT * FROM sales WHERE cust_id = 100 AND prod_id = 2300;
Because the sales table is a BY LINEAR ORDER cluster, the database must only read the zones that include the cust_id value of 100 and prod_id value of 2300.
Leave a Reply