如何使用Oracle数据库分区表

当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个表空间(物理文件上)，这样查询数据时，不至于每次都扫描整张表。

一、背景

话说风和日丽的一天，为提高随着业务增长的大表（3510449行吧）的访问效率，于是决定对表分区，记录如下。

二、实 ***

结合业务，若干条记录会集中在一个日期，查询时也往往只查询一个日期内的数据，于是选取分区字段为时间。

创建分区 比如

CREATE TABLE message_all (

id int(10) NOT NULL AUTO_INCREMENT,

createtime datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'

PRIMARY KEY ( id , createtime )

) ENGINE=InnoDB DEFAULT CHARSET=utf8

PARTITION BY RANGE (YEAR(createtime))

(PARTITION p2015 VALUES LESS THAN (2016) ENGINE = InnoDB,

PARTITION p2016 VALUES LESS THAN (2017) ENGINE = InnoDB,

PARTITION p2017 VALUES LESS THAN (2018) ENGINE = InnoDB,

PARTITION p2018 VALUES LESS THAN MAXVALUE ENGINE = InnoDB)

不过我们表已经有了当然不能这么建，除非你想导一次数据。

如下 *** 作 ：

1、

ALTER TABLE message_all PARTITION BY RANGE (to_days(createtime))

(

PARTITION p2015 VALUES LESS THAN (to_days('2016-01-01')),

PARTITION p2016 VALUES LESS THAN (to_days('2017-01-01')),

PARTITION p2017 VALUES LESS THAN (to_days('2018-01-01')),

PARTITION p2018 VALUES LESS THAN MAXVALUE

);

或者

2、ALTER TABLE message_all PARTITION BY RANGE (YEAR(createtime))

(

PARTITION p2015 VALUES LESS THAN (YEAR('2016-01-01'))

);

然后追加。

ALTER TABLE message_all ADD PARTITION

(

PARTITION p2016 VALUES LESS THAN (YEAR('2017-01-01')),

PARTITION p2017 VALUES LESS THAN (YEAR('2018-01-01')),

PARTITION p2018 VALUES LESS THAN MAXVALUE

);

这里会有几种错误情况：

1、ALTER TABLE message_all PARTITION BY RANGE (to_days(createtime)) ;

[Err] 1492 - For RANGE partitions each partition must be defined

解释：必须指定至少一个分区。

2、[Err] 1492 - A PRIMARY KEY must include all columns in the table's partitioning function

解释：分区字段必须是主键之一。

3、[Err] 1492 - Constant, random or timezone-dependent expressions in (sub)partitioning function are not allowed

解释：分区字段为timestamp，换成datetime。

4、[Err] 1526 - Table has no partition for value xxxx

解释：用追加方式第一次必须覆盖目前所有数据。

总结：

1、创建时必须指定至少一个分区。

2、key必须为主键之一。

3、RANGE处必须为INT型，时间字段用函数转——YEAR()、YEARWEEK()、TO_DAYS()。

4、THAN处必须为INT型，时间字段用函数转——TO_DAYS、TO_SECONDS()、UNIX_TIMESTAMP()。

5、它就是以两个INT比大小划分的文件。

6、所有ENGINE必须一样。

7、范围分区添加只能在最大值后面追加。

8、分区是有上限的貌似1024个。

用到的其他 *** 作

1、删除分区（直接扔掉分区文件，数据也没了）

ALTER TABLE message_all DROP PARTITION p2016;

2、清空分区数据

ALTER TABLE message_all TRUNCATE PARTITION p2017;

3、重定义（可实现：分区拆分、合并、重命名）

ALTER TABLE message_all REORGANIZE PARTITION p201601,p201602,p201603,p201604 INTO

(

PARTITION p2016012 VALUES less than(TO_DAYS('2016-03-01')),

PARTITION p2016034 VALUES less than(TO_DAYS('2016-05-01'))

);

检查/查看你的分区

1、SHOW TABLE STATUS LIKE 'message_all';

2、SELECT FROM information_schemapartitions WHERE table_name='message_all';

3、SHOW CREATE TABLE message_all;

4、EXPLAIN SELECT COUNT(1) FROM message_all WHERE createtime>= '2016-01-01' AND createtime < '2016-12-30';如果用到了分区partitions里会有显示。

5、指定分区查

SELECT COUNT(1) FROM message_all PARTITION (p2016) 表别名 WHERE ;

到这里就结束啦，土豆白。

一些概念

水平分区Partition有以下几种模式

分区、分表、分库的详细理解

一、什么是分区、分表、分库

分区

就是把一张表的数据分成N个区块，在逻辑上看最终只是一张表，但底层是由N个物理区块组成的

分表

就是把一张表按一定的规则分解成N个具有独立存储空间的实体表。系统读写时需要根据定义好的规则得到对应的字表明，然后 *** 作它。

分库

