1. 云图网首页
  2. 技术专区
  3. 数据库

普通表改为分区表

• 数据库

生产库有张表数据量已经很大了，比较可行的方法是将它改成分区表，经分析，打算用交换分区的方法实施，在正式在生产系统上操作之前，先在测试环境上测试一把，以下是详细步骤。

创建普通表

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
 
CREATE TABLE OCP.TABLE_DATE1  
(  
 OUT_MSG CLOB,  
 FIRST_TIME DATE,  
 STATUS VARCHAR2(1),  
 DELETED VARCHAR2(3)
)  
TABLESPACE OCP_TF  
LOB (OUT_MSG) STORE AS  
 ( TABLESPACE LOB_DATA  
 ENABLE STORAGE IN ROW  
 CHUNK 8192  
 RETENTION  
 NOCACHE  
 INDEX (  
 TABLESPACE LOB_DATA  
 STORAGE (  
 INITIAL 64K  
 NEXT 1  
 MINEXTENTS 1  
 MAXEXTENTS UNLIMITED  
 PCTINCREASE 0  
 BUFFER_POOL DEFAULT  
 ))  
 STORAGE (  
 INITIAL 64K  
 MINEXTENTS 1  
 MAXEXTENTS UNLIMITED  
 PCTINCREASE 0  
 BUFFER_POOL DEFAULT  
 )  
 )  
NOCACHE  
NOPARALLEL  
MONITORING;
create index idx_first_time on table_date1(first_time);
create index idx_status on table_date1(status);

创建分区表

1
2
3
4
5
6
7
8
9
10
 
create table TABLE_DATE1_NEW  
(  
 OUT_MSG CLOB,  
 FIRST_TIME DATE,  
 STATUS VARCHAR2(1),  
 DELETED VARCHAR2(3)  
)  
PARTITION BY RANGE (FIRST_TIME)  
(PARTITION P1 VALUES LESS THAN (TO_DATE('2009-11-26', 'YYYY-MM-DD')),  
PARTITION P2 VALUES LESS THAN (MAXVALUE));

建索LOCAL引

1
2
 
create index idx_first_time_NEW on table_date1_NEW(first_time) local ;
create index idx_status_NEW on table_date1_NEW(status) local ;

注意: 如果要交换分区，分区表的索引必须为local类型，且索引列及顺序要与交换的表一致。否则报 ORA-14098: ALTER TABLE EXCHANGE PARTITION 中的表索引不匹配也做了个实验,不加local参数,交换分区后,索引虽然没分区(全局的),但是状态为 UNUSABLE

交换分区

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
ALTER TABLE TABLE_DATE1_NEW EXCHANGE PARTITION P1 WITH TABLE TABLE_DATE1 INCLUDING INDEXES ;
19:45:30 [SYS@tf](mailto:SYS@tf)> select count(*) from ocp.table_date1 ;
 COUNT(*)  
----------  
 835586
已选择 1 行。
  
19:45:40 [SYS@tf](mailto:SYS@tf)> @tablesize  
输入 table_name 的值: TABLE_DATE1
OWNER SEGMENT_NAME SEGMENT_TYPE size(M)  
------------ ----------------------------------- -------------------- ----------  
OCP SYS_IL0000057740C00001$$ LOBINDEX 1  
 IDX_FIRST_TIME INDEX 18  
 TABLE_DATE1 TABLE 57  
 ----------  
sum 76  
OCP_TEST TABLE_DATE1 TABLE 1  
 ----------  
sum 1

