oracle [分区表，临时表，拉链表]

目录

一、分区表

概念

范围分区

列表分区

散列分区

二、临时表

会话级临时表

事务级临时表 (默认情况)

三、拉链表 （ETL算法）

ETL算法

拉链定义

算法

​编辑流程

使用

---

一、分区表

概念

partition
将一张表分成好几个区域放在不同的表空间里（物理文件）
只需要从分区中查找需要的数据

分区是建表之初建立的，不能后期添加。

优点

1. 提高查询效率，避免扫描整个表
2. 如果哪个分区出现故障，其他分区数据不影响
   维修只需要处理故障的分区就行，可用性更高

已经有的表不能变成分区表，创建表的时候建立分区才行

分类：

范围分区(range)
时间，数值
列表分区(list)
例如国家名字，性别
散列分区(hash：哈希)
复合分区 （子分区：subpartition ）
交换分区

范围分区

时间，数值
新增分区高于最后一个分区界限

范围分区 range：
创建:
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>=0 and values <值1
values>=值1 and values <值2
values>=值2 and values <值3
values>=值3 and values <值4
...*/

新增分区：首先表一定是分区表才能新增
新增分区高于最后一个分区界限
alter table 表名 add partition 分区名 values less than (值或日期);
删除分区
如果分区里有数据，会被一起删掉
alter table 表名 drop partition 分区名 ;

日期(v_date)

