PARTITIONID pseudo column

The PARTITIONID pseudo column provides the ability to obtain 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 the original 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:

Column	Type	Description
__ob_partition_id	int	partition_id: the ID of the partition corresponding to the data of the row.
__ob_partition_index	int	partition_index: the index of the partition corresponding to the data of the row.
__ob_partition_name	varchar	partition_name: the name of the partition corresponding to the data of the row.
__ob_sub_partition_id	int	sub_partition_id: the ID of the sub_partition corresponding to the data of the row.
__ob_sub_partition_index	int	sub_partition_index: the index of the sub_partition corresponding to the data of the row.
__ob_sub_partition_name	varchar	sub_partition_name: the name of the sub_partition corresponding to the data of the row.

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

You cannot use the partitionid pseudo column 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 has the pseudo column names mentioned above before upgrading to a version that supports partitionid, the query will still return the actual data of the column. If the column does not exist, the query will return the corresponding pseudo column value.
• In a create table statement: You cannot create a table with column names that conflict with the pseudo column names mentioned above. Otherwise, the system returns an error message OB_ERR_COLUMN_DUPLICATE.
  • This includes statements such as create table tt1 as select c1,__ob_partition_id from t1;.
• In a drop column statement: If a table already has the pseudo column names mentioned above, you can drop the column. Otherwise, the system returns an error message OB_ERR_BAD_FIELD_ERROR.
• In an add column statement: You cannot directly add a partitionid pseudo column that conflicts with existing columns. Otherwise, the system returns an error message OB_ERR_COLUMN_DUPLICATE.
• In a rename column statement: You can only rename existing columns that conflict with the pseudo column names mentioned above to other names. Otherwise, the system returns an error message OB_ERR_COLUMN_DUPLICATE.

Limitations on table types

You can query only user tables and user views that include the pseudo column names mentioned above.

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 users 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 false. This allows you to use the column names mentioned above to create tables.

Here is an example:

tip

After you execute the following command to disable the partitionid pseudo column feature, you need to execute ALTER SYSTEM flush plan cache; to manually clear the plan cache.

ALTER SYSTEM SET _enable_pseudo_partition_id = false;