Data Table Partitioning Solution

Data Table Partitioning Solution
Problem summary:
Data Warehouses have different data volumes based on different business conditions. For a data warehouse, the corresponding table needs to be queried during processing. Therefore, the optimization of the query is the optimization of the entire processing process. We generally create tables with large data volumes by year or by month to optimize the query speed. However, sometimes some dimension tables have a lot of data (dimension tables in the data warehouse cannot use partitions), or tables created on a monthly basis, but the data volume per month is still very large, this seriously affects the processing performance of the data warehouse.

Solution proposal:
Partitions are processed for tables with a large amount of data and split by date or ID based on different situations.

Processing Method:
When creating a database, create multiple file groups to store data separated by different partition functions. Each file group creates multiple data files separately, reducing I/O. It is recommended that each data file should not be greater than 3 GB. You can add data files dynamically later.

1Tables created on a yearly basis are divided into 4 partitions on a quarterly basis, and each table is stored in a file group on a quarterly basis.

Functions and solutions for partitioning by TimeCodeAs follows:

Create Partition Function   [ Pf_datetime_2008 ] (Numeric ( 18 , 0 )) As Range Right   For   Values ( 200804010000 , 200807010000 , 200810010000 )

Note: The split nodes of dates are January 1, April 1, January 1, July 1, and January 1, October 1.

Create Partition Scheme [ Ps_datetime_2008 ]   As Partition [ Pc_datetime_2008 ]   To ( [ Primary ] , [ FG2 ] , [ FG3 ] , [ Fg4 ] )

Note: The three Split points in the function are stored in four different file groups.

The statement for creating a data table is as follows:

Create TableTb_pf

(

IDInt,

DatetimeNumeric (18,0)

)

OnPs_datetime_2008 (Datetime)

2For data tables that are created on a monthly basis or that do not have a date ID, we can use their self-increasing IDs to split partitions.

The code for partitioning functions and solutions by ID is as follows:

 

Create Partition Function   [ Pf_id ] ( Bigint ) As Range Right   For   Values ( 20000000 , 40000000 , 60000000 )

Note: The IDs are divided into 60000000 nodes, nodes, and nodes, respectively.

Create Partition Scheme [ Ps_id ]   As Partition [ Pf_id ]   To ( [ Primary ] , [ FG2 ] , [ FG3 ] , [ Fg4 ] )

The statement for creating a data table is as follows:

Create TableTb_pf

(

IDInt,

DatetimeNumeric (18,0)

)

OnPs_id (ID)

Implementation result:
The insert and update operations on a branch data table with more than 25 million data increase the performance of a non-partitioned data table by about 10.%Left and right