LOB-part5-partition

LOBs支持LIST、RANGE和HASH分区,也支持复杂的堆表。截止12cR1,Oracle推荐在建表的时候同时创建分区,这个时候创建的LOB可以是inline也可以是out-of-line。

创建分区表
env 11.2.0.4

SQL> create table t1(id number,name clob)
  2  lob(name) store as securefile --默认inline
  3  partition by list(id)
  4  (
  5  partition p1 values(1),
  6  partition p2 values(2)
  7  );

Table created.

SQL>
SQL> create table t2(id number,name clob)
  2  lob(name) store as securefile
  3  (disable storage in row)   --out-of-line
  4  partition by list(id)
  5  (
  6  partition p1 values(1),
  7  partition p2 values(2)
  8  );

Table created.

SQL> select table_name,column_name,lob_index_name,def_in_row From user_part_lobs;

TABLE_NAME COLUMN_NAM LOB_INDEX_NAME                 DEF_IN_RO
---------- ---------- ------------------------------ ---------
T1         NAME       SYS_IL0000134938C00002$$       YES
T2         NAME       SYS_IL0000134947C00002$$       NO

SQL> select segment_name,partition_name,segment_type,tablespace_name from user_segments where segment_name = 'T1';

SEGME PARTI SEGMENT_TYPE    TABLESPACE
----- ----- --------------- ----------
T1    P1    TABLE PARTITION LOB1
T1    P2    TABLE PARTITION LOB2

迁移LOB partition

SQL> create table t(id number,create_Date date, name clob)
  2  lob(name) store as securefile
  3  partition by range(create_date)
  4  (partition p1 values less than (TO_DATE('2016-08', 'YYYY-MM')) TABLESPACE lob1,
  5  partition p2 values less than (TO_DATE('2016-09', 'YYYY-MM')) TABLESPACE lob2,
  6  partition pmax values less than(maxvalue) tablespace lob_test);

Split 分区

SQL> alter table t split partition pmax at(TO_DATE('2016-10', 'YYYY-MM')) into (partition pmax,partition p3 
tablespace lob3 lob(name) store as (tablespace lob3));

Table altered.

merge分区
This technique is useful for reclaiming unused partition space。

SQL> select table_name,partition_name,tablespace_name from user_tab_partitions where table_name = 'T';

TABLE PARTI TABLESPACE_NAME
----- ----- ---------------
T     P1    LOB1
T     P2    LOB2
T     P3    LOB3
T     PMAX  LOB_TEST

SQL> alter table t merge partitions p1,p2 into partition p_old tablespace lob_test lob(name) store as (tablespace lob1);

Table altered.
SQL> select table_name,partition_name,tablespace_name from user_tab_partitions where table_name = 'T';

TABLE PARTI TABLESPACE_NAME
----- ----- ---------------
T     P3    LOB3
T     PMAX  LOB_TEST
T     P_OLD LOB_TEST
SQL> select table_name,lob_name,partition_name,lob_partition_name,lob_indpart_name,in_row,tablespace_name 
from user_lob_partitions where table_name = 'T';

TABLE LOB_NAME                  PARTI LOB_PARTITION_NAME LOB_INDPART_NAME   IN_ROW    TABLESPACE_NAME
----- ------------------------- ----- ------------------ ------------------ --------- ---------------
T     SYS_LOB0000134995C00003$$ PMAX  SYS_LOB_P264       SYS_IL_P266        YES       LOB_TEST
T     SYS_LOB0000134995C00003$$ P3    SYS_LOB_P265       SYS_IL_P267        YES       LOB3
T     SYS_LOB0000134995C00003$$ P_OLD SYS_LOB_P268       SYS_IL_P269        YES       LOB1

LOB分区的INDEX
只有domain index和函数基索引(function-based indexes)支持LOB列创建index,其他类型比如unique index不支持LOB列创建index。

