临时表、分区表、拉链表

一、临时表

概念：
临时表就是用来暂时保存临时数据的一个数据库对象，它和普通表有些类似，然而又有很大区别。它只能存储在 临时表空间 ，而非用户的表空间。ORACLE临时表分为会话和事务级别，只对当前会话或事务可见。每个会话只能查看和修改自己的数据。
作用：
当查询语句非常复杂时，我们就可以创建一个临时表时保存查询结果，并进行一系列操作。即：优化查询

①会话级临时表

会话级临时表是指临时表中的数据只在会话生命周期之中存在，当用户退出会话结束的时候，Oracle自动清除临时表中数据。执行commit或者rollback操作，表内数据依然存在，新建一个命令窗口（相当于开启了一个新的会话），表内的数据就查询不到了。
创建语法：

Create Global Temporary Table Temp_Name --创建表名为Temp_Name的临时表
(
Col1 Type1,
Col2 Type2
...
)
On Commit Preserve Rows;

②事务级临时表

事务级临时表是指临时表中的数据只在事务生命周期中存在，当一个事务结束（commit or rollback），Oracle自动清除临时表中数据。
创建语法：

Create Global Temporary Table Temp_Name --创建表名为Temp_Name的临时表
(
Col1 Type1,
Col2 Type2
...
)
On Commit Delete Rows;

③临时表注意事项

• 临时表不支持主外键关系
• 临时表不能永久的保存数据。
• 临时表的数据不会备份，恢复，对其的修改也不会有任何日志信息
• 临时表不会有DML 锁
• 临时表可以创建临时的索引、视图、触发器。
• 如果要DROP会话级别临时表，并且其中包含数据时，必须先截断其中的数据。否则会报错。

二、分区表

概念：
将一张表拆分后分别存储在不同表空间的表存储方式。分区表类型：范围分区(range)，列表分区(list)，散列分区(hash)和复合分区
作用：

• 增强可用性，一个分区损坏不影响其他分区的使用
• 便于维护和恢复，管理表分区比整个表要轻松的多
• 均衡存储（I/O），可以分配不同表空间（磁盘）存储数据
• 改善性能，对一个表的操作分配到表分区将大大提高效率

①范围分区表

创建语法：

create table 表名
(列名 数据类型
...)
partition by range (字段)
( 
partition 分区名1 values less than (值1或日期1),
partition 分区名2 values less than (值2或日期2),
partition 分区名3 values less than (值3或日期3),...
partition 分区名4 values less than (maxvalue)
);
/*values less than 特点：
values <值1 存储在分区1中
values>=值1 and values <值2 存储在分区2中
values>=值2 and values <值3 存储在分区3中
values>=值3 and values <值4...
*/

新增分区：
首先表一定是分区表才能新增,新增分区高于最后一个分区界限值

alter table 表名 add partition 分区名 values less than (值或日期);

删除分区：

alter table 表名 drop partition 分区名;

②列表分区表

创建语法：

create table 表名
(列名 数据类型
...)
partition by list (字段)
( 
partition 分区名1 values (值),
partition 分区名2 values (值),
partition 分区名3 values (值),...
);
/*values less than 特点：
values =值1 存储在分区1中
values=值2 存储在分区2中
values=值3 存储在分区3中
values=值4 ...
*/

新增分区：

alter table 表名 add partition 分区名 values (值);

删除分区：

alter table 表名 drop partition 分区名;

③散裂分区表

创建语法：

create table 表名
(列名 数据类型
...)
partition by hash (字段)
( 
partition 分区名1,
partition 分区名2,
partition 分区名3,...
);
--通过数据库中自带的hash算法分区，缺点：不能确定某条数据的存储分区

新增分区：

alter table 表名 add partition 分区名;

查询分区：

select .. from 表名 partition(分区名);

④复合分区表

创建方式：就是将上面分区方式组合起来使用
创建语法：

