LOB types

The LOB type is used to store data of the TEXT, BLOB, JSON, and Geometry types. The LOB type can be stored in inline (INROW) or out-of-line (OUTROW) storage.

Inline LOB storage

Inline LOB storage stores LOB data with the main table rows. When you read LOB data, you need to perform only one storage access operation.

LOB storage

In external LOB storage, LOB data is stored in a LOB auxiliary table. When reading LOB data, the main table row must be read to obtain the locator for the external LOB. Then, the actual LOB data is retrieved from the LOB auxiliary table using this locator. This process involves two separate storage access operations.

LOB storage conversion

Whether LOB data is stored inline or out of line depends on the size of the LOB column. If the threshold is set to 8192 bytes, data exceeding this threshold is stored out of line, while data less than or equal to this threshold is stored inline.

obclient> CREATE TABLE t(pk int, data text) LOB_INROW_THRESHOLD = 8192;

The above DDL statement specifies that the threshold for converting LOB columns from inline to out of line storage is 8192 bytes. Here, LOB_INROW_THRESHOLD specifies the threshold for LOB columns.

• When the data in a LOB column is less than or equal to 8192 bytes, the LOB data and the main table row are stored together.
• When the data in a LOB column exceeds 8192 bytes, all data is stored in the LOB auxiliary table.

Inline storage generally offers better performance compared to out of line storage. It reduces the number of storage accesses and improves the efficiency of reading LOB data. In scenarios where LOB data is frequently accessed, choosing inline storage can accelerate query performance and reduce system overhead.

LOB types

In MySQL mode, common LOB types are listed in alphabetical order:

• ARRAY: Stores array data types, allowing the storage of multiple values in a collection.
• Roaringbitmap: Stores bitmap data, primarily used for image processing and representation.
• BLOB (Binary Large Object): Stores binary data, such as images or files, with a maximum length of 65,535 bytes.
• GEOMETRY: Stores geographic spatial data, supporting spatial analysis and operations.
• JSON: Stores JSON-formatted data, facilitating structured data processing.
• LONGTEXT: Stores large amounts of text data, with a maximum length of 536,870,910 bytes.
• LONGBLOB: Stores large amounts of binary data, with a maximum length of 536,870,910 bytes.
• MEDIUMBLOB: Stores moderate amounts of binary data, with a maximum length of 16,777,215 bytes.
• MEDIUMTEXT: Stores moderate amounts of text data, with a maximum length of 16,777,215 bytes.
• TEXT: Stores smaller amounts of text data, with a maximum length of 65,535 bytes.

LOB consistency check

When external storage is used, the main table and LOB auxiliary table are associated through LOB IDs: the set of LOB IDs in the main table's locator should match the set of LOB IDs in the LOB auxiliary table. Inconsistencies such as "present in the main table but not in the auxiliary table" or "present in the auxiliary table but not in the main table" can lead to read errors or issues with space recovery.

This feature uses a LOB consistency check task (LOB Task) to scan the main table and collect the set of LOB IDs, then compares this set with the set of LOB IDs obtained from the LOB auxiliary table. It outputs whether the sets are consistent and any discrepancies, facilitating inspections during off-peak hours or confirming data relationships during troubleshooting.

Considerations when used with resource groups and I/O benchmarks

LOB consistency checks scan the main table and LOB auxiliary table, which requires significant I/O and CPU resources. When setting I/O limits for check tasks, it relies on the tenant's disk I/O benchmark (generated by I/O calibration). The following recommendations apply based on the deployment type:

• In environments like public clouds that have already completed I/O calibration: Typically, you can directly configure resource management plans and resource groups within the tenant.
• In private clouds or environments without I/O calibration: First, complete the disk I/O calibration in the system tenant. Then, based on the benchmark, plan the MAX_IOPS and MIN_IOPS for business tenants, and combine this with the I/O limits in the resource group. For details on calibration commands, progress monitoring, and resource isolation for background tasks, see Resource isolation for background tasks. For the steps of disk performance calibration, see Disk performance calibration.

Full example

Before using the LOB consistency check feature, you must perform I/O calibration, create a dedicated resource plan for LOB consistency check, and configure the resource management plan and resource group.

