Oracle LOB类型的分区(?)存储

官方文档: Oracle Database Database SecureFiles and Large Objects Developer's Guide, 19c

环境:

CDB名称: kmg

PDB名称: PM

用户名: 也叫PM

db_*k_cache_size

-- 默认是0, 不能创建对应"块"大小的表空间
-- 更改对应"块"缓存
alter system set db_32k_cache_size=32M scope=both;

创建表空间

create tablespace tbs_32k_01 
  datafile '/u01/app/oracle/oradata/KMG/PM/tbs_32k_01.dbf' 
  size 50M 
  autoextend on 
  blocksize 32k 
  extent management local 
  uniform size 2M;
create tablespace tbs_32k_02 
  datafile '/u01/app/oracle/oradata/KMG/PM/tbs_32k_02.dbf' 
  size 50M 
  autoextend on 
  blocksize 32k 
  extent management local 
  uniform size 2M;

建表

普通表

-- 把LOB字段存储到另一个表空间里:
create table t_papers(
id number(3,0),
author varchar2(64), 
title varchar2(128), 
release_date date, 
contnt clob) 
tablespace users
lob(contnt) store as 
(tablespace tbs_32k_01 chunk 32k storage(initial 2M next 2M) disable storage in row);

分区表

-- 分区存储, 但是将大字段存到另一个表空间里:
create table t_papers(
id number(3,0),
author varchar2(64), 
title varchar2(128), 
release_date date, 
contnt clob) 
lob(contnt) store as 
(tablespace tbs_32k_01 chunk 32k storage(initial 2M next 2M) disable storage in row)
 partition by range (id)
  ( partition pprs_range_p1 values less than (100) tablespace tbs_01,
    partition pprs_range_p2 values less than (200) tablespace tbs_02,
    partition pprs_range_p3 values less than (300) tablespace tbs_03,
    partition pprs_range_other values less than (maxvalue) tablespace tbs_04);

进一步分区

注意! 这不是子分区! 

-- 分区存储, 且大字段也分别存储到不同的表空间里, 并且指定详细的存储方式
-- 指定到TBS_32K_01里的数据是`不去重`, `不压缩`
-- 指定到TBS_32K_02里的数据是`去重`, `高压缩`
-- 注意: 这不是子分区！
create table t_papers(
id number(3,0),
author varchar2(64), 
title varchar2(128), 
release_date date, 
contnt clob)
partition by range (id)
(
  partition pprs_range_p1 values less than (500) tablespace tbs_01
    lob(contnt) store as securefile (tablespace tbs_32k_01 chunk 32k storage(initial 2m next 2m) disable storage in row keep_duplicates nocompress),
  partition pprs_range_other values less than (maxvalue) tablespace tbs_02
    lob(contnt) store as securefile (tablespace tbs_32k_02 chunk 32k storage(initial 2m next 2m) disable storage in row deduplicate compress high)
);

验证

-- 生成数据: 共20条, 两个表空间分别10条。
-- clob字段长度: 10^6
TRUNCATE TABLE T_PAPERS;

DECLARE
  V_TXT CLOB;
BEGIN
  FOR I IN 490..499 LOOP
    V_TXT:='';
    FOR J IN 1..2500 LOOP
      V_TXT:=V_TXT||DBMS_RANDOM.STRING('P',4000);
    END LOOP;
    INSERT INTO T_PAPERS(ID,CONTNT) VALUES(I,V_TXT);
    INSERT INTO T_PAPERS(ID,CONTNT) VALUES(I+10,V_TXT);
  END LOOP;
  COMMIT;
END;
/
-- 查看
SELECT ID FROM T_PAPERS PARTITION(PPRS_RANGE_P1);
SELECT ID FROM T_PAPERS PARTITION(PPRS_RANGE_OTHER);

-- (SYS执行)查看clob字段占用大小
select dl.owner,
       dl.table_name,
       dl.column_name,
       dl.tablespace_name table_tbs_name,
       ds.tablespace_name column_tbs_name,
       dl.chunk,
       ds.segment_name,
       ds.BYTES / 1024 / 1024 size_mb
  from dba_lobs dl
  join dba_segments ds
    on (dl.segment_name = ds.segment_name)
 where dl.owner = 'PM'
   and dl.table_name = 'T_PAPERS'
 order by size_mb desc;
-- 结果:
OWNER  TABLE_NAME  COLUMN_NAME  TABLE_TBS_NAME  COLUMN_TBS_NAME	 CHUNK   SEGMENT_NAME			   SIZE_MB
-----  ----------  -----------  --------------  ---------------  ------  ------ ------------------  -------
PM	   T_PAPERS    CONTNT       USERS           TBS_32K_01       32768   SYS_LOB0000074123C00005$$     222
PM	   T_PAPERS    CONTNT       USERS           TBS_32K_02       32768   SYS_LOB0000074123C00005$$     120
-- 两个表空间(32k)里存的相同的数据, 但是占用的空间相差很大.