发表 xbdba 12c新特性17 分钟读完 (大约2491个字)

12c中维护多分区

12c中可以很方便的通过简单的语句同时操作多个分区，其中包括drop和truncate多个分区，多个分区合并成一个，一个拆分成多个。

DROP/TRUNCATE多个分区

从12c开始可以用一个单独的语句就能维护多个分区，比如drop或truncate多个分区，不过如果表含有域索引，则只能一次删除或截取一个分区。执行这些操作时，分区里的数据也会被清除，如果你想保留这些数据，那么需要用MERGE PARTITIONS方式。

除此之外，在这些操作的过程中，全局索引的维护可以延迟进行，这样即使删除了分区，全局索引仍然可用，这样保证了在业务高峰期我们也能进行分区的删除操作，而索引的维护可以放在低峰时期。

删除分区

在ALTER TABLE语句中可以通过DROP PARTITIONS或DROP SUBPARTITIONS来对range或list的多个分区或子分区进行删除。

1
2
3
4
5
6
7
8
9
10
11
12
13
drop table t1 purge;
create table t1 (a number,b number,c number)
    partition by range(a)
    (partition p1 values less than (1000),
    partition p2 values less than(2000),
    partition p3 values less than(3000),
    partition p4 values less than(4000));

insert into t1 select q*1000,rownum,rownum from dba_objects, ( select rownum q from dual connect by level >= 2);
commit;

create index idx_local on t1(b) local;
create index idx_global on t1(c);

删除分区

1
2
3
4
ALTER TABLE t1
DROP PARTITIONS p1,p2;

Table altered.

检查分区和索引

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT TABLE_NAME,
       partition_name
FROM user_tab_partitions
WHERE TABLE_NAME='T1';

TABLE_NAME PARTITION_NAME
---------- ---------------------------------------------
T1         P3
T1         P4

SELECT index_name,
       status
FROM user_indexes
WHERE table_name='T1';

INDEX_NAME STATUS
---------- --------
IDX_LOCAL  N/A
IDX_GLOBAL UNUSABLE

这里不能删除表的所有分区，如果只剩一个分区，那么就只能删除表。

当你删除多个分区时，全局和分区索引的维护跟你删除单个分区是一样的。删除分区时分区索引也会同时删除，全局索引需要重建。

截断分区

当使用ALTER TABLE … TRUNCATE PARTITION语句来清除表分区里的数据时，可以同时操作多个分区。

截断分区与删除分区类似

1
2
3
4
5
6
7
8
9
10
11
12
13
drop table t1 purge;
create table t1 (a number,b number,c number)
    partition by range(a)
    (partition p1 values less than (1000),
    partition p2 values less than(2000),
    partition p3 values less than(3000),
    partition p4 values less than(4000));

insert into t1 select q*1000,rownum,rownum from dba_objects, ( select rownum q from dual connect by level >= 2);
commit;

create index idx_local on t1(b) local;
create index idx_global on t1(c);

截断分区

1
2
3
ALTER TABLE T1 TRUNCATE PARTITIONS P1,P2;

Table truncated.

检查分区和索引

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SELECT TABLE_NAME,
       partition_name
FROM user_tab_partitions
WHERE TABLE_NAME='T1';

TABLE_NAME PARTITION_NAME
---------- ----------------------------------------
T1         P1
T1         P2
T1         P3
T1         P4


SELECT index_name,
       status
FROM user_indexes
WHERE table_name='T1';

INDEX_NAME STATUS
---------- --------
IDX_LOCAL  N/A
IDX_GLOBAL UNUSABLE

全局索引必须要进行重建，除非之前指定了UPDATE INDEXES或UPDATE GLOBAL INDEXES。

索引维护

drop或truncate分区时，默认是采用异步维护全局索引的方式，然而UPDATE INDEXES语句同样需要带上，是为了向下兼容。

全局索引的维护是与DROP或TRUNCATE分区的维护独立开来的，从而不需要使全局索引失效。索引的维护是采用异步的方式，可以推迟到之后的某个时间点。推迟全局索引的维护是为了在不影响索引可用性的前提下来错峰进行，这样就大大加快了drop和truncate分区的速度和减少了操作过程中资源的消耗。

为了加快删除分区的速度，只会先维护索引的字典信息。这个功能在字典中维护数据对象编号的列表，那些被删除或阶段的对象所对应的索引由于失效而被忽略掉不进行维护。

索引的维护操作能自动的通过定时任务SYS.PMO_DEFERRED_GIDX_MAINT_JOB来清理所有的全局索引。默认是在每天凌晨2点进行，当然你也可以手动运行这个job。

