/*
在FactoryDB中有销售表SalesOrderDeails,包括如下字段:
销售流水号SalesID(idtentity(1,1)),
销售日期OrderDate,
产品号ProductID,
销售数量Amount,
销售单价UnitPrice。要求在OrderDate上建立分区索引,日按照销售期OrderDate分区,边界值为
(’2008-01-01’,’2009-01-01’),边界值放置在右边分区,要求将索引分区分配给文件组fg5,fg6,fg7)。
(1)创建分区函数;
(2)创建分区方案;
(3)创建分区索引Index_SalesOrderDeatils_OrderDate;
*/
--创建分区函数
create partition function pf_OrderDate(datetime)
as range right
for values('2008-01-01','2009-01-01')--创建分区方案
create partition scheme ps_OrderDate
as partition pf_OrderDate
to(fg5,fg6,fg7)--创建分区表
create table SalesOrderDeails(
SalesID int identity(1,1) primary key,
OrderDate datetime,
Amount int,
UnitPrice money
)on ps_OrderDate(OrderDate)
--创建分区索引
create index Index_SalesOrderDeatils_OrderDate
on SalesOrderDeails(OrderDate)
我这样写有没有问题啊?主要是建立分区索引一定要建立分区表的吗
请高手指点一下,说说你们对分区索引的认识
在FactoryDB中有销售表SalesOrderDeails,包括如下字段:
销售流水号SalesID(idtentity(1,1)),
销售日期OrderDate,
产品号ProductID,
销售数量Amount,
销售单价UnitPrice。要求在OrderDate上建立分区索引,日按照销售期OrderDate分区,边界值为
(’2008-01-01’,’2009-01-01’),边界值放置在右边分区,要求将索引分区分配给文件组fg5,fg6,fg7)。
(1)创建分区函数;
(2)创建分区方案;
(3)创建分区索引Index_SalesOrderDeatils_OrderDate;
*/
--创建分区函数
create partition function pf_OrderDate(datetime)
as range right
for values('2008-01-01','2009-01-01')--创建分区方案
create partition scheme ps_OrderDate
as partition pf_OrderDate
to(fg5,fg6,fg7)--创建分区表
create table SalesOrderDeails(
SalesID int identity(1,1) primary key,
OrderDate datetime,
Amount int,
UnitPrice money
)on ps_OrderDate(OrderDate)
--创建分区索引
create index Index_SalesOrderDeatils_OrderDate
on SalesOrderDeails(OrderDate)
我这样写有没有问题啊?主要是建立分区索引一定要建立分区表的吗
请高手指点一下,说说你们对分区索引的认识
(1)局部分区索引:
create index <idx_name> on <table_name>(col_name)
local
(partition <par_idx_name> [tablespace ts_name],
partition <par_idx_name> [tablespace ts_name]
...--------------------------------------------表有多少分区,就在此指定多少个子句.
) (2)全局索引:
create index <idx_name> on <table_name>(col_name)
global partition by range (col_name)
(
partition <par_idx_name> values less than(value) [tablespace ts_name],
partition <par_idx_name> values less than(value) [tablespace ts_name],
...------------------------------------------表有多少分区,就在此指定多少个子句.
) (3)也可以直接在分区表上创建索引
create index <idx_name> on <table_name>(col_name)
---try事例
--建立分区方案Create Partition Scheme MyPS1
As Partition MyPF1
To ([Primary],[Primary])
Go
/*
1,建立分区函数,分区方案是有先后顺序的。
2,分区函数提供的值的数目n,不能超过 999。所创建的分区数等于 n + 1
*/
Go
--建立测试表
Create Table Duty(
id int Identity(1,1) Not Null,
Name nvarchar(50),
WorkDate datetime,
WorkHours numeric(5,1),
Constraint PK_Duty Primary Key Clustered(id Asc,WorkDate Asc) On MyPS1(WorkDate)
)
On MyPS1(WorkDate)
Go
Create Table DutyHistory(
id int Identity(1,1) Not Null,
Name nvarchar(50),
WorkDate datetime,
WorkHours numeric(5,1),
Constraint PK_DutyHistory Primary Key Clustered(id Asc,WorkDate Asc) On MyPS1(WorkDate)
)
On MyPS1(WorkDate)
As Partition MyPF1
To ([Primary],[Primary])
Go
/*
1,建立分区函数,分区方案是有先后顺序的。
2,分区函数提供的值的数目n,不能超过 999。所创建的分区数等于 n + 1
*/
Go
--建立测试表
Create Table Duty(
id int Identity(1,1) Not Null,
Name nvarchar(50),
WorkDate datetime,
WorkHours numeric(5,1),
Constraint PK_Duty Primary Key Clustered(id Asc,WorkDate Asc) On MyPS1(WorkDate)
)
On MyPS1(WorkDate)
Go
Create Table DutyHistory(
id int Identity(1,1) Not Null,
Name nvarchar(50),
WorkDate datetime,
WorkHours numeric(5,1),
Constraint PK_DutyHistory Primary Key Clustered(id Asc,WorkDate Asc) On MyPS1(WorkDate)
)
On MyPS1(WorkDate)
--建立分区函数
Create Partition Function MyPF1(datetime)
As Range Left
For Values('2007/12/31')
Go
--建立分区方案Create Partition Scheme MyPS1
As Partition MyPF1
To ([Primary],[Primary])
Go
/*
1,建立分区函数,分区方案是有先后顺序的。
2,分区函数提供的值的数目n,不能超过 999。所创建的分区数等于 n + 1
*/
Go
--建立测试表
Create Table Duty(
id int Identity(1,1) Not Null,
Name nvarchar(50),
WorkDate datetime,
WorkHours numeric(5,1),
Constraint PK_Duty Primary Key Clustered(id Asc,WorkDate Asc) On MyPS1(WorkDate)
)
On MyPS1(WorkDate)
Go
Create Table DutyHistory(
id int Identity(1,1) Not Null,
Name nvarchar(50),
WorkDate datetime,
WorkHours numeric(5,1),
Constraint PK_DutyHistory Primary Key Clustered(id Asc,WorkDate Asc) On MyPS1(WorkDate)
)
On MyPS1(WorkDate)
create partition function pf_OrderDate(datetime)
as range right
for values('2008-01-01','2009-01-01')--创建分区方案
create partition scheme ps_OrderDate
as partition pf_OrderDate
to(fg5,fg6,fg7)
--创建分区表
create table SalesOrderDeails(
SalesID int identity(1,1) primary key nonclustered ,
OrderDate datetime,
Amount int,
UnitPrice money
)
create clustered index IX_SalesOrderDeails_OrderDate on SalesOrderDeails(OrderDate) on ps_OrderDate(OrderDate)