PARTITION BY RANGE - Oracle to MariaDB Migration

Both Oracle and MariaDB support range partitioning, which allows you to partition a table based on a range of values for a specified column, but some clauses differ and require conversion.

Oracle:

   -- A sample range-partitioned table
   CREATE TABLE orders
   (
     name CHAR(1),
     created_dt TIMESTAMP(6)
   )
   PARTITION BY RANGE (created_dt) INTERVAL (NUMTODSINTERVAL(1, 'DAY')) 
    (PARTITION "P_01JAN2025" VALUES LESS THAN (TIMESTAMP '2025-01-01 00:00:00'),
     PARTITION "P_02JAN2025" VALUES LESS THAN (TIMESTAMP '2025-01-02 00:00:00')); 
  /* Table created. */

MariaDB - Oracle Compatibility:

   -- A sample range-partitioned table
   CREATE TABLE orders
   (
     name CHAR(1),
     created_dt DATETIME(6)
   )
   PARTITION BY RANGE COLUMNS (created_dt)  
    (PARTITION `P_01JAN2025` VALUES LESS THAN (TIMESTAMP '2025-01-01 00:00:00'),
     PARTITION `P_02JAN2025` VALUES LESS THAN (TIMESTAMP '2025-01-02 00:00:00')); 
  /* Query OK, 0 rows affected */

Note that we use RANGE COLUMNS instead of RANGE in MariaDB. RANGE can only be used for integer partition values, otherwise, an error is returned:

  ERROR 1697 (HY000): VALUES value for partition '<value>' must have type INT

The INTERVAL clause in Oracle allows automatically creating new partitions when inserted data exceeds all existing partitions.

Oracle:

  -- A new partition dynamically added (named as SYS_Pn)
  INSERT INTO orders VALUES ('A', TIMESTAMP '2025-01-02 01:01:01');
  /* 1 row created. */

You can use the following query to check the partitions in Oracle:

Oracle:

  SELECT partition_name, high_value 
  FROM ALL_TAB_PARTITIONS 
  WHERE TABLE_NAME = 'ORDERS';

Result:

MariaDB requires that a partition to exist before inserting data into it:

MariaDB - Oracle Compatibility:

  -- A new partition dynamically added (named as SYS_Pn)
  INSERT INTO orders VALUES ('A', TIMESTAMP '2025-01-02 01:01:01');
  /* ERROR 1526 (HY000): Table has no partition for value from column_list */
 
  -- Manually add the partition first
  ALTER TABLE orders ADD PARTITION 
    (PARTITION `P_03JAN2025` VALUES LESS THAN (TIMESTAMP '2025-01-03 00:00:00'));
 
  -- Now you can insert rows  
  INSERT INTO orders VALUES ('A', TIMESTAMP '2025-01-02 01:01:01');
  /* Query OK, 1 row affected */

For more information, see Oracle to MariaDB Migration - Oracle Compatibility Mode.