一旦分表，一个库中的表会越来越多

将整个数据库比作图书馆，一张表就是一本书。当要在一本书中查找某项内容时，如果不分章节，查找的效率将会下降。而同理，在数据库中就是分区。

二、常用的单机数据库的瓶颈

问题描述

单个表数据量越大，读写锁，插入 *** 作重新建立索引效率越低。

单个库数据量太大（一个数据库数据量到就是极限）

单个数据库服务器压力过大

读写速度遇到瓶颈（并发量几百）

三、分区

什么时候考虑使用分区？

一张表的查询速度已经慢到影响使用的时候。

sql经过优化

数据量大

表中的数据是分段的

对数据的 *** 作往往只涉及一部分数据，而不是所有的数据

分区解决的问题

主要可以提升查询效率

分区的实现方式（简单）

mysql5 开始支持分区功能

四、分表

什么时候考虑分表？

一张表的查询速度已经慢到影响使用的时候。

sql经过优化

数据量大

当频繁插入或者联合查询时，速度变慢

分表解决的问题

分表后，单表的并发能力提高了，磁盘I/O性能也提高了，写 *** 作效率提高了

查询一次的时间短了

数据分布在不同的文件，磁盘I/O性能提高

读写锁影响的数据量变小

插入数据库需要重新建立索引的数据减少

分表的实现方式（复杂）

需要业务系统配合迁移升级，工作量较大

分区和分表的区别与联系

分区和分表的目的都是减少数据库的负担，提高表的增删改查效率。

分区只是一张表中的数据的存储位置发生改变，分表是将一张表分成多张表。

当访问量大，且表数据比较大时，两种方式可以互相配合使用。

当访问量不大，但表数据比较多时，可以只进行分区。

常见分区分表的规则策略（类似）

Range（范围）

Hash（哈希）

按照时间拆分

Hash之后按照分表个数取模

在认证库中保存数据库配置，就是建立一个DB，这个DB单独保存user_id到DB的映射关系

打开MsSQL2008,找到作业该项,如果打不开或者SQL Server代理是未启动状态,请先在windows服务中启动SQL Server代理(参考),

2

右击MsSQL2008对象资源管理器中的作业,选择新建作业,输入该作业你想用的名称,类别不用管,说明里面是输入一些该作业完成的功能,可不写,请务必勾选已启用复选框

3

点击新建作业窗体左侧的步骤项,点击右侧区域下方的新建按钮,输入步骤名称,类型请选择Transact-SQL脚本(T-SQL),运行身份默认,数据库请选择要进行分区的数据库,请不要选择master默认的,命令文本框中输入如下代码:

/--------------------创建数据库的文件组和物理文件------------------------/

declare @tableName varchar(50), @fileGroupName varchar(50), @ndfName varchar(50), @newNameStr varchar(50), @fullPath

varchar(50), @newDay varchar(50), @oldDay datetime, @partFunName varchar(50), @schemeName varchar(50)

set @tableName='要分区的数据库名称'

set @newDay=CONVERT(varchar(100), GETDATE(), 23)--23:按天 114:按时间

set @oldDay=cast(CONVERT(varchar(10),dateadd(day,-1,getdate()), 120 ) as datetime)

set @newNameStr=Replace(Replace(@newDay,':','_'),'-','_')

set @fileGroupName=N'G'+@newNameStr

set @ndfName=N'F'+@newNameStr+''

set @fullPath=N'E:\\SQLDataBase\\UserData\\'+@ndfName+'ndf'

set @partFunName=N'pf_Time'

set @schemeName=N'ps_Time'

--创建文件组

if exists(select from sysfilegroups where name=@fileGroupName)

begin

print '文件组存在,不需添加'

end

else

begin

exec('ALTER DATABASE '+@tableName+' ADD FILEGROUP ['+@fileGroupName+']')

print '新增文件组'

if exists(select from syspartition_schemes where name =@schemeName)

begin

exec('alter partition scheme '+@schemeName+' next used ['+@fileGroupName+']')

print '修改分区方案'

end

if exists(select from syspartition_range_values where function_id=(select function_id from

syspartition_functions where name =@partFunName) and value=@oldDay)

begin

