The LOB type is used to store data types such as TEXT, BLOB, JSON, and Geometry. The storage methods for LOB are divided into two types: in-row storage (INROW) and out-of-row storage (OUTROW).
Considerations
In the current version, tables containing LOB columns have the following considerations:
- Starting from V4.6.2.1, direct load is supported for data import, including incremental direct load.
- Starting from V4.6.2.1, offline DDL operations involving out-of-row data in LOB columns are supported.
- Starting from V4.6.2.2, you can use OBCDC to synchronize changes to tables containing LOB columns in
FULLmode. When updating row data, the synchronization data includes the content of LOB columns even if the columns remain unchanged. In an upgrade scenario, only changes generated after all OBServer nodes are upgraded are supported for synchronization. - JSON partial update is not supported. When updating a JSON document, a full update is performed.
Inline storage
Inline storage stores LOB data together with the row data of the primary table, requiring only one storage access operation when reading LOB data.
External storage
External storage involves storing LOB data in a LOB auxiliary table. The LOB auxiliary table consists of a LOB Meta table and a LOB Piece table, where the LOB Meta table stores metadata about the LOB, and the LOB Piece table stores the shard data of the LOB. When reading LOB data, you must first read the primary table row to obtain the locator of the external LOB, and then use this locator to read the actual LOB data from the LOB auxiliary table. This process requires two storage access operations.
LOB storage conversion
Whether LOB data is stored internally or externally depends on the data volume of the LOB column. Suppose a threshold of 8,192 bytes is set. If the data volume exceeds 8,192 bytes, it is stored externally; otherwise, it is stored internally.
obclient> CREATE TABLE t(pk int, data text) LOB_INROW_THRESHOLD = 8192;
The preceding DDL statement specifies the INROW to OUTROW threshold for LOB columns in the table as 8,192 bytes. Here, LOB_INROW_THRESHOLD represents the threshold for LOB columns.
- When the data in a LOB column is less than or equal to 8,192 bytes, the LOB data is stored together with the row data of the primary table.
- When the data in a LOB column exceeds 8,192 bytes, all the data is stored in a LOB auxiliary table.
Note
Changing the value of lob_inrow_threshold from a larger value to a smaller value requires triggering an offline DDL operation.
Inline storage outperforms out-of-line storage, as it reduces storage access counts and improves the efficiency of reading LOB data. This is particularly beneficial in scenarios where LOB data is frequently accessed, as choosing inline storage can accelerate query speeds and reduce system overhead.
LOB value types
Common LOB types are listed in alphabetical order as follows:
- ARRAY: A data type for storing arrays, allowing the collection of multiple values.
- Roaringbitmap: used to store bitmap data, mainly for image processing and representation.
- BLOB (Binary Large Object): used to store binary data, such as images or files, with a maximum length of 65,535 bytes.
- GEOMETRY: Stores geospatial data and supports spatial analysis and operations.
- JSON: Stores data in JSON format, facilitating the processing of structured data.
- LONGTEXT: Used to store large amounts of text data. The maximum column size is approximately 4 TB (the strict upper limit is
4 TB - 1bytes, or4,398,046,511,103bytes). A single LOB write can be up to about 512 MB; data exceeding 512 MB must be appended in batches using LOB streaming write. - LONGBLOB: Stores large amounts of binary data. The maximum column size is approximately 4 TB (the strict upper limit is
4 TB - 1bytes, or4,398,046,511,103bytes). A single LOB write can be up to 512 MB; data exceeding 512 MB must be appended in batches using LOB streaming write. - MEDIUMBLOB: used to store medium amounts of binary data, with a maximum length of 16,777,215 bytes.
- MEDIUMTEXT: used to store medium amounts of text data, with a maximum length of 16,777,215 bytes.
- TEXT: stores a small amount of text data, with a maximum length of 65,535 bytes.
LOB Streaming Write
When writing a large LOB, loading the entire content into memory at once can easily result in high memory pressure. OceanBase AI Database supports LOB streaming write through the LOB_WRITEAPPEND function: it is called multiple times within an UPDATE statement, appending a segment of data each time, thereby reducing the memory footprint per write.
Streaming write is suitable for scenarios where large objects or file-like content needs to be uploaded in batches. For syntax, parameters, limitations, examples, and idempotence and retry instructions, see LOB_WRITEAPPEND.
Note
LOB data written via streaming append does not support CDC synchronization. OBCDC will report an error when encountering such writes. It is recommended to add tables that use LOB streaming append to the OBCDC tb_black_list blacklist to exclude them from the synchronization scope.
LOB Consistency Verification
During external storage linkage, the primary table and the LOB auxiliary table are associated through LOB IDs: the set of LOB IDs in the primary table locator must match the set of LOB IDs present in the LOB auxiliary table. Inconsistencies such as "LOB IDs present in the primary table but absent in the auxiliary table" or "LOB IDs present in the auxiliary table but absent in the primary table" can lead to read errors or prevent space from being reclaimed.
This feature uses a LOB task to scan the primary table and collect a set of LOB IDs. It then compares this set with the LOB IDs obtained from scanning the LOB auxiliary table, and outputs whether they are consistent and any discrepancy information. This facilitates routine inspections during off-peak hours or troubleshooting to confirm whether data relationships are normal.
Notice
The validation scan the data in the primary table and the LOB auxiliary tables, which incurs some I/O and CPU overhead. Please perform this operation during off-peak hours and configure an appropriate resource group for the task as described below.
Usage instructions in conjunction with resource groups and I/O benchmarks
LOB consistency verification scans the primary table and the LOB auxiliary table, which incurs significant I/O and CPU overhead. When imposing limits such as an I/O cap on a verification task, it relies on the tenant-side disk I/O baseline (generated by I/O calibration). The following recommendations are provided for different deployment scenarios:
- Environments where I/O calibration has been completed by default, such as public clouds: In these cases, you can usually configure resource management plans and resource groups directly within the tenant.
- Private cloud or environments where I/O calibration has not been performed: First, perform disk I/O calibration in the sys tenant. Then, plan the
MAX_IOPSandMIN_IOPSfor units in the business tenant based on the benchmark, ensuring they align with the I/O quotas in the resource group. For calibration commands, progress viewing, and resource isolation from background tasks, see Resource isolation for background tasks and Disk performance calibration; for detailed steps, refer to Disk Performance Calibration.
Complete example
When using the consistency verification feature, you must first perform I/O calibration, then create a dedicated resource plan for LOB verification, and configure the resource management plan and resource group.
Step 1: I/O calibration (optional)
If I/O calibration has not been performed, you must first execute it in the sys tenant. It may take 1 to 2 minutes for the calibration to take effect.
Notice
Commands in this section must be executed in the sys tenant.
ALTER SYSTEM RUN JOB 'io_calibration';
After calibration, you can view the calibration task status through GV$OB_IO_CALIBRATION_STATUS and view the benchmark IOPS of each node through GV$OB_IO_BENCHMARK. When configuring Unit IOPS for a tenant, it is recommended to reserve about 10% margin based on the benchmark (for example, the IOPS corresponding to reading 16 KB), to avoid disk fullness affecting online business latency.
-- View Calibration Progress
SELECT * FROM GV$OB_IO_CALIBRATION_STATUS;
-- Query calibration value
SELECT * FROM GV$OB_IO_BENCHMARK;
When configuring unit-level IOPS for a tenant, it is recommended to refer to the IOPS baseline value obtained from the GV$OB_IO_BENCHMARK query and reserve about a 10% margin on this basis (i.e., set the value to the IOPS corresponding to 90% of 16K). This helps prevent bottlenecks when disk resources are fully utilized, ensuring that the response time of front-end SQL queries is not affected.
Step 2: Create a dedicated resource plan for LOB verification
You can create a separate resource plan for the business tenant that requires verification, map LOB verification-related background tasks to a dedicated resource group, and limit CPU and IOPS. In the following example, the plan name is LOB_CHECK and the resource group name is also LOB_CHECK. The FUNCTION mapping assigns background tasks with the value LOB_CHECK to this group. Adjust parameters such as MGMT_P1, UTILIZATION_LIMIT, MAX_IOPS, MIN_IOPS, and WEIGHT_IOPS according to your actual specifications.
For syntax and parameter descriptions of each DBMS_RESOURCE_MANAGER subprogram, see Overview of DBMS_RESOURCE_MANAGER.
CALL DBMS_RESOURCE_MANAGER.CREATE_PLAN('LOB_CHECK', 'plan for lob_check');
CALL DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP( CONSUMER_GROUP => 'LOB_CHECK', COMMENT => 'LOB_CHECK');
CALL DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
PLAN => 'LOB_CHECK',
GROUP_OR_SUBPLAN => 'LOB_CHECK',
COMMENT => 'LOB_CHECK_GROUP',
MGMT_P1 => 30,
UTILIZATION_LIMIT => 30,
MAX_IOPS => 20,
MIN_IOPS => 0,
WEIGHT_IOPS => 20);
CALL DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING('FUNCTION', 'LOB_CHECK', 'LOB_CHECK');
After you enable a resource management plan, the above mappings and limits take effect for sessions and background tasks:
SET GLOBAL resource_manager_plan = 'LOB_CHECK';
For the meaning and considerations of resource_manager_plan, see resource_manager_plan.
Step 3: Configure a scheduled task (optional)
Notice
Run the commands in this section under a business tenant.
To perform regular LOB consistency verification, you can create a scheduled task.
Enable task: Disabled by default and must be manually enabled. The default trigger time is 4:00 a.m. every Sunday.
CALL DBMS_SCHEDULER.ENABLE('lob_check_job');Stop the scheduled task
CALL DBMS_SCHEDULER.DISABLE('lob_check_job');Modify the trigger cycle and start time of a scheduled task
CALL DBMS_SCHEDULER.ENABLE('lob_check_job'); CALL DBMS_LOB_MANAGER.RESCHEDULE_JOB('2025-12-01 17:48:00', 'FREQ=DAYLY; INTERVAL=1');View details of a scheduled task:
SELECT * FROM DBA_SCHEDULER_JOBS WHERE job_name='lob_check_job';The return result is shown below:
*************************** 1. row *************************** OWNER: SYS JOB_NAME: lob_check_job JOB_SUBNAME: NULL JOB_STYLE: REGULAR JOB_CREATOR: NULL CLIENT_ID: NULL GLOBAL_UID: NULL PROGRAM_OWNER: SYS PROGRAM_NAME: NULL JOB_TYPE: PLSQL_BLOCK JOB_ACTION: DBMS_LOB_MANAGER.CHECK_LOB_INNER() NUMBER_OF_ARGUMENTS: NULL SCHEDULE_OWNER: NULL SCHEDULE_NAME: NULL SCHEDULE_TYPE: NULL START_DATE: 2025-12-01 17:48:00.000000 +08:00 REPEAT_INTERVAL: FREQ=DAYLY; INTERVAL=1 EVENT_QUEUE_OWNER: NULL EVENT_QUEUE_NAME: NULL EVENT_QUEUE_AGENT: NULL EVENT_CONDITION: NULL EVENT_RULE: NULL FILE_WATCHER_OWNER: NULL FILE_WATCHER_NAME: NULL END_DATE: 4000-01-01 00:00:00.000000 +08:00 JOB_CLASS: DEFAULT_JOB_CLASS ENABLED: 1 AUTO_DROP: 0 RESTART_ON_RECOVERY: NULL RESTART_ON_FAILURE: NULL STATE: NULL JOB_PRIORITY: NULL RUN_COUNT: NULL MAX_RUNS: NULL FAILURE_COUNT: 0 MAX_FAILURES: NULL RETRY_COUNT: NULL LAST_START_DATE: NULL LAST_RUN_DURATION: +000000000 02:00:00.000000 NEXT_RUN_DATE: 2025-12-23 17:48:00.000000 +08:00 SCHEDULE_LIMIT: NULL MAX_RUN_DURATION: +000 02:00:00 LOGGING_LEVEL: NULL STORE_OUTPUT: NULL STOP_ON_WINDOW_CLOSE: NULL INSTANCE_STICKINESS: NULL RAISE_EVENTS: NULL SYSTEM: NULL JOB_WEIGHT: NULL NLS_ENV: NULL SOURCE: NULL NUMBER_OF_DESTINATIONS: NULL DESTINATION_OWNER: NULL DESTINATION: NULL CREDENTIAL_OWNER: NULL CREDENTIAL_NAME: NULL INSTANCE_ID: NULL DEFERRED_DROP: NULL ALLOW_RUNS_IN_RESTRICTED_MODE: NULL COMMENTS: LOB consistency check job, runs weekly to check LOB data consistency FLAGS: 0 RESTARTABLE: NULL CONNECT_CREDENTIAL_OWNER: NULL CONNECT_CREDENTIAL_NAME: NULL 1 row in setAfter the task is triggered, you can query the
oceanbase.DBA_OB_LOB_CHECK_TASKSview to view the progress of the LOB verification task. Example:SELECT * FROM DBA_OB_LOB_CHECK_TASKS;The return result is shown below:
+-------+-------------+----------+-----------+---------+---------------------+---------------------+--------------+------------+----------+------------------+------------+-------------+------------+-----------+-------------+ | LS_ID | TABLE_NAME | TABLE_ID | TABLET_ID | TASK_ID | START_TIME | END_TIME | TRIGGER_TYPE | STATUS | MISS_CNT | MISMATCH_LEN_CNT | ORPHAN_CNT | CORRECT_CNT | RET_CODE | TASK_TYPE | SCAN_INDEX | +-------+-------------+----------+-----------+---------+---------------------+---------------------+--------------+------------+----------+------------------+------------+-------------+------------+-----------+-------------+ | -1 | NULL | -3 | -3 | 1 | 2025-12-23 17:48:00 | 2025-12-23 17:48:00 | PERIODIC | TRIGGERING | 0 | 0 | 0 | 0 | OB_SUCCESS | LOB_CHECK | PRIMARY KEY | | 1 | __all_table | 3 | 3 | 1 | 2025-12-23 17:48:02 | 2025-12-23 17:48:02 | PERIODIC | PREPARED | 0 | 0 | 0 | 0 | OB_SUCCESS | LOB_CHECK | PRIMARY KEY | +-------+-------------+----------+-----------+---------+---------------------+---------------------+--------------+------------+----------+------------------+------------+-------------+------------+-----------+-------------+ 2 rows in setAfter modifying the schedule using
DBMS_LOB_MANAGER.RESCHEDULE_JOB, you can view fields such asNEXT_RUN_DATEthroughDBA_SCHEDULER_JOBS.
Step 4: Perform LOB consistency verification
Manually trigger LOB consistency verification
-- Trigger a consistency check for all LOB auxiliary tables of the tenant. CALL DBMS_LOB_MANAGER.CHECK_LOB(); -- Trigger the consistency verification of LOB data for tables with table_id in this array for the tenant. CALL DBMS_LOB_MANAGER.CHECK_LOB('[500001, 500003]');Cancel the current task
CALL DBMS_LOB_MANAGER.CANCEL_JOB("check_lob");Stop the task.
CALL DBMS_LOB_MANAGER.SUSPEND_JOB("check_lob");Resume the task.
CALL DBMS_LOB_MANAGER.RESUME_JOB("check_lob");Modify the trigger cycle and start time of a scheduled task.
CALL DBMS_LOB_MANAGER.RESCHEDULE_JOB('2025-12-01 17:48:00', 'FREQ=DAYLY; INTERVAL=1');
Step 5: View the LOB consistency verification results
Query the DBA_OB_LOB_CHECK_TASKS view for the progress of the current LOB task, and query the DBA_OB_LOB_CHECK_EXCEPTION_RESULT view for exception results.
SELECT * FROM DBA_OB_LOB_CHECK_TASKS;
SELECT * FROM DBA_OB_LOB_CHECK_EXCEPTION_RESULT;