发表 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 分钟读完 (大约1081个字)

12c rman从备份集中active duplicate

12c以前的版本中，ACTIVE DUPLICATE进程使用生产库的进程通过网络将镜像副本发送到辅助库，这会是一个很消耗时间的任务，因为复制进程是直接按照源库大小相同比例进行复制。而在现在的12c中，数据库复制进程可以使用备份集来代替镜像副本。那么需要传输的数据库大小就会大幅度减小，因为RMAN备份的时候会跳过没有使用的块和已经提交了的回滚块等。同时也可以使用压缩或者切片的方式来提高复制传输的速度。另外新的从辅助库发起的辅助通道会用来从源库进行拉取备份集，而不是以前的从源库将备份集推送到辅助库，从而降低了源库的负载压力。

12c中，一个pull进程由备份集来决定，连接最初从源库建立，辅助实例则会通过网络接收到所需的数据库文件。恢复进程则是在辅助实例上发起，因此对源库的资源消耗更少了。

基于DUPLICATE语句，RMAN动态决定使用哪种进程，pull或者push。这样保证了现有已存在的脚本仍然有效。

  • 当你指定USING BACKUPSET，RMAN使用pull方法
  • 当你在DUPLICATE之前指定SET ENCRYPTION，RMAN自动使用pull方法并且创建备份集。这些要传输到目标端的备份集是被加密的
  • SECTION SIZE语句将数据文件切分多个小块，这样在辅助实例上并行恢复的时候可以利用多个通道的优势。为了更有效的使用并发，可以配置多个辅助通道
  • 通过USING COMPRESSED BACKUPSET语句，文件会通过压缩备份集的格式进行传输。RMAN创建备份时采取对未使用的块压缩方式，因此缩小了需要通过网络传输的备份集大小

示例

1
2
3
4
5
6
7
sys@ORA12C> select con_id,name,open_mode from v$pdbs;

    CON_ID NAME       OPEN_MODE
---------- ---------- ----------
         2 PDB$SEED   READ ONLY
         3 PDB12C     READ WRITE
         4 PDB3       READ WRITE

登录两边实例

1
2
3
4
5
6
7
8
[oracle@testyum ~]$ rman target sys/oracle@ora12c auxiliary sys/oracle@ora12c_dg

Recovery Manager: Release 12.2.0.1.0 - Production on Tue Aug 27 13:18:24 2019

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORA12C (DBID=383814323)
connected to auxiliary database: ORA12C (not mounted)

开始执行复制脚本

1
2
3
4
5
6
7
8
9
10
11
run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate auxiliary channel aux1 type disk;
allocate auxiliary channel aux2 type disk;
allocate auxiliary channel aux3 type disk;
allocate auxiliary channel aux4 type disk;
DUPLICATE TARGET DATABASE TO 'ora12c'
FROM ACTIVE DATABASE
USING BACKUPSET;
}

完整日志：rman_duplicate_backupset.log

通过日志可以看出一些端倪

1
2
3
4
5
6
7
8
9
10
11
12
13
contents of Memory Script:
{
   sql clone "alter system set  control_files = 
  ''/u01/app/oracle/oradata/ORA12C/controlfile/o1_mf_gcm27myg_.ctl'', ''/u01/app/oracle/fast_recovery_area/ora12c/ORA12C/controlfile/o1_mf_gcm27ndj_.ctl'' comment=
 ''Set by RMAN'' scope=spfile";
   sql clone "alter system set  db_name = 
 ''ORA12C'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone from service  'ora12c' primary controlfile;
   alter clone database mount;
}

这里辅助库是直接通过restore from service 的方式恢复了主库的控制文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
contents of Memory Script:
{
   sql clone 'alter database flashback off';
   set newname for clone datafile  1 to new;
   set newname for clone datafile  3 to new;
   set newname for clone datafile  4 to new;
   set newname for clone datafile  5 to new;
   set newname for clone datafile  6 to new;
   set newname for clone datafile  7 to new;
   set newname for clone datafile  8 to new;
   set newname for clone datafile  9 to new;
   set newname for clone datafile  10 to new;
   set newname for clone datafile  11 to new;
   set newname for clone datafile  12 to new;
   set newname for clone datafile  134 to new;
   set newname for clone datafile  135 to new;
   set newname for clone datafile  136 to new;
   set newname for clone datafile  137 to new;
   restore
   from  nonsparse   from service 
 'ora12c'   clone database
   ;
   sql 'alter system archive log current';
}

