Hidden column mechanisms

2024-10-14 06:56:23  Updated

During schema migration or incremental synchronization of the DDL statement CREATE TABLE, OceanBase Migration Service (OMS) provides corresponding mechanisms based on the actual situation of the data migration project.

Background

If you do not advance to the forward switchover step in a data migration project, you must manually drop the hidden columns and unique indexes based on the type of the data migration project as guided by this topic.

When you advance to the forward switchover step in a data migration project, OMS will automatically drop the hidden columns and unique indexes based on the type of the data migration project.

  • When you migrate data from an Oracle database to an Oracle tenant of OceanBase Database, OMS will add hidden columns to all tables at the destination, and create unique indexes based on the hidden columns. These columns and indexes will be automatically dropped during the forward switchover step. For more information about how to create a project, see Migrate data from an Oracle database to an Oracle tenant of OceanBase Database.

    OMS automatically adds hidden columns to the CREATE TABLE statement. Here is an example:

    CREATE TABLE "table1" (
 "S1" NVARCHAR2(2) NOT NULL,
 "S2" NVARCHAR2(20) DEFAULT 'Default value field',
 "OMS_OBJECT_NUMBER" NUMBER INVISIBLE,
 "OMS_RELATIVE_FNO" NUMBER INVISIBLE,
 "OMS_BLOCK_NUMBER" NUMBER INVISIBLE,
 "OMS_ROW_NUMBER" NUMBER INVISIBLE
 );

  • To migrate tables without a unique key for data migration projects of the following types, OMS adds hidden columns to corresponding tables at the destination and create unique indexes based on the hidden columns. These columns and indexes will be automatically dropped in the forward switchover step.

    Source Destination Reference
    MySQL tenant of OceanBase Database MySQL tenant of OceanBase Database Migrate data within OceanBase Database
    Oracle tenant of OceanBase Database Oracle tenant of OceanBase Database Migrate data within OceanBase Database
    Oracle tenant of OceanBase Database Oracle database Migrate data from an Oracle tenant of OceanBase Database to an Oracle database
    Oracle database MySQL tenant of OceanBase Database Migrate data from an Oracle database to a MySQL tenant of OceanBase Database

    • For data migration between MySQL tenants of OceanBase Database, OMS will automatically add hidden columns, hidden column comments, and unique indexes to the CREATE TABLE statement.

      // Create a hidden column and a unique index for a table without a partitioning key column.
create table `table2` (
    `c1` binary(1),
    `OMS_PK_INCRMT` bigint COMMENT 'Reserved for data migration tasks of OMS',
    UNIQUE KEY `UK_table2_OBPK_INCRMT` (`OMS_PK_INCRMT`)
);
// Create a hidden column and a unique index for a table with a partitioning key column.
create table `partlistcolumns_key_nopri` (
    `col1` int(11),
    `col2` decimal(10,2),
    `col3` decimal(10,2),
    `col4` bit(1),
    `col5` tinyint(4),
    `col6` smallint(6),
    `OMS_PK_INCRMT` bigint COMMENT 'Reserved for data migration tasks of OMS',
    UNIQUE KEY `UK_partlistcolumns_key_nopri_OBPK_INCRMT` (`col1`,`col6`, `OMS_PK_INCRMT`) LOCAL
)
default charset=utf8mb4
default collate=utf8mb4_general_ci
PARTITION BY LIST COLUMNS (col1)
SUBPARTITION BY KEY (col6)
subpartition template (
    SUBPARTITION `p0`,
    SUBPARTITION `p1`,
    SUBPARTITION `p2`,
    SUBPARTITION `p3`
)
(
    PARTITION `p1` VALUES IN (1),
    PARTITION `p2` VALUES IN (2),
    PARTITION `p3` VALUES IN (3),
    PARTITION `p4` VALUES IN (4)
);

    • For data migration between Oracle tenants of OceanBase Database, OMS will automatically add hidden columns to the CREATE TABLE statement.

      CREATE TABLE "table4" (
   "V1" CHAR(20 BYTE) DEFAULT 'test',
   "OMS_PK_INCRMT" NUMBER INVISIBLE
);

    • For data migration from an Oracle tenant of OceanBase Database to an Oracle database, OMS will automatically add hidden columns to the CREATE TABLE statement.

      // Versions earlier than Oracle Database 12c
