38-Oracle 23 ai Accelerate Securefiles LOB Performance

小伙伴们在日常的操作中，使用LOB对象会很多么，工业的附件、医疗的图像、电子病历的长长的几千上万字的描述和图像等，这些在查询和使用过程中都是极度折磨，第一速度慢、第二是占用超大空间，且业务需要从这些字段中拆解信息，统计、上传等。

Oracle的大对象（LOB）存储技术经历了从BasicFile到SecureFile的革新，在23 ai默认用SecureFile带来性能与智能管理的提升。

一、LOB技术原理与架构解析

​LOB（Large Object）​​ 是Oracle处理大文件（如图片、视频、文档）的核心数据类型，分为：

存储架构核心机制​

 实践脚本：创建含LOB列的表

SYS@CDB$ROOT> 
create tablespace SECURE_LOB_TS 
DATAFILE 'SECURE_LOB_TS.dbf' size 100M 
autoextend ON SEGMENT SPACE MANAGEMENT AUTO;

--Tablespace SECURE_LOB_TS created.

    CREATE TABLE medical_images (
      image_id NUMBER PRIMARY KEY,
      description VARCHAR2(100),
      image_data BLOB
    ) LOB(image_data) STORE AS SECUREFILE (
      TABLESPACE SECURE_LOB_TS 
      COMPRESS HIGH 
      DEDUPLICATE
    );
--Table MEDICAL_IMAGES created.

二、SecureFile 在LOB上的提升

自11g引入的SecureFile彻底重构了LOB存储，19c可以选择是否SecureFile，23 ai 默认SecureFile，有了这个支持才有了向量数据存入lob字段，存储体验SecureFile的速度和性能。

​关键技术特性

脚本实测（23 ai环境）​ 

-- 测试SecureFile与BasicFile读性能
ALTER SESSION SET db_securefile = 'FORCE';
--Session altered.
CREATE TABLE medical_images (
      image_id NUMBER PRIMARY KEY,
      description VARCHAR2(100),
      image_data BLOB
    ) LOB(image_data) STORE AS SECUREFILE (
      TABLESPACE SECURE_LOB_TS 
      COMPRESS HIGH 
      DEDUPLICATE
    );
--Table MEDICAL_IMAGES created.

CREATE TABLE test_basicfile (id NUMBER, data BLOB) 
  LOB(data) STORE AS BASICFILE;
  
CREATE TABLE test_basicfile (
      image_id NUMBER PRIMARY KEY,
      description VARCHAR2(100),
      image_data BLOB
    ) LOB(image_data) STORE AS basicfile (
      TABLESPACE SECURE_LOB_TS 
    );
--Table TEST_BASICFILE created.

--SecureFile启用HIGH压缩和去重

-- 查询表空间的空间管理方式
SELECT tablespace_name, segment_space_management 
FROM dba_tablespaces 
WHERE tablespace_name = 'SECURE_LOB_TS';

-- 查询LOB的存储属性
ALTER TABLE medical_images ALLOCATE EXTENT;
ALTER TABLE test_basicfile ALLOCATE EXTENT;
--
SELECT table_name, securefile, compression, deduplication 
FROM dba_lobs WHERE table_name='MEDICAL_IMAGES';
--
TABLE_NAME        SECUREFILE    COMPRESSION    DEDUPLICATION
_________________ _____________ ______________ ________________
MEDICAL_IMAGES    YES           HIGH           LOB

--
SELECT table_name, securefile, compression, deduplication 
FROM dba_lobs WHERE table_name='TEST_BASICFILE';
--
TABLE_NAME         SECUREFILE    COMPRESSION    DEDUPLICATION
__________________ _____________ ______________ ________________
TEST_BASICFILE    YES           NO             NO

三、典型应用场景与优化实践

​场景1：医疗PACS系统（BLOB存储）​​

CREATE TABLE ct_scans (
  scan_id NUMBER, 
  patient_id NUMBER,
  scan_data BLOB
    ) LOB(scan_data) STORE AS SECUREFILE (
      TABLESPACE SECURE_LOB_TS 
      COMPRESS HIGH 
      DEDUPLICATE
    );

场景2：文档管理（CLOB去重）​​

CREATE TABLE contracts (
  contract_id   NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  contract_name VARCHAR2(100),
  sign_date     DATE DEFAULT SYSDATE,
  contract_text CLOB
)
LOB(contract_text) STORE AS SECUREFILE (  -- 启用SecureFile特性
  TABLESPACE SECURE_LOB_TS
  COMPRESS HIGH          -- 启用高压缩
  DEDUPLICATE            -- 启用去重（初始状态）
  CACHE                  -- 缓存高频访问数据
);
--Table CONTRACTS created.
-- 测试：插入10,000份相似合同
DECLARE
  base_text CLOB := '本合同由甲方...（此处为1KB基础合同模板）';
BEGIN
  FOR i IN 1..10000 LOOP
    INSERT INTO contracts (contract_name, contract_text)
    VALUES (
      'CONTRACT_' || i,
      base_text || CHR(10) || '附加条款: ' || DBMS_RANDOM.STRING('X', 10)  -- 添加随机差异
    );
  END LOOP;
  COMMIT;
END;
/
--PL/SQL procedure successfully completed.
-- 查看实际空间占用
SELECT 
  segment_name,
  ROUND(bytes/1024/1024, 2) AS size_mb
FROM user_segments 
WHERE segment_name IN (
  SELECT segment_name 
  FROM user_lobs 
  WHERE table_name = 'CONTRACTS'
);
--
SEGMENT_NAME                    SIZE_MB
____________________________ __________
SYS_LOB0000073323C00004$$          0.25
--
-- 对比去重开关状态的空间差异
ALTER TABLE contracts MODIFY LOB(contract_text) (KEEP_DUPLICATES);  -- 关闭去重
ALTER TABLE contracts MODIFY LOB(contract_text) (DEDUPLICATE);      -- 启用去重

四、迁移与维护实战指南

​BasicFile → SecureFile迁移

-- 在线迁移
ALTER TABLE test_basicfile MOVE 
  LOB(image_data) STORE AS SECUREFILE (
      TABLESPACE SECURE_LOB_TS 
      COMPRESS HIGH 
      DEDUPLICATE
);
--Table TEST_BASICFILE altered.

-- 监控进度
SELECT table_name, securefile, compression, deduplication 
FROM dba_lobs WHERE table_name='TEST_BASICFILE';
TABLE_NAME        SECUREFILE    COMPRESSION    DEDUPLICATION
_________________ _____________ ______________ ________________
TEST_BASICFILE    YES           HIGH           LOB

维护操作​

-- 在线启用去重-无需重建表去重
ALTER TABLE contracts MODIFY LOB(contract_text) (
  DEDUPLICATE);
--Table CONTRACTS altered.
  -- 确认LOB类型为SecureFile
SELECT table_name, securefile 
FROM dba_lobs 
WHERE table_name = 'CONTRACTS';  -- 应返回"YES"
--
TABLE_NAME    SECUREFILE
_____________ _____________
CONTRACTS     YES

-- 检查去重状态
SELECT deduplication 
FROM dba_lobs 
WHERE table_name = 'CONTRACTS';
--
DEDUPLICATION
________________
LOB

•  统计信息收集

EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SYS',
tabname => 'MEDICAL_IMAGES',
method_opt => 'FOR COLUMNS image_data SIZE AUTO');
--
PL/SQL procedure successfully completed.

五、总结与最佳实践建议