拓扑园

  • O&M
    • Universal部署
    • PHP+VUE+Laravel相关
  • Oracle性能优化
  • Oracle项目案例
    • Oracle近期项目案例(目录)
    • Oracle实战问题解析(目录)
    • Oracle数据库名变更流程(2种方式)
    • Oracle数据库目录更换流程(使用Oracle的clone工具)
    • Oracle数据库迁移方案(目录)
    • 标准化文档系列
  • Oracle基础知识
    • LLL的Oracle培训(分类)
    • LLL的docker培训(分类)
    • 标准化文档系列--(分类)
    • Oracle核心经典分析(分类)
    • 图灵小队----(分类并包含以下文章)
    • --MySQL8.0/Oracle/Memcached/Redis等安装配置于RHEL/OL6/7/8.X系列-运行环境最优配置
    • --PG安装配置于RHEL/9X系列-运行环境最优配置
    • --自动维护任务详解-开启、关闭信息统计收集(统计信息)
    • --图灵小队—Oracle/PostgreSQL下创建一个用户测试表(自行定义数据行)
    • --图灵小队-Oracle存储过程导出表的明细_UTL_FILE(文章)
    • --图灵小队-Oracle数据库删除/卸载操作指南(文章)
    • --图灵小队-Oracle常用性能查询SQL语句(文章)
    • --图灵小队-Oracle数据库上线前检查(文章)
    • --图灵小队-Oracle常用SQL语句(文章)
    • --图灵小队—Linux/Oracle脚本/MySQL合集(持续更新)
    • --图灵小队-Oracle技巧记录(文章)
    • ADG
    • RAC
    • ASM
    • OGG
    • RMAN
    • EXPDP/IMPDP
    • 工厂数据导入导出系列
  • MySQL
    • MySQL数据库规范
    • MySQL项目案例
    • MySQL安装配置
    • MYSQL集群项目
    • MySQL常见处理
    • MySQL-Sysbench专题
    • MySQL-Percona Toolkit专题
  • Linux
    • Shell编程
    • kubernetes
    • docker
    • Linux
    • PHP
    • Nginx
    • haproxy
    • mail
    • 网站
    • 域名
    • 网址收藏
  • 数据中心
    • 新框架系统集合
    • 工作文档
    • EBS数据文件扩容
    • VMware虚拟化
    • EBS系列
    • 大数据
    • SVN
    • zabbix
    • SAP
    • 备份相关
    • FC交换机
    • SVN
  • K-Studing
    • D8-Python学习
    • Oracle/MySQl等面试题
    • LG-MySQL
    • LG-Docker/K8S
    • LG-PostgreSQL
    • LG-ORACLE_BBED
    • LG-ORACLE
    • LG-Elasticsearch(ES)+ELK
    • Oracle-19C-OCP
    • WERN_ORACLE培训
    • redis数据库
    • Nginx培训学习系列
  • 其他
    • 外研英语4年级下册-听力
    • 影视系列
    • 如何使用iTunes软件通过抓包下载旧版本的ios的app
天高任鸟飞
Oracle/MySQL数据库恢复/数据迁移/生产规范报告技术交流:TEL:18562510581(微信同号);加微信入群
  1. 首页
  2. 技术学习系列
  3. WERN_ORACLE培训
  4. 正文

第10部分 oracle11g的表分区

2020年3月24日 648点热度 0人点赞 0条评论

一、分区表

(1)允许用户将一个表分成多个分区

(2)用户可以执行查询,只访问表中的特定分区

(3)将不同的分区存储在不同的磁盘,提高访问性能和安全性

(4)可以独立地备份和恢复每个分区

目的:对于大数据量的表,比如一个表数据超过上亿条的身份证信息,如果查询一个人的信息,从上亿条中进行检索会非常慢,此时就需要用到分区表进行查询,会极大提高查询速度。

二、传统的表分区的类型(范围分区、散列分区、列表分区、复合分区)

image.png

1、范围分区:RANGE

  • 范围分区-以表中的一个列或一组列的值的范围分区

  • 范围分区的语法:

PARTITION BY RANGE (column_name)

(

  PARTITION part1 VALUE LESS THAN(range1),

  PARTITION part2 VALUE LESS THAN(range2),

  ...

  [PARTITION partN VALUE LESS THAN(MAXVALUE)]

);

    (1)创建分区表

create table sales(
  product_id varchar2(5),sales_count number(10,2)
)
partition by range(sales_count)
(
  partition p1 values less than (1000),//——p1<1000
  partition p2 values less than (2000),//——1000=<p2<2000
  partition p3 values less than (3000) //——2000=<p3<3000
);

    (2)查看分区表:

SQL>select * from user_tab_partitions u where table_name='SALES';

image.png

(3)插入数据,进行测试;插入普通表和分区表的方式是一样的

SQL>insert into sales values('1',600);

SQL>insert into sales values('2',1000);

SQL>insert into sales values('3',2300);

SQL>insert into sales values('4',6000);——超过范围值,就无法插入

