PARTITIONID pseudocolumns

The PARTITIONID pseudocolumn provides the ability to obtain the partition ID where each row of data resides. The value of the PARTITIONID pseudocolumn is directly filled in by the tablescan operator when reading raw data and is not actually stored. This document mainly describes how to use the PARTITIONID pseudocolumn.

Depending on partition levels and functionality, the following six pseudocolumns are introduced:

Basic syntax

SELECT operation

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

Example:

SELECT c1, "__ob_partition_id" FROM t1;

UPDATE operation

You cannot modify the partitionid pseudocolumns, but they can be used in the WHERE clause.

Example:

UPDATE t1 SET c1 = c1 WHERE __ob_partition_index = 2;

INSERT operation

The pseudocolumns cannot appear in the column list of an INSERT statement.

Restrictions

Column name conflicts with pseudocolumns

• Query statements: If a table already had one of these pseudocolumn names before upgrading to the version that supports partitionid, queries for that column will still return the actual column data; otherwise, the pseudocolumn value is returned.
• Table creation statements**: You cannot create a table with column names that conflict with the pseudocolumn names listed above; otherwise, an OB_ERR_COLUMN_DUPLICATE error is returned.
  • 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.
• Drop column: If a table already has a column name that matches a pseudocolumn, you can drop it normally; otherwise, an OB_ERR_BAD_FIELD_ERROR is returned.
• Add column: You cannot directly add a column that conflicts with the partitionid pseudocolumn names; otherwise, an OB_ERR_COLUMN_DUPLICATE error is returned.
• Rename column: You can only rename existing columns that conflict with the pseudocolumn names to something else; otherwise, an OB_ERR_COLUMN_DUPLICATE error is returned.

Table type restrictions

Only supported for user tables and user views (that include pseudocolumn names in their definition).

Feature switch

The PARTITIONID pseudocolumn 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, meaning users cannot create tables with these column names.

To address this, you can disable the PARTITIONID pseudocolumn feature using the configuration item _enable_pseudo_partition_id. If your business needs to use these column names for tables, you can choose to turn it off.

Example:

ALTER SYSTEM SET _enable_pseudo_partition_id = false;