这里是通restore from service 恢复了数据文件。

看到这里很容易就联想到之前说过的12c新特性12c新特性：Rman中通过网络恢复文件

所以从备份集中的在线复制其实就是采用了新特性，然后从辅助库发起连接，使用RESTORE FROM SERVICE的方式将数据文件恢复到本地。

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

12c不可见列

在12c中，Oracle允许隐藏某些列。这个操作可以由用户自己来控制，通过CREATE TABLE或者ALTER TABLE等方式。被隐藏的列之后也可以通过ALTER TABLE还原。一般访问表时都是无法显示出隐藏字段的，比如select * from table或者DESC TABLE。

隐藏列的基本特性：

  • 一般对表的访问都不会显示隐藏列（SELECT * FROM TABLE, DESC TABLE）
  • 数据库通常会按表创建的顺序来存放字段，如果你将这张表新增了一个字段，这个新字段默认会排在表的最后。当表含有隐藏字段时，则表字段的顺序不会包含这些隐藏字段。
  • SELECT语句中只有显式的查询不可见列才能返回结果

示例

创建表

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 (a int,b int invisible, c int NOT NULL);

xb@PDB12C> @desc t2;
           Name                            Null?    Type
           ------------------------------- -------- ----------------------------
    1      A                                        NUMBER(38)
    2      C                               NOT NULL NUMBER(38)

SELECT COLUMN_NAME,
       COLUMN_ID,
       HIDDEN_COLUMN
FROM USER_TAB_COLS
WHERE TABLE_NAME ='T2';

COLUMN_NAME           COLUMN_ID HID
-------------------- ---------- ---
A                             1 NO
B                               YES
C                             2 NO

可以看到通过desc查询表结构是无法看到字段b的，不可见列B的COLUMN_ID变为空，而第三个字段C的COLUMN_ID为2。

不过这里可以通过设置选项COLINVISIBLE来让DESC命令看到不可见列

1
2
3
4
5
6
7
SET COLINVISIBLE ON
xb@PDB12C> @desc t2
           Name                            Null?    Type
           ------------------------------- -------- ----------------------------
    1      A                                        NUMBER(38)
    2      C                               NOT NULL NUMBER(38)
    3      B (INVISIBLE)                            NUMBER(38)

插入部分数据

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
xb@PDB12C> INSERT INTO T2 values (1,2,3);
INSERT INTO T2 values (1,2,3)
            *
ERROR at line 1:
ORA-00913: too many values

xb@PDB12C> INSERT INTO T2 values (1,2);

1 row created.

xb@PDB12C> INSERT INTO T2(a,b,c) values(3,4,5);

1 row created.

xb@PDB12C> SELECT * FROM t2;

         A          C
---------- ----------
         1          2
         3          5

xb@PDB12C> SELECT A,B,C FROM t2;

         A          B          C
---------- ---------- ----------
         1                     2
         3          4          5

恢复不可见列

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
ALTER TABLE t2 modify(b visible);

xb@PDB12C> @desc t2;
           Name                            Null?    Type
           ------------------------------- -------- ----------------------------
    1      A                                        NUMBER(38)
    2      C                               NOT NULL NUMBER(38)
    3      B                                        NUMBER(38)

SELECT COLUMN_NAME,
       COLUMN_ID,
       HIDDEN_COLUMN
FROM USER_TAB_COLS
WHERE TABLE_NAME ='T2';

COLUMN_NAME           COLUMN_ID HID
-------------------- ---------- ---
A                             1 NO
B                             3 NO
C                             2 NO

可以看到将字段B重新可见后，它的COLUMN_ID发生了改变，所以要注意的是如果这时你再通过INSERT INTO T2插入数据时，第二个值不是插到B，而是C，所以在写sql的时候一定注意规范，将插入的字段名称都写在语句里。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
INSERT INTO t2
VALUES(6,
       7,
       8);


SELECT *
FROM t2;

         A          C          B
---------- ---------- ----------
         1          2
         3          5          4
         6          7          8

将NOT NULL字段不可见

1
2
3
4
5
6
7
8
ALTER TABLE t2 modify(c invisible);