Step 1: I/O calibration (optional)

If you have not performed I/O calibration, you must perform I/O calibration in the sys tenant. The calibration result takes effect after 1-2 minutes.

ALTER SYSTEM RUN JOB 'io_calibration';

After the calibration is completed, you can query the GV$OB_IO_CALIBRATION_STATUS view to view the calibration task status and the GV$OB_IO_BENCHMARK view to view the IOPS of each node. When you configure the IOPS of a tenant, we recommend that you reserve about 10% of the IOPS of the baseline (for example, the IOPS corresponding to 16 KB reads) to avoid disk fullness affecting the latency of online business.

-- View the calibration progress.
SELECT * FROM GV$OB_IO_CALIBRATION_STATUS;
-- Query the calibration value.
SELECT * FROM GV$OB_IO_BENCHMARK;

When you configure the IOPS of a tenant, we recommend that you reserve about 10% of the IOPS of the baseline (for example, the IOPS corresponding to 16 KB reads) to avoid disk fullness affecting the latency of online business.

Step 2: Create a resource plan for LOB consistency check

In the business tenant where you want to run the LOB consistency check, you can create a dedicated resource plan for LOB consistency check, map the LOB consistency check-related background tasks to a dedicated resource group, and limit the CPU and IOPS. The following example creates a resource plan named LOB_CHECK and a resource group named LOB_CHECK. It maps the background tasks whose value is LOB_CHECK to the resource group by using the FUNCTION mapping. You must adjust the values of MGMT_P1, UTILIZATION_LIMIT, MAX_IOPS, MIN_IOPS, and WEIGHT_IOPS as needed.

For more information about the syntax and parameters of the subprograms of DBMS_RESOURCE_MANAGER, see DBMS_RESOURCE_MANAGER overview.

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 activate the resource management plan, the mapping and limits take effect for the sessions and background tasks:

SET GLOBAL resource_manager_plan = 'LOB_CHECK';

For more information about the resource_manager_plan parameter, see resource_manager_plan.

Step 3: Configure a scheduled task (optional)

If you want to run the LOB consistency check periodically, you can create a scheduled task.

1. Enable the task. By default, the task is disabled. You must enable it manually. By default, the task is triggered at 4:00 AM on Sunday.

   CALL DBMS_SCHEDULER.ENABLE('lob_check_job');
   

2. Stop the scheduled task.

   CALL DBMS_SCHEDULER.DISABLE('lob_check_job');
   

3. Modify the trigger cycle and start time of the 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 the details of the scheduled task:

   SELECT * FROM DBA_SCHEDULER_JOBS WHERE job_name='lob_check_job';
   

   The following example shows the query result.

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

   After the task is triggered, you can query the oceanbase.DBA_OB_LOB_CHECK_TASKS view to view the progress of the LOB consistency check task. The following example shows the query result.

   SELECT * FROM DBA_OB_LOB_CHECK_TASKS;
   

   The following example shows the query result.

   +-------+-------------+----------+-----------+---------+---------------------+---------------------+--------------+------------+----------+------------------+------------+-------------+------------+-----------+-------------+
   | 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 you modify the scheduling by using the DBMS_LOB_MANAGER.RESCHEDULE_JOB procedure, you can query the DBA_SCHEDULER_JOBS view to view the NEXT_RUN_DATE field.

Step 4: Run the LOB consistency check

1. Manually trigger the LOB consistency check.

   -- Trigger the LOB consistency check for all tables with LOB auxiliary tables in the current tenant.
   CALL DBMS_LOB_MANAGER.CHECK_LOB();
   -- Trigger the LOB consistency check for tables with the specified table IDs in the current 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 the scheduled task.

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

Step 5: View the LOB consistency check result

You can query the DBA_OB_LOB_CHECK_TASKS view to view the progress of the LOB consistency check task and the DBA_OB_LOB_CHECK_EXCEPTION_RESULT view to view the exception result.

SELECT * FROM DBA_OB_LOB_CHECK_TASKS;
SELECT * FROM DBA_OB_LOB_CHECK_EXCEPTION_RESULT;

References

• Bitmap functions overview
• BLOB
• Spatial data types overview
• JSON
• TEXT