LOB types

The LOB type is used to store data types such as TEXT, BLOB, JSON, and Geometry. LOB storage can be either in-row or out-of-row.

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

Outbound storage stores LOB data in a LOB auxiliary table. 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 involves two separate 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 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, which is set to 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.

Inline storage outperforms out-of-line storage, as it reduces storage access times 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 types

In MySQL-compatible mode, the common LOB types are listed in alphabetical order as follows:

• ARRAY: stores data of the ARRAY type. It can store a collection of multiple values.
• RoaringBitmap: stores data of the Roaring bitmap type. It is mainly used for image processing and representation.
• BLOB: stores binary data such as images or files. The maximum length is 65,535 bytes.
• GEOMETRY: stores geospatial data. It can be used for spatial analysis and operations.
• JSON: stores data in the JSON format. It can facilitate the processing of structured data.
• LONGTEXT: stores massive text data. The maximum length is 536,870,910 bytes.
• LONGBLOB: stores massive binary data. The maximum length is 536,870,910 bytes.
• MEDIUMBLOB: stores a medium amount of binary data. The maximum length is 16,777,215 bytes.
• MEDIUMTEXT: stores a medium amount of text data. The maximum length is 16,777,215 bytes.
• TEXT: stores a small amount of text data. The maximum length is 65,535 bytes.

LOB consistency verification

When OUTROW storage is used, 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 "IDs present in the primary table but absent in the auxiliary table" or "IDs present in the auxiliary table but absent in the primary table" can lead to issues like read anomalies or space that cannot be reclaimed.

This feature uses a LOB verification task (LOB Task) to scan the primary table and collect the LOB ID set, then compares it with the LOB ID set obtained by scanning the LOB auxiliary table. It outputs whether the sets are consistent and provides discrepancy information, facilitating inspections during off-peak hours or troubleshooting to confirm whether data relationships are normal.

Usage instructions in conjunction with resource groups and I/O benchmarks

LOB consistency verification scans the primary table and the LOB auxiliary table, consuming significant I/O and CPU resources. When setting limits such as I/O ceilings for the verification task, these depend on the disk I/O benchmark of the tenant (generated by I/O calibration). Recommendations vary depending on the deployment environment:

• Environments where I/O calibration has been completed by default, such as public clouds: Typically, you can directly configure a resource management plan and a resource group within the tenant.
• Private clouds or environments where I/O calibration has not been performed: You must first complete disk I/O calibration in the sys tenant, then plan the MAX_IOPS / MIN_IOPS for units in the business tenant based on the benchmark, and coordinate this with the I/O limits in the resource group. For calibration commands, progress viewing, and the relationship with resource isolation for background tasks, see Resource isolation for background tasks; disk performance calibration steps are described separately in 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 need to execute it in the sys tenant first. Calibration may take 1-2 minutes from completion to taking effect.

ALTER SYSTEM RUN JOB 'io_calibration';

After calibration is complete, you can check the status of the calibration task via GV$OB_IO_CALIBRATION_STATUS and view the benchmark IOPS for each node via GV$OB_IO_BENCHMARK. When configuring unit IOPS for a tenant, it is recommended to leave approximately 10% headroom relative to the benchmark (for example, 90% of the IOPS corresponding to a 16KB read) to prevent disk I/O saturation from increasing latency for online workloads.

-- 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 suggested to refer to the IOPS benchmark value obtained from the GV$OB_IO_BENCHMARK query and leave approximately 10% headroom relative to that value (i.e., set the value to 90% of the benchmark IOPS for 16 KB I/O). This prevents bottlenecks when disk resources are fully utilized, ensuring the response time for frontend SQL queries is not affected.

Step 2: Create a dedicated resource plan for LOB verification

Within the business tenant where verification is required, you can create a separate resource plan to map LOB verification-related background tasks to a dedicated resource group and limit CPU and IOPS. The following example plan is named LOB_CHECK, and the resource group is named 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');

The above mappings and limits take effect for sessions and background tasks after the resource management plan is activated:

SET GLOBAL resource_manager_plan = 'LOB_CHECK';

For meaning and considerations of resource_manager_plan, see resource_manager_plan.

Step 3: Configure a scheduled task (Optional)

To perform LOB consistency verification periodically, you can create a scheduled task.

1. Start a task: By default, it is disabled and needs to be manually started. The default trigger time is 4:00 AM every Sunday.

   CALL DBMS_SCHEDULER.ENABLE('lob_check_job');
   

2. Stop a scheduled task

   CALL DBMS_SCHEDULER.DISABLE('lob_check_job');
   

3. 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=DAILY; INTERVAL=1');
   

   View detailed information about a scheduled task:

   SELECT * FROM DBA_SCHEDULER_JOBS WHERE job_name='lob_check_job';
   

   A sample return result is as follows:

   *************************** 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=DAILY; 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 set
   

   After a task is triggered, to view the progress of the LOB verification task, you can query the oceanbase.DBA_OB_LOB_CHECK_TASKS view. An example is shown below.

   SELECT * FROM DBA_OB_LOB_CHECK_TASKS;
   

   A sample return result is as follows:

   +-------+-------------+----------+-----------+---------+---------------------+---------------------+--------------+------------+----------+------------------+------------+-------------+------------+-----------+-------------+
   | 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 set
   

   After modifying the schedule using DBMS_LOB_MANAGER.RESCHEDULE_JOB, you can view fields such as NEXT_RUN_DATE via DBA_SCHEDULER_JOBS.

Step 4: Perform LOB consistency verification

1. Manually trigger LOB consistency verification

   -- Trigger a consistency verification for all LOB auxiliary tables of the tenant.
   CALL DBMS_LOB_MANAGER.CHECK_LOB();
   -- Trigger the LOB consistency verification for tables with table IDs in this array for the tenant.
   CALL DBMS_LOB_MANAGER.CHECK_LOB('[500001, 500003]');
   

2. Cancel the current task

   CALL DBMS_LOB_MANAGER.CANCEL_JOB("check_lob");
   

3. Pause the task

   CALL DBMS_LOB_MANAGER.SUSPEND_JOB("check_lob");
   

4. Resume the task

   CALL DBMS_LOB_MANAGER.RESUME_JOB("check_lob");
   

5. Modify the trigger cycle and start time of a scheduled task

   CALL DBMS_LOB_MANAGER.RESCHEDULE_JOB('2025-12-01 17:48:00', 'FREQ=DAILY; INTERVAL=1');
   

Step 5: View LOB consistency verification results

View the progress of the current LOB task via the DBA_OB_LOB_CHECK_TASKS view, and view exception results via DBA_OB_LOB_CHECK_EXCEPTION_RESULT.

SELECT * FROM DBA_OB_LOB_CHECK_TASKS;
SELECT * FROM DBA_OB_LOB_CHECK_EXCEPTION_RESULT;

References

• Overview of roaring bitmap functions
• BLOB
• Overview of spatial data types
• JSON
• TEXT