DBMS_PART包提供了一个维护和管理分区对象的接口，作为优先使用异步维护全局索引的结果，全局索引可以包含指向那些已经不存在数据段的条目，这些陈旧的索引行在对表或索引上进行查询、DDL、DML操作时都不会导致任何影响正确性的问题。

DBMS_PART.CLEANUP_GIDX过程会确认和清除这些全局索引来保证存储的效率和性能。

局部全局索引优化

视图USER_INDEXES和USER_IND_PARTITIONS中新增了ORPHANED_ENTRIES字段，这个字段用来表示全局索引是否包含因为延迟索引维护产生的陈旧的条目。

字段可以为以下三种值：

  • YES:索引包含单独的条目
  • NO:索引不包含任何单独的条目
  • N/A：该属性不适用-针对分区索引或非分区表上的索引

你可以强制清理那些需要维护的索引：

  • DBMS_PART.CLEANUP_GIDX：收集那些需要被清理的全局索引，用来将这些索引恢复到正常状态
1
exec DBMS_PART.CLEANUP_GIDX('SCHEMA','INDEXNAME');
  • ALTER INDEX REBUILD [PARTITION]: 重建整个索引或索引分区，重建完之后的索引不会包含任何陈旧的条目
1
ALTER INDEX INDEXNAME REBUILD;
  • ALTER INDEX COALESCE [PARTITION] CLEANUP: 清理所有索引块中孤立的条目
1
ALTER INDEX  INDEXNAME COALESCE CLEANUP;

合并多个分区

12c开始允许将多个分区或子分区合并到一个分区或子分区。

range分区/子分区

当合并多个range分区时，分区必须是相邻的，并按分区的边界值的升序指定。

新分区继承了原分区的最高边界

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE TABLE prod_list
(Prod_name VARCHAR2(30),
Prod_id   NUMBER(4))
PARTITION BY RANGE(prod_id)
(
PARTITION prod_1 VALUES LESS THAN (100),
PARTITION prod_2 VALUES LESS THAN (200),
PARTITION prod_3 VALUES LESS THAN (300),
PARTITION prod_4 VALUES LESS THAN (400),
PARTITION prod_5 VALUES LESS THAN (500),
PARTITION prod_6 VALUES LESS THAN (600),
PARTITION prod_other VALUES LESS THAN (MAXVALUE)
);

ALTER TABLE prod_list MERGE PARTITIONS prod_1,prod_2,prod_3 INTO partition old;

将prod_list分区表中的三个分区合并成了一个，新分区中包含了之前三个分区代表的最早的商品ID，表示那些还未售出的商品。

你可以不用将每个分区都一一列出来，而是直接指定最小和最大的分区，中间通过TO关键字来表示范围

1
ALTER TABLE prod_list MERGE PARTITIONS prod_1 TO prod_3 INTO partition old;

LIST或系统分区/子分区

对于LIST分区来说，合并的时候就不需要相邻了，因为不需要分区是有序。另外不能将range表分区使用的TO语法来指定LIST分区。

当合并多个LIST分区，生成的分区包含的数据为原分区所有值的集合的合并。

DEFAULT的list分区与其他list分区合并产生的结果依然是DEFAULT分区。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE TABLE sales_list
(salesman_name VARCHAR2(30),
sales_state   VARCHAR2(20))
PARTITION BY LIST(sales_state)
(
PARTITION sales_CA VALUES('California'),
PARTITION sales_NY VALUES ('New York'),
PARTITION sales_NJ VALUES ('New Jersey'),
PARTITION sales_CT VALUES ('Connecticut'),
PARTITION sales_PA VALUES ('Pennsylvania'),
PARTITION sales_IL VALUES('Illinois'),
PARTITION sales_other VALUES(DEFAULT) 
);

ALTER TABLE sales_list MERGE PARTITIONS sales_NY, sales_NJ, sales_CT INTO PARTITION TRI_STATE;

单分区分离成多个

跟前面的合并类似，只不过操作是刚好相反

Range分区

将一个range分区prod100分离成多个分区，用于表示prod25,prod50,prod75和prod100，prod100分区源自原来的prod100分区的最高边界

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
drop table prod_list
CREATE TABLE prod_list
(Prod_name VARCHAR2(30),
Prod_id   NUMBER(4))
PARTITION BY RANGE(prod_id)
(
PARTITION prod100 VALUES LESS THAN (100),
PARTITION prod200 VALUES LESS THAN (200),
PARTITION prod300 VALUES LESS THAN (300),
PARTITION prodother VALUES LESS THAN (MAXVALUE)
);