INSERT INTO t2
VALUES(5,
       6);
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("XB"."T2"."C")

所以即使字段不可见，但是因为有非空约束，所以还是要插入非空值到列

1
2
3
4
5
6
INSERT INTO t2 (a,b,c)
VALUES(5,
       6,
       7);

1 row created.

当非空字段有默认值的时候，那插入就不会有问题了。

不可见列上的约束键

不可见列上也时可以创建主键和外键约束的，与其他字段没什么区别

1
2
3
CREATE TABLE test1(a number,b number invisible PRIMARY KEY);

CREATE TABLE test2(c number, d number invisible REFERENCES test1(b));

限制

  • 有些类型的表不能包含不可见列

    • 外部表
    • 聚簇表
    • 临时表

  • 用户自定义类型的属性不能是不可见

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

12c返回指定行数

如果对返回结果进行行数的过滤时，通常都是通过order by + rownum <的方式，对于返回中间行数的情况就需要通过嵌套分页的写法，而在其他类型的数据库比如Mysql中可以比较方便的直接通过limit的方式

1
2
3
4
SELECT *
FROM t1
ORDER BY 1 LIMIT 0,
                 10;

row limiting概念

从12c开始，oracle对这方面的功能大大的增强，引入了Row_limiting_clause语句格式，允许查询sql能限制返回的行数以及返回结果集开始的行位置。许多查询sql需要限制返回的行数和修改返回行数的起始位置，比如在返回结果集中只需要过滤top-N的数据，这个时候就只需要FETCH FIRST和OFFSET选项就能简单完成。

Row_limiting_clause语句通过指定起始偏移量，返回行数n或者返回记录数的百分比，将这些选项都接在常规sql的order by语句后面。

它主要通过以下选项来实现：

  • OFFSET

    用来指定限制返回行数开始前需要跳过的行数的整数值，如果offset为负数或未指定，则默认为0并且限制返回行数时从第一行开始。如果值为NULL或者大于等于返回行的总数，那么最终返回0行，等于是进行了截断。

  • FETCH

    指定返回多少行或者多少百分比的记录，如果不指定，则默认返回所有行，第一行从offset+1开始

  • FIRST | NEXT

    这些关键字可以互换使用，根据场景来判断

  • Rowcount | percent PERCENT

    使用Rowcount来指定返回的行数，负值或者NULL则被当做0。如果指定的值大于最大返回行数，则只返回可返回的最大行数。percent PERCENT类似，表示返回的百分比。

    如果这两个值都没指定，则默认返回一行。

  • ONLY | WITH TIES

    ONLY只返回指定行数的精确值，而WITH TIES则在除了指定行数以外，如果还有与最后一行数据同样的order by值的时候会将其他满足条件的记录都返回，如果使用WITH TIES则必须配合order by语句，否则额外的行都不会返回。

ROW limiting语句的限制：

  1. 不能用于for update语句
  2. select后面不能包含序列的CURRVAL或NEXTVAL
  3. 物化视图如果包含row limiting，则无法刷新

示例

创建一张测试表，插入部分重复数据

1
2
3
4
5
6
7
8
9
10
11
12
13
DROP TABLE t1 purge;

CREATE TABLE t1 (id number);

INSERT INTO t1
SELECT LEVEL
FROM dual CONNECT BY LEVEL >=5;

INSERT INTO t1
SELECT *
FROM t1;

 COMMIT;

现在t1表则包含10条数据，其中一半是重复的

1
2
3
4
5
6
7
8
9
10
11
12
13
14
xb@PDB12C> select * from t1 order by 1; 

        ID
----------
         1
         1
         2
         2
         3
         3
         4
         4
         5
         5

按id排序只取前5行数据

1
2
3
4
5
6
7
8
9
10
11
SELECT *
FROM t1
ORDER BY 1 FETCH FIRST 5 ROWS ONLY;

        ID
----------
         1
         1
         2
         2
         3

因为ID=3的记录有两条，通过with ties将与第5行id值一样的记录也展示出来

1
2
3
4
5
6
7
8
9
10
11
12
SELECT *
FROM t1
ORDER BY 1 FETCH FIRST 5 ROWS WITH ties;

        ID
----------
         1
         1
         2
         2
         3
         3

用percent取id前30%的数据

1
2
3
4
5
6
7
8
9
SELECT *
FROM t1
ORDER BY 1 FETCH FIRST 30 percent ROWS ONLY;

        ID
