• Data Warehouse Service

dws
  1. Help Center
  2. Data Warehouse Service
  3. Developer Guide
  4. Query Performance Optimization
  5. Optimization Cases
  6. Case: Selecting an Appropriate Distribution Column

Case: Selecting an Appropriate Distribution Column

Symptom

Tables are defined as follows:

CREATE TABLE t1 (a int, b int);
CREATE TABLE t2 (a int, b int);

The following query is executed:

SELECT * FROM t1, t2 WHERE t1.a = t2.b;

Optimization Analysis

If a is the distribution column of t1 and t2:

CREATE TABLE t1 (a int, b int) DISTRIBUTE BY HASH (a);
CREATE TABLE t2 (a int, b int) DISTRIBUTE BY HASH (a);

Then Streaming exists in the execution plan and the data volume is heavy among DNs, as shown in Figure 1.

Figure 1 Selecting an appropriate distribution column (1)

If a is the distribution column of t1 and b is the distribution column of t2:

CREATE TABLE t1 (a int, b int) DISTRIBUTE BY HASH (a);
CREATE TABLE t2 (a int, b int) DISTRIBUTE BY HASH (b);

Then Streaming does not exist in the execution plan, and the data volume among DNs is decreasing and the query performance is increasing, as shown in Figure 2.

Figure 2 Selecting an appropriate distribution column (2)