alter table prod_list split partition prod100 into 
  (partition prod25 values less than (25),
   partition prod50 values less than (50),
   partition prod75 values less than (75),
   partition prod100);

换一个日期的例子，将表range_sales的sales_q1_2012分区分离成3个分区，用于表示第一季度的三个月份。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
CREATE TABLE range_sales
    ( prod_id        NUMBER(6)
    , cust_id        NUMBER
    , time_id        DATE
    , quantity_sold  NUMBER(3)
    , amount_sold    NUMBER(10,2)
    ) 
PARTITION BY RANGE (time_id)
  (PARTITION SALES_Q1_2012 VALUES LESS THAN (TO_DATE('01-APR-2012','DD-MON-YYYY')),
   PARTITION SALES_Q2_2012 VALUES LESS THAN (TO_DATE('01-JUL-2012','DD-MON-YYYY')),
   PARTITION SALES_Q3_2012 VALUES LESS THAN (TO_DATE('01-OCT-2012','DD-MON-YYYY')),
   PARTITION SALES_Q4_2012 VALUES LESS THAN (MAXVALUE));

   ALTER TABLE range_sales SPLIT PARTITION sales_q1_2012 INTO
   (PARTITION sales_jan_2012 values less than (TO_DATE('01-FEB-2012','dd-MON-yyyy')),
   PARTITION sales_feb_2012 values less than (TO_DATE('01-MAR-2012','dd-MON-yyyy')),
   PARTITION sales_mar_2012);

要将range分区表拆分成N个分区，需要在要分割分区的分区范围内指定(N-1)个分区键值。新分区的上界值需要按升序排序，第N个新分区的上界为被分割的分区的上界，第N个新分区的名称和物理属性可以在拆分时指定。

List分区

customer分区表被分割成3个

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE TABLE sales_list
(salesman_name VARCHAR2(30),
sales_state   VARCHAR2(20))
PARTITION BY LIST(sales_state)
(
PARTITION sales_west VALUES('California', 'Hawaii'),
PARTITION sales_east VALUES ('New York', 'Virginia', 'Florida'),
PARTITION sales_central VALUES('Texas', 'Illinois'),
PARTITION sales_other VALUES(DEFAULT) 
);

ALTER TABLE sales_list SPLIT PARTITION sales_east INTO
   (PARTITION NY values ('New York'),
    PARTITION FL values ('Florida'),
    PARTITION rest_sales_east);

将LIST分区拆分成N个分区时，需要指定（N-1）个分区键值，每个分区键值代表了（N-1）个分区需要插入的值。源分区余下的值则都被插入了第N个分区当中。前（N-1）个列值不能包含当前所有分区的值，因为这样的话第N个分区就为空了。同样新的（N-1）个分区不能包含任何当前分区不存在的值。

拆分多个分区的限制：

  • 不适用hash分区
  • 不能对索引组织表使用并行
  • 如果表是索引组织表或则表上有本地域索引，那么你只能将分区拆分成2个新分区
发表 xbdba 12c新特性7 分钟读完 (大约1026个字)

12c新特性：只读分区

除了只读表以外，12c中引入了只读分区特性，每个分区或子分区可以独立的设置成只读或者读写，这样对于那些需要对指定分区数据的访问权限做控制的情况带来了方便。如果这个分区或子分区被设置成了只读模式，那么任何对这个分区或子分区的数据进行修改的操作都会报错，这样对这些数据进行了很好的保护。

只读分区：

  • 只读属性确保了数据的不变
  • 数据的不变并不会阻止表的DDL操作，比如新增和修改列
  • 表和分区或子分区都可以被设成只读

只读对象与只读表空间：

  • 只读表空间保护物理文件不被修改

    允许那些不接触存储的DDL操作，比如ALTER TABLE SET UNUSED,DROP TABLE

    不保证数据的不变

  • 只读对象保护数据不被修改

    数据的不变

    不阻止对存储的变化操作，比如ALTER TABLE MOVE COMPRESS,ALTER TABLE MERGE PARTITIONS等

只读分区

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
drop table orders purge;

create table orders(order_id number,order_date date) read write
PARTITION BY RANGE(order_date)
( partition q1_2015 values less than (to_date('2014-10-01','yyyy-mm-dd')) READ ONLY,
 partition q2_2015 values less than (to_date('2015-01-01','yyyy-mm-dd')) READ ONLY,
 partition q3_2015 values less than (to_date('2015-04-01','yyyy-mm-dd')),
 partition q4_2015 values less than (to_date('2015-07-01','yyyy-mm-dd'))
);

