8.1 表分区
分而治之
目的:
- 安全(鸡蛋不要放在一个篮子里)
- 效率****(快速找到南方it学院所有姓张的人)
- 便于维护
场景:
- 数据量极大(大于 2G)
- 历史数据比重比较大
分类:
- 范围分区
- 列表分区
- 哈希分区(hash)
在以上分区的基础上,可以两两结合,形成 复合分区,但常用的就是两种:
- 范围-列表分区
- 范围-哈希分区
范围分区:
-- 创建一个普通表的语句
create table person1 (id int primary key, name varchar2(20), birth date);
-- 数据将会在同一个表空间同一个段内
insert into person1 values (1, 'sss', sysdate);
-- 创建一个分区表
-- 这里是按照生日进行范围分区
-- 语句的基本格式就是在普通建表的语句上,增加 partition by 语句块
create table person2 (name varchar2(20), birth date)
partition by range (birth)
(
partition p1 values less than (to_date('19950101','yyyymmdd')), -- 'values less than'
partition p2 values less than (to_date('20000101','yyyymmdd')),
partition p3 values less than (maxvalue) -- 默认分区
);
-- 插入,数据会根据分区的情况进入不同的分区内
insert into person2 values ('张三', to_date('19940707'));
insert into person2 values ('李四', to_date('19980707'));
insert into person2 values ('王五', to_date('20040707'));
-- 查询表中所有数据
select * from person2;
-- 查询特定分区上数据
select * from person2 partition (p3);
-- 可以为不同的分区指定不同的表空间
-- 没有指定表空间的分区,使用用户的默认表空间
-- 所以,一个表内的数据可以存在于不同表空间里,也就是可以存放在不同数据文件中,不同磁盘上
-- 因此,分区表能增强数据的安全性
create table person3 (name varchar2(20), birth date)
partition by range (birth)
(
partition p1 values less than (to_date('19950101','yyyymmdd')) tablespace system,
partition p2 values less than (to_date('20000101','yyyymmdd')) tablespace sysaux,
partition p3 values less than (maxvalue) tablespace users
);
-- 可以在其他类型上进行范围分区
-- 也可以在多个字段上进行范围分区
create table person4 (name varchar2(20), birth date, score number)
partition by range (birth, score)
(
partition p1 values less than (to_date('19900101','yyyymmdd'), 60),
partition p2 values less than (to_date('19900101','yyyymmdd'), 90),
partition p3 values less than (to_date('19990101','yyyymmdd'), 60),
partition p4 values less than (to_date('19990101','yyyymmdd'), 90),
partition p5 values less than (maxvalue, maxvalue)
);
列表分区:
-- 如果是生日的这样的字段,数据是连续的,应该使用范围分区
create table person (name varchar2(20), birth date)
partition by range(birth)
(
partition p1 values less than (to_date('19900101', 'yyyymmdd')) tablespace users,
partition p2 values less than (maxvalue)
);
insert into person values ('aaa', to_date('19871212', 'yyyymmdd'));
select * from person partition (p1);
/*
where birth between 1987 and 1990
where sex in ('男', '女')
*/
-- 但是像性别、民族等字段,更适合使用的是列表分区
-- 下面一个例子,使用性别作为分区字段,男的一个区,女的一个区
create table person2 (name varchar2(20), sex varchar(10))
partition by list (sex)
(
partition p1 values ('男'),
partition p2 values ('女')
);
insert into person2 values ('aaa', '男');
insert into person2 values ('bbb', '女');
insert into person2 values ('ccc', '未知'); -- 报错
select * from person2 partition (p2);
-- 默认分区的写法
create table person3 (name varchar2(20), sex varchar(10))
partition by list (sex)
(
partition p1 values ('男'),
partition p2 values ('女'),
partition p3 values (default)
);
insert into person3 values ('ccc', '未知');
select * from person3 partition (p3);
-- 可以为每个分区指定表空间
create table person3 (name varchar2(20), sex varchar(10))
partition by list (sex)
(
partition p1 values ('男') tablespace users,
partition p2 values ('女') tablespace system,
partition p3 values (default)
);
哈希分区:
-- 哈希分区
-- 主要用在一些比较离散,不好分类的数据上,比如产品名字
-- 让 oracle 使用哈希算法自动计算数据的分区
-- 创建语句,非常简单
create table person4 (name varchar2(20), sex varchar2(10))
partition by hash (name)
(
partition p1,
partition p2 tablespace users
);
insert into person4 values ('aaa', '男');
insert into person4 values ('收款', '男');
select * from person4 partition (p1);
-- 上面的语句可以进一步简化为:
create table person5 (name varchar2(20), sex varchar2(10))
partition by hash (name)
partitions 5;
-- 为每个分区指定表空间
create table person6 (name varchar2(20), sex varchar2(10))
partition by hash (name)
partitions 3 store in (users, system, sysaux);
范围-列表分区:
-- 首先,按照生日进行列表分区,分了三个区
-- 其次,在每个分区内,又按照性别分了三个区
-- 所以,总共是 3 个分区 9 个子分区
create table person8 (name varchar2(20), sex varchar2(10), birth date)
partition by range(birth)
subpartition by list(sex)
subpartition template
(
subpartition sp01 values ('男'),
subpartition sp02 values ('女'),
subpartition sp03 values (default)
)
(
partition p1 values less than (to_date('19900101', 'yyyymmdd')),
partition p2 values less than (to_date('20000101', 'yyyymmdd')),
partition p3 values less than (maxvalue)
);
insert into person8 values ('aaa', '男', to_date('19900202'));
-- 查询这条数据,有以下三种方式:
select * from person8;
select * from person8 partition (p1);
select * from person8 subpartition (p1_sp01);
范围-哈希分区:
-- 先按照生日,将数据分为三个区
-- 然后在每个分区内,又按照哈希算法分成了三个区
-- 这样就保证了每个分区内的数据尽量的少,而且分区进行平衡
create table person7 (name varchar2(20), birth date)
partition by range (birth)
subpartition by hash (name) subpartitions 3
(
partition p1 values less than (to_date('19900101', 'yyyymmdd')),
partition p2 values less than (to_date('20000101', 'yyyymmdd')),
partition p3 values less than (maxvalue)
);
相关字典表:
select * from user_objects where object_name ='PERSON8';
select * from user_tables where table_name = 'PERSON8';
select * from user_tab_partitions where table_name = 'PERSON8';
select * from user_tab_subpartitions where table_name = 'PERSON8';
操作表分区:
-- 添加分区
alter table person add partition p9 values less than (MAXVALUE);
alter table person add partition p9 values (1, 2); -- 针对 list 分区
alter table person add partition; -- 针对 hash 分区
-- 删除分区
alter table person drop partition p3;
-- 删除分区内数据
alter table person truncate partition p3;
-- 合并相邻分区
alter table person merge partitions p2, p3 into partition p8;
-- 拆分分区
alter table person split partition p2 at (3000) into (partition p3, partition p14); -- 范围分区的拆分
alter table person split partition p2 values (1,2) into (partition p3, partition p4); -- 列表分区的拆分
alter table person split partition p2 into (partition p3 values (1, 2), partition p4 values (3), partition p5); -- 列表分区的拆分
-- 重命名分区
alter table person rename partition p2 to p12;
8.2 物化视图
以空间换时间
物化视图,可以看做是 加了同步功能的临时表,它占用实际的存储空间。
创建的物化视图,可以在 user_tables
字典表里查到,因为本质上它就是一张表。
如果要创建这样的临时表,需要考虑几个方面:
- 什么时候初始化数据
build immediate -- 建表的时候顺带初始化数据,默认 build deferred -- 数据延迟创建
- 如果基表中的数据发生变化的时候,以什么样的策略保持同步
refresh complete -- 全量刷新,即将旧的完全删掉,再重建 refresh fast -- 增量刷新 refresh force -- 由 Oracle 自动判断刷新方式,默认 refresh never -- 不同步数据
- 如果基表中的数据发生变化,数据同步的时机
on commit -- 基本的事务提交立刻同步到物化视图 on demand start with 开始时间 next 下一个时间 -- 定时同步
- 如果查询基表中的数据,而这些数据同时包含在物化视图中,那么,是不是让 Oracle 将查询语句更改为查询物化视图
enable query rewrite disable query rewrite -- 默认
创建物化视图的示例:
-- 创建一个简单的物化视图的例子
create materialized view mv_emp1
build immediate -- 创建物化视图的时候,立刻初始化数据
refresh complete -- 采取全量同步的方式
on commit -- 基表中数据提交会触发同步行为
-- on demand sysdate next sysdate + 1 -- 每天更新一次
enable query rewrite
as
select * from emp where sal > 3000;
-- 查询
select * from mv_emp1;
-- 向基表中插入数据
insert into emp (empno, ename, sal) values (8377, '王武', 4444);
-- 事务的提交才会触发同步,所以这一步不可少
commit;
-- 查询,看数据是否已经同步
select * from mv_emp1;
-- 如果要增量同步,需要记录在上次更新到下次更新时间内,基表内所有变化
-- 在这里,使用的是物化视图日志
-- 创建语句如下:
create materialized view log on emp [with rowid/primary key];
-- 然后就可以指定增量同步的方式了
create materialized view mv_emp2
build immediate
refresh fast -- 采取增量同步的方式
on demand sysdate next sysdate + 1 -- 每天更新一次,on demand 后面还有其他很多不同形式的命令
enable query rewrite
as
select * from emp where sal > 3000;
-- 删除
drop materialized view log on emp;
drop materialized view mv_emp2;
8.3 索引
位图索引示例:
字段 | 字段 | 索引 | 索引 | 索引 | 索引 | 索引 | 查询(xb=女 and hy=否) | ||
---|---|---|---|---|---|---|---|---|---|
XB | HY | xb=男 | xb=女 | xb=未知 | hy=是 | hy=否 | 将两个索引做与运算 | ||
男 | 是 | 1 | 0 | 0 | 1 | 0 | 0 | ||
女 | 是 | 0 | 1 | 0 | 1 | 0 | 0 | ||
女 | 否 | 0 | 1 | 0 | 0 | 1 | 1(符合条件) | ||
男 | 是 | 1 | 0 | 0 | 1 | 0 | 0 | ||
100亿 | 100亿 | - | - | - | - | - | - | ||
未知 | 否 | 0 | 0 | 1 | 0 | 1 | 0 | ||
女 | 是 | 0 | 1 | 0 | 1 | 0 | 0 |