----------
         1
         1
         2

percent配合with ties

1
2
3
4
5
6
7
8
9
10
SELECT *
FROM t1
ORDER BY 1 FETCH FIRST 30 percent ROWS WITH ties;

        ID
----------
         1
         1
         2
         2

以前的版本如果要取中间的数据只能用嵌套分页的办法，比如取根据ID排序第3到5行的数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# before 12c
SELECT id
FROM
  (SELECT rownum rn,
          id
   FROM
     (SELECT id
      FROM t1
      ORDER BY 1)
   WHERE rownum >=5)
WHERE rn >=3;

# after 12c
SELECT id
FROM t1
ORDER BY 1
OFFSET 2 ROWS FETCH NEXT 3 ROWS ONLY;

不根据id排序，而是根据rownum取5-7行数据

1
2
3
4
5
6
7
8
9
10
SELECT rownum,
       id
FROM t1
OFFSET 4 ROWS FETCH NEXT 3 ROWS ONLY;

    ROWNUM         ID
---------- ----------
         5          5
         6          1
         7          2

效率问题

之前做分页的时候都会碰到id between 5 and 7的写法，我们知道这是很低效的，所有都是通过rownum的嵌套方式来书写，那新的写法执行效率又如何？

对此做一个10053 trace，看下执行计划是如何

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
48
49
50
51
52
53
54
oradebug setmypid
oradebug event 10053 trace name context forever,level 12;

SELECT id
FROM xb.t1
ORDER BY 1
OFFSET 2 ROWS FETCH NEXT 3 ROWS ONLY;

oradebug event 10053 trace name context off;

sys@ORA12C> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/ora12c/ora12c/trace/ora12c_ora_112949.trc

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "from$_subquery$_002"."ID" "ID"
FROM
  (SELECT "T1"."ID" "ID",
          "T1"."ID" "rowlimit_$_0",
          ROW_NUMBER() OVER (
                             ORDER BY "T1"."ID") "rowlimit_$$_rownumber"
   FROM "XB"."T1" "T1"
   WHERE 2>CASE WHEN (2>=0) THEN 2 ELSE 0 END +3) "from$_subquery$_002"
WHERE "from$_subquery$_002"."rowlimit_$$_rownumber">=CASE
                                                         WHEN (2>=0) THEN 2
                                                         ELSE 0
                                                     END +3
  AND "from$_subquery$_002"."rowlimit_$$_rownumber">2
ORDER BY "from$_subquery$_002"."rowlimit_$_0"

----- Plan Table -----

============
Plan Table
============
--------------------------------------------+-----------------------------------+
| Id  | Operation                 | Name    | Rows  | Bytes | Cost  | Time      |
--------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT          |         |       |       |     4 |           |
| 1   |  VIEW                     |         |    10 |   390 |     4 |  00:00:01 |
| 2   |   WINDOW SORT PUSHED RANK |         |    10 |   130 |     4 |  00:00:01 |
| 3   |    TABLE ACCESS FULL      | T1      |    10 |   130 |     3 |  00:00:01 |
--------------------------------------------+-----------------------------------+
Query Block Name / Object Alias(identified by operation id):
------------------------------------------------------------
 1 - SEL$1                / from$_subquery$_002@SEL$2
 2 - SEL$1
 3 - SEL$1                / T1@SEL$1
------------------------------------------------------------
Predicate Information:
----------------------

*** 2019-08-21T13:50:43.663264+08:00 (PDB12C(3))
1 - filter(("from$_subquery$_002"."rowlimit_$$_rownumber">=CASE  WHEN (2>=0) THEN 2 ELSE 0 END +3 AND "from$_subquery$_002"."rowlimit_$$_rownumber">2))
2 - filter(ROW_NUMBER() OVER ( ORDER BY "ID")>=CASE  WHEN (2>=0) THEN 2 ELSE 0 END +3)

可以看到实际上是对sql做了转换，先通过分析函数将伪列的值查出，然后判断OFFSET值是否符合要求，如果大于0则为2，然后返回后面3行。

通过这个大概能猜到如果是with ties的写法，那么可能就是把ROW_NUMBER分析函数换成了RANK。

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

In-Database Archiving