create table testfuhe
(
v_date date,
v_month varchar2(6),
v_day varchar2(8),
client_no varchar2(4),
fee number
) partition by range (v_date) subpartition by list (fee)
(
   partition p_201712 values less than (to_date('2018-01-01', 'YYYY-MM-DD'))
   (subpartition p1 values ('10'),--存储fee=10并且v_date时间早于18年一月一号的数据
    subpartition p2 values ('20'),
    subpartition p3 values ('30')
   ),
   partition p_201801 values less than (to_date('2018-02-01', 'YYYY-MM-DD'))
   (subpartition p4 values ('40'),
    subpartition p5 values ('50'),
    subpartition p6 values ('60')
   )
);

三、拉链表

概念：
拉链表不是数据库中实际存在的表而是为节约存储空间而提出的解决方案。具体就是记录数据在某一时间区间内的状态以及数据在某一时点上的变化的数据存储方式。
作用：

• 节约存储空间
• 记录数据变化

缺点：
一旦断链比较难恢复，需要人工恢复。

拉链算法：

• 建立临时表 VT_NEW 用于存放转换、处理后的数据
• 建立临时表 VT_INC 用于存放比对出的增量数据
• 修改目标表进行关链更新操作
• 修改目标表进行开链插入操作

详细过程说明：
源表SRC(快照表)：

ID	name	bal	date
001	张三	1000	2018-01-01
001	张三	1000	2018-01-02
001	张三	100	2018-01-03
002	李四	200	2018-01-03
…

我们明确目标表TAG(拉链表)的实现：

ID	name	bal	start_date	end_date
001	张三	1000	2018-01-01	2018-01-03
001	张三	100	2018-01-03	2999-12-31
002	李四	200	2018-01-03	2999-12-31
…

第一步：

要实现上面整个转换过程需要先建立临时表 VT_NEW 转换一下
临时表VT_NEW

ID	name	bal	start_date	end_date
001	张三	1000	2018-01-01	2999-12-31
001	张三	1000	2018-01-02	2999-12-31
001	张三	100	2018-01-03	2999-12-31
002	李四	200	2018-01-03	2999-12-31
…

实现SQL：

INSERT	INTO VT_NEW(ID,NAME,BAL,START_DATE,END_DATE)
SELECT	ID,NAME,BAL,DATE,'2999-12-31'
FROM SRC;

第二步：

建立临时表 VT_INC 用于存放比对出的增量数据
这里我们假设：
001 张三 1000 2018-01-01
这条数据以及在拉链表中已存在：
001 张三 1000 2018-01-01 2999-12-31
那么这条数据我们就不抽取了。

INSERT	INTO VT_INC(ID,NAME,BAL,START_DATE,END_DATE)
SELECT	ID,NAME,BAL,START_DATE,END_DATE
FROM	VT_NEW
WHERE	(ID,NAME,BAL) NOT IN (
SELECT	ID,NAME,BAL 
FROM	TAG 
WHERE	END_DATE = '2999-12-31');

临时表VT_INC

ID	name	bal	start_date	end_date
001	张三	100	2018-01-03	2999-12-31
002	李四	200	2018-01-03	2999-12-31
…

以下两条记录被排除
001 张三 1000 2018-01-01 2999-12-31
001 张三 1000 2018-01-02 2999-12-31

第三步：

修改目标表进行关链更新操作

UPDATE TAR
SET END_DT = '2018-01-03' --一般用sysdate-1表示
WHERE END_DT = '2999-12-31' AND ID IN (SELECT ID FROM VT_INC);

目标表TAG(拉链表)：

ID	name	bal	start_date	end_date
001	张三	1000	2018-01-01	2018-01-03

第四步：

修改目标表进行开链插入操作

INSERT INTO TAR(ID,NAME,BAL,START_DT,END_DT)
SELECT ID,NAME,BAL,START_DT,END_DT
FROM VT_INC;

目标表TAG(拉链表)：

ID	name	bal	start_date	end_date
001	张三	1000	2018-01-01	2018-01-03
001	张三	100	2018-01-03	2999-12-31
002	李四	200	2018-01-03	2999-12-31