查看TABLE_DATE1_NEW表和索引大小

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
19:45:47 [SYS@tf](mailto:SYS@tf)> select segment_name,partition_name,bytes/1024/1024 M  
19:45:48 2 From dba_segments where segment_name in ('SYS_IL0000057744C00001$$','IDX_FIRST_TIME_NEW');
SEGMENT_NAME PARTITION_NAME M  
----------------------------------- ------------------------------ ----------  
SYS_IL0000057744C00001$$ SYS_IL_P63 1  
SYS_IL0000057744C00001$$ SYS_IL_P64 1  
IDX_FIRST_TIME_NEW P1 1  
IDX_FIRST_TIME_NEW P2 1
已选择4行。
再次查看TABLE_DATE1_NEW表和索引大小  
19:47:35 [SYS@tf](mailto:SYS@tf)> select segment_name,partition_name,bytes/1024/1024 M  
19:49:46 2 From dba_segments where segment_name in ('SYS_IL0000057744C00001$$','IDX_FIRST_TIME_NEW');
SEGMENT_NAME PARTITION_NAME M  
----------------------------------- ------------------------------ ----------  
SYS_IL0000057744C00001$$ SYS_IL_P63 1  
SYS_IL0000057744C00001$$ SYS_IL_P64 1  
IDX_FIRST_TIME_NEW P1 18  
IDX_FIRST_TIME_NEW P2 1

注意 : IDX_FIRST_TIME_NEW 由原来1M变为现在18M

1
2
3
4
 
alter table TABLE_DATE1 rename to TABLE_DATE1_OLD;
alter table TABLE_DATE1_NEW rename to TABLE_DATE1;
drop table table_date1_old;(删除原表)
alter index IDX_FIRST_TIME_NEW rename to IDX_FIRST_TIME;(索引RENAME)

到这里,普通表转化成分区表成功完成

增加分区

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
 
alter table TABLE_DATE1 drop partition P2; (删除上界分区)
alter table ocp.TABLE_DATE1  
add partition TABLE_DATE1_2009_11 values less than (TO_DATE(' 2009-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  
 tablespace OCP_TF  
 pctfree 10  
 initrans 1  
 maxtrans 255  
 storage  
 (  
 initial 1M  
 next 1M  
 minextents 1  
 maxextents unlimited  
 pctincrease 0  
 );  
  
  
alter table ocp.TABLE_DATE1  
add partition TABLE_DATE1_2009_12 values less than (TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  
 tablespace OCP_TF  
 pctfree 10  
 initrans 1  
 maxtrans 255  
 storage  
 (  
 initial 1M  
 next 1M  
 minextents 1  
 maxextents unlimited  
 pctincrease 0  
 );

分区分析

1
2
 
EXECUTE DBMS_STATS.GATHER_TABLE_STATS(ownname=>'OCP',tabname=>'TABLE_DATE1',partname=>'TABLE_DATE1_2009_11',estimate_percent=>10,granularity=>'PARTITION',CASCADE=>TRUE);  
EXECUTE DBMS_STATS.GATHER_TABLE_STATS(ownname=>'OCP',tabname=>'TABLE_DATE1',partname=>'TABLE_DATE1_2009_12',estimate_percent=>10,granularity=>'PARTITION',CASCADE=>TRUE);

附录 EXCHANGE PARTITION 选项

WITH TABLE table
Specify the table with which the partition or subpartition will be exchanged.
INCLUDING INDEXES
Specify INCLUDING INDEXES if you want local index partitions or subpartitions to be exchanged with the corresponding table index (for a nonpartitioned table) or local indexes (for a hash-partitioned table).
EXCLUDING INDEXES
Specify EXCLUDING INDEXES if you want all index partitions or subpartitions corresponding to the partition and all the regular indexes and index partitions on the exchanged table to be marked UNUSABLE.
WITH VALIDATION
Specify WITH VALIDATION if you want Oracle Database to return an error if any rows in the exchanged table do not map into partitions or subpartitions being exchanged.
WITHOUT VALIDATION
Specify WITHOUT VALIDATION if you do not want Oracle Database to check the proper mapping of rows in the exchanged table.

原创文章，作者：kepupublish，如若转载，请注明出处：https://blog.ytso.com/236353.html

赞 (0)
0
上一篇 2022年1月24日
下一篇 2022年1月24日

相关推荐

发表回复

登录后才能评论