数据库内归档可以将表中的行标记为非活动状态来进行归档，这些非活动的行可以使用压缩来进行优化，但是应用程序看不到这些数据。如果想要看到这些数据的话可以通过在会话级别设置参数。

通过数据库内归档的技术，可以在一个数据库当中保存更多的数据，并维持更长的数据保存周期，并且不会降低应用程序的性能。这些归档数据也可以通过压缩来提高性能，应用升级的过程中，也可以延迟对这些归档数据的修改以此来提高升级效率。

开启数据库内归档

ROW ARCHIVAL子句用来开启数据库内归档，可以通过在CREATE TABLE的时候指定，也可以在创建完毕以后ALTER TABLE进行修改。

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
drop table t1 purge;

create table t1 (id number,name varchar2(10))
row archival;

# 开启或关闭归档
alter table t1 no row archival;
alter table t1 row archival;

# 查看字段
col column_name for a20
col data_type for a20
SELECT column_id,
 column_name,
 data_type,
 data_length,
 hidden_column
 FROM user_tab_cols
 WHERE table_name = 'T1'
 ORDER BY column_id;

 COLUMN_ID COLUMN_NAME          DATA_TYPE            DATA_LENGTH HID
---------- -------------------- -------------------- ----------- ---
         1 ID                   NUMBER                        22 NO
         2 NAME                 VARCHAR2                      10 NO
           ORA_ARCHIVE_STATE    VARCHAR2                    4000 YES        >>>>====如果开启数据库内归档，则会自动生成这个隐藏字段

# 关闭归档
alter table t1 no row archival;
SELECT column_id,
 column_name,
 data_type,
 data_length,
 hidden_column
 FROM user_tab_cols
 WHERE table_name = 'T1'
 ORDER BY column_id;

 COLUMN_ID COLUMN_NAME          DATA_TYPE            DATA_LENGTH HID
---------- -------------------- -------------------- ----------- ---
         1 ID                   NUMBER                        22 NO
         2 NAME                 VARCHAR2                      10 NO         >>>>==== ORA_ARCHIVE_STATE字段消失

生成一些数据

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

insert into t1 
select level,'xb'||level from dual connect by level >=10;
commit;

col ora_archive_state for a10
select ora_archive_state,id,name from t1;

ORA_ARCHIV         ID NAME
---------- ---------- ----------
0                   1 xb1
0                   2 xb2
0                   3 xb3
0                   4 xb4
0                   5 xb5
0                   6 xb6
0                   7 xb7
0                   8 xb8
0                   9 xb9
0                  10 xb10

归档行数据

归档只是隐藏了行数据，并未实际的删除，这个隐藏参数主要是由ora_archive_state值来进行控制，只要是非&#8217;0&#8217;值，这行数据就会被隐藏，也就是归档的意思

1
2
3
4
5
6
7
8
9
10
11
12
13
14
update t1 
set ora_archive_state='10'
where id >6;
commit;

select ora_archive_state,id,name from t1;

ORA_ARCHIV         ID NAME
---------- ---------- ----------
0                   6 xb6
0                   7 xb7
0                   8 xb8
0                   9 xb9
0                  10 xb10

这时可以发现ID<6的数据都隐藏了，应用程序无法看到，正如前面所说，如果要看到全部数据，可以在会话级别设置参数

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
alter session set row archival visibility=all;
select ora_archive_state,id,name from t1;

ORA_ARCHIV         ID NAME
---------- ---------- ----------
10                  1 xb1
10                  2 xb2
10                  3 xb3
10                  4 xb4
10                  5 xb5
0                   6 xb6
0                   7 xb7
0                   8 xb8
0                   9 xb9
0                  10 xb10

# 再设置成只看活动的行
alter session set row archival visibility=active;
select ora_archive_state,id,name from t1;

ORA_ARCHIV         ID NAME
---------- ---------- ----------
0                   6 xb6
0                   7 xb7
0                   8 xb8
0                   9 xb9
0                  10 xb10

表之间归档属性传播

当通过CTAS等方式创建表时，行的归档属性是不会从原表传播到目标表的

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
drop table t2 purge;
create table t2 as select * from t1;

# 查询目标表，由于目标表没有开启归档，所有不包含隐藏列
xb@PDB12C> select id,name,ora_archive_state from t2;
select id,name,ora_archive_state from t2
               *