image.png

image.png

SQL> select * from sales partition(p1);——查看

image.png

(4)增加一个分区:

SQL> alter table sales add partition p4 values less than(maxvalue);//无穷大

image.png

2、散列分区(补足范围分区的弊端)——HASH分区

  • 允许用户对不具有逻辑范围的数据进行分区 

  • 通过在分区键上执行HASH函数决定存储的分区

  • 将数据平均地分布到不同的分区

语法:

        PARTITION BY HASH (column_name)

        PARTITIONS number_of_partitions;

        或

        PARTITION BY HASH (column_name)

        ( PARTITION part1 [TABLESPACE tbs1],

          PARTITION part2 [TABLESPACE tbs2],

          ...

          PARTITION partN [TABLESPACE tbsN]

        );

(1)创建散列分区表

create table my_emp(    
       empno number,ename varchar2(10)
    )
    partition by hash(empno)
    (
    partition p1,partition p2
    )

SQL> select * from user_tab_partitions u where table_name='MY_EMP';

 image.png

查看分区中的值:SQL>select * from my_emp partition(p1);

image.png

3、列表分区-LIST

  • 允许用户将不相关的数据组织在一起

语法:

PARTITION BY LIST (column_name)

(

  PARTITION part1 VALUES (values_list1),

  PARTITION part2 VALUES (values_list2),

  ...

  PARTITION partN VALUES (DEFAULT)

);

(1)创建列表分区:

create table personCity(
id number,name varchar2(10),city varchar2(10)
)
partition by list(city)
(
partition east values('kaifeng','shangqiu'),
  partition western   values('luoyang'),
  partition south values('xuchang'),
  partition north values('xinxiang')
)

(2)插入数据

insert into personcity values(1,'A','kaifeng');
insert into personcity values(2,'B','shangqiu');
insert into personcity values(3,'C','luoyang');

(3)查看数据分区表

select * from personcity partition(eastern);
image.png

4、复合分区

  • 范围分区与散列分区,或范围分区与列表分区的组合

  • 语法并不难,最重要的是知道如何使用

    语法:

    

    PARTITION BY RANGE (column_name1)

    SUBPARTITION BY HASH (column_name2)

    SUBPARTITIONS number_of_partitions

    (

      PARTITION part1 VALUE LESS THAN(range1),

      PARTITION part2 VALUE LESS THAN(range2),

      ...

      PARTITION partN VALUE LESS THAN(MAXVALUE)

    );

例子:

create table student (
sno number,sname varchar2(10)
)
partition by range(sno)
subpartition by hash(sname)
subpartitions 4
(
partition p1 values less than(1000),
partition p2 values less than(2000),
partition p3 values less than(maxvalue)
)

通过常用的方法,只能看到range 3个大分区,但是每个分区中的4个子分区是无法查看的

image.png

可以查询子分区的方法,在EM中查看

image.png

三、11g新增的表分区的类型——引用分区

  • 引用分区:基于由外键引用的父表的分区的方法,它依赖已有的父表子表的关系,子表通过外键关联到父表,进而继承了父表的分区方式而不需自己创建,子表还继承了父表的维护操作。 

   1,主表是范围分区,子表是引用分区 

   2,主表是列表分区,子表是引用分区 

   3,主表是散列分区,子表是引用分区 

例子:

    1、    创建父表(以主表为范围分区进行展示)

create table student(
	stu_id number primary key,stu_name varchar2(10),grade varchar2(10)
)
partition by range(stu_id)
(
    partition par_stu1 values less than (1000),
    partition par_stu2 values less than (2000),
    partition par_stu3 values less than (maxvalue)
)

2、创建字表(创建外键的即为子表)

create table score(
	id number primary key,stu_id number not null,cource_name varchar2(20),
	score number,
	constraint fk_score foreign key(stu_id) references student(stu_id)
)
partition by reference(fk_score);  ---表示引用分区,引用外键(fk_score)建立的分区

外键是参照了student的stu_id这一列,而在student的stu_id这一列已经有了3个range分区了。而字表score进行了一个引用分区,引用的是fk_score这个外键,对应的是student表中的stu_id列。因此,子表同样会创建和父表一样的分区(即引用分区)

主表(父表)student

子表:score

查询:SQL> select * from user_tab_partitions u where u.table_name in('STUDENT','SCORE');

image.png

虽然HIGH_VALUE值,score中是空的,但是是和student的high_value对应的,是继承关系(根据下面的例子)

例子:通过例子可以看出子表的引用分区和父表的分区是一致的

SQL>inset into student values(600,'AA','grade 1');
SQL>inset into student values(1200,'BB','grade 2');
SQL>inset into score values(1,600,'Math','80');
SQL>inset into score values(2,1200,'English','90');

1585147984654208.png

四、11g新增的表分区的类型——间隔分区

  • 间隔分区:可以完全自动地根据间隔阈值创建范围分区,它是范围分区的扩展 。 

     在数据仓库中有广泛的应用

