Two common data partitioning methods (take Teradata as an example)

A basic principle for massive data performance optimization is "Partitioning"Also called "sharding). Partitioning is actually the principle of drawer in daily work and life: we put our items into multiple small drawers according to some logic, which is generally better than mixing them in a large drawer; however, a small drawer is too large, or the logic is confusing, and the results may be counterproductive.

The partition Syntax of Teradata is relatively simple. Commonly Used partition by time. As long as you add it to the end of the create table statement, you can create a partition for the whole year of 2013.In order to save trouble, it can be divided into 5 to 10 years at a time):

 
 

  
  
PARTITION BY RANGE_N(  
  
  
    Rcd_Dt BETWEEN DATE '2013-01-01' AND DATE '2013-12-31'
  
  
    EACH INTERVAL '1' DAY, NO RANGE
  
  
); 
 
 

Another commonBut not easy to grasp)Is partitioned by string value. In the preceding time partition, we can see the RANGE_N keyword. The CASE_N keyword is used for value-based partitioning, as shown in the following example:

 
 

  
  
PARTITION BY CASE_N(   
  
  
 (CASE WHEN (my_field='A') THEN (1) ELSE (0) END)=1,    
  
  
 (CASE WHEN (my_field='B') THEN (2) ELSE (0) END)=2,    
  
  
 (CASE WHEN (my_field='C') THEN (3) ELSE (0) END)=3,    
  
  
NO CASE OR UNKNOWN);
 
 

Further, the syntax elements are as follows:

 
 

  
  
my_field='A'
 
 

It can be changed to a format similar to this:

 
 

  
  
SUBSTR(my_field,1,1) IN ('E','F','G') 
 
 

In reality, because access data has changed from full table scan to partition scan, some steps can achieve a performance improvement of 10-times. For complex and time-consuming large jobs, the running time can always be shortened by more than half. It is very interesting that even experienced developers do not have a good grasp of Data partitions. The concept of data partitioning is to go beyond a specific database. In my nearly ten years of career, most performance problems can be solved through data partitioning.

This article is from the iData blog, please be sure to keep this source http://idata.blog.51cto.com/4581576/1188058