create table test
(
v_date date,
v_month varchar2(6),
v_day varchar2(8),
client_no varchar2(4),
fee number
)
partition by range (v_date)
(
  partition p_201712 values less than --第一个分区：2018年1月1号之前的
  (to_date(' 2018-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
  partition p_201801 values less than --第二分区：2018年2月1号之前的
  (to_date(' 2018-02-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
  partition p_201802 values less than --第三分区：2018年3月1号之前的
  (to_date(' 2018-03-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
  partition p_201803 values less than --第四分区：2018年4月1号之前的
  (to_date(' 2018-04-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')),
  partition p_201804 values less than (maxvalue) --第五分区：2018年4月之后
);

查询使用
--查找p_201803分区数值等于10的数据
select * from test partition(p_201803) where fee=10; 

insert 分区
只能在创建好的分区插入数据
--p_201803分区里插入一条数据
insert into test partition(p_201803)
select to_date('20180331','yyyymmdd'),'201803','20180331','0001',80
from dual ;
commit;

--删除第五分区
alter table test drop partition p_201804; 
--新增一个分区里面存放2018年5月1号之前的数据
alter table test add partition p_201804 values less than 
(to_date(' 2018-05-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS'));

数值 (fee) 

create table test
(
v_date date,
v_month varchar2(6),
v_day varchar2(8),
client_no varchar2(4),
fee number
)
partition by range (fee)
(
  partition p_1 values less than (10),
  partition p_10 values less than (20),
  partition p_20 values less than (30),
  partition p_30 values less than (40),
  partition p_40 values less than (50),
  partition p_50 values less than (60),
  partition p_60 values less than (70),
  partition p_70 values less than (MAXVALUE)
  );

select * from test partition(p_10);
alter table test drop partition p_70;
alter table test add partition p_70 values less than (MAXVALUE);

列表分区

一般用于数据可枚举，有限个值，可以考虑列表分区，例如国家名字，性别

列表分区 list：
创建:
create table 表名
(列名 数据类型
...)
partition by list (字段)
( 
partition 分区名1 values (值),
partition 分区名2 values (值),
partition 分区名3 values (值),...
);
新增分区：
alter table 表名 add partition 分区名 values (值);
删除分区
alter table 表名 drop partition 分区名 ;

create table test
(
v_date date,
v_month varchar2(6),
v_day varchar2(8),
client_no varchar2(4),
fee number
)
partition by list (v_month)
(
  partition p_201801 values ('201801'),
  partition p_201802 values ('201802'),
  partition p_201803 values ('201803')
);

select * from test partition(p_201803);
alter table test add partition p_201804 values ('201804');
alter table test drop partition p_201804;

insert into test
select to_date('20180401','yyyymmdd'),'201801','20180101','0001',0
from dual 

散列分区

利用hash函数打散某列使数据均匀分布，一般用于均衡I/O，缺点数据不容易管理

散列分区 hash：
创建:
create table 表名
(列名 数据类型
...)
partition by hash (字段)
( 
partition 分区名1,
partition 分区名2,
partition 分区名3,...
);
新增分区：
alter table 表名 add partition 分区名;

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

create table test
(
v_date date,
v_month varchar2(6),
v_day varchar2(8),
client_no varchar2(4),
fee number
)
partition by hash (v_month)
(
  partition p1,
  partition p2,
  partition p3
  );

表：
create table test
(
v_date date,
v_month varchar2(6),
v_day varchar2(8),
client_no varchar2(4),
fee number
);
数据：
insert into test
select to_date('20180101','yyyymmdd'),'201801','20180101','0001',0
from dual 
union all
select to_date('20180120','yyyymmdd'),'201801','20180101','0001',10
from dual 
union all
select to_date('20180131','yyyymmdd'),'201801','20180101','0001',20
from dual 
union all
select to_date('20180201','yyyymmdd'),'201802','20180201','0001',30
from dual 
union all
select to_date('20180220','yyyymmdd'),'201802','20180220','0001',40
from dual 
union all
select to_date('20180228','yyyymmdd'),'201802','20180228','0001',50
from dual 
union all
select to_date('20180301','yyyymmdd'),'201803','20180301','0001',60
from dual 
union all
select to_date('20180320','yyyymmdd'),'201803','20180320','0001',70
from dual 
union all
select to_date('20180331','yyyymmdd'),'201803','20180331','0001',80
from dual ;
commit;

范围分区自动扩展

根据年: INTERVAL(NUMTOYMINTERVAL(1,'YEAR'))
根据月: INTERVAL(NUMTOYMINTERVAL(1,'MONTH'))
根据天: INTERVAL(NUMTODSINTERVAL(1,'DAY'))
根据时分秒: NUMTODSINTERVAL( n, { 'DAY'|'HOUR'|'MINUTE'|'SECOND'})
------------------------------------------------------------------
创建分区

create table t_range (
id number PRIMARY KEY, 
test_date date)
partition by range (test_date) interval (numtoyMinterval(1,'MONTH'))
(
    partition p_2014_01_01 values less than (to_date('2014-01-01', 'yyyy-mm-dd'))
)；

insert into t_range select rownum,
to_date(to_char(sysdate - 140, 'J') +
trunc(dbms_random.value(0, 80)),
'J')
from dual
connect by rownum <= 100000;
commit;

二、临时表

临时表就是用来暂时保存临时数据（亦或叫中间数据）的一个数据库对象
存储在临时表空间

会话级临时表

每一个SQL窗口 = 单独会话
当会话结束删除数据（自动删除）
on commit preserve rows

--创建会话级临时表
 create global temporary table 表名(
    列名 列类型,
    ......
 ) on commit preserve rows;

create global temporary table temp1(
      id number,
      name varchar2(100)
)on commit preserve rows;

select * from temp1;
insert into temp1 values(1,'tom');
commit;

事务级临时表 (默认情况)

当commit/rollback或结束会话时，会清除临时表数据
on commit delete rows

 --创建事务级临时表
 create global temporary table 表名(
    列名 列类型,
    ......
 )  on commit delete rows;

 create global temporary table temp2(
   id number,
   name varchar2(1000)
 )  on commit delete rows;
 
 select * from temp2;
 insert into temp2 values(1,'amy');
 commit;

三、拉链表 （ETL算法）

ETL算法

数据仓库特点
建立在业务系统之上

1. 面向主题
   数据仓库内的信息是按主题进行组织的
2. 实时性要求不高
3. 保存过去和现在的数据
4. 数据很少更新
5. 支持分析决策 （管理层）
6. 数据是提炼的或者综合的

业务系统特点

1. 面向应用 （app）
2. 数据是详细的
3. 数据可以更新，实时
4. 支持事务处理
5. 保持当前数据

拉链定义

数据仓库中
记录数据在某一时间内的状态以及数据在某一时点上的变化的数据存储方式

优点
数据优化

 

1. 节省存储空间，避免没必要的重复数据（化繁为简）
2. 记录数据变化，更加直观

缺点
断链难以恢复

算法

新增数据 ==> 开链
删除数据 ==> 关链
修改数据 ==> 先关链，在开新的拉链

流程

 

事务级临时表       

        1.创建临时表 TA_ONE
           存放、转换处理后的数据
           所有数据抽过来

create table TA_ONE as select * from TA_yuan where 1=0;
--获取 TA_OLD 的表结构

insert into TA_ONE (ID,NAME,BAL,START_DT,END_DT) --添加数据
select ID,NAME,BAL,to_date('2022-04-04',yyyyMMdd),to_date('2999-12-31',yyyyMMdd) from SJ_yuan;
 --从 SJ_yuan 中数据添加进去
 commit;

        2.创建临时表 TA_TWO
           存放比对出增量的数据
           新增修改

create table TA_TWO as select * from TA_yuan where 1=0;

insert into TA_TWO (ID,NAME,BAL,START_DT,END_DT) 
select ID,NAME,BAL,START_DT,END_DT 
from TA_ONE 
where (ID,NAME,BAL) not in (
    select * from TA_yuan where END_DT = to_date('2999-12-31',yyyyMMdd));
commit;

        3.修改 TA_yuan
           关链

update TA_yuan set END_DT = to_date('2022-04-04',yyyyMMdd)
where END_DT = to_date('2999-12-31',yyyyMMdd) and ID in (select ID from TA_TWO);
--开链状态 并关链
commit;

        4.修改
           开链插入数据

insert into TA_yuan select * from TA_TWO;
commit;

使用

四号的数据

开始日期<=‘2014-04-04’ 
 AND 
 结束日期 > ‘2014-04-04’;