Document Type | Technical Information
Category | Migration
Applicable Product Version | Tibero 6 FS03 and above
Document Number | TMITI020
Overview
This document introduces the Virtual Column feature provided by Tibero and guides you on how to migrate it.
Note
This document supplements the execution details and other specifics from the existing Tibero Virtual Column migration method document.
Link to the original document How to Migrate Tibero Virtual Columns
Virtual Column
Definition
- A Virtual Column is a virtual column whose value is automatically calculated using other column values or expressions.
- It is used when you need to create new values based on other columns.
- Supported from Oracle 11g and later, and from Tibero 6 FS03 and later versions.
Constraints
- Values are not stored in the DB but calculated during execution and returned.
- Can be used in Update and Delete conditions, but the column value itself cannot be changed.
(Direct input of data into Virtual Columns is not possible)
Method
Creating a Virtual Column
|
COLUMN_NAME [DATATYPE] [GENERATED ALWAYS] AS [EXPRESSION] [VIRTUAL]
- COLUMN_NAME : Name of the virtual column - DATATYPE : Data type of the virtual column (if omitted, the data type of the expression is used) - AS EXPRESSION : Expression applied to the virtual column - GENERATED ALWAYS, VIRTUAL : Syntax indicating the column is virtual, can be omitted
EX) CREATE TABLE EMP10( EMPNO NUMBER CONSTRAINTS PK_EMP10 PRIMARY KEY, ENAME VARCHAR2(50), PAY NUMBER, ANNUAL_INCOME NUMBER GENERATED ALWAYS AS (PAY * 12) VIRTUAL ); |
Checking Virtual Columns
You can check using the following views and queries.
|
View : desc dba_tbl_columns SQL> desc dba_tbl_columns
COLUMN_NAM TYPE CONSTRAINT ------------------------------------------------------------------------------ OWNER VARCHAR(128) TABLE_NAME VARCHAR(128) COLUMN_NAME VARCHAR(128) DATA_TYPE VARCHAR(65532) DATA_TYPE_OWNER VARCHAR(128) DATA_LENGTH NUMBER DATA_PRECISION NUMBER DATA_SCALE NUMBER NULLABLE VARCHAR(1) COLUMN_ID NUMBER DATA_DEFAULT LONG - expression check DEFAULT_LENGTH NUMBER CHAR_COL_DECL_LENGTH NUMBER CHAR_LENGTH NUMBER CHAR_USED VARCHAR(1) VIRTUAL_COLUMN VARCHAR(1) - Indicates if Virtual Column
SQL : select * from dba_tbl_columns where VIRTUAL_COLUMN='Y';
EX) create table tb_virtual (c1 number, c2 number, c3 number generated always as (c1 + c2) virtual);
SQL> select table_name, column_name, data_type, data_default, virtual_column from dba_tbl_column where virtual_column='Y';
TABLE_NAME COLUMN_NAM DATA_TYPE DATA_DEFAULT VIRTUAL_COLUMN ---------- ---------- ---------- -------------------- -------------- TB_VIRTUAL C3 NUMBER ("C1" + "C2") Y |
Migrating Virtual Columns
Because Virtual Columns do not allow direct data input, the error TBR-8143: Cannot insert a value in a virtual column occurs during data migration.
An exception is when creating a duplicate table using CTAS. In general cases, data migration is possible using the following methods.
tbexport/tbimport
The utilities tbexport and tbimport provided by Tibero can be used to migrate tables with Virtual Columns.
DB Link
When migrating via DB Link, you can perform a Select on all columns except the Virtual Column and then Insert, enabling proper migration.
|
EX) -- Table creation create table tb_virtual (c1 number, c2 number, c3 number generated always as (c1 + c2) virtual);
-- Data loading through DB Link insert /*+ append parallel(8) */ into tb_virtual (c1, c2) select /*+ parallel(8) */ c1, c2 from tb_virtual@link_t;
commit; |
Tablemigrator
Data migration of tables owning Virtual Columns is possible using Tablemigrator, which provides a column order change function.
Note
This feature is available by downloading the latest T-UP from TechNet.
|
EX) -- Table creation create table tb_virtual (c1 number, c2 number, c3 number generated always as (c1 + c2) virtual);
-- Data loading insert into tb_virtual(c1, c2) values (10, 10); insert into tb_virtual(c1, c2) values (10, 10); insert into tb_virtual(c1, c2) values (10, 10); commit;
-- migrator.properties configuration SOURCE_TYPE=DEFAULT SOURCE_DRIVER=com.m.internal.jdbc.MInternalDriver SOURCE_URL=jdbc:internal:thin:@localhost:8629:tibero SOURCE_USER=sys SOURCE_PASSWORD=tibero SOURCE_SCHEMA=TIBERO SOURCE_TABLE=tb_virtual(c1, c2)
TARGET_TYPE=TIBERO TARGET_DRIVER=com.m.internal.jdbc.MInternalDriver TARGET_URL=jdbc:internal:thin:@localhost:8629:tibero TARGET_USER=sys TARGET_PASSWORD=tibero TARGET_SCHEMA=TIBERO TARGET_TABLE=tb_virtual(c1, c2)
-- Execute Tablemigrator sh migrator.sh PROPERTY_FILE=migrator.properties
-- Execution result ## [WARNING] Ignored parameters: INSERT_THREAD_COUNT
=== Parameters === PROPERTY_FILE=migrator.properties SOURCE_TYPE=DEFAULT SOURCE_DRIVER=com.m.internal.jdbc.MInternalDriver SOURCE_URL=jdbc:internal:thin:@localhost:8629:tibero SOURCE_USER=sys SOURCE_PASSWORD=************ SOURCE_SCHEMA=TIBERO SOURCE_SCHEMAORIGN=TIBERO SOURCE_TABLE=tb_virtual(c1, c2) SELECT_SUBPARTITION= TARGET_TYPE=DEFAULT TARGET_DRIVER=com.m.internal.jdbc.MInternalDriver TARGET_URL=jdbc:internal:thin:@localhost:8629:tibero TARGET_USER=sys TARGET_PASSWORD=************ TARGET_SCHEMA=TIBERO TARGET_TABLE=tb_virtual(c1, c2) SELECT_FETCH_SIZE=1024 SELECT_AS_BYTE=N INSERT_BATCH=Y INSERT_PARALLEL=N INSERT_THREAD_COUNT=1 INSERT_ZERO_LENGTH_STRING_AS_NULL=Y
==================
SOURCE_SCHEMA : TIBERO SOURCE_TABLE : tb_virtual(c1, c2) TARGET_SCHEMA : TIBERO TARGET_TABLE : tb_virtual(c1, c2)
[E0]1674180265486 - STARTED [E0]1674180265490 - TOTAL Extracted ROWS: 3 (source schema = TIBERO, source table = tb_virtual) [L0]1674180265491 Loader started [L0]1674180265514 TOTAL Loaded ROWS: 3 (target schema = TIBERO, target table = tb_virtual) Loader finished [VERIFICATION] Source Info : TIBERO.tb_virtual : 3 Rows, Target Info : TIBERO.tb_virtual : 3 Rows [OK] Loading is ended. Elapsed Time (milliseconds) : 160 |