oracle 分表分区操作

最新推荐文章于 2024-07-03 09:31:24 发布

Murphy丶杨

最新推荐文章于 2024-07-03 09:31:24 发布

阅读量5.7k

点赞数 4

分类专栏：数据库文章标签： oracle 分表分区优化数据库

本文链接：https://blog.csdn.net/Reinhard_yang/article/details/88643054

版权

数据库专栏收录该内容

5 篇文章 0 订阅

订阅专栏

第一步：分表

如果历史表中存储了很多年的数据，会造成严重的数据冗余。那如果将历史表分表存储，比如每年创建一个表，数据存储到对应的年表中，必定会减少很多数据量。（如果分成年表数据量还是过大，可以细分到月表，天表…）。

我们这里以创建年表为例，写一个创建年表的存储过程，利用PLSQL定时任务定时执行此存储过程（定时每年12月31号创建下一年的年表）。存储过程如下，定时任务查看此篇文章：PLSQL执行Oracle定时任务

CREATE OR REPLACE 
PROCEDURE CREATE_YEAR_TABLE IS
  /*变量*/
  grantSql VARCHAR2(50);
  yearStr    VARCHAR2(4);
  tableCount int(2);
  createSql  VARCHAR2(1000);
BEGIN
  /*权限*/
  grantSql := 'grant create any table to thtf_taiyuan';
  EXECUTE IMMEDIATE grantSql;
  /*创建年表	注意create table 后边的空格*/
  SELECT TO_CHAR(SYSDATE, 'yyyy')+1 INTO yearStr FROM dual;
  createSql := 'CREATE TABLE ' || 'THTF_TABLE_YEAR_' || yearStr ||
		'( SHE_SHI_CODE VARCHAR2(100),
		SHE_SHI_TYPE NUMBER DEFAULT 1,
		FEN_GONG_SI VARCHAR2(100),
		SHUI_HAO NUMBER(20,4) DEFAULT 0,
		PRE_SHUI_HAO NUMBER(20,4) DEFAULT 0,
		DIAN_HAO NUMBER(20,4) DEFAULT 0,
		PRE_DIAN_HAO NUMBER(20,4) DEFAULT 0,
		RE_HAO NUMBER(20,4) DEFAULT 0,
		PRE_RE_HAO NUMBER(20,4) DEFAULT 0,
		SHI_JIAN DATE DEFAULT SYSDATE,
		STATE NUMBER DEFAULT 1 )';
  SELECT COUNT(1) INTO tableCount FROM user_tables WHERE table_name = CONCAT('THTF_TABLE_YEAR_', yearStr);
  IF tableCount = 0 THEN
    EXECUTE IMMEDIATE createSql;
    COMMIT;
  END IF;
END CREATE_YEAR_TABLE;

第二步：分区

年表创建过后，查询就是查询年表中的数据，可是虽然分表了，但是年表中的数据量仍然很大，查询速度虽然有提升，但并不能满足用户的要求。便考虑到分表再分区，即将历史数据以不同的年表来存储，在年表中按月分区。

说道分区，要恶补一下了~

数据库分区：就是减少SQL操作的数据量，从而提升查询效率。表分区后，逻辑上仍然是一张表，只不过将表中的数据在物理上存放到多个表空间上。这样在查询数据时，会查询相应分区的数据，避免了全表扫描。

分区又分为水平分区、垂直分区。

水平分区：就是对行进行分区，举个例子来说，就是一个表中有1000万条数据，每100万条数据划一个分区，这样就将表中数据分到10个分区中去。水平分区要通过某个特定的属性列进行分区，比如我用的列就是Date时间。

垂直分区：通过对标垂直划分来减少表的宽度，从而提升查询效率。比如一个学生表中，有他相关的信息列，还有论文列以CLOB存储。这些以CLOB存储的论文并不会经常被访问到，这时候就要把这些不经常使用的CLOB划分到另一个分区，需要访问时再调用它。

