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 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:

Basic syntax

SELECT operation

In Oracle-compatible mode, because of the __ob_ prefix, you must use __ob_partition_id for queries.

Here is an example:

SELECT c1,"__ob_partition_id" from t1;

UPDATE operation

You cannot modify the partitionid pseudo-column, but it can appear in the where clause.

Here is an example:

UPDATE t1 SET c1=c1 WHERE __ob_partition_index=2;

INSERT operation

It cannot appear in the column list of an insert statement.

Limitations

Column name conflict with pseudo-column fields

• In a query statement: If a table already has one of the above pseudo-column names before upgrading to a version that supports partitionid, the query will still return the real data of that column. If the column does not exist, it will return the corresponding pseudo-column value.
• In a create table statement: You cannot create a table with a column name that conflicts with the above pseudo-column names. Otherwise, it will return the OB_ERR_COLUMN_DUPLICATE error.
  • This includes statements like create table tt1 as select c1,__ob_partition_id from t1; in MySQL-compatible mode or create table tt1 as select c1,"__ob_partition_id" as "__ob_partition_id" from t1 in Oracle-compatible mode.
• When dropping a column: If the table already has one of the above pseudo-column names, you can delete it. Otherwise, it will return the OB_ERR_BAD_FIELD_ERROR error.
• When adding a column: You cannot directly add a conflicting partitionid pseudo-column. Otherwise, it will return the OB_ERR_COLUMN_DUPLICATE error.
• When renaming a column: You can only rename existing columns that conflict with the above pseudo-column names to other names. Otherwise, it will return the OB_ERR_COLUMN_DUPLICATE error.

Table type limitations

Only user tables and user views defined with the pseudo-column names can query these pseudo-columns.

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 configuring the _enable_pseudo_partition_id parameter. 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;