ERROR at line 1:
ORA-00904: "ORA_ARCHIVE_STATE": invalid identifier


xb@PDB12C> select id,name  from t2;

        ID NAME
---------- --------------------
         1 xb1
         2 xb2
         3 xb3
         4 xb4
         5 xb5
         6 xb6
         7 xb7
         8 xb8
         9 xb9
        10 xb10

开启目标表的归档,查询会发现ora_archive_state值都是0，也就是说表的行都是活动状态

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
alter session set row archival visibility = all;

alter table t2 row archival;

xb@PDB12C> select id,name,ora_archive_state  from t2;

        ID NAME                 ORA_ARCHIV
---------- -------------------- ----------
         1 xb1                  0
         2 xb2                  0
         3 xb3                  0
         4 xb4                  0
         5 xb5                  0
         6 xb6                  0
         7 xb7                  0
         8 xb8                  0
         9 xb9                  0
        10 xb10                 0

通过dbms_ilm.archivestatename过程来更新ora_archive_state列

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
update t2
set ora_archive_state=dbms_ilm.archivestatename(1)
where id >= 5;

commit;

xb@PDB12C> select id,name,ora_archive_state  from t2;

        ID NAME                 ORA_ARCHIV
---------- -------------------- ----------
         1 xb1                  1
         2 xb2                  1
         3 xb3                  1
         4 xb4                  1
         5 xb5                  1
         6 xb6                  0
         7 xb7                  0
         8 xb8                  0
         9 xb9                  0
        10 xb10                 0

重新查询原表的数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
xb@PDB12C> select id,name,ora_archive_state  from t1;

        ID NAME                 ORA_ARCHIV
---------- -------------------- ----------
         1 xb1                  10
         2 xb2                  10
         3 xb3                  10
         4 xb4                  10
         5 xb5                  10
         6 xb6                  0
         7 xb7                  0
         8 xb8                  0
         9 xb9                  0
        10 xb10                 0

这个时候，将原表的数据再次插入到目标表当中，看ora_archive_state的状态值是否也会插入过去

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
insert into t2
select id,name||'_new' from t1;
commit;

xb@PDB12C> select id,name,ora_archive_state  from t2;

        ID NAME                 ORA_ARCHIV
---------- -------------------- ----------
         1 xb1                  1
         2 xb2                  1
         3 xb3                  1
         4 xb4                  1
         5 xb5                  1
         6 xb6                  0
         7 xb7                  0
         8 xb8                  0
         9 xb9                  0
        10 xb10                 0
         1 xb1_new              0
         2 xb2_new              0
         3 xb3_new              0
         4 xb4_new              0
         5 xb5_new              0
         6 xb6_new              0
         7 xb7_new              0
         8 xb8_new              0
         9 xb9_new              0
        10 xb10_new             0

通过上面的结果可以看到ora_archive_state属性值并没有传播到新表当中去，新表中的行默认都仍然是活动状态，原来非活动的5条记录状态保持不变。

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

Identity Columns

标识列是12c的一个新特性，能自动生成列里的值，一部分功能与mysql中的自增列有点类似，简化了应用程序的开发和其他关系型数据库迁移到oracle。这个特性主要是利用一个默认的序列生成器，通过这个生成器来绝对字段增加或者减少的整数值。

1
2
3
4
5
6
7
8
9
10
11
12
13
drop table t1 purge;
create table t1
(id number generated by default as identity,
name varchar2(10));

insert into t1 (name) values ('xb');
commit;

select * from t1;

        ID NAME
---------- ----------
         1 xb

对于标识列来说，我们要么指定一个默认的序列去生成字段值或者只对那些插入NULL的情况生成字段值。根据标识列的定义可以有GENERATED ALWAYS和GENERATED BY DEFAULT关键字。

GENERATED ALWAYS

oracle使用序列生成器来确定列值，但也可以显示的指定某个值。如果指定了ON NULL关键字那么序列生成器会在你插入NULL值的时候生成一个值给字段。

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
drop table t1 purge;
create table t1
(id number generated by default as identity,
name varchar2(10));

insert into t1 (name) values ('xb');
commit;

xb@PDB12C> insert into t1 values(null,'xb2');
insert into t1 values(null,'xb2')
                      *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("XB"."T1"."ID")

select * from t1;

        ID NAME
---------- ----------
         1 xb