总的来说，分区的主要目的还是避免了全表扫描，从而提升查询速度。

接下来在上面的存储过程的基础上，我们创建按月分区。

CREATE OR REPLACE 
PROCEDURE CREATE_YEAR_TABLE IS
  grantSql VARCHAR2(50);
  yearStr    VARCHAR2(4);
  tableCount int(2);
  createSql  VARCHAR2(1000);
BEGIN
  /*权限*/
  grantSql := 'grant create any table to thtf_taiyuan';
  EXECUTE IMMEDIATE grantSql;
  /*创建年表	注意create table 后边的空格*/
  SELECT TO_CHAR(SYSDATE, 'yyyy')+1 INTO yearStr FROM dual;
  createSql := 'CREATE TABLE ' || 'THTF_TABLE_YEAR_' || yearStr ||               
	'( SHE_SHI_CODE VARCHAR2(100),
	SHE_SHI_TYPE NUMBER DEFAULT 1,
	FEN_GONG_SI VARCHAR2(100),
	SHUI_HAO NUMBER(20,4) DEFAULT 0,
	PRE_SHUI_HAO NUMBER(20,4) DEFAULT 0,
	DIAN_HAO NUMBER(20,4) DEFAULT 0,
	PRE_DIAN_HAO NUMBER(20,4) DEFAULT 0,
	RE_HAO NUMBER(20,4) DEFAULT 0,
	PRE_RE_HAO NUMBER(20,4) DEFAULT 0,
	SHI_JIAN DATE DEFAULT SYSDATE,
	STATE NUMBER DEFAULT 1 ) 
        /*按月分区*/
	PARTITION BY RANGE(SHI_JIAN) 
	INTERVAL(NUMTOYMINTERVAL(1,''' || 'MONTH' || '''))
	( PARTITION PART1 VALUES LESS THAN(TO_DATE('''|| CONCAT(yearStr, '-11-01') ||''','''|| 'YYYY-MM-DD' ||''')) )';
  SELECT COUNT(1) INTO tableCount FROM user_tables WHERE table_name = CONCAT('THTF_TABLE_YEAR_', yearStr);
  IF tableCount = 0 THEN
    EXECUTE IMMEDIATE createSql;
		--添加注释
		EXECUTE IMMEDIATE 'COMMENT ON COLUMN ' || 'THTF_TABLE_YEAR_' || yearStr || '.SHE_SHI_CODE IS ''设施编号''';
		EXECUTE IMMEDIATE 'COMMENT ON COLUMN ' || 'THTF_TABLE_YEAR_' || yearStr || '.SHE_SHI_TYPE IS ''设施类型''';
		EXECUTE IMMEDIATE 'COMMENT ON COLUMN ' || 'THTF_TABLE_YEAR_' || yearStr || '.FEN_GONG_SI IS ''分公司''';
		EXECUTE IMMEDIATE 'COMMENT ON COLUMN ' || 'THTF_TABLE_YEAR_' || yearStr || '.SHUI_HAO IS ''水耗''';
		EXECUTE IMMEDIATE 'COMMENT ON COLUMN ' || 'THTF_TABLE_YEAR_' || yearStr || '.PRE_SHUI_HAO IS ''上一小时水耗''';
		EXECUTE IMMEDIATE 'COMMENT ON COLUMN ' || 'THTF_TABLE_YEAR_' || yearStr || '.DIAN_HAO IS ''电耗''';
		EXECUTE IMMEDIATE 'COMMENT ON COLUMN ' || 'THTF_TABLE_YEAR_' || yearStr || '.PRE_DIAN_HAO IS ''上一小时电耗''';
		EXECUTE IMMEDIATE 'COMMENT ON COLUMN ' || 'THTF_TABLE_YEAR_' || yearStr || '.RE_HAO IS ''热耗''';
		EXECUTE IMMEDIATE 'COMMENT ON COLUMN ' || 'THTF_TABLE_YEAR_' || yearStr || '.PRE_RE_HAO IS ''上一小时热耗''';
		EXECUTE IMMEDIATE 'COMMENT ON COLUMN ' || 'THTF_TABLE_YEAR_' || yearStr || '.SHI_JIAN IS ''时间''';
		EXECUTE IMMEDIATE 'COMMENT ON COLUMN ' || 'THTF_TABLE_YEAR_' || yearStr || '.STATE IS ''状态值''';
    COMMIT;
  END IF;