exec('alter partition function '+@partFunName+'() split range('''+@newDay+''')')

print '修改分区函数'

end

end

--创建NDF文件

if exists(select from sysdatabase_files where [state]=0 and (name=@ndfName or physical_name=@fullPath))

begin

print 'ndf文件存在,不需添加'

end

else

begin

exec('ALTER DATABASE '+@tableName+'ADD FILE (NAME ='+@ndfName+',

FILENAME = '''+@fullPath+''')TO FILEGROUP ['+@fileGroupName+']')

print '新创建ndf文件'

end

/--------------------以上创建数据库的文件组和物理文件------------------------/

--分区函数

if exists(select from syspartition_functions where name =@partFunName)

begin

print '此处修改需要在修改分区函数之前执行'

end

else

begin

exec('CREATE PARTITION FUNCTION '+@partFunName+'(DateTime)AS RANGE RIGHTFOR VALUES ('''+@newDay

+''')')

print '新创建分区函数'

end

--分区方案

if exists(select from syspartition_schemes where name =@schemeName)

begin

print '此处修改需要在修改分区方案之前执行'

end

else

begin

exec('CREATE PARTITION SCHEME '+@schemeName+' AS PARTITION '+@partFunName+' TO

(''PRIMARY'','''+@fileGroupName+''')')

print '新创建分区方案'

end

print '---------------以下是变量定义值显示---------------------'

print '当前数据库:'+@tableName

print '当前日期:'+@newDay+'(用作随机生成的各种名称和分区界限)'

print '合法命名方式:'+@newNameStr

print '文件组名称:'+@fileGroupName

print 'ndf物理文件名称:'+@ndfName

print '物理文件完整路径:'+@fullPath

print '分区函数:'+@partFunName

print '分区方案:'+@schemeName

/

--查看创建的分区函数

select from syspartition_functions

--查看分区函数的临界值

select from syspartition_range_values

--查询分区方案

select from syspartition_schemes

--查询表数据在哪个分区中存储,where条件查询第一个分区中存在的数据

select ,$partitionpf_SaveTime(分区字段) as Patition from 表名 where $partitionpf_SaveTime(分区字段)=1

/

GO

点击确定按钮

上述代码中的变量名称,路径等均可自行修改,上述是按天为单位,以G开头的日期作为文件组名称,以F开头的日期作为物理分区文件名即ndf文件名称

4

选择新建分区左侧的计划项,然后点击右侧区域下方的新建按钮,设定新建分区的时间间隔,图中设置的是每天创建一个新的分区,用户也可以自行修改,按月,按周,按自定义时间等

其他的条目,通知,警报,目标可自行设置,也可不设置,至此自动创建分区的计划任务已成功设置

END

步骤二:对表应用分区方案和分区函数

右击要分区的表,选择存储菜单下的创建分区,上述步骤一中创建的分区函数是按datetime类型进行的分区,所以创建分区的时候需要选择相应类型的字段作为分区依据,用户也可以根据int型或其他类型的字段进行分区,选择下一步,使用现有分区函数下一步使用现有分区方案,下一步会自动按照分区方案执行的日期进行分区,继续点击下一步选择立即执行,完成后即可完成的整体的表分区自动执行

需注意:刚设置完第一步的计划任务,可能不会执行第一步的分区方案的代码,也就意味着没有创建分区函数和分区方案,第二步设置的时候使用现有分区函数和使用现有分区方案也就不可用,可先把第一步的代码执行一遍即可

分表是将一个大表按照一定的规则分解成多张具有独立存储空间的实体表，我们可以称为子表，每个表都对应三个文件，MYD数据文件，MYI索引文件，frm表结构文件。这些子表可以分布在同一块磁盘上，也可以在不同的机器上。app读写的时候根据事先定义好的规则得到对应的子表名，然后去 *** 作它。

oracle分区表的分区有四种类型：范围分区、散列分区、列表分区和复合分区。

特点如下：

1、范围分区

就是根据数据库表中某一字段的值的范围来划分分区。

数据中有空值，Oracle机制会自动将其规划到maxvalue的分区中。

2、散列分区

根据字段的hash值进行均匀分布，尽可能地实现各分区所散列的数据相等。

散列分区即为哈希分区，Oracle采用哈希码技术分区，具体分区如何由Oracle说的算，也可能我下一次搜索就不是这个数据了。

3、列表分区

列表分区明确指定了根据某字段的某个具体值进行分区，而不是像范围分区那样根据字段的值范围来划分的。

4、复合分区

根据范围分区后，每个分区内的数据再散列地分布在几个表空间中，这样我们就要使用复合分区。复合分区是先使用范围分区，然后在每个分区同再使用散列分区的一种分区方法。

比如将part_date的记录按时间分区，然后每个分区中的数据分三个子分区，将数据散列地存储在三个指定的表空间中。

扩展资料：

分区的恢复方法：

如果数据库运行在archive 模式下，那么一旦数据库损坏则可以通过冷备份(热备份)和归档备份将数据库恢复到断点状态。

数据库控制文件恢复（假设所有控制文件均被破坏）：

数据库基于文件系统： 利用 *** 作系统的tar、cp等命令即可。

数据库基于裸设备：dd if=$ORACLE_BASE/conbak of=/dev/rdrd/drd1 seek=12

参考资料来源：百度百科-oracle数据库

以上就是关于如何使用Oracle数据库分区表全部的内容，包括:如何使用Oracle数据库分区表、Mysql分区表Partition、数据库分区和分表的区别等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！