CREATE TABLE "table3" (
    "id" NUMBER,
    "status" VARCHAR2(1 BYTE),
    "OMS_PK_INCRMT" NUMBER
);
// Oracle Database 12c and later
CREATE TABLE "table3" (
    "id" NUMBER,
    "status" VARCHAR2(1 BYTE),
    "OMS_PK_INCRMT" NUMBER INVISIBLE
);

    • For data migration from an Oracle database to a MySQL tenant of OceanBase Database, OMS will automatically add hidden columns and hidden column comments to the CREATE TABLE statement.

      CREATE TABLE "table5" (
    "V1" CHAR(20) BINARY DEFAULT 'test',
    `OMS_OBJECT_NUMBER` BIGINT COMMENT 'Reserved for data migration tasks of OMS',
    `OMS_RELATIVE_FNO` BIGINT COMMENT 'Reserved for data migration tasks of OMS',
    `OMS_BLOCK_NUMBER` BIGINT COMMENT 'Reserved for data migration tasks of OMS',
    `OMS_ROW_NUMBER` BIGINT COMMENT 'Reserved for data migration tasks of OMS',
UNIQUE KEY `UK_{table}_OMS_ROWID` (
    `OMS_OBJECT_NUMBER`,
    `OMS_RELATIVE_FNO`,
    `OMS_BLOCK_NUMBER`,
    `OMS_ROW_NUMBER`
    )
);

Prerequisites

  • "{partition_col_0}" or `{partition_col_0}` indicates a table partitioning key column. Multiple such fields may exist. Separate the fields with commas (,), for example, "{partition_col_0}","{partition_col_1}","{partition_col_2}" or `{partition_col_0}`,`{partition_col_1}`,`{partition_col_2}`.

  • {schema}, {table}, {partition_col_0}, and {partition_col_1} are respectively placeholders for the database name, table name, and partitioning key column names.

Data migration from an Oracle database to an Oracle tenant of OceanBase Database

When you migrate data from an Oracle database to an Oracle tenant of OceanBase Database, OMS will add hidden columns and create unique indexes for the tables, regardless of whether they have primary keys.

  • Add hidden columns

    ALTER TABLE "{schema}"."{table}" ADD "OMS_OBJECT_NUMBER" NUMBER INVISIBLE;
ALTER TABLE "{schema}"."{table}" ADD "OMS_RELATIVE_FNO" NUMBER INVISIBLE;
ALTER TABLE "{schema}"."{table}" ADD "OMS_BLOCK_NUMBER" NUMBER INVISIBLE;
ALTER TABLE "{schema}"."{table}" ADD "OMS_ROW_NUMBER" NUMBER INVISIBLE;

  • Add unique indexes

    // A partitioning key column is available.
CREATE UNIQUE INDEX "{schema}"."UK_{table}_OMS_ROWID" ON "{schema}"."{table}" ("OMS_OBJECT_NUMBER", "OMS_RELATIVE_FNO", "OMS_BLOCK_NUMBER", "OMS_ROW_NUMBER", "{partition_col_0}", "{partition_col_1}") LOCAL;
// No partitioning key column is available.
CREATE UNIQUE INDEX "{schema}"."UK_{table}_OMS_ROWID" ON "{schema}"."{table}" ("OMS_OBJECT_NUMBER", "OMS_RELATIVE_FNO", "OMS_BLOCK_NUMBER", "OMS_ROW_NUMBER");

  • Drop unique indexes

    DROP INDEX "{schema}"."UK_{table}_OMS_ROWID";

  • Drop hidden columns

    ALTER TABLE "{schema}"."{table}" DROP ("OMS_OBJECT_NUMBER", "OMS_RELATIVE_FNO", "OMS_BLOCK_NUMBER", "OMS_ROW_NUMBER");

Migrate data between MySQL tenants of OceanBase Database

When you migrate data between MySQL tenants of OceanBase Database, OMS will add columns and create unique indexes for tables without unique keys.

Notice

In active-active disaster recovery scenarios, you can migrate only tables with unique keys.

You can run the following command to check whether a table has unique keys. Replace {schema} and {table} with the actual database and table names respectively. If no result is returned, the table does not have unique keys.

SELECT 1 FROM information_schema.statistics WHERE TABLE_SCHEMA = {schema} AND TABLE_NAME = {table} GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME HAVING count(1) = count(IF(upper(nullable) != 'YES' and NON_UNIQUE = 0, 1, NULL));

  • Add columns

    ALTER TABLE `{schema}`.`{table}` ADD COLUMN `OMS_PK_INCRMT` BIGINT;

  • Add unique indexes

    // A partitioning key column is available.
