Note
This feature is an experimental feature in the current version.
Fork Table creates a new target table based on the data of the source table at a specific snapshot. Logically, the target table is an identical copy of the source table at that snapshot (including both the table structure and data), but it exists as an independent table object, so that subsequent read and write operations do not affect each other.
Syntax
FORK TABLE source_table TO destination_table [WITH READ LOCK];
WITH READ LOCK can only be placed at the end of the FORK TABLE statement. This clause applies only to FORK TABLE and not to FORK DATABASE.
Parameters
Parameter |
Description |
|---|---|
source_table |
Source table name. It can be db.table or table (the current database is used). |
destination_table |
Target table name. It can be db.table or table (the current database is used). |
WITH READ LOCK |
Optional clause, which can only be placed at the end of a statement. When this clause is specified, OceanBase AI Database establishes a short-term write barrier on the source table before selecting a snapshot, waits for existing write transactions to the source table to complete, and blocks new DML writes to the source table. The write barrier is automatically released after the target table's schema is published and the target table becomes visible to users. If this clause is not specified, no additional lock is placed on the source table, maintaining the original behavior. This clause is supported starting from V4.6.2 BP1 hotfix1. |
Application scenarios
Create a copy of the table and execute read/write operations
Quickly create a table copy that can be independently read from and written to without affecting the source table.
-- Create a copy
FORK TABLE t1 TO t1_fork;
-- Query the copy
SELECT count(*) FROM t1_fork;
-- Write to the copy (this does not affect the source table t1)
INSERT INTO t1_fork VALUES (...);
Lock the source table while creating a copy
To prevent write operations on the source table until the target table becomes visible, you can specify the WITH READ LOCK clause. The write barrier is automatically released after the target table becomes visible to users, and write operations to the source table resume immediately.
-- Create a copy and block writes to the source table until the target table becomes visible.
FORK TABLE test_db.src_table TO test_db.dst_table WITH READ LOCK;
Data versioning and branch management
Derive branches from the main version, experiment on them, and roll back or promote changes at any time.
-- Step 1: Create a branch (the data baseline is a snapshot at the time of the fork).
FORK TABLE t1 TO t1_branch_v2;
-- Step 2: Perform changes on the branch (such as data modification, feature updates, and index adjustments).
-- ... Perform DML on t1_branch_v2 ...
-- Step 3a: Rollback – Drop the branch
DROP TABLE t1_branch_v2;
-- Step 3b: Promote the branch to the main version (using an atomic RENAME)
RENAME TABLE t1 TO t1_backup, t1_branch_v2 TO t1;
DROP TABLE t1_backup;
A/B testing and sandbox validation
Create isolated copys based on online tables, conduct safe experiments on the copies, and retain or discard them as needed after completion.
-- Create an experimental table
FORK TABLE t_search_prod TO t_search_exp;
-- Modify and evaluate on the test table
-- ... Perform write operations and query evaluation on t_search_exp ...
-- Experiment failed: Drop the experiment table
DROP TABLE t_search_exp;
Core features
Note
Parallel fork and background parallel processing are supported starting from V4.6.2 BP1. Write barriers are supported starting from V4.6.2 BP1 hotfix1.
Features |
Description |
|---|---|
| Efficient creation | Reuses the data organization and storage structure of the source table, avoiding complete data replication, making it suitable for large table scenarios. |
| Online execution | The source table can continue to support DML read and write operations during the fork process. |
| Write barrier | When the WITH READ LOCK clause is specified, OceanBase AI Database establishes a short-term write barrier on the source table before selecting a snapshot, blocking DML writes to the source table. The write barrier is automatically released after the target table becomes visible. |
| Independently writable | The target table can independently perform operations such as INSERT, UPDATE, DELETE, and DDL without affecting the source table. |
| Snapshot consistency | The data in the target table is based on a snapshot point automatically selected by the system, ensuring data consistency. |
| Progressive availability | The target table becomes accessible after creation. The refinement of backend data organization does not affect snapshot semantics or availability. |
| Incremental storage | Subsequent data written to the target table incurs additional storage overhead. |
| Parallel fork | The same source can be forked to multiple targets simultaneously. |
| Background parallel processing | Background data processing uses multi-threaded parallel execution to improve the overall completion efficiency of forks. |
Performance considerations
- In typical small-scale scenarios,
FORK TABLEcan complete the creation of the target table and make it accessible within a few hundreds of milliseconds. - After the target table becomes available, data organization may continue in the background.
- In scenarios with large transactions, complex data structures, or resource constraints, processing time may increase, but this does not affect external snapshot consistency or isolation semantics.
- For single-table multi-partition scenarios, the system has optimized statistics processing at the start of a fork, reducing initiation latency.
Privilege requirements
To initiate a Fork Table operation, you must have:
- The
SELECTprivilege on the source table (either global, database-level, or table-level). - The
CREATEprivilege on the target database.
Fork only replicates the schema and data, not the privileges on the source table. Privileges for the target table are determined as if it were a newly created object. Fork can only be executed within user tenant and is not supported in the sys tenant.
Operation mutual exclusion
During a Fork Table operation, the source and target tables are mutually exclusive with related DDL operations until the fork completes its finalization. DML read and write operations can continue on the source table during the fork.
Limitations
The current version has the following limitations:
- Cascaded forks are not supported until the background processing of the current fork operation is complete.
- The
WITH READ LOCKclause applies only toFORK TABLEand not toFORK DATABASE.
Unsupported table types and indexes:
- Internal tables, temporary tables.
- Tables with foreign keys.
- HNSW vector indexes, IVF indexes, semantic indexes, spatial indexes, hybrid indexes, columnstore indexes, asynchronous indexes.
- Partitioned tables with global indexes.
- Tables with materialized view logs, or tables referenced by materialized views (as an incremental refresh data source).
Other limitations:
- If the target table already exists or has the same name as a foreign key placeholder parent table (mock FK parent table), the system returns an error.
- Tables that have been moved to the recycle bin cannot be forked.
- The system or internal tables cannot be forked.
Considerations
- The Fork operation does not affect the source table. The source table continues to provide read and write services normally after the fork.
- By default (when
WITH READ LOCKis not specified), the Fork process does not block DML on the source table, so new writes can still occur to the source table. To prevent writes to the source table until the target table becomes visible, specify theWITH READ LOCKclause. - After creation, the target table is completely independent of the source table. Modifications made to either table are not reflected on the other.
- The snapshot point selected for the Fork operation is automatically determined by the system. Users do not need to (and cannot) specify a particular time point.
- The target table created by a fork can be read from normally until the source table is deleted or its data is cleaned up.