注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

PostgreSQL 中文网

 
 
 

日志

 
 

普通表改为分区表  

2010-12-27 20:38:37|  分类: Oracle |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

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

--创建普通表
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);

--创建新分区表
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引
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
 
--交换分区
ALTER TABLE TABLE_DATE1_NEW EXCHANGE PARTITION P1 WITH TABLE TABLE_DATE1 INCLUDING INDEXES ;  

19:45:30  SYS@tf> select count(*) from ocp.table_date1 ;

  COUNT(*)
----------
    835586

已选择 1 行。


19:45:40 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
19:45:43 SYS@tf>
19:45:47 SYS@tf>

--查看TABLE_DATE1_NEW表和索引大小
19:45:47 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> 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

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)

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


--增加分区
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
    );

 

--分区分析
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.

  评论这张
 
阅读(28746)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2016