Oracle 使用LOB进行复合范围哈希间隔划分

askTom 2018-06-02

408

问题描述

嗨，

我想使用Range-Hash间隔分区方案使用LOB列对表进行分区。

但是我不确定在这种情况下确切的分区是如何分布的，我也注意到以下基于我如何指定分区LOB存储方案的差异。

方法1
------------

CREATE TABLE interval_tab1 (
  id            NUMBER,
  code          VARCHAR2(10),
  big_val       BLOB,
  created_date  DATE
)
PARTITION BY RANGE (created_date)
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
SUBPARTITION BY HASH (id)
SUBPARTITION TEMPLATE(
SUBPARTITION sp1  ,
SUBPARTITION sp2  ,
SUBPARTITION sp3  ,
SUBPARTITION sp4  )
(
   PARTITION part_01 values LESS THAN (TO_DATE('01-JUL-2018','DD-MON-YYYY'))
   LOB(big_val) STORE AS SECUREFILE (DISABLE STORAGE IN ROW CACHE LOGGING)
);


insert into interval_tab1 values(1,'AA1',utl_raw.cast_to_raw('sfhjdshafjhdsjkfhsdcmndxzA'),sysdate);
insert into interval_tab1 values(2,'AA2',utl_raw.cast_to_raw('sfhjdshafjhdsjkfhsdcmndxzB'),sysdate);
insert into interval_tab1 values(3,'AA3',utl_raw.cast_to_raw('sfhjdshafjhdsjkfhsdcmndxzC'),sysdate);
insert into interval_tab1 values(4,'AA4',utl_raw.cast_to_raw('sfhjdshafjhdsjkfhsdcmndxzD'),sysdate);

insert into interval_tab1 values(5,'AA1',utl_raw.cast_to_raw('sfhjdshafjhdsjkfhsdcmndxzA'),to_date('20180701','YYYYMMDD'));
insert into interval_tab1 values(6,'AA2',utl_raw.cast_to_raw('sfhjdshafjhdsjkfhsdcmndxzB'),to_date('20180701','YYYYMMDD'));
insert into interval_tab1 values(7,'AA3',utl_raw.cast_to_raw('sfhjdshafjhdsjkfhsdcmndxzC'),to_date('20180701','YYYYMMDD'));
insert into interval_tab1 values(8,'AA4',utl_raw.cast_to_raw('sfhjdshafjhdsjkfhsdcmndxzD'),to_date('20180701','YYYYMMDD'));

commit;

select * from USER_LOB_SUBPARTITIONS;
select segment_name, segment_type, tablespace_name from user_segments;

我看到当创建新的间隔分区时，LOB存储选项恢复为默认值。

方法2
---------

CREATE TABLE interval_tab (
  id            NUMBER,
  code          VARCHAR2(10),
  big_val       BLOB,
  created_date  DATE
)
LOB(big_val) STORE AS SECUREFILE (DISABLE STORAGE IN ROW CACHE LOGGING)
PARTITION BY RANGE (created_date)
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
SUBPARTITION BY HASH (id)
SUBPARTITIONS 4
(
   PARTITION part_01 values LESS THAN (TO_DATE('01-JUL-2018','DD-MON-YYYY'))
);

insert into interval_tab values(1,'AA1',utl_raw.cast_to_raw('sfhjdshafjhdsjkfhsdcmndxzA'),sysdate);
insert into interval_tab values(2,'AA2',utl_raw.cast_to_raw('sfhjdshafjhdsjkfhsdcmndxzB'),sysdate);
insert into interval_tab values(3,'AA3',utl_raw.cast_to_raw('sfhjdshafjhdsjkfhsdcmndxzC'),sysdate);
insert into interval_tab values(4,'AA4',utl_raw.cast_to_raw('sfhjdshafjhdsjkfhsdcmndxzD'),sysdate);

insert into interval_tab values(5,'AA1',utl_raw.cast_to_raw('sfhjdshafjhdsjkfhsdcmndxzA'),to_date('20180701','YYYYMMDD'));
insert into interval_tab values(6,'AA2',utl_raw.cast_to_raw('sfhjdshafjhdsjkfhsdcmndxzB'),to_date('20180701','YYYYMMDD'));
insert into interval_tab values(7,'AA3',utl_raw.cast_to_raw('sfhjdshafjhdsjkfhsdcmndxzC'),to_date('20180701','YYYYMMDD'));
insert into interval_tab values(8,'AA4',utl_raw.cast_to_raw('sfhjdshafjhdsjkfhsdcmndxzD'),to_date('20180701','YYYYMMDD'));

commit;

select * from USER_LOB_SUBPARTITIONS;
select segment_name, segment_type, tablespace_name from user_segments;

此方法将保留跨初始分区和新间隔分区的LOB存储参数。

如果我想使LOB存储在子分区级别上分布，我是否遵循正确的方法？，还有其他选择吗？我试图在模板级别指定LOB存储参数，但它不起作用。

对于这两种方法，user_segments仅显示2个lob段，索引和lob，为什么它没有分布到子分区级别？

专家解答

因此，您希望在 (子) 分区级别具有相同的lob存储设置？

您可以通过在表级别而不是分区级别定义lob存储来克服此问题:

DROP TABLE interval_tab1 PURGE;

