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 raw data and 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 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

The partitionid pseudo column cannot appear in the column list of an insert statement.

Limitations

Column name conflict with pseudo column fields

• Query statements: If a table already has the pseudo column names before upgrading to a version that supports partitionid, the query statement will still return the real data of the column. If the column does not exist, it will return the corresponding pseudo column value.
• Create table statements: When creating a table, you cannot create a column with a name that conflicts with the pseudo column names. Otherwise, an error OB_ERR_COLUMN_DUPLICATE will be returned.
  • This includes statements like create table tt1 as select c1,__ob_partition_id from t1; in MySQL mode or create table tt1 as select c1,"__ob_partition_id" as "__ob_partition_id" from t1 in Oracle mode.
• Drop column: If the table already has the pseudo column names, you can delete the column. Otherwise, an error OB_ERR_BAD_FIELD_ERROR will be returned.
• Add column: You cannot directly add a conflicting partitionid pseudo column. Otherwise, an error OB_ERR_COLUMN_DUPLICATE will be returned.
• Rename 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.

Table type limitations

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

Feature switch

The PARTITIONID pseudo column will occupy 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 address this issue, you can disable the PARTITIONID pseudo column feature by configuring the _enable_pseudo_partition_id parameter. If your business requires using these column names to create tables, you can choose to disable the feature.

Here is an example:

ALTER SYSTEM SET _enable_pseudo_partition_id = false;