xb@PDB12C> @desc t1
           Name                            Null?    Type
           ------------------------------- -------- ----------------------------
    1      ID                              NOT NULL NUMBER
    2      NAME                                     VARCHAR2(10)

可以看到通过序列生成器生成的字段在创建的时候默认就是NOT NULL属性，因为这里没有对NULL的情况进行指定，所以序列生成器在碰到NULL的情况无法生成值，则会报错

加上ON NULL关键字

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
drop table t1 purge;
create table t1
(id number generated by default on NULL as identity,
name varchar2(10));

insert into t1 values(1,'xb');
insert into t1 values(9,'xb2');
insert into t1 values(null,'xb3');
commit;

xb@PDB12C> select * from t1;

        ID NAME
---------- ----------
         1 xb
         9 xb2
         1 xb3

这样当插入的字段值为NULL时，生成器则会自动生成一个整数值给字段。通过执行计划也可以看到，在对有序列生成器属性的表插入数据时，其实是访问的序列

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
xb@PDB12C> insert into t1 values(null,'xb3');

1 row created.

xb@PDB12C> @x
Display execution plan for last statement for this session from library cache...

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  4b1kmg40ra83t, child number 0
-------------------------------------
insert into t1 values(null,'xb3')

Plan hash value: 948190082

---------------------------------------------------------
| Id  | Operation                | Name         | Cost  |
---------------------------------------------------------
|   0 | INSERT STATEMENT         |              |     1 |
|   1 |  LOAD TABLE CONVENTIONAL | T1           |       |
|   2 |   SEQUENCE               | ISEQ$$_90071 |       |
---------------------------------------------------------

   - cpu costing is off (consider enabling it)


16 rows selected.

# 而这个序列也是真实存在的，说明是用户自动创建的
xb@PDB12C> select * from user_sequences;

SEQUENCE_NAME         MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER S E S K
-------------------- ---------- ---------- ------------ - - ---------- ----------- - - - -
ISEQ$$_90071                  1 1.0000E+28            1 N N         20          21 N N N N

Generated Always

字段无法被用户所更新，所以序列生成器生成的值是列唯一可能的值，如果像之前一样指定某个值或修改都会报错

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
drop table t2 purge;
create table t2
(id number generated always as identity,
name varchar2(10));

xb@PDB12C> insert into t2 values(1,'xb');
insert into t2 values(1,'xb')
*
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column

xb@PDB12C> insert into t2 values(null,'xb');
insert into t2 values(null,'xb')
*
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column

insert into t2(name) values('xb');
insert into t2(name) values('xb2');
insert into t2(name) values('xb3');
commit;

xb@PDB12C> select * from t2;

        ID NAME
---------- ----------
         1 xb
         2 xb2
         3 xb3

xb@PDB12C> update t2 set id=4 where name='xb';
update t2 set id=4 where name='xb'
              *
ERROR at line 1:
ORA-32796: cannot update a generated always identity column

由于是使用的序列，那么也可以用到一些序列中的特性，可以指定不同的初始值和累加值

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
drop table t3 purge;
create table t3
(id number generated always as identity (start with 2 increment by 2),
name varchar2(10));

insert into t3(name) values('xb');
insert into t3(name) values('xb2');
insert into t3(name) values('xb3');
commit;

xb@PDB12C> select * from t3;

        ID NAME
---------- ----------
         2 xb
         4 xb2
         6 xb3

通过USER_TAB_IDENTITY_COLS等视图可以查询到一些标识列的信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
set linesize 300
col table_name for a10
col COLUMN_NAME for a10
col IDENTITY_OPTIONS for a70
select * from USER_TAB_IDENTITY_COLS;

TABLE_NAME COLUMN_NAM GENERATION SEQUENCE_NAME        IDENTITY_OPTIONS
---------- ---------- ---------- -------------------- ----------------------------------------------------------------------
T3         ID         ALWAYS     ISEQ$$_90075         START WITH: 2, INCREMENT BY: 2, MAX_VALUE: 999999999999999999999999999
                                                      9, MIN_VALUE: 1, CYCLE_FLAG: N, CACHE_SIZE: 20, ORDER_FLAG: N

T1         ID         BY DEFAULT ISEQ$$_90079         START WITH: 1, INCREMENT BY: 1, MAX_VALUE: 999999999999999999999999999
                                                      9, MIN_VALUE: 1, CYCLE_FLAG: N, CACHE_SIZE: 20, ORDER_FLAG: N