CREATE UNIQUE INDEX `UK_{table}_OBPK_INCRMT` ON `{schema}`.`{table}` (`{partition_col_0}`, `{partition_col_1}`, `OMS_PK_INCRMT`) LOCAL;
// No partitioning key column is available.
CREATE UNIQUE INDEX `UK_{table}_OBPK_INCRMT` ON `{schema}`.`{table}` (`OMS_PK_INCRMT`);

  • Drop unique indexes

    DROP INDEX `UK_{table}_OBPK_INCRMT` ON `{schema}`.`{table}`;

  • Drop columns

    ALTER TABLE `{schema}`.`{table}` DROP COLUMN `OMS_PK_INCRMT`;

Data migration between Oracle tenants of OceanBase Database

When you migrate data between Oracle tenants of OceanBase Database, OMS will add hidden columns and create unique indexes for tables without primary keys.

Notice

In active-active disaster recovery scenarios, you can migrate only tables with unique keys.

You can execute the following statement to check whether a table has a primary key. You must replace {schema} and {table} in the statement with the actual database name and table name. If no result is returned, the table has no primary key.

SELECT 1 FROM ( SELECT DC.OWNER, DC.TABLE_NAME, DC.CONSTRAINT_NAME FROM ALL_CONS_COLUMNS DCC JOIN ALL_CONSTRAINTS DC ON DCC.CONSTRAINT_NAME = DC.CONSTRAINT_NAME AND DCC.OWNER = DC.OWNER JOIN ALL_TAB_COLUMNS DTC ON DCC.COLUMN_NAME = DTC.COLUMN_NAME AND DCC.OWNER = DTC.OWNER AND DCC.TABLE_NAME = DTC.TABLE_NAME WHERE DCC.OWNER = {schema} AND DCC.TABLE_NAME = {table} AND DC.CONSTRAINT_TYPE IN ('U', 'P') GROUP BY DC.OWNER, DC.TABLE_NAME, DC.CONSTRAINT_NAME HAVING COUNT(*) = COUNT( CASE DTC.NULLABLE WHEN 'Y' THEN NULL ELSE 1 END ) MINUS SELECT TABLE_OWNER, TABLE_NAME, INDEX_NAME FROM ALL_IND_EXPRESSIONS WHERE TABLE_OWNER = {schema} AND TABLE_NAME = {table} );

  • Add hidden columns

    ALTER TABLE "{schema}"."{table}" ADD "OMS_PK_INCRMT" NUMBER INVISIBLE;

  • Add unique indexes

    // A partitioning key column is available.
CREATE UNIQUE INDEX "{schema}"."UK_{table}_OBPK_INCRMT" ON "{schema}"."{table}"("{partition_col_0}", "{partition_col_1}", "OMS_PK_INCRMT") LOCAL;
// No partitioning key column is available.
CREATE UNIQUE INDEX "{schema}"."UK_{table}_OBPK_INCRMT" ON "{schema}"."{table}"("OMS_PK_INCRMT");

  • Drop unique indexes

    DROP INDEX "{schema}"."UK_{table}_OBPK_INCRMT";

  • Drop hidden columns

    ALTER TABLE "{schema}"."{table}" DROP COLUMN "OMS_PK_INCRMT";

Data migration from an Oracle tenant of OceanBase Database to an Oracle database

When you migrate data from an Oracle tenant of OceanBase Database to an Oracle database, OMS will add hidden columns and create unique indexes for tables without primary keys.

You can execute the following statement to check whether a table has a primary key. You must replace {schema} and {table} in the statement with the actual database name and table name. If no result is returned, the table has no primary key.

SELECT 1 FROM ( SELECT DC.OWNER, DC.TABLE_NAME, DC.CONSTRAINT_NAME FROM ALL_CONS_COLUMNS DCC JOIN ALL_CONSTRAINTS DC ON DCC.CONSTRAINT_NAME = DC.CONSTRAINT_NAME AND DCC.OWNER = DC.OWNER JOIN ALL_TAB_COLUMNS DTC ON DCC.COLUMN_NAME = DTC.COLUMN_NAME AND DCC.OWNER = DTC.OWNER AND DCC.TABLE_NAME = DTC.TABLE_NAME WHERE DCC.OWNER = {schema} AND DCC.TABLE_NAME = {table} AND DC.CONSTRAINT_TYPE IN ('U', 'P') GROUP BY DC.OWNER, DC.TABLE_NAME, DC.CONSTRAINT_NAME HAVING COUNT(*) = COUNT( CASE DTC.NULLABLE WHEN 'Y' THEN NULL ELSE 1 END ) MINUS SELECT TABLE_OWNER, TABLE_NAME, INDEX_NAME FROM ALL_IND_EXPRESSIONS WHERE TABLE_OWNER = {schema} AND TABLE_NAME = {table} );

  • Add hidden columns

    // Versions earlier than Oracle Database 12c
