专栏首页乐沙弥的世界实验三:SQL server 2005基于已存在的表创建分区

实验三:SQL server 2005基于已存在的表创建分区

如转载,请注明出处:http://blog.csdn.net/robinson_0612/archive/2009/11/07/4783702.aspx

      随着当今数据库的容量越来越快的朝着在大型数据库或超大型数据库的发展,对于数据库中的大型表以及具有各种访问模式的表的可伸缩性和可管理性运行环境变得尤为重要,SQL server从SQL server 7.0的分区视图到SQL server 2000中的分区视图中到SQL server 2005所使用的分区表,不断改善大型表所面临的性能、阻塞、备份空间、时间、运营成本等。当表和索引非常大的时候,通过分区表的实现,可以将数据分为更小,更易于管理,获得更好的可操作性能。本实验介绍基于已存在的表来如何创建分区,管理分区。

一、实验目的:对于已经存在的表且不断增大的情况下构建分区表,管理分区表,提高其性能。

二、主要步骤:对于已经存在的表,我们可以采取以下步骤来对其创建分区表     1.创建分区函数     2.创建分区架构并关联到分区函数     3.删除已经存在的聚集索引     4.基于分区架构重建聚集索引

三、实验环境:     1. windows xp pro (英文版) + sp2

    2. SQL server 2005 Developer + sp3     3.实验数据库Performance,此数据库参照实验二:SQL server 2005高可用性之----数据库镜像 中的生成脚本生成数据库,本实验对其数据库的存放做了调整,将数据和日志文件存放在D:/SQL_Data/Performance目录下。     4.对已存在要创建的分区表为:Performance数据库下的Orders表.     5.对Orders表中的orderdate列按年进行水平分区

四、具体试验步骤:          1.创建分区函数        确定分区的数目及分区的列,列的数据类型。本例将Orders表的orderdate按年份水平分五个区,则需要定义四个边界点值。如下,

use Performance; go      Create partition function Part_func_orders(datetime) as range left for values('20021231 23:59:59.997',                  '20031231 23:59:59.997',                  '20041231 23:59:59.997',                  '20051231 23:59:59.997'); go --或者使用range right来创建分区函数 Create partition function Part_func_orders(datetime) as range right for values('20030101 00:00:00.000',                  '20040101 00:00:00.000',                  '20050101 00:00:00.000',                  '20060101 00:00:00.000'); go /*分区值的表示范围(使用range left) –infinity < x1 <= 20021231  20030101 < x2 <= 20031231  20040101 < x3 <= 20041231  20050101 < x4 <= 20051231  20060101 < x5 <= +infinity infinity本应当为20020101或20061231,此处仅用于说明表示范围 ---------------------------------------------------------- 分区值的表示范围(使用range right) –infinity < x1 < 20030101  20030101 <= x2 < 20040101  20040101 <= x3 < 20050101  20050101 <= x4 < 20060101  20060101 <= x5 < +infinity 通过以上分析表明当range中使用left时,分区的范围右边为小于等于values所指定的值, 当range中使用right时,分区范围左边为大于等于values所指定的值。 

规律:在使用 LEFT 分区函数时,第一个值将作为第一个分区中的上边界。在使用 RIGHT 分区函数时,第一个值将作为第二个分区的下边界*/

    2. 添加文件组和文件         针对所创建的分区来创建文件组和文件,我们可以创建五个文件组,五个不同的ndf文件来存放不同年份的orders,可以放置于不同的磁盘来减少I/O的开销,也可以在一个文件组中创建多个文件来存放不同年份的orders,本例创建了四个文件组,其中有一年的orders放置到了Primary组中。

alter database Performance add filegroup [FG1]; go alter database Performance add filegroup [FG2]; go alter database Performance add filegroup [FG3]; go alter database Performance add filegroup [FG4]; go alter database Performance add file (name = FG1_data,filename = 'D:/SQL_Data/Performance/FG1_data.ndf',size = 3MB) to filegroup [FG1]; alter database Performance add file (name = FG2_data,filename = 'D:/SQL_Data/Performance/FG2_data.ndf',size = 3MB) to filegroup [FG2]; alter database Performance add file (name = FG3_data,filename = 'D:/SQL_Data/Performance/FG3_data.ndf',size = 3MB) to filegroup [FG3]; alter database Performance add file (name = FG4_data,filename = 'D:/SQL_Data/Performance/FG4_data.ndf',size = 3MB) to filegroup [FG4]; go

    3. 创建分区架构并关联到分区函数

Create partition scheme Part_func_orders_scheme as partition Part_func_orders to ([FG1],[FG2],[FG3],[FG4],[Primary]); go

    4.重建索引(删除聚集索引以及需要分区字段的索引后重建该类索引,表被按分区值将分配到各文件组)

