MAX_PT

Purpose

This function returns the maximum value of the partition key for partitions that contain data in a partitioned table.

Limitations and considerations

• The caller must have the SELECT object privilege on the target table (object privilege check is performed in Oracle mode). If the privilege is not granted, the call will fail.
• The table name in the parameters must be a constant (a string literal that can be resolved during parsing). Variables or non-constant expressions cannot be used as table names.
• The queried table must be a partitioned table with a Range or List partitioning type.
• The queried table must be an internal table. External tables and DBLinks are not supported.
• Only single-column partition keys are supported. Multi-column partition keys are not supported.
• Expression-based partitioning (where the partition key is not a direct column reference but an expression defined in the partitioning clause) is not supported. In such cases, MAX_PT cannot be used.
• For a subpartitioned table, only the values of the top-level partitions are returned.
• If all partitions are empty, NULL is returned.
• If different partition values exist within the same partition, the result of MAX_PT is undefined, and a value from any row in the partition is returned.

Syntax

MAX_PT('[schema_name.]table_name')

Parameters

• schema_name: Optional. The name of the schema where the table is located. If not specified, the default schema of the current session is used. You can also use the 'schema_name.table_name' format in the parameter string (with at most one . separator).
• table_name: The name of the table.

Return value

Returns the maximum value of the partition key for partitions that contain data, based on the max() comparison logic. The data type is the same as the partition key.

Examples

1. Create a partitioned table test_tbl1.

   obclient> CREATE TABLE test_tbl1 (col1 INT, col2 INT, col3 INT)
       PARTITION BY LIST (col1) (
           PARTITION P0 VALUES (1),
           PARTITION P1 VALUES (2),
           PARTITION P2 VALUES (3),
           PARTITION P3 VALUES (4)
       );
   

2. Insert test data

   obclient> INSERT INTO test_tbl1 VALUES
       (1,1,1),
       (2,2,2),
       (3,3,3),
       (4,4,4),
       (1,1,2),
       (2,2,2);
   

   The return result is as follows:

   Query OK, 6 rows affected
   Records: 6  Duplicates: 0  Warnings: 0
   

3. Query the maximum partition key value for partitions that contain data in the test_tbl1 table.

   obclient> SELECT MAX_PT('TEST_TBL1') FROM DUAL;
   

   The return result is as follows:

   +---------------------+
   | MAX_PT('TEST_TBL1') |
   +---------------------+
   |                   4 |
   +---------------------+
   1 row in set