向存在索引的表插入大数据问题

[ufo20020427]

每月需向某表添加约800万条数据，并且该表还需查询及统计。
由于客户对插入及统计都有时间限制。
现在采取的方案包括
1.对该表按月份字段进行分区。（但分区后插入速度会更慢，但查询统计时应该会快些）
2.插入前删除所有索引(drop index )，插入后再创建索引create index。

但现在的问题时，随着插入的数据越来越多（虽然分区了），以后每次的drop index, create index
将会越来越慢吧？

我的想法是，能否只drop 、create某个分区上的索引（因为每月插入的数据，都肯定是往同一个区的）。

或者大家针对大量数据插入，有什么好的解决方案（要求是能尽快完成插入，当然因为表要做查询统计，不可能不建索引）。

谢谢！

[ufo20020427]

补充一下： 1.800多万条是一次性从文本文件导入 2.目前采用了openrowset(Bulk)的方式，如果没有索引的话，1分多钟就可以完成了。 现在分区及建了索引，第一次插入就要5分多钟了，估计随着数据的递增（索引的删除与重建会越来越慢），总体速度会越来越久。

[LongRui888]

对了，每个月要插入800w条数据，是比如： 10月到了，插入800万，这个就是10月的数据 11月到了，插入800万，这个就是11月的数据， 是这样吗

[ufo20020427]

@yupeigu 是的。

[LongRui888]

试了一下：

--对分区3的索引，重新组织一下，这个可以
alter index idx_temp_UserBill on temp_UserBill
reorganize partition= 3 


--但对分区3的索引，禁用，就不可以，会报错，那儿要drop索引，再重建，就更会报错了
alter index idx_temp_UserBill on temp_UserBill
disable partition= 3 
/*
消息 102，级别 15，状态 1，第 2 行
'disable' 附近有语法错误。
*/

[ufo20020427]

@yupeigu 如果不能先停用，只能组织好像就无法满足了。呵呵。 大伙对大数据的插入，都是先删索引，再建索引吗？

[LongRui888]

引用 5 楼 ufo20020427 的回复:

@yupeigu 如果不能先停用，只能组织好像就无法满足了。呵呵。 大伙对大数据的插入，都是先删索引，再建索引吗？

可以这样，先建个表，把数据导入到表中， 然后在分区表中，新增一个分区，里面没有数据，然后，把刚导入了数据的表，switch为分区表的一个分区

[LongRui888]

引用 5 楼 ufo20020427 的回复:

@yupeigu 如果不能先停用，只能组织好像就无法满足了。呵呵。 大伙对大数据的插入，都是先删索引，再建索引吗？

给你做了一个例子，其实就是，你导入数据，和当前的分区表没有关系。 首先，你再建立一个分区表，结构和原来的表是一样的，然后把新的一个月数据800w导入到这个临时建立的分区表中， 然后，导入后，要建立相应的索引，这个索引和原来的表一样， 最后，切换，也就是switch，就是把刚才导入的数据，所在的分区表的分区，switch到原来表的新分区中。当然，在swith之前，必须要split出一个新的分区，指定使用的下一个文件组是哪个： 基本上是秒级切换，对当前业务没有任何影响：

alter database wc
set single_user
with rollback immediate

drop database wc




--1.创建数据库  
create database wc  
on primary  
(  
    name = wc_data,  
    filename = 'D:\wc_data.mdf'  
)  
log on  
(  
    name = wc_log1,  
    filename = 'd:\wc_log1.ldf'    
),  
(  
    name = wc_log2,  
    filename = 'd:\wc_log2.ldf'  
)  
  
go


use wc
go

--4.创建分区函数,3个值,但是会有4个分区
create partition function wcLeftRange(char(6))  
as range left for values('201309','201310','201311')  


  
--5.创建分区方案,4个文件组  
create partition scheme wcLeftRangeScheme  
as partition wcLeftRange  
to ([primary],[primary],[primary],[primary])



--6.建表,聚集索引,插入数据
create table temp_UserBill
(
BusinessDate char(6), F1 varchar(100), F2 Varchar(100)
)

create clustered index idx_temp_UserBill on temp_UserBill(BusinessDate)


insert into temp_UserBill
select '201309','aa1','bb1' union all
select '201309','aa2','bb2' union all
select '201310','aa1','bb1' union all
select '201310','aa2','bb2' union all
select '201311','aa1','bb1' union all
select '201311','aa2','bb2' union all
select '201312','aa','bb' 

go


--7.要转化为分区表,必须要删除聚集索引
drop index idx_temp_UserBill on temp_UserBill


--8.然后再重新建立一个聚集索引,并且指定:分区方案和分区字段
create clustered index idx_temp_UserBill on temp_UserBill(BusinessDate)
on wcLeftRangeScheme(BusinessDate)  


--9.查询表中的数据,是在那个分区里
select *,  
  
       --$partition函数,后面是分区函数名称,列名称,显示了这条数据是在第几个分区 
       $partition.wcLeftRange(BusinessDate) as partition   
from temp_UserBill 
/*
BusinessDate	F1	F2	partition
201309	        aa1	bb1	1
201309	        aa2	bb2	1
201310	        aa1	bb1	2
201310	        aa2	bb2	2
201311	        aa1	bb1	3
201311	        aa2	bb2	3
201312	        aa	bb	4
*/





--10.1指定下一个要使用的分区文件组  
alter partition scheme wcLeftRangeScheme  
next used [primary]  
  
--10.2定义一个新的边界值来创建一个新的分区  
alter partition function wcLeftRange()  
split range ('201312')  
  
  
  
  
--11.新建一个表,你把800w数据,导入到这个表  
create table Import_UserBill
(
BusinessDate char(6), F1 varchar(100), F2 Varchar(100)
)on wcLeftRangeScheme(BusinessDate)   


insert into Import_UserBill
select '201401','aa1','bb1' union all
select '201401','aa2','bb2'


--上面先导入数据,再建立索引,速度会更快  
create clustered index idx_Import_UserBill on Import_UserBill(BusinessDate)
on  wcLeftRangeScheme(BusinessDate)
  
  
  
       
--12.把源表的第2个分区转到目标表  
--要求:源表和目标表必须在同一个文件组上,且目标表必须为空  
--当然,目标表也可以是分区表,也必须为空,且在同一个文件组上  
alter table Import_UserBill   
SWITCH partition 5  to dbo.temp_UserBill partition 5


--13.
select *,
 --$partition函数,后面是分区函数名称,列名称,显示了这条数据是在第几个分区 
       $partition.wcLeftRange(BusinessDate) as partition 
from temp_UserBill
/*
BusinessDate	F1	F2	partition
201309	aa1	bb1	1
201309	aa2	bb2	1
201310	aa1	bb1	2
201310	aa2	bb2	2
201311	aa1	bb1	3
201311	aa2	bb2	3
201312	aa	bb	4
201401	aa1	bb1	5
201401	aa2	bb2	5
*/