EXEC sp_helpindex N'orders' --查看orders中使用的索引 drop index idx_cl_od on orders; go create clustered index idx_cl_od on orders(orderdate)

on Part_func_orders_scheme(orderdate); go

    5. 查看分区的相关情况

--查看分区及分区范围的情况 select * from sys.partitions where object_id = object_id('orders'); select * from sys.partition_range_values;

--查看分区架构情况 select * from sys.partition_schemes;

--查看某一特定分区列值属于哪个分区 select Performance.$partition.Part_func_orders('20050325') as partition_num;

--查看某一特定分区的记录 select * from orders where Performance.$partition.Part_func_orders(orderdate) = 2

--查看各分区所包含的记录数 select $partition.Part_func_orders(orderdate) as partition_num,   count(*) as record_num from orders group by $partition.Part_func_orders(orderdate) order by $partition.Part_func_orders(orderdate);

    6.分区的管理

--增加分区值,增加分区之前应先增加或设置新分区使用的文件组 alter database Performance add filegroup [FG5]; go

alter database Performance add file (name = FG5_data,filename = 'D:/SQL_Data/Performance/FG5_data.ndf',size = 3MB ) to filegroup [FG5]; go

alter partition scheme Part_func_orders_scheme next used [FG5]; go

alter partition function Part_func_orders() split range('20061231 23:59:59.997') go

insert into orders select 10000001,'C0000012906',213,'I','20070101','a' union all select 10000002,'C0000019995',213,'I','20070109','a' union all select 10000003,'C0000019996',410,'I','20070512','a'; go

select * from orders where Performance.$partition.Part_func_orders(orderdate) = 6

--合并分区 --合并分区后,以下将新增的三条记录放到了第5个分区中

alter partition function Part_func_orders() merge range('20061231 23:59:59.997'); go

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • Oracle 分区表

    随着表的不断增大,对于新纪录的增加、查找、删除等(DML)的维护也更加困难。对于数据库中的超大型表,可通过把它的数据分成若干个小表,从而简化数据库的管理活动。...

    Leshami
  • MySQL修改复制用户及密码

        在生产环境中有时候需要修改复制用户账户的密码,比如密码遗失,或者由于多个不同的复制用户想统一为单独一个复制账户。对于这些操作应尽可能慎重以避免操作不同导...

    Leshami
  • consistent gets减少,cost增加?

      在一条SQL语句中,当使用索引时,cosistent gets 减少,而cost增加。理论上在稳定后的执行计划中,physical reads为零值的前...

    Leshami
  • ClickHouse|MergeTree引擎之数据分区

    前面通过 一文了解ClickHouse 介绍过ClickHouse,特性,结构,使用场景。自己并未完全深入学习clickhouse,因为公司打算小范围使用Cl...

    用户1278550
  • win 7和Ubuntu 12.04 双系统下的磁盘分区问题

    断续学Linux以来就一直没完全搞清楚磁盘分区的问题,现在虽然还是不清楚,但应该有点思路了,且记下。 我装了双系统,首先看在win 7下看到的磁盘分区情况: ?...

    s1mba
  • 深入理解Linux磁盘的奥秘

    当我们想在系统里增加一块硬盘的时候,要做以下这四步工作: 对磁盘进行分区 对新建的分区进行格式化,目的是为了创建系统可用的文件系统 对新建的文件系统进行检验 将...

    大闲人柴毛毛
  • Linux--分区与挂载

    整个磁盘的存储大小为: 存储容量 = 磁头数 × 磁道(柱面)数 × 每道扇区数 × 每扇区字节数

    None_Ling
  • Linux 磁盘划分和分区介绍

    在Linux系统中一切都是文件,硬件设备也不例外。既然是文件,就必须有文件名称。系统内核中的udev设备管理器会自动把硬件名称规范起来,目的是让用户通过设备文件...

    刘銮奕
  • Qt开源作品8-通用控件移动

    在做一些项目的过程中,有一种应用场景是需要拖动设备在一个容器中,自由拖动摆放到合适的位置,然后保存对应设备的坐标位置信息,在软件启动好以后自动加载配置好的坐标位...

    feiyangqingyun
  • 磁盘分区以及解决反序安装操作系统所带来的困扰

    对于刚接触电脑的小嫩甚至IT江湖混迹已久的老道,磁盘分区一直是一件很令人头疼的事情啊!什么主分区、什么扩展分区啊,还有逻辑分区、分区表、引导记录这些,无一不让人...

    CN_Simo

扫码关注云+社区

领取腾讯云代金券