# 查看user_tab_partitions视图
set linesize 300
col table_name for a30
col partition_name for a30
col read_only for a30
select table_name,partition_name,read_only from user_tab_partitions where table_name='ORDERS';

TABLE_NAME                     PARTITION_NAME                 READ_ONLY
------------------------------ ------------------------------ ------------------------------
ORDERS                         Q1_2015                        YES
ORDERS                         Q2_2015                        YES
ORDERS                         Q3_2015                        NO
ORDERS                         Q4_2015                        NO

这里orders表默认是读写属性，但是因为q1_2015和q2_2015两个分区指明了是只读，所以无法进行数据的修改，而其他两个分区则为可读写。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
xb@PDB12C> insert into orders values(1,'2014-09-01');
insert into orders values(1,'2014-09-01')
*
ERROR at line 1:
ORA-14466: Data in a read-only partition or subpartition cannot be modified.


xb@PDB12C> insert into orders values(1,'2015-06-01');

1 row created.

xb@PDB12C> rollback;

Rollback complete.

同样也可以将表默认设置成只读模式，部分分区设成读写模式。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
drop table orders purge;

create table orders(order_id number,order_date date) read only
PARTITION BY RANGE(order_date)
( partition q1_2015 values less than (to_date('2014-10-01','yyyy-mm-dd')) READ write,
 partition q2_2015 values less than (to_date('2015-01-01','yyyy-mm-dd')) ,
 partition q3_2015 values less than (to_date('2015-04-01','yyyy-mm-dd')),
 partition q4_2015 values less than (to_date('2015-07-01','yyyy-mm-dd'))
);

set linesize 300
col table_name for a30
col partition_name for a30
col read_only for a30
select table_name,partition_name,read_only from user_tab_partitions where table_name='ORDERS';

TABLE_NAME                     PARTITION_NAME                 READ_ONLY
------------------------------ ------------------------------ ------------------------------
ORDERS                         Q1_2015                        NO
ORDERS                         Q2_2015                        YES
ORDERS                         Q3_2015                        YES
ORDERS                         Q4_2015                        YES

只读子分区

1
2
3
4
5
6
7
8
9
10
11
12
13
14
drop table orders purge;

create table orders(order_id number,order_date date) read write
PARTITION BY list(order_id) 
subpartition by range(order_date)(
partition q1 values(1) read only
( subpartition q1_2014 values less than (to_date('2014-10-01','yyyy-mm-dd')),
 subpartition q1_2015 values less than (to_date('2015-01-01','yyyy-mm-dd')) 
),
partition q2 values(2)
( subpartition q2_2014 values less than (to_date('2014-10-01','yyyy-mm-dd')) read only,
 subpartition q2_2015 values less than (to_date('2015-01-01','yyyy-mm-dd')) 
)
);

这里注意到，对于表来说默认是读写，但对于q1分区默认却是只读，所以q1下的两个子分区都是只读状态，而q2分区默认是读写，q2_2014子分区是只读，另一个也是可读写，通过这个例子说明了在表、分区、子分区三个级别都可以独立的设置读写或只读属性。

1
2
3
4
5
6
7
8
xb@PDB12C> select partition_name, subpartition_name, read_only from user_tab_subpartitions where table_name ='ORDERS';

PARTITION_NAME                 SUBPARTITION_NAME              READ_ONLY
------------------------------ ------------------------------ ------------------------------
Q1                             Q1_2014                        YES
Q1                             Q1_2015                        YES
Q2                             Q2_2014                        YES
Q2                             Q2_2015                        NO

分区或子分区的读写状态也是可以变更的

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
xb@PDB12C> alter table orders modify partition q1 read write;

Table altered.

xb@PDB12C> alter table orders modify subpartition q1_2014 read only;

Table altered.

xb@PDB12C> select partition_name, subpartition_name, read_only from user_tab_subpartitions where table_name ='ORDERS';

PARTITION_NAME                 SUBPARTITION_NAME              READ_ONLY
------------------------------ ------------------------------ ------------------------------
Q1                             Q1_2014                        YES
Q1                             Q1_2015                        NO
Q2                             Q2_2014                        YES
Q2                             Q2_2015                        NO

而状态为只读的子分区也是无法修改数据的

1
2
3
4
5
6
7
8
9
10
11
12
13
14
xb@PDB12C> insert into orders values(1,'2014-06-01');
insert into orders values(1,'2014-06-01')
*
ERROR at line 1:
ORA-14466: Data in a read-only partition or subpartition cannot be modified.


xb@PDB12C> insert into orders values(1,'2014-11-01');

1 row created.

xb@PDB12C> rollback;

