PARTITIONID pseudo column

The PARTITIONID pseudocolumn provides the ability to obtain the partition ID of each row of data. The value of the PARTITIONID pseudocolumn is directly filled when the tablescan operator reads the original data and will not be stored in actual storage. This topic covers the usage of the PARTITIONID pseudocolumn.

The following six pseudo columns are primarily introduced based on the partition level and function:

Basic syntax

SELECT operations

Example:

select c1,__ob_partition_id from t1;

Update operation

Modifications are not allowed to the partitionid pseudocolumn, but it can appear in WHERE clauses.

Example:

UPDATE t1 SET c1=c1 WHERE __ob_partition_index=2;

INSERT operation

The identifier cannot appear in the column list of the INSERT statement.

Limitations and considerations

Limitations when column names and pseudo columns conflict with each other

• Query statements: If a table already has this pseudo-column before the database is upgraded to a version that supports the PARTITIONID option, the query statement will return the actual data in the column. If the table does not have this pseudo-column, the query statement will return the corresponding pseudo-column value.
• DDL Statement: When a table is created, it is disallowed to have a column whose name conflicts with the name of a pseudocolumn described earlier. If a conflict occurs, the error OB_ERR_COLUMN_DUPLICATE is returned.
  • MySQL compatible mode, for example: sql CREATE TABLE tt1 AS SELECT c1, __ob_partition_id FROM t1;
• Drop column: When a table has already a column with the name of the pseudo column specified earlier, you can delete the column. Otherwise, an error OB_ERR_BAD_FIELD_ERROR is returned.
• Add column: You cannot directly add a partitionid pseudocolumn that conflicts with an existing partitionid pseudocolumn. Otherwise, an error is returned.
• Rename column: You can only modify the column name of an existing column that conflicts with the name of a pseudo column. Otherwise, an error is returned: OB_ERR_COLUMN_DUPLICATE.

Limitations for table types

You can query these pseudocolumns only in user-defined table and view definitions that include pseudocolumn names.

Feature Switch

The six __ob_partition_id columns, __ob_partition_index, __ob_partition_name, __ob_sub_partition_id, __ob_sub_partition_index, and __ob_sub_partition_name are occupied by the PARTITIONID pseudocolumn. This prevents you from creating tables with these six column names.

The pseudo column PARTITIONID functionality can be disabled by configuring the _enable_pseudo_partition_id parameter. When your business requires using the column names mentioned above to create tables, you can choose to disable this functionality.

Here is an example:

ALTER SYSTEM SET _enable_pseudo_partition_id = false;