Difference between revisions of "Table Partitioning"
From iDempiere en
(Created page with "='''Feature:''' Table Partitioning Support= '''Goal:''' Technical '''Developers''': Hengsin, Elaine Tan '''Feature''' '''Ticket: [https://idempiere.atlassia...") Tag: visualeditor |
m Tag: visualeditor |
||
| Line 22: | Line 22: | ||
==Configuration== | ==Configuration== | ||
| − | Configure the partition information at Table and Column window | + | Configure the partition information at '''Table and Column''' window |
=== Table === | === Table === | ||
*'''Partition:''' Indicates this is a partitioned table | *'''Partition:''' Indicates this is a partitioned table | ||
| − | *'''Partitioning Method:''' Indicates how the Table is partitioned (List or Range) | + | *'''Partitioning Method:''' Indicates how the Table is partitioned (List or Range) |
| + | **List partitioning - The data distribution is defined by a discrete list of values | ||
| + | **Range Partitioning - The data is distributed based on a range of values | ||
*'''Create/update partition:''' Process which create or update table partitions based on the information in the table and column records | *'''Create/update partition:''' Process which create or update table partitions based on the information in the table and column records | ||
[[File:Table Partitioning Configuration Table.png|none|thumb|800x800px|List partitioning]] | [[File:Table Partitioning Configuration Table.png|none|thumb|800x800px|List partitioning]] | ||
| Line 34: | Line 36: | ||
*'''Partition Key:''' Indicates this is a partition key | *'''Partition Key:''' Indicates this is a partition key | ||
*'''Partition Key Sequence:''' Indicates the order of the partition keys where the lowest number comes first | *'''Partition Key Sequence:''' Indicates the order of the partition keys where the lowest number comes first | ||
| − | *'''Range Partition Interval:''' Indicates the interval used in a range partitioning (date or number) | + | *'''Range Partition Interval:''' Indicates the interval used in a range partitioning (date or number) |
| + | **Examples of date intervals: 1 year; 6 months; | ||
| + | **Examples of number intervals: 5000; 100000; | ||
[[File:Table Partitioning Configuration Column.png|none|thumb|855x855px|List partitioning]] | [[File:Table Partitioning Configuration Column.png|none|thumb|855x855px|List partitioning]] | ||
[[File:Table Partitioning Configuration Column Range.png|none|thumb|824x824px|Range Partitioning]] | [[File:Table Partitioning Configuration Column Range.png|none|thumb|824x824px|Range Partitioning]] | ||
==Execution of the process== | ==Execution of the process== | ||
| − | The process can be called from the Table and Column window or Menu | + | The process can be called from the '''Table and Column''' window or '''Menu''' |
=== Table and Column window === | === Table and Column window === | ||
| − | The process can be called from the '''Create/update partition''' button on the Table and Column window. | + | The process can be called from the '''Create/update partition''' button on the '''Table and Column''' window. |
| − | [[File:Table Partitioning Process.png | + | [[File:Table Partitioning Process.png|thumb|550x550px|alt=|none]] |
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
=== Menu === | === Menu === | ||
| − | The process can be called from the '''Create/Update Table Partition''' menu item. The Partitioning Method and Table Name parameters are optional. By default, the process will execute the process for all partitioned table records (i.e. Table > Partition = Yes) | + | The process can be called from the '''Create/Update Table Partition''' menu item. The '''Partitioning Method''' and '''Table Name''' parameters are optional. By default, the process will execute the process for all partitioned table records (i.e. Table > Partition = Yes) |
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
| − | |||
* '''Partitioning Method:''' The partitioning method of the table | * '''Partitioning Method:''' The partitioning method of the table | ||
* '''Table Name:''' This is a table name, or a LIKE clause (e.g. AD_%) | * '''Table Name:''' This is a table name, or a LIKE clause (e.g. AD_%) | ||
| + | |||
| + | [[File:Table Partitioning Process MenuItem.png|thumb|800x800px|alt=|none]] | ||
== View the table partition info == | == View the table partition info == | ||
Revision as of 10:00, 15 December 2023
Feature: Table Partitioning Support
Goal: Technical
Developers: Hengsin, Elaine Tan
Feature Ticket: IDEMPIERE-5943
Description: Table partitioning support for PostgreSQL and Oracle
- Support partition by Range and List
- Support partition of new and existing table
- Support moving of data and constraints from existing table to partitioned table (For PostgreSQL only)
- Support auto addition of new partition through scheduled process
Not cover: Sub partition
Limitations:
- PostgreSQL does not support list/range partitioning with more than one partition key
Configuration
Configure the partition information at Table and Column window
Table
- Partition: Indicates this is a partitioned table
- Partitioning Method: Indicates how the Table is partitioned (List or Range)
- List partitioning - The data distribution is defined by a discrete list of values
- Range Partitioning - The data is distributed based on a range of values
- Create/update partition: Process which create or update table partitions based on the information in the table and column records
Column
- Partition Key: Indicates this is a partition key
- Partition Key Sequence: Indicates the order of the partition keys where the lowest number comes first
- Range Partition Interval: Indicates the interval used in a range partitioning (date or number)
- Examples of date intervals: 1 year; 6 months;
- Examples of number intervals: 5000; 100000;
Execution of the process
The process can be called from the Table and Column window or Menu
Table and Column window
The process can be called from the Create/update partition button on the Table and Column window.
Menu
The process can be called from the Create/Update Table Partition menu item. The Partitioning Method and Table Name parameters are optional. By default, the process will execute the process for all partitioned table records (i.e. Table > Partition = Yes)
- Partitioning Method: The partitioning method of the table
- Table Name: This is a table name, or a LIKE clause (e.g. AD_%)
View the table partition info
Read-only. Capture the database partition info from the database to the Table Partition table (i.e. AD_TablePartition). Each Table Partition record represents an actual database partition
- Name: The name of the partition
- Expression: SQL clause of the partition
Auto addition of new partition
A schedule process called Create/Update Table Partitions is created to run the Create/update partition process daily.