create table sale_detail
(
    sale_detail_id number,product_id number,quantity number,sale_date date
)
partition by range(sale_date)
interval(numtoyminterval(1,'MONTH')) ————interval自动增长
(
    partition p_201006 values less than(to_date('20100601','yyyymmdd'))
);

interval——自动增长

select * from user_tab_partitions u where u.table_name in('SALE_DETAIL');

image.png

插入新数据:

SQL>insert into sale_detail values(1,100,20,to_date('20100121','yyyymmdd'));此句小于6.1号,会插入到分区p_201006中
SQL>insert into sale_detail values(2,100,30,to_date('20100621','yyyymmdd'));
SQL>insert into sale_detail values(3,100,30,to_date('20100721','yyyymmdd'));

查看:

SQL>select * from sale_detail partition(p_201006);

image.png

再次查看分区:发现自动创建了2个分区,且范围为7.1号,8.1号。原因是定义自动增长时,按照一个月一个月的增加。

image.png

如果突然增加到11月,则只会顺延增加到 12月份的分区

SQL>insert into sale_detail values(3,100,30,to_date('20101121','yyyymmdd'));

image.png

五、11g新增的表分区的类型——基于虚拟列的分区

  • 基于虚拟列的分区:把分区建立在某个虚拟列上,即建立在函数或表达式的计算结果上,来完成某种任务

1、创建表:

create table sale(
    sale_id number primary key,product_id number,price number, 
    quantity number,sale_date date,
    total_price as(price * quantity)virtual————通过vitual指定虚拟列
)
partition by range(total_price)
(
    partition p_1000 values less than(1000),
    partition p_2000 values less than(2000),
    partition p_3000 values less than(maxvalue)
)

2、插入数据

SQL>insert into sale(sale_id,product_id,price,quantity,sale_date)values(1,100,15,20,sysdate);

image.png

3、查看,因为15*20=300,所以计入了p_1000分区表

image.png

六、11g新增的表分区的类型——系统分区

  • 系统分区:不指定分区列,由ORACLE来完成分区的控制和管理,它没有了范围分区或列表分区的界限。

1、创建表

SQL>create table person(
    id number,name varchar2(10)
)
partition by system(
    partition p1,partition p2,partition p3
)

image.png

image.png

2、测试数据

SQL> insert into  person partition(p1) values(1,'A');
SQL> insert into  person partition(p2) values(2,'B');
SQL> insert into  person partition(p3) values(3,'C');

image.png

3、查看分区

SQL> select * from person partition(P1);
SQL> select * from person partition(P2);
SQL> select * from person;

image.png

image.png

七、操作已分区的表

  • 在已分区的表中插入数据与操作普通表完全相同(貌似系统分区除外,需要制定分区),Oracle会自动将数据保存到对应的分区查询、修改和删除分区表时可以显式指定要操作的分区 

  • 查询、修改和删除分区表时可以显式指定要操作的分区 

INSERT INTO SALES3 VALUES (‘P001’, ’02-3月-2001', 2000);
INSERT INTO SALES3 VALUES (‘P002’, ’10-5月-2001', 2508);
INSERT INTO SALES3 VALUES (‘P003’, ’05-7月-2001', 780);
INSERT INTO SALES3 VALUES (‘P004’, ’12-9月-2001', 1080);
SELECT * FROM SALES3 PARTITION (P3);
DELETE FROM SALES3 PARTITION (P2);

八、分区维护操作

  • 分区维护操作修改已分区表的分区。

  • 分区维护的类型:

        计划事件 - 定期删除最旧的分区

        非计划事件 - 解决应用程序或系统问题

  • 分区维护操作有:

        1、添加分区——在最后一个分区之后添加新分区

  SQL> ALTER TABLE SALES ADD PARTITION P4 VALUES LESS THAN (4000);

image.png       

 2、删除分区——删除一个指定的分区,分区的数据也随之删除

  SQL> ALTER TABLE SALES DROP PARTITION P4;

        3、截断分区——删除指定分区中的所有记录 

  SQL> ALTER TABLE SALES TRUNCATE PARTITION P3;

        4、合并分区——将范围分区或复合分区的两个相邻分区连接起来

  SQL> ALTER TABLE SALES MERGE PARTITIONS S1, S2 INTO PARTITION S2;

        5、拆分分区——将一个大分区中的记录拆分到两个分区中

 SQL> ALTER TABLE SALES SPLIT PARTITION P2 AT (1500)INTO (PARTITION P21, PARTITION P22);



本作品采用 知识共享署名 4.0 国际许可协议 进行许可
标签: 暂无
最后更新:2020年3月26日

admin

这个人很懒,什么都没留下

打赏 点赞
< 上一篇
下一篇 >

文章评论

您需要 登录 之后才可以评论

COPYRIGHT © 2022 拓扑园. ALL RIGHTS RESERVED.

Theme Kratos Made By Seaton Jiang

鲁ICP备2021020523号

鲁ICP备2021020523号