Partition policy examples-ApsaraDB for OceanBase (Deprecated)(OceanBase)-阿里云帮助中心

Set a partition policy for an OceanBase MySQL table

Example: Set a partition creation policy and a partition deletion policy for the order table in the test_data database.

In the SQL window, edit the SQL statement to create the `order` table.

CREATE TABLE `order` (
`time` date NOT NULL,
 `parti_key` int(11),
 `name` varchar(120) DEFAULT NULL
) partition by range columns(time, parti_key)
(partition p2023_01 values less than ('2023-01-01', 20230101),
partition p2023_02 values less than ('2023-02-01', 20230201))

On the Tickets tab of the SQL Developer window, click Partition Plan > New Partition Plan.
In the New Partition Plan pane, click to edit the partition policy.
In the Set Partition Policy pane, select Creation Policy and Deletion Policy under Partition Policy.
1. Create a partition policy
  1. Set the number of partitions to create.
  2. Create rules.
    As shown in the figure above, you can set a partition policy for the time and parti_key partition keys in the order table.
    - time: This partition key is of the DATE type. Therefore, you can use Sequential Increment as the partition creation policy. For example, the creation policy for the time partition key uses the current time (the time when the scheduled partitioning job runs) as the base time to generate multiple partitions at 1 month intervals.
    - parti_key: The type of this partition key is INT. Use Custom as the partition creation policy. Suppose that you need to generate several partitions at 1 month intervals based on the current time. As described in the Custom partition creation policy section, you can generate the expression by following the steps below:
      1. Obtain the base time for partition generation: Because the base time is the current time, you can directly use the now() function.
      2. Define the interval for partition generation: Because the example uses a 1 month interval, enter 1 as the fixed interval in Create Rule > Details > Interval, and add an interval expression to the expression from the previous step to generate the next partition. The resulting expression is now() + interval ${INTERVAL} month, where ${INTERVAL} refers to the value entered in Create Rule > Details > Interval.
      3. Generate a partition filter expression that matches the data type of the partition key. The data type of parti_key is int, but the expression generated in the previous step is of the date type. You must convert the expression to the int type. You can first convert the value to a numeric string and then to the int type, as shown in the following expression: cast(date_format(now() + interval ${INTERVAL} month, '%Y%m01') as signed).
        The expression includes the following parts:
        CAST(xxx AS SIGNED): Converts a field to an integer.
        DATE_FORMAT(): Transforms a field into a date format.
        NOW(): Returns the current date.
        Note
        In ODC, you can also use the ${LAST_PARTITION_VALUE} variable to reference the upper bound of the latest partition in the current database. For example, ${LAST_PARTITION_VALUE} + interval ${INTERVAL} month means that subsequent partitions are generated starting from this value.
        INTERVAL ${INTERVAL} MONTH: A monthly interval expression, where ${INTERVAL} is a variable that uses the standard syntax for the time interval function in ODC.
        '%Y%m01': Date format.
  3. Select a naming method.
    As shown in the figure, the selected naming method is Prefix+Suffix. This method consists of a fixed string prefix and a variable time-based suffix.
    1. Fixed prefix: You can specify a custom prefix.
    2. Variable suffix: You can use the upper bound of the time-based partition key as the naming suffix for the current partition. You can also select the output format for the time and the time increment interval.
    3. After you finish the configuration, click the Test Generation button to view the generated partition names.
  4. Select whether to copy the statistics from the last partition of the table to the new partitions.
2. Delete a partition policy
  - Number of partitions to retain: Retains the N most recent partitions and deletes all others.
  - Set partition data retention period: A partition is deleted only if its upper bound is earlier than the specified retention time.
  - Reindex after deletion: Controls whether to automatically reindex after deleting partitions to ensure index integrity and query performance.
3. Click Preview SQL in the lower-right corner of the Partition Policy pane to view the SQL statement generated from the policy settings.
4. Click OK to finish setting the partition policy.
Click Submit to create the new partition plan.

Set a partition policy for an OceanBase Oracle table

Example: Set a partition creation policy and a partition deletion policy for the order table in the SYS database.

In the SQL window, edit the SQL statement to create the `order` table.

CREATE TABLE "SYS"."order" (
  "TIME" DATE NOT NULL ENABLE,
  "KEY" NUMBER(*,0) NOT NULL ENABLE,
  "NAME" VARCHAR2(120) DEFAULT NULL
) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
 partition by range("TIME", "KEY")
(partition "p2024_01" values less than (TO_DATE(' 2024-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'),20240101),
partition "p2024_02" values less than (TO_DATE(' 2024-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'),20240201));

On the Tickets tab of the SQL Developer window, click Partition Plan > New Partition Plan.
In the New Partition Plan pane, click to edit the partition policy.
In the Set Partition Policy pane, select Creation Policy and Deletion Policy under Partition Policy.
1. Create a partition policy
  1. Set the number of partitions to create.
  2. Create rules.
    As shown in the figure above, for the order table, you can set a partition policy for its TIME and KEY partition keys.
    - TIME: This partition key is of the DATE type. Therefore, you can use Sequential Increment as the partition creation policy. In the example, the creation policy for the TIME partition key uses the current time (that is, the actual runtime of the scheduled partitioning job) as the base time to generate several partitions at 1 month intervals.
    - For the partition key KEY of the NUMBER type, this example uses the Custom policy to create new partitions. Assume that you need to generate several partitions at 1 month intervals based on the current system time. As described in the Custom Partition Creation Policy section, you can generate the expression by following the steps below:
      1. You can use the sysdate function to obtain the base time for partition generation because the base time is the current system time.
      2. Define the interval for partition generation: Because this example uses a 1 month interval, enter NUMTOYMINTERVAL(1, 'MONTH') in Create Rule > Details > Interval as the fixed interval, and add an interval expression to the expression from the previous step to generate the next partition. The expression is SYSDATE + ${INTERVAL}, where ${INTERVAL} refers to the value entered in Create Rule > Details > Interval.
      3. Generate a partition filter expression that matches the data type of the partition key. The partition key KEY is of the NUMBER type, but the expression generated in the previous step is of the date type. You must convert the expression to the NUMBER type. You can first convert the expression to a string in a numeric format and then convert the string to the NUMBER type. For example: TO_CHAR(SYSDATE + ${INTERVAL}, 'YYYYMM"01"').
        The expression includes the following parts:
        TO_CHAR: Converts a field to a character string.
        SYSDATE: Returns the current system time.
        YYYYMM"01": The date format.
  3. Select a naming method.
    As shown in the figure, the selected naming method is Prefix+Suffix. This method consists of a fixed string prefix and a variable time-based suffix.
    1. Fixed prefix: You can specify a custom prefix.
    2. Variable suffix: You can use the upper bound of the time-based partition key as the naming suffix for the current partition. You can also select the output format for the time and the time increment interval.
    3. After you finish the configuration, click the Test Generation button to view the generated partition names.
  4. Do not copy statistics from the last partition of the table to the new partitions.
2. Deletion policy
  - Number of partitions to retain: Specifies the number of recent partitions to retain. All older partitions are deleted.
  - Set partition data retention period: A partition is deleted only if its upper bound is earlier than the specified retention time.
  - Reindex after deletion: Controls whether to automatically reindex after deleting partitions to ensure index integrity and query performance.
3. Click Preview SQL in the lower-right corner of the Partition Policy pane to view the SQL statement generated from the policy settings.
4. Click OK to finish setting the partition policy.
Click Submit to create the new partition plan.

Set a partition policy for an OceanBase MySQL table

Set a partition policy for an OceanBase Oracle table

References