Rollback complete.
发表 xbdba 12c新特性8 分钟读完 (大约1155个字)

12c新特性：外部表分区

从12.2版本开始允许对外部表进行分区化，让外部表也能享受到分区带来的便利性，比如分区裁剪和智能分区关联等。对于现在外部数据量较大的情况，对外部表的分区提供更高的查询效率和提升了数据的可维护性。

外部表分区与其他分区表类似，但是也有一些差异。外部表的文件可以存储在文件系统上，或者Hadoop分布式文件系统上。绝大部分一般分区策略都可以适用在分区外部表上，对于一个分区外部表来说，每个分区的存储都可以单独通过指定目录和文件来确定。

创建分区外部表

如果要创建一个简单的分区外部表，只需要通过CREATE TABLE语句配合ORGANIZATION短句再加上PARTITION BY，如果是复合外部分区表，则要加上SUBPARTITION BY。PARTITION BY和SUBPARTITION BY指定了每个分区和子分区的外部文件存放的位置。

新建两个目录，分别赋权给用户

1
2
3
4
5
create or replace directory dir1 as '/tmp/dir1';
create or replace directory dir2 as '/tmp/dir2';

grant read,write on directory dir1 to xb;
grant read,write on directory dir2 to xb;

构建一些数据，生成外部文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
set markup csv on quote on
set trimspool on linesize 1000 feedback off pagesize 0

set echo off
set feedback off
set pagesize 0
set termout off
set heading off
spool /tmp/p1.txt
select name, object_id, owner, object_name
  from (select 'HU' name, rownum rn, object_id, owner, object_name
          from dba_objects)
 where rn >= 500;
spool off

spool /tmp/p2.txt
select name, object_id, owner, object_name
  from (select 'BJ' name, rownum rn, object_id, owner, object_name
          from dba_objects)
 where rn >= 1000
   and rn > 500;
spool off

spool /tmp/p3.txt
select name, object_id, owner, object_name
  from (select 'SH' name, rownum rn, object_id, owner, object_name
          from dba_objects)
 where rn >= 1500
   and rn >1000;
spool off

然后根据上面生成的三个外部文件来创建外部表，每个分区可以指定单独的目录

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
DROP TABLE t_ext purge;

create table t_ext (
  name  varchar2(2),
  object_id     number,
  owner         varchar2(100),
  object_name   varchar2(100)
)
organization external (
  type oracle_loader
  default directory dir1
  access parameters (
    fields csv with embedded terminated by ',' optionally enclosed by '"'
    missing field values are null (
      name,
      object_id,
      owner,
      object_name 
    )
  )
)
reject limit unlimited
partition by list (name) (
  partition p1 values ('HU') location ('p1.txt'),
  partition p2 values ('BJ') location ('p2.txt'),
  partition p3 values ('SH') default directory dir2 location ('p3.txt'),
  partition pdefault values (default)
);

数据查询都正常

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
xb@PDB12C> select * from t_ext partition(p1) where rownum>10;

NA  OBJECT_ID OWNER                OBJECT_NAME
-- ---------- -------------------- --------------------
HU         16 SYS                  TS$
HU         20 SYS                  ICOL$
HU          8 SYS                  C_FILE#_BLOCK#
HU         37 SYS                  I_OBJ2
HU         22 SYS                  USER$
HU         33 SYS                  I_TAB1
HU         40 SYS                  I_OBJ5
HU         31 SYS                  CDEF$
HU         41 SYS                  I_IND1

9 rows selected.

xb@PDB12C> select * from t_ext partition(p2) where rownum>10;

NA  OBJECT_ID OWNER                OBJECT_NAME
-- ---------- -------------------- --------------------
BJ        503 SYS                  SVCOBJ_ACCESS$
BJ        504 SYS                  IDX1_SVCOBJ_ACCESS$
BJ        505 SYS                  SVCOBJ_ACCESS_ATTR$
BJ        506 SYS                  CODEAUTH$
BJ        507 SYS                  I_CODEAUTH1
BJ        508 SYS                  OBJECT_GRANT
BJ        509 SYS                  SYSTEM_GRANT
BJ        510 SYS                  USER_GRANT
BJ        512 SYS                  AUDSES$

9 rows selected.

xb@PDB12C> select * from t_ext partition(p3) where rownum>10;