T2         ID         ALWAYS     ISEQ$$_90073         START WITH: 1, INCREMENT BY: 1, MAX_VALUE: 999999999999999999999999999
                                                      9, MIN_VALUE: 1, CYCLE_FLAG: N, CACHE_SIZE: 20, ORDER_FLAG: N

这个特性目前也有一些限制条件：

  • 每张表只能由一个标识列
  • 标识列的数据类型必须是数字型，而且不能是用户自定义的类型
  • 标识列中不能有DEFAULT关键字
  • 标识列中，NOT NULL和NON DEFERRABLE约束都是隐式存在
  • CTAS语句无法继承字段的标识属性
发表 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个列

    每个分区键必须唯一

  • 不支持对单列级别再指定默认分区，比如(&#8216;HU&#8217;,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 分钟读完 (大约826个字)

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

自动创建list分区的特性，会根据需要自动对list分区表中每一个分区字段值创建单独的分区，这样大大减轻了DBA的工作负担，就不用依靠手动操作来维护大量表分区的创建，即使有不在原分区范围的内的值出现，也不需要将它们全部放在DEFAULT分区里面。

自动list分区其实跟以前的list分区基本类似，只是在维护上更方便一些，你只有在分区键确定的情况下才能创建自动list分区。由于是根据加载数据的情况来判断是否需要自动新建分区，这点有些类似interval分区的概念。

按照以往的模式新建一个分区表，根据省份名称来进行分区，分别插入了湖北、北京、上海几条数据

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

create table t1(
id number(10),
prov varchar2(20)
)
partition by list(prov)
(
partition p_hu values('hubei'),
partition p_bj values('beijing'),
partition p_sh values('shanghai')
);

insert into t1 values(1,'hubei');
insert into t1 values(2,'beijing');
insert into t1 values(3,'shanghai');
commit;

现在由于业务扩展，需要新增其他省份的数据进来，如果在没有DBA介入的情况下，直接新增数据

1
2
3
4
5
xb@PDB12C> insert into t1 values(4,'guangdong');
insert into t1 values(4,'guangdong')
            *
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition

很显然guangdong并不不能落在我们现有的任何一个分区当中，那么就会直接报错，那么就需要DBA进行手动添加相关分区，这样的操作可能会发生N次，无疑是一个比较低效的做法。

自动list分区

那么来尝试使用新的list分区特性：

可以直接对现有表通过ALTER TABLE的方式来修改分区

1
alter table t1 set partitioning automatic;

也可以通过加关键字AUTOMATIC对表进行重建

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(10),
prov varchar2(20)
)
partition by list(prov) automatic
(
partition p_hu values('hubei'),
partition p_bj values('beijing'),
partition p_sh values('shanghai')
);

insert into t1 values(1,'hubei');
insert into t1 values(2,'beijing');
insert into t1 values(3,'shanghai');
commit;

insert into t1 values(4,'guangdong');
commit;

可以发现这次就能正常插入数据无报错，查询partition视图可以发现新生成的以SYS开头的分区名称

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

column table_name format a30
column partition_name format a30
column high_value format a15

select table_name,
       partition_name,
       high_value,
       num_rows
from   user_tab_partitions
order by 1, 2;

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE        NUM_ROWS
------------------------------ ------------------------------ --------------- ----------
T1                             P_BJ                           'beijing'                1
T1                             P_HU                           'hubei'                  1
T1                             P_SH                           'shanghai'               1
T1                             SYS_P1096                      'guangdong'              1

新生成的分区就容纳了新增的guangdong记录，这样即使以后再有新的省份数据添加进来也不需要DBA的介入了，也可以在user_part_tables里查询新的AUTOLIST来字段来查看表是否是自动分区

1
2
3
4
5
6
7
8
col autolist for a10
SELECT table_name,
       autolist
FROM   user_part_tables;

TABLE_NAME                     AUTOLIST
------------------------------ ----------
T1                             YES

Tips:

使用这个新特性时，要慎重规划分区的分区键，否则就会造成自动创建大量的分区，特别是对那些以时间作为分区条件的时候尤为注意，最好是通过truncate等方式对字段进行处理后再分区。

关注我

链接

分类

最新文章

归档

标签

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
×