END CREATE_YEAR_TABLE;

如果分区要细化到天，将分区语句改为如下：

PARTITION BY RANGE(SHI_JIAN) 
	INTERVAL(NUMTOYMINTERVAL(1,''' || 'DAY' || '''))
	( PARTITION PART1 VALUES LESS THAN(TO_DATE('''|| CONCAT(yearStr, '-01-01') ||''','''|| 'YYYY-MM-DD' ||''')) )';

创建完分区后，如何查询表中有哪些分区呢？

--查分区数
SELECT table_name,partition_name from user_tab_partitions where table_name = 'THTF_TABLE_YEAR_2017'

如何查询分区中的数据呢？

--查分区数据
SELECT * FROM THTF_TABLE_YEAR_2017 PARTITION(PART1)

Range分区：

Range分区是应用范围比较广的表分区方式，它是以列的值的范围来做为分区的划分条件，将记录存放到列值所在的range分区中。

如按照时间划分，2010年1月的数据放到a分区，2月的数据放到b分区，在创建的时候，需要指定基于的列，以及分区的范围值。

在按时间分区时，如果某些记录暂无法预测范围，可以创建maxvalue分区，所有不在指定范围内的记录都会被存储到maxvalue所在分区中。

如：

create table pdba (id number, time date) partition by range (time)
(
   partition p1 values less than (to_date('2010-10-1', 'yyyy-mm-dd')),
   partition p2 values less than (to_date('2010-11-1', 'yyyy-mm-dd')),
   partition p3 values less than (to_date('2010-12-1', 'yyyy-mm-dd')),
   partition p4 values less than (maxvalue)
)

Hash分区：

对于那些无法有效划分范围的表，可以使用hash分区，这样对于提高性能还是会有一定的帮助。hash分区会将表中的数据平均分配到你指定的几个分区中，列所在分区是依据分区列的hash值自动分配，因此你并不能控制也不知道哪条记录会被放到哪个分区中，hash分区也可以支持多个依赖列。

如：

create table test
(
transaction_id number primary key,
item_id number(8) not null
)
partition by hash(transaction_id)
(
partition part_01 tablespace tablespace01,
partition part_02 tablespace tablespace02,
partition part_03 tablespace tablespace03
);

List分区：

List分区也需要指定列的值，其分区值必须明确指定，该分区列只能有一个，不能像range或者hash分区那样同时指定多个列做为分区依赖列，但它的单个分区对应值可以是多个。

在分区时必须确定分区列可能存在的值，一旦插入的列值不在分区范围内，则插入/更新就会失败，因此通常建议使用list分区时，要创建一个default分区存储那些不在指定范围内的记录，类似range分区中的maxvalue分区。
在根据某字段，如城市代码分区时，可以指定default，把非分区规则的数据，全部放到这个default分区。

如：

create table custaddr
(
id varchar2(15 byte) not null,
areacode varchar2(4 byte)
)
partition by list (areacode)
( partition t_list025 values ('025'), 
partition t_list372 values ('372') , 
partition t_list510 values ('510'),
partition p_other values (default)
)

组合分区：

如果某表按照某列分区之后，仍然较大，或者是一些其它的需求，还可以通过分区内再建子分区的方式将分区再分区，即组合分区的方式。

组合分区呢在10g中有两种：range-hash，range-list。注意顺序，根分区只能是range分区，子分区可以是hash分区或list分区。