NA  OBJECT_ID OWNER                OBJECT_NAME
-- ---------- -------------------- --------------------
SH       1007 SYS                  STREAMS$_DEST_OBJS
SH       1008 SYS                  STREAMS$_DEST_OBJS_I
SH       1020 SYS                  STREAMS$_SM_ID
SH       1021 SYS                  FGR$_FILE_GROUPS
SH       1022 SYS                  I_FGR$_FILE_GROUPS1
SH       1032 SYS                  FGR$_TABLESPACE_INFO
SH       1035 SYS                  FGR$_TABLE_INFO
SH       1036 SYS                  I_FGR$_TABLE_INFO1
SH       1037 SYS                  I_FGR$_TABLE_INFO2

9 rows selected.

这里默认的目录是DIR1，但是单独为P3分区指定了目录DIR2。除了指定default directory，也可以用LOCATION语句

1
partition p3 values ('SH') LOCATION ('dir2:p3.txt')

收集表的统计信息，查看数据的分布情况

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
exec dbms_stats.gather_table_stats(user, 't_ext');

col table_name format a20
col partition_name format a20
col high_value format a20

select table_name,
       partition_name,
       high_value,
       num_rows
from   user_tab_partitions
where  table_name = 'T_EXT'
order by 1, 2;

TABLE_NAME           PARTITION_NAME       HIGH_VALUE             NUM_ROWS
-------------------- -------------------- -------------------- ----------
T_EXT                P1                   'HU'                        500
T_EXT                P2                   'BJ'                        500
T_EXT                P3                   'SH'                        500
T_EXT                PDEFAULT             default                       0

子分区外部表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
DROP TABLE t_ext purge;

create table t_ext (
  name  varchar2(2),
  object_id     number,
  owner         varchar2(100),
  object_name   varchar2(100)
)
organization external (
  type oracle_loader
  default directory dir1
  access parameters (
    fields csv with embedded terminated by ',' optionally enclosed by '"'
    missing field values are null (
      name,
      object_id,
      owner,
      object_name 
    )
  )
)
reject limit unlimited
partition by list (name)
subpartition by range (object_id) (
 partition p1 values ('HU') (
   subpartition p1_s1 values less than (200) location ('p1.txt'),
   subpartition p1_smax values less than (maxvalue) location ('p1.txt')
 ),
 partition p2 values ('BJ') (
   subpartition p2_s1 values less than (200) location ('p2.txt'),
   subpartition p2_smax values less than (maxvalue) location ('p2.txt')
 ),
 partition p3 values ('SH') default directory dir2(
   subpartition p3_s1 values less than (200) location ('p3.txt'),
   subpartition p3_smax values less than (maxvalue) location ('p3.txt')
 )
);

收集表的统计信息，查看数据的分布情况

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
exec dbms_stats.gather_table_stats(user, 't_ext');

col table_name format a20
col partition_name format a20
col subpartition_name for a20
col high_value format a20

select table_name,
       partition_name,
       subpartition_name,
       high_value,
       num_rows
from   user_tab_subpartitions
where  table_name = 'T_EXT'
order by 1, 2;

TABLE_NAME           PARTITION_NAME       SUBPARTITION_NAME    HIGH_VALUE             NUM_ROWS
-------------------- -------------------- -------------------- -------------------- ----------
T_EXT                P1                   P1_S1                200                         500
T_EXT                P1                   P1_SMAX              MAXVALUE                    500
T_EXT                P2                   P2_S1                200                         500
T_EXT                P2                   P2_SMAX              MAXVALUE                    500
T_EXT                P3                   P3_S1                200                         500
T_EXT                P3                   P3_SMAX              MAXVALUE                    500
发表 xbdba 12c新特性5 分钟读完 (大约810个字)

12c新特性：多列list分区

从12.2开始，oracle支持建立多个列组成分区键的分区表了。创建多列list分区表与一般的list分区并无太大的区别，除了在指定分区键时包含了用逗号隔开的多个列。每个独立的分区可以包含多列的集合，通常每张表只会有一个DEFAULT分区，不支持子DEFAULT分区。

多列list分区

新建一个多列list分区，插入部分数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
drop table t1 purge;

create table t1 (
id number,
prov varchar2(10),
name varchar2(10)
)
partition by list (prov,name)
(
    partition p1 values(('HU','hubei'),('BJ','beijing')),
    partition p2 values(('SH','shanghai')),
    partition pdefault values (DEFAULT)
);

insert into t1 values(1,'HU','hubei');
insert into t1 values(2,'BJ','beijing');
insert into t1 values(3,'SH','shanghai');
insert into t1 values(4,'ZJ','zhejiang');
commit;

exec dbms_stats.gather_table_stats(user,'T1');

查看分区表数据的分区情况

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
column table_name format a20
column partition_name format a20
column high_value format a40

select table_name,
       partition_name,
       high_value
from   user_tab_partitions
where  table_name = 'T1'
order by 1, 2;

