本文目标：指导项目侧人员再遇到此类改动需求时可以自己参照更改。
需求：Sybase数据库，普通表t_jingyu修改为按天分区的分区表。
1.sp_help查看t_jingyu的表结构，索引等信息
2.sp_rename重命名普通表t_jingyu及其主键pk_t_jingyu和索引idx_t_jingyu_1。
需求：Sybase数据库，普通表t_jingyu修改为按天分区的分区表。
1.sp_help查看t_jingyu的表结构，索引等信息
|
1
2
|
sp_help t_jingyugo |
提示：可以直接用DBArtisan工具Extract原建表语句参考
2.sp_rename重命名普通表t_jingyu及其主键pk_t_jingyu和索引idx_t_jingyu_1。
|
1
2
3
4
5
6
|
sp_rename t_jingyu,t_jingyu_bakgosp_rename "t_jingyu_bak.pk_t_jingyu",pk_t_jingyu_bak
gosp_rename "t_jingyu_bak.idx_t_jingyu_1",idx_t_jingyu_1_bak
go |
3.确定上面备份无问题后，创建分区表t_jingyu,分区索引。
3.1创建分区表t_jingyu
3.1创建分区表t_jingyu
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
|
CREATE TABLE dbo.t_jingyu
( oid varchar(64) NOT
NULL,
related_rnc varchar(64) NULL,
start_time datetime NOT
NULL
)LOCK DATAROWSPARTITION BY
RANGE (start_time)
(p20140601 VALUES
<= (‘2014-06-01 23:59:59.999‘) ON
seg_d_wrnop,
p20140602 VALUES
<= (‘2014-06-02 23:59:59.999‘) ON
seg_d_wrnop,
p20140603 VALUES
<= (‘2014-06-03 23:59:59.999‘) ON
seg_d_wrnop,
p20140604 VALUES
<= (‘2014-06-04 23:59:59.999‘) ON
seg_d_wrnop,
p20140605 VALUES
<= (‘2014-06-05 23:59:59.999‘) ON
seg_d_wrnop,
p20140606 VALUES
<= (‘2014-06-06 23:59:59.999‘) ON
seg_d_wrnop,
p20140607 VALUES
<= (‘2014-06-07 23:59:59.999‘) ON
seg_d_wrnop,
p20140608 VALUES
<= (‘2014-06-08 23:59:59.999‘) ON
seg_d_wrnop,
p20140609 VALUES
<= (‘2014-06-09 23:59:59.999‘) ON
seg_d_wrnop,
p20140610 VALUES
<= (‘2014-06-10 23:59:59.999‘) ON
seg_d_wrnop,
p20140611 VALUES
<= (‘2014-06-11 23:59:59.999‘) ON
seg_d_wrnop,
p20140612 VALUES
<= (‘2014-06-12 23:59:59.999‘) ON
seg_d_wrnop,
p20140613 VALUES
<= (‘2014-06-13 23:59:59.999‘) ON
seg_d_wrnop,
p20140614 VALUES
<= (‘2014-06-14 23:59:59.999‘) ON
seg_d_wrnop,
p20140615 VALUES
<= (‘2014-06-15 23:59:59.999‘) ON
seg_d_wrnop,
p20140616 VALUES
<= (‘2014-06-16 23:59:59.999‘) ON
seg_d_wrnop,
p20140617 VALUES
<= (‘2014-06-17 23:59:59.999‘) ON
seg_d_wrnop,
p20140618 VALUES
<= (‘2014-06-18 23:59:59.999‘) ON
seg_d_wrnop,
p20140619 VALUES
<= (‘2014-06-19 23:59:59.999‘) ON
seg_d_wrnop,
p20140620 VALUES
<= (‘2014-06-20 23:59:59.999‘) ON
seg_d_wrnop,
p20140621 VALUES
<= (‘2014-06-21 23:59:59.999‘) ON
seg_d_wrnop,
p20140622 VALUES
<= (‘2014-06-22 23:59:59.999‘) ON
seg_d_wrnop,
p20140623 VALUES
<= (‘2014-06-23 23:59:59.999‘) ON
seg_d_wrnop,
p20140624 VALUES
<= (‘2014-06-24 23:59:59.999‘) ON
seg_d_wrnop,
p20140625 VALUES
<= (‘2014-06-25 23:59:59.999‘) ON
seg_d_wrnop,
p20140626 VALUES
<= (‘2014-06-26 23:59:59.999‘) ON
seg_d_wrnop,
p20140627 VALUES
<= (‘2014-06-27 23:59:59.999‘) ON
seg_d_wrnop,
p20140628 VALUES
<= (‘2014-06-28 23:59:59.999‘) ON
seg_d_wrnop,
p20140629 VALUES
<= (‘2014-06-29 23:59:59.999‘) ON
seg_d_wrnop,
p20140630 VALUES
<= (‘2014-06-30 23:59:59.999‘) ON
seg_d_wrnop)
go |
3.2创建惟一性非聚簇分区索引(代替了原表主键的作用)
|
1
2
3
4
5
|
CREATE UNIQUE NONCLUSTERED INDEX
pk_t_jingyu
ON
dbo.t_jingyu(oid,start_time)
ON
seg_i_wrnop
LOCAL INDEX
go |
|
1
2
3
4
5
|
CREATE NONCLUSTERED INDEX
idx_t_jingyu_1
ON
dbo.t_jingyu(start_time,related_rnc)
ON
seg_i_wrnop
LOCAL INDEX
go |
4.选择性插入需要的数据到新表
|
1
2
|
insert into t_jingyu select
* from t_jingyu_bak where
条件
go |