分类目录归档:12c新特性

12c中维护多分区

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

DROP/TRUNCATE多个分区

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

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

删除分区

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

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

删除分区

ALTER TABLE t1
DROP PARTITIONS p1,p2;

Table altered.

检查分区和索引

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语句来清除表分区里的数据时,可以同时操作多个分区。
截断分区与删除分区类似

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

截断分区

ALTER TABLE T1 TRUNCATE PARTITIONS P1,P2;

Table truncated.

检查分区和索引

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 INDEXESUPDATE 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_INDEXESUSER_IND_PARTITIONS中新增了ORPHANED_ENTRIES字段,这个字段用来表示全局索引是否包含因为延迟索引维护产生的陈旧的条目。

字段可以为以下三种值:

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

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

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

合并多个分区

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

range分区/子分区

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

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

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关键字来表示范围

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

LIST或系统分区/子分区

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

当合并多个LIST分区,生成的分区包含的数据为原分区所有值的集合的合并。
DEFAULT的list分区与其他list分区合并产生的结果依然是DEFAULT分区。

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分区的最高边界

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个分区,用于表示第一季度的三个月份。

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个

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个新分区

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创建备份时采取对未使用的块压缩方式,因此缩小了需要通过网络传输的备份集大小

示例

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

登录两边实例

[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)

开始执行复制脚本

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

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

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 的方式恢复了主库的控制文件

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的方式将数据文件恢复到本地。

12c不可见列

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

隐藏列的基本特性:

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

示例

创建表

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命令看到不可见列

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)

插入部分数据

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

恢复不可见列

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的时候一定注意规范,将插入的字段名称都写在语句里。

INSERT INTO t2
VALUES(6,
       7,
       8);


SELECT *
FROM t2;

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

将NOT NULL字段不可见

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")

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

INSERT INTO t2 (a,b,c)
VALUES(5,
       6,
       7);

1 row created.

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

不可见列上的约束键

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

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

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

限制

  • 有些类型的表不能包含不可见列
    • 外部表
    • 聚簇表
    • 临时表
  • 用户自定义类型的属性不能是不可见

12c返回指定行数

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

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

row limiting概念

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

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,则无法刷新

示例

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

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条数据,其中一半是重复的

xb@PDB12C> select * from t1 order by 1; 

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

按id排序只取前5行数据

SELECT *
FROM t1
ORDER BY 1 FETCH FIRST 5 ROWS ONLY;

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

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

SELECT *
FROM t1
ORDER BY 1 FETCH FIRST 5 ROWS WITH ties;

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

用percent取id前30%的数据

SELECT *
FROM t1
ORDER BY 1 FETCH FIRST 30 percent ROWS ONLY;

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

percent配合with ties

SELECT *
FROM t1
ORDER BY 1 FETCH FIRST 30 percent ROWS WITH ties;

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

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

# 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行数据

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,看下执行计划是如何

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。

In-Database Archiving

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

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

开启数据库内归档

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

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字段消失

生成一些数据

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值来进行控制,只要是非’0’值,这行数据就会被隐藏,也就是归档的意思

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的数据都隐藏了,应用程序无法看到,正如前面所说,如果要看到全部数据,可以在会话级别设置参数

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等方式创建表时,行的归档属性是不会从原表传播到目标表的

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,也就是说表的行都是活动状态

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列

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

重新查询原表的数据

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的状态值是否也会插入过去

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条记录状态保持不变。

Identity Columns

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

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 ALWAYSGENERATED BY DEFAULT关键字。

GENERATED ALWAYS

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

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关键字

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时,生成器则会自动生成一个整数值给字段。通过执行计划也可以看到,在对有序列生成器属性的表插入数据时,其实是访问的序列

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

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

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

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

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等视图可以查询到一些标识列的信息

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 NULLNON DEFERRABLE约束都是隐式存在
  • CTAS语句无法继承字段的标识属性