TABLE_NAME           PARTITION_NAME       HIGH_VALUE
-------------------- -------------------- ----------------------------------------
T1                   P1                   ( 'HU', 'hubei' ), ( 'BJ', 'beijing' )
T1                   P2                   ( 'SH', 'shanghai' )
T1                   PDEFAULT             DEFAULT

这里可以看到符合分区要求的数据都落在了对应的分区内，而zhejiang则落在了DEFAULT分区内。

可以通过分区扩展语法来查询每个分区的数据

1
2
3
4
5
6
xb@PDB12C> select * from t1 partition(p1);

        ID PROV       NAME
---------- ---------- ----------
         1 HU         hubei
         2 BJ         beijing

也可以通过直接带入分区包含的数据的方式

1
2
3
4
5
6
xb@PDB12C> select * from t1 partition for('HU','hubei');

        ID PROV       NAME
---------- ---------- ----------
         1 HU         hubei
         2 BJ         beijing

这里也可以直接将P1分区拆分成多个分区

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
xb@PDB12C> alter table t1 split partition p1 into (partition p1a values('HU','hubei'),partition p1b) online;

Table altered.

xb@PDB12C> select * from t1 partition(p1a);

        ID PROV       NAME
---------- ---------- ----------
         1 HU         hubei

xb@PDB12C> select * from t1 partition(p1b);

        ID PROV       NAME
---------- ---------- ----------
         2 BJ         beijing

在线对分区表的操作并不会影响DML操作，这个也是12c引入的新特性。

这里有一些多列lsit分区的特性要说明一下：

  • 可以指定超过一个列来作为分区键

    最高不超过16个列

    每个分区键必须唯一

  • 不支持对单列级别再指定默认分区，比如(‘HU’,DEFALUT)的情况是不允许的，避免造成歧义

  • 支持函数

    支持分区级别和子分区级别

    支持堆表

    支持外部表

    支持引用分区和自动列分区

多列的自动list分区

auto-list partition：12c新特性：自动创建list分区

多列跟之前的一样也支持自动list，不过这时就要把default分区去掉。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
drop table t1 purge;

create table t1 (
id number,
prov varchar2(10),
name varchar2(10)
)
partition by list (prov,name) AUTOMATIC                 >>>>====
(
    partition p1 values(('HU','hubei'),('BJ','beijing')),
    partition p2 values(('SH','shanghai'))
);

insert into t1 values(1,'HU','hubei');
insert into t1 values(2,'BJ','beijing');
insert into t1 values(3,'SH','shanghai');
insert into t1 values(4,'ZJ','zhejiang');
commit;

exec dbms_stats.gather_table_stats(user,'T1');

查看不在分区条件范围内的zhejiang 数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
column table_name format a20
column partition_name format a20
column high_value format a40

select table_name,
       partition_name,
       high_value
from   user_tab_partitions
where  table_name = 'T1'
order by 1, 2;

TABLE_NAME           PARTITION_NAME       HIGH_VALUE
-------------------- -------------------- ----------------------------------------
T1                   P1                   ( 'HU', 'hubei' ), ( 'BJ', 'beijing' )
T1                   P2                   ( 'SH', 'shanghai' )
T1                   SYS_P1197            ( 'ZJ', 'zhejiang' )
发表 xbdba 12c新特性6 分钟读完 (大约970个字)

12c Create Table for Exchange Partition

以前版本中如果我们要对一个分区表做EXCHANGE PARTITION时，都会通过create table as select * XXX来新建一张普通表用于exchange，但是可能遇到这样的情况，源表的部分字段被禁用或不可见，则会导致exchange的时候失败，原因是因为两个表的表结构不一致。

而在新的12.2版本中，提供了一种新的建表方法，通过FOR EXCHANGE WITH语句来确保做exchange分区操作时的表与分区表形状一致，当然这种建表的方法并不会同步建索引。

下面总结了CREATE TABLE FOR EXCHANGE WITH操作所带来的一些影响：

  • 这个DDL操作主要是为了方便创建用于做exchange partition操作的表

  • 这个操作其实就是对要做exchange表的克隆，包含字段的顺序以及属性

  • 新建的表无法重命名，都是从源表继承下来

  • 这个DDL操作中唯一一个可以改变的逻辑属性是表是否为partition

    比如源表为一个复合分区表，如果多个子分区交换到新表的分区下，这种情形时，你就可以指定新表是否需要为分区表。

    子分区与分区之间可以是非对称的，但是在建表的时候必须要精确匹配分区与子分区的对应关系。

  • 可以指定的物理属性则为表使用的段属性

  • 使用这个DDL操作可以复制的列包括不限于以下：不可用列、不可见列、虚拟列、函数索引表达式列以及其他内部设置和属性

