sql2005表分区之增加分区
来源:互联网 发布:蒙泰rip软件下载 编辑:程序博客网 时间:2024/05/07 11:16
1,创建分区
2,创建分区函数
3,创建分区架构
4,创建表
5,创建聚合索引
分区函数共有三个分临界值:100,200,300,有四个文件组[Data Partition DB FG1]到[... FG4],存储规则如下:
现在要求加入500这个临界值,并加入文件组[Data Partition DB FG5],如下操作:
1,建立文件组:
然后我们来看有什么变化:
分区架构:
分区函数:
这时的存储规则:
可以看到新加的文件组并没有排到最后一们,而FG4成为新的临界点。那我们加入新的临界点400呢,
变化如下
分区架构:
分区函数:
存储规则将变为
所以对于具体时间维的数据仓库来说,时间做为临界点,是不断增加的,我们一开始创建事实表分区时,最后一个分区应该叫做类似[Data Partiotion DB FG LAST]的名称,
最后,有一个问题还没有实践,对于一个有1000w条记录的文件组,用一个文件保存和用十个文件保存,查询效率会有什么样的不同呢?
创建分区
USE [master]
GO
/**//****** Object: Database [Data Partition DB] Script Date: 10/08/2006 23:09:53 ******/
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'Data Partition DB2')
DROP DATABASE [Data Partition DB2]
GO
CREATE DATABASE [Data Partition DB2]
ON PRIMARY
(NAME='Data Partition DB Primary FG',
FILENAME=
'C:/Data2/Primary/Data Partition DB Primary FG.mdf',
SIZE=5,
MAXSIZE=500,
FILEGROWTH=1 ),
FILEGROUP [Data Partition DB FG1]
(NAME = 'Data Partition DB FG1',
FILENAME =
'C:/Data2/FG1/Data Partition DB FG1.ndf',
SIZE = 5MB,
MAXSIZE=500,
FILEGROWTH=1 ),
FILEGROUP [Data Partition DB FG2]
(NAME = 'Data Partition DB FG2',
FILENAME =
'C:/Data2/FG2/Data Partition DB FG2.ndf',
SIZE = 5MB,
MAXSIZE=500,
FILEGROWTH=1 ),
FILEGROUP [Data Partition DB FG3]
(NAME = 'Data Partition DB FG3',
FILENAME =
'C:/Data2/FG3/Data Partition DB FG3.ndf',
SIZE = 5MB,
MAXSIZE=500,
FILEGROWTH=1 ),
FILEGROUP [Data Partition DB FG4]
(NAME = 'Data Partition DB FG4',
FILENAME =
'C:/Data2/FG4/Data Partition DB FG4.ndf',
SIZE = 5MB,
MAXSIZE=500,
FILEGROWTH=1 )
USE [master]
GO
/**//****** Object: Database [Data Partition DB] Script Date: 10/08/2006 23:09:53 ******/
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'Data Partition DB2')
DROP DATABASE [Data Partition DB2]
GO
CREATE DATABASE [Data Partition DB2]
ON PRIMARY
(NAME='Data Partition DB Primary FG',
FILENAME=
'C:/Data2/Primary/Data Partition DB Primary FG.mdf',
SIZE=5,
MAXSIZE=500,
FILEGROWTH=1 ),
FILEGROUP [Data Partition DB FG1]
(NAME = 'Data Partition DB FG1',
FILENAME =
'C:/Data2/FG1/Data Partition DB FG1.ndf',
SIZE = 5MB,
MAXSIZE=500,
FILEGROWTH=1 ),
FILEGROUP [Data Partition DB FG2]
(NAME = 'Data Partition DB FG2',
FILENAME =
'C:/Data2/FG2/Data Partition DB FG2.ndf',
SIZE = 5MB,
MAXSIZE=500,
FILEGROWTH=1 ),
FILEGROUP [Data Partition DB FG3]
(NAME = 'Data Partition DB FG3',
FILENAME =
'C:/Data2/FG3/Data Partition DB FG3.ndf',
SIZE = 5MB,
MAXSIZE=500,
FILEGROWTH=1 ),
FILEGROUP [Data Partition DB FG4]
(NAME = 'Data Partition DB FG4',
FILENAME =
'C:/Data2/FG4/Data Partition DB FG4.ndf',
SIZE = 5MB,
MAXSIZE=500,
FILEGROWTH=1 )
2,创建分区函数
分区函数
use [Data Partition DB2]
GO
CREATE PARTITION FUNCTION [Data Partition Range](int)
AS RANGE LEFT FOR VALUES (100,200,300)
use [Data Partition DB2]
GO
CREATE PARTITION FUNCTION [Data Partition Range](int)
AS RANGE LEFT FOR VALUES (100,200,300)
3,创建分区架构
创建分区架构
USE [Data Partition DB2]
go
CREATE PARTITION SCHEME [Data Partition Scheme]
AS PARTITION [Data Partition Range]
TO ([Data Partition DB FG1], [Data Partition DB FG2], [Data Partition DB FG3],[Data Partition DB FG4]);
USE [Data Partition DB2]
go
CREATE PARTITION SCHEME [Data Partition Scheme]
AS PARTITION [Data Partition Range]
TO ([Data Partition DB FG1], [Data Partition DB FG2], [Data Partition DB FG3],[Data Partition DB FG4]);
4,创建表
创建表
USE [Data Partition DB2]
go
CREATE TABLE MyTable
(ID INT NOT NULL,
Date DATETIME,
Cost money)
ON [Data Partition Scheme] (ID);
USE [Data Partition DB2]
go
CREATE TABLE MyTable
(ID INT NOT NULL,
Date DATETIME,
Cost money)
ON [Data Partition Scheme] (ID);
5,创建聚合索引
创建聚合索引
USE [Data Partition DB2]
go
CREATE UNIQUE CLUSTERED INDEX MyTable_IXC
ON MyTable(ID)
ON [Data Partition Scheme] (ID)
USE [Data Partition DB2]
go
CREATE UNIQUE CLUSTERED INDEX MyTable_IXC
ON MyTable(ID)
ON [Data Partition Scheme] (ID)
分区函数共有三个分临界值:100,200,300,有四个文件组[Data Partition DB FG1]到[... FG4],存储规则如下:
分区号 | 范围 | 文件组名 |
1 | x<=100 | [Data Partition DB FG1] |
2 | 100<x<=200 | [Data Partition DB FG2] |
3 | 200<x<=300 | [Data Partition DB FG3] |
4 | 300<x | [Data Partition DB FG4] |
现在要求加入500这个临界值,并加入文件组[Data Partition DB FG5],如下操作:
1,建立文件组:
ALTER DATABASE [Data Partition DB2]ADD FILEGROUP [Data Partition DB FG5]
ALTER DATABASE [Data Partition DB2]ADD FILE(NAME='Data Partition DB FG5',
FILENAME='D:/Database/Data Partition DB FG5.ndf') TO FILEGROUP [Data Partition DB FG5];
2,更改分区架构 ALTER DATABASE [Data Partition DB2]ADD FILE(NAME='Data Partition DB FG5',
FILENAME='D:/Database/Data Partition DB FG5.ndf') TO FILEGROUP [Data Partition DB FG5];
ALTER PARTITION SCHEME [Data Partition Scheme]
NEXT USED [Data Partition DB FG5];
3,更改分区函数NEXT USED [Data Partition DB FG5];
ALTER PARTITION FUNCTION [Data Partition Range](int)
SPLIT RANGE (500)
SPLIT RANGE (500)
然后我们来看有什么变化:
分区架构:
USE [Data Partition DB2]
GO
CREATE PARTITION SCHEME [Data Partition Scheme] AS PARTITION [Data Partition Range]
TO ([Data Partition DB FG1],
[Data Partition DB FG2],
[Data Partition DB FG3],
[Data Partition DB FG5],
[Data Partition DB FG4])
GO
CREATE PARTITION SCHEME [Data Partition Scheme] AS PARTITION [Data Partition Range]
TO ([Data Partition DB FG1],
[Data Partition DB FG2],
[Data Partition DB FG3],
[Data Partition DB FG5],
[Data Partition DB FG4])
分区函数:
USE [Data Partition DB2]
GO
CREATE PARTITION FUNCTION [Data Partition Range](int) AS RANGE LEFT FOR VALUES (100, 200, 300, 500)
GO
CREATE PARTITION FUNCTION [Data Partition Range](int) AS RANGE LEFT FOR VALUES (100, 200, 300, 500)
这时的存储规则:
分区号 | 范围 | 文件组名 |
1 | x<=100 | [Data Partition DB FG1] |
2 | 100<x<=200 | [Data Partition DB FG2] |
3 | 200<x<=300 | [Data Partition DB FG3] |
4 | 300<x<=500 | [Data Partition DB FG5] |
5 | 500<x | [Data Partition DB FG4] |
可以看到新加的文件组并没有排到最后一们,而FG4成为新的临界点。那我们加入新的临界点400呢,
变化如下
分区架构:
USE [Data Partition DB2]
GO
CREATE PARTITION SCHEME [Data Partition Scheme] AS PARTITION [Data Partition Range]
TO ([Data Partition DB FG1],
[Data Partition DB FG2],
[Data Partition DB FG3],
[Data Partition DB FG5],
[Data Partition DB FG6],
[Data Partition DB FG4])
GO
CREATE PARTITION SCHEME [Data Partition Scheme] AS PARTITION [Data Partition Range]
TO ([Data Partition DB FG1],
[Data Partition DB FG2],
[Data Partition DB FG3],
[Data Partition DB FG5],
[Data Partition DB FG6],
[Data Partition DB FG4])
分区函数:
USE [Data Partition DB2]
GO
CREATE PARTITION FUNCTION [Data Partition Range](int) AS RANGE LEFT FOR VALUES (100, 200, 300,400, 500)
GO
CREATE PARTITION FUNCTION [Data Partition Range](int) AS RANGE LEFT FOR VALUES (100, 200, 300,400, 500)
存储规则将变为
分区号 | 范围 | 文件组名 |
1 | x<=100 | [Data Partition DB FG1] |
2 | 100<x<=200 | [Data Partition DB FG2] |
3 | 200<x<=300 | [Data Partition DB FG3] |
4 | 300<x<=400 | [Data Partition DB FG5] |
5 | 400<x<=500 | [Data Partition DB FG6] |
6 | 500<x | [Data Partition DB FG4] |
所以对于具体时间维的数据仓库来说,时间做为临界点,是不断增加的,我们一开始创建事实表分区时,最后一个分区应该叫做类似[Data Partiotion DB FG LAST]的名称,
最后,有一个问题还没有实践,对于一个有1000w条记录的文件组,用一个文件保存和用十个文件保存,查询效率会有什么样的不同呢?
- sql2005表分区之增加分区
- sql2005表分区之增加分区
- SQL2005表分区
- 表增加范围分区
- 分区表及分区索引(8)--增加和收缩表分区
- 分区表 分区索引 增加 收缩 删除 表分区
- Oracle分区之二:表分区
- MYSQL之表分区----按日期分区
- MYSQL之表分区----按日期分区
- 分享SQL2005分区实现教程
- 分享SQL2005分区实现教程
- Oracle定时删除、增加表分区
- 增加、删除和收缩表分区!
- 创建,增加,删除mysql表分区
- 创建,增加,删除mysql表分区
- ubuntu增加新分区
- 在windows7增加分区
- linux 增加Swap分区
- SQLite 体系结构
- 回国礼物大家谈(2008-09版)
- “柔道”战略解析Google战胜微软三部曲
- rails用session存储数据
- vs2008 report rdlc 数据源 提示未加载文件或程序集
- sql2005表分区之增加分区
- 清空系统临时文件的批处理命令
- Flex中你最应该知道的10点
- 五句话足以改变人生
- MON JAN 12,2009
- Study Android, Step by Step(四) 安装SDK
- 车祸中人受到的伤害
- 易语言对象内存模型
- 关于JSON parents及parent