PARTITIONID pseudo column

The PARTITIONID pseudo column provides the ability to retrieve the partition ID of each row of data. The value of the PARTITIONID pseudo column is filled in directly by the tablescan operator when reading raw data, and it does not involve actual storage. This topic describes how to use the PARTITIONID pseudo column.

Depending on the partition level and functionality, the following six pseudo columns are introduced:

Basic syntax

SELECT operation

Here is an example:

select c1,__ob_partition_id from t1;

UPDATE operation

You cannot modify the partitionid pseudo column. However, you can use it in the WHERE clause.

Here is an example:

UPDATE t1 SET c1=c1 WHERE __ob_partition_index=2;

INSERT operation

The partitionid pseudo column cannot be included in the column list of an INSERT statement.

Limitations

Limitations on column names conflicting with pseudo column fields

• In a query statement: If a table already contains one of the pseudo column names before upgrading to a version that supports partitionid, the query will still return the actual data of that column. If the column does not exist, the pseudo column value will be returned.
• In a CREATE TABLE statement: You cannot create a table that contains a column name conflicting with any of the pseudo column names. Otherwise, an error OB_ERR_COLUMN_DUPLICATE will be returned.
  • This also applies to statements like create table tt1 as select c1,__ob_partition_id from t1; in MySQL mode.
• When dropping a column: If the table already contains one of the pseudo column names, you can drop the column. Otherwise, an error OB_ERR_BAD_FIELD_ERROR will be returned.
• When adding a column: You cannot directly add a partitionid pseudo column that conflicts with existing columns. Otherwise, an error OB_ERR_COLUMN_DUPLICATE will be returned.
• When renaming a column: You can only rename existing columns that conflict with the pseudo column names to other names. Otherwise, an error OB_ERR_COLUMN_DUPLICATE will be returned.

Limitations on table types

You can only query these pseudo columns from user tables and user views that include the pseudo column names in their definitions.

Feature switch

The PARTITIONID pseudo column occupies six column names: __ob_partition_id, __ob_partition_index, __ob_partition_name, __ob_sub_partition_id, __ob_sub_partition_index, and __ob_sub_partition_name. This prevents you from creating tables that include these column names.

To resolve this issue, you can disable the PARTITIONID pseudo column feature by setting the configuration parameter _enable_pseudo_partition_id to off. This allows you to use the column names to create tables when needed.

Here is an example:

ALTER SYSTEM SET _enable_pseudo_partition_id = false;