Skip to Main Content
  • Questions

  • Composite Range-Hash interval partitioning with LOB

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Rasike.

Asked: June 02, 2018 - 5:40 am UTC

Last updated: June 18, 2018 - 2:10 pm UTC

Version: 11.2.0.4

Viewed 1000+ times

You Asked

Hi,

I would like to partition a table with a LOB column using Range-Hash interval partitioning scheme.

But I not sure how the exact partition gets distributed in this scenario and also I noticed following differences based on how I specify my partition LOB storage scheme.

Method 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;


I am seeing that LOB storage options are getting back to defaults when a new interval partition gets created.

Method 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;


This method will preserve the LOB storage parameters across initial partition and new interval partition.

If I want to make the LOB storage to get distributed at subpartition level , am I following correct method ? , is there any other options ? I tried to specify the LOB storage parameters at template level but it did not work.

For both methods user_segments show only 2 segments for lobs , index and the lob only , why is it not getting distributed to subpartition level ?

with LiveSQL Test Case:
https://livesql.oracle.com/apex/livesql/s/grxzi2gnmmi0iey6e6bxbexz2

and Chris said...

So you want the same lob storage settings at the (sub)partition level?

You can overcome this by defining lob storage at the table level, instead of the partition level:

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

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

LOB column using Range-Hash interval partitioning scheme

Rasike Gomes, June 12, 2018 - 1:35 am UTC

Thanks Chris ! , I do have a follow-up, it is to do with the securefile option. Is it still valid to specify the disable/enable in row option in the context of securefile storage option?
Chris Saxon
June 18, 2018 - 2:10 pm UTC

Yes:

create table t (
  c1 clob,
  c2 clob
) lob (c1) store as securefile ( enable storage in row ),
  lob (c2) store as securefile ( disable storage in row );

select column_name, in_row 
from   user_lobs
where  table_name = 'T';

COLUMN_NAME   IN_ROW   
C1            YES      
C2            NO

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.