CREATE TABLE interval_tab1 (
  id            NUMBER,
  code          VARCHAR2(10),
  big_val       BLOB,
  created_date  DATE
)
LOB(big_val) STORE AS SECUREFILE (DISABLE STORAGE IN ROW CACHE LOGGING)
PARTITION BY RANGE (created_date)
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
SUBPARTITION BY HASH (id)
SUBPARTITION TEMPLATE(
SUBPARTITION sp1  ,
SUBPARTITION sp2  ,
SUBPARTITION sp3  ,
SUBPARTITION sp4  )
(
   PARTITION part_01 values LESS THAN (TO_DATE('01-JUL-2018','DD-MON-YYYY'))
);

insert into interval_tab1 values(1,'AA1',utl_raw.cast_to_raw('sfhjdshafjhdsjkfhsdcmndxzA'),sysdate);
insert into interval_tab1 values(2,'AA2',utl_raw.cast_to_raw('sfhjdshafjhdsjkfhsdcmndxzB'),sysdate);
insert into interval_tab1 values(3,'AA3',utl_raw.cast_to_raw('sfhjdshafjhdsjkfhsdcmndxzC'),sysdate);
insert into interval_tab1 values(4,'AA4',utl_raw.cast_to_raw('sfhjdshafjhdsjkfhsdcmndxzD'),sysdate);

insert into interval_tab1 values(5,'AA1',utl_raw.cast_to_raw('sfhjdshafjhdsjkfhsdcmndxzA'),to_date('20180701','YYYYMMDD'));
insert into interval_tab1 values(6,'AA2',utl_raw.cast_to_raw('sfhjdshafjhdsjkfhsdcmndxzB'),to_date('20180701','YYYYMMDD'));
insert into interval_tab1 values(7,'AA3',utl_raw.cast_to_raw('sfhjdshafjhdsjkfhsdcmndxzC'),to_date('20180701','YYYYMMDD'));
insert into interval_tab1 values(8,'AA4',utl_raw.cast_to_raw('sfhjdshafjhdsjkfhsdcmndxzD'),to_date('20180701','YYYYMMDD'));

commit;

DROP TABLE interval_tab PURGE;

CREATE TABLE interval_tab (
  id            NUMBER,
  code          VARCHAR2(10),
  big_val       BLOB,
  created_date  DATE
)
LOB(big_val) STORE AS SECUREFILE (DISABLE STORAGE IN ROW CACHE LOGGING)
PARTITION BY RANGE (created_date)
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
SUBPARTITION BY HASH (id)
SUBPARTITIONS 4
(
   PARTITION part_01 values LESS THAN (TO_DATE('01-JUL-2018','DD-MON-YYYY'))
);

insert into interval_tab values(1,'AA1',utl_raw.cast_to_raw('sfhjdshafjhdsjkfhsdcmndxzA'),sysdate);
insert into interval_tab values(2,'AA2',utl_raw.cast_to_raw('sfhjdshafjhdsjkfhsdcmndxzB'),sysdate);
insert into interval_tab values(3,'AA3',utl_raw.cast_to_raw('sfhjdshafjhdsjkfhsdcmndxzC'),sysdate);
insert into interval_tab values(4,'AA4',utl_raw.cast_to_raw('sfhjdshafjhdsjkfhsdcmndxzD'),sysdate);

insert into interval_tab values(5,'AA1',utl_raw.cast_to_raw('sfhjdshafjhdsjkfhsdcmndxzA'),to_date('20180701','YYYYMMDD'));
insert into interval_tab values(6,'AA2',utl_raw.cast_to_raw('sfhjdshafjhdsjkfhsdcmndxzB'),to_date('20180701','YYYYMMDD'));
insert into interval_tab values(7,'AA3',utl_raw.cast_to_raw('sfhjdshafjhdsjkfhsdcmndxzC'),to_date('20180701','YYYYMMDD'));
insert into interval_tab values(8,'AA4',utl_raw.cast_to_raw('sfhjdshafjhdsjkfhsdcmndxzD'),to_date('20180701','YYYYMMDD'));

commit;

select table_name, subpartition_name, securefile, cache, in_row 
from   USER_LOB_SUBPARTITIONS
order  by 1, 2;

TABLE_NAME      SUBPARTITION_NAME   SECUREFILE   CACHE   IN_ROW   
INTERVAL_TAB    SYS_SUBP2578        YES          YES     NO       
INTERVAL_TAB    SYS_SUBP2579        YES          YES     NO       
INTERVAL_TAB    SYS_SUBP2580        YES          YES     NO       
INTERVAL_TAB    SYS_SUBP2581        YES          YES     NO       
INTERVAL_TAB    SYS_SUBP2592        YES          YES     NO       
INTERVAL_TAB    SYS_SUBP2593        YES          YES     NO       
INTERVAL_TAB    SYS_SUBP2594        YES          YES     NO       
INTERVAL_TAB    SYS_SUBP2595        YES          YES     NO       
INTERVAL_TAB1   PART_01_SP1         YES          YES     NO       
INTERVAL_TAB1   PART_01_SP2         YES          YES     NO       
INTERVAL_TAB1   PART_01_SP3         YES          YES     NO       
INTERVAL_TAB1   PART_01_SP4         YES          YES     NO       
INTERVAL_TAB1   SYS_SUBP2563        YES          YES     NO       
INTERVAL_TAB1   SYS_SUBP2564        YES          YES     NO       
INTERVAL_TAB1   SYS_SUBP2565        YES          YES     NO       
INTERVAL_TAB1   SYS_SUBP2566        YES          YES     NO

oracle asktom

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

Oracle 使用LOB进行复合范围哈希间隔划分

问题描述

专家解答

评论