如：

create table test
(
transaction_id number primary key,
transaction_date date
)
partition by range(transaction_date) subpartition by hash(transaction_id)
subpartitions 3 store in (tablespace01,tablespace02,tablespace03)
(
partition part_01 values less than(to_date(’2009-01-01’,’yyyy-mm-dd’)),
partition part_02 values less than(to_date(’2010-01-01’,’yyyy-mm-dd’)),
partition part_03 values less than(maxvalue)
);
create table emp_sub_template (deptno number, empname varchar(32), grade number)
partition by range(deptno) subpartition by hash(empname)
subpartition template
(subpartition a tablespace ts1,
subpartition b tablespace ts2,
subpartition c tablespace ts3,
subpartition d tablespace ts4
)
(partition p1 values less than (1000),
partition p2 values less than (2000),
partition p3 values less than (maxvalue)
);
create table quarterly_regional_sales
(deptno number, item_no varchar2(20),
txn_date date, txn_amount number, state varchar2(2))
tablespace ts4
partition by range (txn_date)
subpartition by list (state)
(partition q1_1999 values less than (to_date('1-apr-1999','dd-mon-yyyy'))
(subpartition q1_1999_northwest values ('or', 'wa'),
subpartition q1_1999_southwest values ('az', 'ut', 'nm'),
subpartition q1_1999_northeast values ('ny', 'vm', 'nj'),
subpartition q1_1999_southeast values ('fl', 'ga'),
subpartition q1_1999_northcentral values ('sd', 'wi'),
subpartition q1_1999_southcentral values ('ok', 'tx')
),
partition q2_1999 values less than ( to_date('1-jul-1999','dd-mon-yyyy'))
(subpartition q2_1999_northwest values ('or', 'wa'),
subpartition q2_1999_southwest values ('az', 'ut', 'nm'),
subpartition q2_1999_northeast values ('ny', 'vm', 'nj'),
subpartition q2_1999_southeast values ('fl', 'ga'),
subpartition q2_1999_northcentral values ('sd', 'wi'),
subpartition q2_1999_southcentral values ('ok', 'tx')
),
partition q3_1999 values less than (to_date('1-oct-1999','dd-mon-yyyy'))
(subpartition q3_1999_northwest values ('or', 'wa'),
subpartition q3_1999_southwest values ('az', 'ut', 'nm'),
subpartition q3_1999_northeast values ('ny', 'vm', 'nj'),
subpartition q3_1999_southeast values ('fl', 'ga'),
subpartition q3_1999_northcentral values ('sd', 'wi'),
subpartition q3_1999_southcentral values ('ok', 'tx')
),
partition q4_1999 values less than ( to_date('1-jan-2000','dd-mon-yyyy'))
(subpartition q4_1999_northwest values ('or', 'wa'),
subpartition q4_1999_southwest values ('az', 'ut', 'nm'),
subpartition q4_1999_northeast values ('ny', 'vm', 'nj'),
subpartition q4_1999_southeast values ('fl', 'ga'),
subpartition q4_1999_northcentral values ('sd', 'wi'),
subpartition q4_1999_southcentral values ('ok', 'tx')
)
);

Murphy丶杨

关注

4
点赞
踩
35

收藏

觉得还不错? 一键收藏
1
评论
oracle 分表分区操作

第一步：分表如果历史表中存储了很多年的数据，会造成严重的数据冗余。那如果将历史表分表存储，比如每年创建一个表，数据存储到对应的年表中，必定会减少很多数据量。（如果分成年表数据量还是过大，可以细分到月表，天表…）。我们这里以创建年表为例，写一个创建年表的存储过程，利用PLSQL定时任务定时执行此存储过程（定时每年12月31号创建下一年的年表）。存储过程如下，定时任务查看此篇文章：PLSQL执行O...
复制链接

扫一扫

专栏目录