Oracle SQL 学习笔记15 - 大数据量操作

2020-02-06 本文已影响0人赵阳_c149

INSERT ALL

Oracle 中 INSERT ALL 是指把同一批数据插入到不同的表中。主要用于数据仓库分发数据。这方式要比写多个 INSERT INTO 语句效率要高。因为不论插入多少张表，主表（参考 dual）只会被读取一次。
假如，现在有个需求，把表 t 的中数据分别插入到 t1、t2，如果你不知道 insert all，你可能会使用 insert into 插入 2 次，在两次 insert 过程中，有可能 t 表的数据发生了改变，从而导致 t1、t2 表得到的数据不一样，正确的写法是用 INSERT ALL 【1】

INSERT ALL语句的类型

主要有四种类型：

Unconditional ALL INSERT

      INSERT ALL 
         INTO stu1(id, NAME, sex) 
         INTO stu2(id, NAME, sex)
      SELECT t.id, t.name, t.sex FROM stu t WHERE t.id = 10001;

Conditional ALL INSERT

INSERT ALL
  WHEN id >= 2 THEN
    INTO stu1(ID, NAME, sex)
  WHEN id >= 3 THEN
    INTO stu2(ID, NAME, sex) 
SELECT t.id, t.name, t.sex FROM stu t;

Conditional FIRST INSERT
仅对第一个 when 进行匹配（第一次匹配成功后，break）

INSERT FIRST
  WHEN id >= 2 THEN
    INTO stu1(ID, NAME, sex)
  WHEN id >= 3 THEN
    INTO stu2(ID, NAME, sex) 
SELECT t.id, t.name, t.sex FROM stu t;

Pivoting INSERT

INSERT ALL
  INTO  sales_info VALUES (employee_id, week_id, sales_MON)
  INTO  sales_info VALUES (employee_id, week_id, sales_TUE)
  INTO  sales_info VALUES (employee_id, week_id, sales_WED)
  INTO  sales_info VALUES (employee_id, week_id, sales_THUR)
  INTO  sales_info VALUES (employee_id, week_id, sales_FRI)
  SELECT EMPLOYEE_ID, week_id, sales_MON, sales_WED, sales_THUR, sales_FRI
  FROM sales_source_data;

insert_all.png

INSERT ALL 语法

INSERT [ALL] [conditional_insert_clause]
[insert_into_clause values_clause]  (subquery)

其中，insert_into_clause values_clause 可以表示为：

[ALL] [FIRST]
[WHEN condition THEN] [insert_into_clause values_caluse]
[ELSE] [insert_into_clause values_clause]

使用Merge语句

在一个语句中进行有条件的更新和插入删除操作。如果数据已经存在，则更新；不存在则插入。
优势：

不用单独写update和insert
性能高且易用
主要用于数据仓库的大量数据处理

语法

MERGE INTO table_name table_alias
  USING (table|view|sub_query) alias 
  ON (join condition)
  WHEN MATCHED THEN
    UPDATE SET
    col1 = col1_val
    col2 = col2_val
  WHEN NOT MATCHED THEN
    INSERT (column_list)
    VALUES (column_values)

了解几种Flashback语句

从10g开始，Oracle开始大面积引入“Flashback”技术，或者Flashback技术工具集合，来提供多级别多粒度的“逻辑恢复”。经过若干年的发展，Flashback家族已经有诸多的分支技术，依托不同的技术，来实现多粒度的数据恢复。

His.JPG

FLASHBACK依赖于undo机制【3】。Undo记录的是数据DML操作的前镜像，这是Oracle的核心机制之一。

Flashback Version Query

Flashback Version Query引入了一些数据表“伪列”，可以提供对数据版本的操作和检索。【2】

SELECT salary FROM employees3
WHERE emplopyee_id = 107;

更新表：

UPDATE employees3 SET salary = salary * 1.30
WHERE employee_id = 107;
COMMIT;

查看历史数据:

SELECT salary FROM employees3
  VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
WHERE employee_id = 107;

FLASHBACK TABLE 语句

FLASHBACK TABLE 用于修复误操作的数据。它将表恢复到较旧的时间点，易用、可靠、快速，而且可在线操作。

FLASHBACK  TABLE [schema.]table [,[schema.]table]...
TO {TIMESTAMP | SCN} expr
[{ENABLE | DISABLE } TRIGGERS];

FLASHBACK DROP 语句

Flashback Drop 允许您将之前删除的表（但不是截断的表）恢复到刚好删除它之前的状态，同时还会恢复所有索引以及任何触发器和权限。唯一的主键和非空约束也会被恢复，但不包括外键。
删除表：

DROP TABLE emp2;

查看回收站中被删除表的信息：

SELECT original_name, operation, droptime
FROM recyclebin;
# 或者
show recyclebin;

闪回：

FLASHBACK TABLE emp2 TO BEFORE DROP;

【1】https://blog.csdn.net/qq_34745941/article/details/81462536
【2】聊聊闪回版本查询Flashback Version Query
【3】[Oracle]理解undo表空间