12c 分区表LOB的一些变化
前面的LOB文章有提到12c中db_securefile这个参数的设置已经从11g版本中的PERMITTED变更为12c下的PREFERRED。比如要进行版本升级,从老的10g或者11g升级到12c,有些分区表LOB列的原始定义是basicfile LOB,那么在升级之后会发生什么呢?

Env 12.1.0.2 pdb

SQL> alter session set db_securefile = permitted;

Session altered.

SQL> create table t(id number,create_Date date, name clob)
  2  lob(name) store as basicfile
  3  partition by range(create_date)
  4  (partition p1 values less than (TO_DATE('2016-08', 'YYYY-MM')) TABLESPACE lob_test,
  5  partition p2 values less than (TO_DATE('2016-09', 'YYYY-MM')) TABLESPACE lob_test);

Table created.

SQL> select partition_name,securefile from user_lob_partitions where table_name = 'T';

PARTITION_ SECURE
---------- ------
P1         NO
P2         NO

SQL> alter table t add partition p3 values less than (TO_DATE('2016-10', 'YYYY-MM')) lob(name) store as securefile;

Table altered.

SQL> select partition_name,securefile from user_lob_partitions where table_name = 'T';

PARTITION_ SECURE
---------- ------
P1         NO
P2         NO
P3         YES

SQL> alter session set db_securefile = preferred;
preferred;
Session altered.

SQL>
SQL> alter table t add partition p4 values less than (TO_DATE('2016-11', 'YYYY-MM'));--没有人工指定securefile,采用12c的默认参数

Table altered.

SQL> select partition_name,securefile from user_lob_partitions where table_name = 'T';

PARTITION_ SECURE
---------- ------
P4         YES
P1         NO
P2         NO
P3         YES

虽然在创建表的过程中我们制定了表级是使用basicfile,但是当参数为preferred的时候,新创建的分区为securefile模式。
接着看下12c中默认preferred参数下创建basicfile表的时候表和分区的LOB。

SQL> create table t1(id number,create_Date date, name clob)
  2  lob(name) store as basicfile
  3  partition by range(create_date)
  4  (partition p1 values less than (TO_DATE('2016-08', 'YYYY-MM')) TABLESPACE lob_test,
  5  partition p2 values less than (TO_DATE('2016-09', 'YYYY-MM')) TABLESPACE lob_test);

Table created.

SQL> create table t2(id number,create_Date date, name clob)
  2  lob(name) store as basicfile
  3  partition by range(create_date)
  4  (partition p1 values less than (TO_DATE('2016-08', 'YYYY-MM')) lob(name) store as basicfile TABLESPACE lob_test,
  5  partition p2 values less than (TO_DATE('2016-09', 'YYYY-MM')) lob(name) store as basicfile TABLESPACE lob_test);

Table created.

SQL> select table_name,securefile from user_lobs;
TABLE SECUREFIL
----- ---------
T1    NO
T2    NO


SQL> select table_name,partition_name,securefile from user_lob_partitions order by 1;

TABLE PARTI SECUREFIL
----- ----- ---------
T1    P1    YES
T1    P2    YES
T2    P1    NO
T2    P2    NO

在12c的默认的db_securefile的情况下创建分区表,即使在表级指定了使用basicfile,但是分区并没有继承使用basicfile,而是直接使用了securefile。只有在每个分区上显式的指定使用basicfile,分区表和分区才使用老的basicfile。当然,这都是在ASSM表空间管理模式下的情况,如果使用MSSM表空间管理的情况下Oracle还是会默认使用basicfile。

此条目发表在12c, LOB分类目录。将固定链接加入收藏夹。

发表评论

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 更改 )

Twitter picture

You are commenting using your Twitter account. Log Out / 更改 )

Facebook photo

You are commenting using your Facebook account. Log Out / 更改 )

Google+ photo

You are commenting using your Google+ account. Log Out / 更改 )

Connecting to %s