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 directly filled in by the tablescan operator when reading raw data, and it is not stored. 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. 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 specified in the column list of an INSERT statement.

Limitations

Limitations on column names conflicting with pseudo-column fields

• In a SELECT statement: If a table already contains the pseudo-column names before upgrading to a version that supports partitionid, the query will return the actual data of the column. If the table does not contain the pseudo-column names, the query will return the values of the pseudo-columns.
• In a CREATE TABLE statement: You cannot create a table with a column name that conflicts with the pseudo-column names. Otherwise, an error OB_ERR_COLUMN_DUPLICATE is returned.
  • This applies to statements like create table tt1 as select c1,__ob_partition_id from t1; in MySQL-compatible mode.
• When dropping a column: If the table already contains the pseudo-column names, you can drop the column. Otherwise, an error OB_ERR_BAD_FIELD_ERROR is 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 is returned.
• When renaming a column: You can only rename a column that conflicts with the pseudo-column names to a different name. Otherwise, an error OB_ERR_COLUMN_DUPLICATE is 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 parameter _enable_pseudo_partition_id to false. This allows you to use the column names to create tables as needed.

Here is an example:

ALTER SYSTEM SET _enable_pseudo_partition_id = false;