以前版本CTAS

创建一个测试分区表作为源表，包含了几种特殊类型的字段

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
drop table t1 purge;

create table t1 (
id number,
col1 varchar2(100),
col2 varchar2(100),
col3 generated always as (id+100) virtual
)
partition by range (id)
(
partition p1 values less than (200),
partition p2 values less than (400)
);

create index idx_t1 on t1(id);

分别修改col1和col2的字段属性

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
xb@PDB12C> alter table t1 set unused(col1);

Table altered.

xb@PDB12C> alter table t1 modify col2 invisible;

Table altered.

xb@PDB12C> @desc t1
           Name                            Null?    Type
           ------------------------------- -------- ----------------------------
    1      ID                                       NUMBER                        >>>>====已经看不到col1和col2两个字段
    2      COL3                                     NUMBER

col column_name for a40
col data_type for a20
col data_length for 999999999999
col data_default for a20
col virtual_column for a20
SELECT column_name, data_type, data_length, data_default, virtual_column
      FROM user_tab_cols
     WHERE table_name = 'T1';

COLUMN_NAME                              DATA_TYPE              DATA_LENGTH DATA_DEFAULT         VIRTUAL_COLUMN
---------------------------------------- -------------------- ------------- -------------------- --------------------
ID                                       NUMBER                          22                      NO
SYS_C00002_19031410:18:00$               VARCHAR2                       100                      NO
COL2                                     VARCHAR2                       100                      NO
COL3                                     NUMBER                          22 "ID"+100             YES

创建一张新的普通表用来做EXCHANGE PARTITION

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
drop table t2 purge;
create table t2 as select * from t1 where 1=2;
create index idx_t2 on t2(id);

COLUMN_NAME                              DATA_TYPE              DATA_LENGTH DATA_DEFAULT         VIRTUAL_COLUMN
---------------------------------------- -------------------- ------------- -------------------- --------------------
ID                                       NUMBER                          22                      NO
COL3                                     NUMBER                          22                      NO                   >>>>====新表字段并非虚拟列


insert into t2 
select level from dual connect by rownum>=200;

commit;

alter table t1
  exchange partition p1
  with table t2
  without validation
  update global indexes;

ERROR at line 3:
ORA-14096: tables in ALTER TABLE EXCHANGE PARTITION must have the same number of columns

可以看到报错提示字段数量不一致，CTAS对于这种情况就不太适用。

新版本FOR EXCHANGE WITH

现在可以使用12.2新的语句CREATE TABLE ... FOR EXCHANGE WITH

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
drop table t2 purge;

create table t2 
    for exchange with table t1;
create index idx_t2 on t2(id);

col column_name for a40
col data_type for a20
col data_length for 999999999999
col data_default for a20
col virtual_column for a20
SELECT column_name, data_type, data_length, data_default, virtual_column
      FROM user_tab_cols
     WHERE table_name = 'T2';

COLUMN_NAME                              DATA_TYPE              DATA_LENGTH DATA_DEFAULT         VIRTUAL_COLUMN
---------------------------------------- -------------------- ------------- -------------------- --------------------
COL2                                     VARCHAR2                       100                      NO
SYS_C00002_19031410:18:00$               VARCHAR2                       100                      NO
ID                                       NUMBER                          22                      NO
COL3                                     NUMBER                          22 "ID"+100             YES

可以看到现在新建的T2表字段属性就跟T1完全一样，继续剩下的操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
insert into t2(id)
select level  from dual connect by rownum>=200;

commit;

xb@PDB12C> 
alter table t1
  exchange partition p1
  with table t2
  without validation
   update global indexes;

Table altered.

xb@PDB12C> select * from t1 partition (p1) where rownum>=10;

        ID       COL3
---------- ----------
         1        101
         2        102
         3        103
         4        104
         5        105
         6        106
         7        107
         8        108
         9        109
        10        110

10 rows selected.

则可以顺利完成分区交换操作。

关注我

链接

分类

最新文章

归档

标签

12c15
18c1
Backup & Recovery7
Internal1
Patch1
asm1
awr1
bbed2
bug1
cbo3
cluster3
cursor1
dataguard4
datapump2
dbua1
dg broker1
dns1
emcc1
histograms1
internal2
json3
linux1
migration1
mmon1
multitenant3
ogg2
ora-8
partition5
pl/sql1
redefinition1
result cache1
rman3
scripts3
sql tuning1
ssh1
statistics5
xtts1
yum1
zabbix3
zabbix preprocessing1
×