ApsaraDB for OceanBase:Examples

This topic provides examples of setting a partition creation strategy and a partition deletion strategy.

Set table partitioning strategies for an OceanBase MySQL data source

Example: Set a partition creation strategy and a partition deletion strategy for the order table in the test_424 database of the mysql424 data source.

1. In the SQL window, write an SQL statement to create a table named order.

   

   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 Tickets tab of the SQL window, choose Partitioning Plan > Create New.

   

3. In the Create Partitioning Plan panel, clickto set partitioning strategies.

   

4. In the Configure partitioning strategy panel, select Creation Strategy and Deletion Strategy under Partitioning Strategy.

   

5. Set the number of partitions to be created.

6. Configure partition creation rules.

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

   • time: The data type of this partitioning key is date, which is a time type. Therefore, you can select Sequentially Increase as the creation method. In the example, according to the partition creation strategy for the time partitioning key, the current time (the time when the partitioning plan task is run) is used as the reference time and partitions are created at a time interval of 1 month.

   • parti_key: The data type of this partitioning key is int, which is not a time type. Therefore, you can select only Custom as the creation method. Assume that you need to use the current time as the reference time and create partitions at a time interval of 1 month. According to the description in the Custom partition creation strategy section, you can perform the following steps to generate the partitioning expression:

     1. Obtain the reference time for partition creation: The current time is used as the reference time. Therefore, you can directly use the now() function.

     2. Define a time interval for partition creation: In the example, the time interval is 1 month. Therefore, you must enter 1 in the Interval field in the Rule column in the Creation Rule section, and add an interval expression on the basis of the expression generated in Step 1 to generate the next partition. To be specific, the expression is now () + interval ${INTERVAL} month, where ${INTERVAL} is the value of the Interval field in the Rule column in the Creation Rule section.

     3. Generate the partitioning expression of the same data type as the partitioning key: The data type of the parti_key partitioning key is int but the expression generated after Step 1 and Step 2 are completed is of a time type. Therefore, you must convert the expression into the int type. You can convert the expression into a numeric string first and then into the int type, namely, cast(date_format(now() + interval ${INTERVAL} month, '%Y%m01') as signed).

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

        • DATE_FORMAT(): converts a field value into the date format.

        • NOW(): returns the current date.

        • INTERVAL ${INTERVAL} MONTH: an expression that specifies an interval by month.${INTERVAL}is a specific syntax for the time interval function in ODC.

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

7. Select a partition naming format.

   As shown in the preceding figure, select Prefix + Suffix for Naming Convention, which comprises a fixed string prefix and a time-based variable suffix.

   1. Fixed prefix: You can define a prefix as needed.

   2. Variable suffix: You can select the upper bound of the partition corresponding to the partitioning key of a time type as the suffix of the name of the current partition. You can also select the formatted output format of time and the increment interval of time.

   3. After the configuration, click Test Generation to view the partition name.

8. In the Delete Partitions section, set the number of partitions to retain and choose whether to rebuild indexes after deletion.

9. Click Preview SQL in the lower-right corner of the Configure partitioning strategy panel to view the SQL statement generated by the configured partitioning strategies.

   

10. Click OK.

Set table partitioning strategies for an OceanBase Oracle data source

Example: Set a partition creation strategy and a partition deletion strategy for the order table in the OBORACLE database of the oboracle_430 data source.

1. In the SQL window, write an SQL statement to create a table namedorder.

   

   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 Tickets tab of the SQL window, choose Partitioning Plan > Create New.

   

3. In the Create Partitioning Plan panel, clickto set partitioning strategies.

   

4. In the Configure partitioning strategy panel, select Creation Strategy and Deletion Strategy under Partitioning Strategy.

   

5. Set the number of partitions to be created.

6. Configure partition creation rules.

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

   • TIME: The data type of this partitioning key is DATE, which is a time type. Therefore, you can select Sequentially Increase as the creation method. In the example, according to the partition creation strategy for the TIME partitioning key, the current time (the time when the partitioning plan task is run) is used as the reference time and partitions are created at a time interval of 1 month.

   • KEY: The data type of this partitioning key is NUMBER, which is not a time type. Therefore, you can select only Custom as the creation method. Assume that you need to use the current system time as the reference time and create partitions at a time interval of 1 month. According to the description in the Custom partition creation strategy section, you can perform the following steps to generate the partitioning expression:

     1. Obtain the reference time for partition creation: The current time is used as the reference time. Therefore, you can directly use the SYSDATE function.

     2. Define a time interval for partition creation: In the example, the time interval is 1 month. Therefore, you must enter 1 in the Interval field in the Rule column in the Creation Rule section, and add an interval expression on the basis of the expression generated in Step 1 to generate the next partition. To be specific, the expression is SYSDATE + ${INTERVAL}, where ${INTERVAL} is the value of the Interval field in the Rule column in the Creation Rule section.

     3. Generate the partitioning expression of the same data type as the partitioning key: The data type of the KEY partitioning key is NUMBER but the expression generated after Step 1 and Step 2 are completed is of a time type. Therefore, you must convert the expression into the NUMBER type. You can convert the expression into a numeric string first and then into the NUMBER type, namely, TO_CHAR(SYSDATE + ${INTERVAL}, 'YYYYMM"01"').

        • TO_CHAR: converts a field value into the character type.

        • SYSDATE: returns the current system time.

        • YYYYMM"01": the date format.

7. Select a partition naming format.

   As shown in the preceding figure, select Prefix + Suffix for Naming Convention, which comprises a fixed string prefix and a time-based variable suffix.

   1. Fixed prefix: You can define a prefix as needed.

   2. Variable suffix: You can select the upper bound of the partition corresponding to the partitioning key of a time type as the suffix of the name of the current partition. You can also select the formatted output format of time and the increment interval of time.

   3. After the configuration, click Test Generation to view the partition name.

8. In the Delete Partitions section, set the number of partitions to retain and choose whether to rebuild indexes after deletion.

9. Click Preview SQL in the lower-right corner of the Configure partitioning strategy panel to view the SQL statement generated by the configured partitioning strategies.

   

10. Click OK.

References

• Partitioning plan tasks

• Set partitioning strategies