Examples of partitioning strategies

This topic describes how to set partition creation strategies and partition deletion strategies.

Set a partitioning strategy for an OceanBase MySQL compatible mode table

The following example describes how to set a partitioning strategy for the order table in the test_424 database of the mysql424 data source. The example also describes how to set a drop partitioning strategy for the order table.

1. In the SQL window, execute the following 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))
   

2. On the Ticket tab, choose Partitioning Plan > Create Partitioning Plan.

   

3. In the Create Partitioning Plan panel, click to edit the partitioning strategy.

   

4. In the Partitioning Strategy panel, select the Creation Strategy node under Partitioning Strategy to create a partitioning strategy.

   

5. Specify the number of partitions to be created.

6. Create rules.

   As shown in the preceding figure, you can set the partitioning strategy for the time and parti_key partitioning keys of the order table.

   • time: The time partitioning key is of the DATE type, which is a time type. Therefore, you can set the Sequential Increment strategy as the partitioning strategy for this partitioning key. In this example, the creation strategy of the time partitioning key is to generate several partitions based on the current time (that is, the time when the partitioning plan task actually runs) as the starting point, with a monthly interval.

   • parti_key: The parti_key partitioning key is of the INT type. You can set the Custom strategy as the partitioning strategy. Assume that you want to generate partitions based on the current time as the starting point, with a monthly interval. To set a custom partitioning strategy, follow the steps described in the Custom Partitioning Strategy section. The expression can be generated based on the following steps:

     1. Obtain the base time for generating partitions. In this example, the current time serves as the base time. Therefore, you can directly use the now() function.

     2. Define the interval for generating partitions. In this example, partitions are to be generated with a monthly interval. To set the Create Rule, click Rule Details > Interval. Enter 1 as the fixed interval and add the interval expression to the basis expression in Step 1 to generate the next partition. The expression is now () + interval ${INTERVAL} month, where ${INTERVAL} refers to the value entered in Creation Rule > Rule Details > Interval.

     3. Generate a partitioning expression corresponding to the type of the partitioning key. The parti_key partitioning key is of the int type, but the expression generated in Step 1 is of the time type. Therefore, you need to convert it to an int type. You can first convert it to a numeric string and then convert it to an int type. The expression is: cast(date_format(now() + interval ${INTERVAL} month, '%Y%m01') as signed).

        Here are some points to note:

        • CAST(xxx AS SIGNED): converts a field to an integer.

        • DATE_FORMAT(): converts a field to a date format.

        • NOW(): returns the current date.

          Note

          In ODC, you can also use the ${LAST_PARTITION_VALUE} variable to introduce the upper limit of the partition in the current database for subsequent partition generation. For example, ${LAST_PARTITION_VALUE} + interval ${INTERVAL} month generates partitions from the upper limit of the latest partition in the database.

        • INTERVAL ${INTERVAL} MONTH: a monthly interval expression. ${INTERVAL} is a fixed usage in ODC.

        • '%Y%m01': the date format.

7. Select a naming method.

   As shown in the preceding figure, the selected naming method is Prefix+Suffix, which consists of a fixed prefix and a suffix based on time.

   1. Fixed prefix: You can specify the prefix as you like.

   2. Suffix: You can select the upper bound of the time-type partitioning key as the suffix for naming the current partition. You can also specify the formatted output format and the incremental interval of time.

   3. After the settings are completed, you can click Test Generation to view the partition names.

8. In the Drop Partitions section, set the time interval to pre-create the corresponding number of partitions and specify whether to rebuild indexes after the partitions are dropped.

9. In the OK button in the lower-right corner of the Partitioning Strategy panel, to view the SQL statements generated based on the partitioning strategy.

   

10. Click OK to complete the setting of the partitioning strategy.

Set a partitioning strategy for an OceanBase Oracle compatible mode table

The following example describes how to set a partitioning strategy for the order table in the OBORACLE database under the oboracle_430 data source.

1. In the SQL window, execute the following statement to create the order table.

   

   CREATE TABLE "OBORACLE"."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));
   

2. On the Ticket tab, choose Partitioning Plan > Create Partitioning Plan.

   

3. In the Create Partitioning Plan panel, click to edit the partitioning strategy.

   

4. In the Partitioning Strategy panel, select the Partitioning Strategy > Creation Strategy checkbox to create a partitioning strategy.
   

   

5. Specify the number of partitions to be created.

6. Create rules.

   As shown in the preceding figure, you can set a partitioning strategy for the TIME and KEY partitioning keys of the order table.

   • TIME: This partitioning key is of the DATE type and therefore a time type. You can use Sequential Increment as the partitioning strategy for this key. In this example, the creation strategy for the TIME partitioning key is to generate several partitions starting from the current time (that is, the time when the partitioning plan task actually runs) with an interval of 1 month.

   • KEY: This partitioning key is of the NUMBER type. You can use Custom as the partitioning strategy. Assume that you want to generate partitions with an interval of 1 month starting from the current system time as the baseline. To create a custom partitioning strategy, follow the steps outlined in Create a custom partitioning strategy. The expression can be generated in the following steps:

     1. Obtain the baseline time for partition generation. In this example, the current system time is used as the baseline. You can directly use the sysdate function.

     2. Define the interval for partition generation. In this example, partitions are to be generated with an interval of 1 month. To create partitions with an interval of 1 month, set Creation Rule > Rule Details > Interval to NUMTOYMINTERVAL(1, 'MONTH') for fixed intervals, and add this to the expression in step 1 to generate the next partition. The expression will be in a datetime format, such as SYSDATE + ${INTERVAL}, where ${INTERVAL} indicates the value set in Creation Rule > Rule Details > Interval.

     3. Generate an expression for the partitioning key type. The KEY partitioning key is of the NUMBER type, but the expression generated in step 1 is in a datetime format. You need to convert it to the NUMBER type. You can first convert it to a numeric string and then convert it to the NUMBER type. The expression will be TO_CHAR(SYSDATE + ${INTERVAL}, 'YYYYMM"01"').

        Here,

        • TO_CHAR: converts a field to a character string.

        • SYSDATE: returns the current system time.

        • YYYYMM"01": the datetime format.

7. Select a naming method.

   As shown in the preceding figure, the selected naming method is Prefix+Suffix, which consists of a fixed string prefix and a time-based suffix.

   1. Fixed prefix: You can specify this prefix as you like.

   2. Time-based suffix: You can select the upper bound of a time-type partition key as the suffix for the current partition. You can also specify the datetime format and the increment interval of the time.

   3. After you configure the preceding parameters, click Test Generation to view the partition names.

8. In the Drop Partitions section, set the time interval to pre-create the specified number of partitions and specify whether to rebuild indexes after the partitions are dropped.

9. In the Partitioning Strategy panel, click Preview SQL to view the SQL statement generated based on the specified partitioning strategy.

   

10. Click OK.

References

• Manage partitioning plans

• Set partitioning strategies