ALTER TABLE "{schema}"."{table}" ADD "OMS_PK_INCRMT" NUMBER;
// Oracle Database 12c and later
ALTER TABLE "{schema}"."{table}" ADD "OMS_PK_INCRMT" NUMBER INVISIBLE;

  • Add unique indexes

    // A partitioning key column is available.
CREATE UNIQUE INDEX "{schema}"."{table}" ON "{schema}"."{table}"("{partition_col_0}", "{partition_col_1}", "OMS_PK_INCRMT") LOCAL;
// No partitioning key column is available.
CREATE UNIQUE INDEX "{schema}"."{table}" ON "{schema}"."{table}"("OMS_PK_INCRMT");

  • Drop unique indexes

    DROP INDEX "{schema}"."{table}";

  • Drop hidden columns

    ALTER TABLE "{schema}"."{table}" DROP COLUMN "OMS_PK_INCRMT";

Data migration from an Oracle database to a MySQL tenant of OceanBase Database

When you migrate data from an Oracle database to a MySQL tenant of OceanBase Database, OMS will add columns and create unique indexes for tables without unique keys.

You can run the following command to check whether a table has unique keys. Replace {schema} and {table} with the actual database and table names respectively. If no result is returned, the table does not have unique keys.

select 1 from ALL_CONS_COLUMNS DCC join ALL_CONSTRAINTS DC ON DCC.CONSTRAINT_NAME = DC.CONSTRAINT_NAME AND DCC.OWNER = DC.OWNER join ALL_TAB_COLUMNS DTC on DCC.COLUMN_NAME = DTC.COLUMN_NAME AND DCC.OWNER = DTC.OWNER AND DCC.TABLE_NAME = DTC.TABLE_NAME where DCC.owner = {schema} AND DCC.TABLE_NAME = {table} and DC.constraint_type in ('U', 'P') group by DC.OWNER, DC.TABLE_NAME, DC.CONSTRAINT_NAME having count(*) = count(CASE DTC.NULLABLE WHEN 'Y' THEN NULL ELSE 1 END);

  • Add columns

    ALTER TABLE `{schema}`.`{table}` ADD COLUMN `OMS_OBJECT_NUMBER` BIGINT;
ALTER TABLE `{schema}`.`{table}` ADD COLUMN `OMS_RELATIVE_FNO` BIGINT;
ALTER TABLE `{schema}`.`{table}` ADD COLUMN `OMS_BLOCK_NUMBER` BIGINT;
ALTER TABLE `{schema}`.`{table}` ADD COLUMN `OMS_ROW_NUMBER` BIGINT;

  • Add unique indexes

    // A partitioning key column is available.
CREATE UNIQUE KEY `UK_{table}_OMS_ROWID` (`OMS_OBJECT_NUMBER`, `OMS_RELATIVE_FNO`, `OMS_BLOCK_NUMBER`, `OMS_ROW_NUMBER`, `{partition_col_0}`, `{partition_col_1}`) LOCAL;
// No partitioning key column is available.
CREATE UNIQUE KEY `UK_{table}_OMS_ROWID` (`OMS_OBJECT_NUMBER`, `OMS_RELATIVE_FNO`, `OMS_BLOCK_NUMBER`, `OMS_ROW_NUMBER`);

  • Drop unique indexes

    DROP INDEX UK_{table}_OMS_ROWID ON {table};

  • Drop columns

    ALTER TABLE `{schema}`.`{table}`  DROP COLUMN `OMS_OBJECT_NUMBER`, DROP COLUMN `OMS_RELATIVE_FNO`, DROP COLUMN `OMS_BLOCK_NUMBER`, DROP COLUMN `OMS_ROW_NUMBER`;
Last
Next

OceanBaseOceanBase

OceanBase, A Highly Scalable Database for Transactional, Analytical, and AI Workloads.

Company

About OceanBaseContact UsPartner

Product

OceanBase CloudOceanBase EnterpriseQuick StartDownloadPricing

Resources

DocsBlogWhite PaperTrust Center

Follow us

LinkedInLinkedInDiscordDiscordTwitterTwitterYoutubeYoutubeForumForumGitHubGitHubStack OverflowStack Overflow

© OceanBase 2024. All rights reserved | Cloud Service Agreement | Privacy Policy | Legal | Security

Contact Us