首页 » ORACLE » table split partitions test(拆分表分区)

table split partitions test(拆分表分区)

随着数据的爆炸,表再所难免用到分区,再分区…

SQL> l
 create table testspli(id int not null,
 stuno varchar2(10),
 status number(1),
 stime date default sysdate)
 partition by range(stime)
 (partition p_2007 values less than(to_date('2008-1-1','yyyy-mm-dd')),
 partition p_2008 values less than(to_date('2009-1-1','yyyy-mm-dd')),
 partition p_2009 values less than(to_date('2010-1-1','yyyy-mm-dd')),
 partition p_more values less than(MAXVALUE)
 )
SQL> /

Table created.

SQL> create sequence seq;

insert into testspli values(seq.nextval,'stu001',1,to_date('2007-1-1','yyyy-mm-dd'));
insert into testspli values(seq.nextval,'stu001',1,to_date('2008-1-1','yyyy-mm-dd'));
insert into testspli values(seq.nextval,'stu001',1,to_date('2009-1-1','yyyy-mm-dd'));
insert into testspli values(seq.nextval,'stu001',1,to_date('2010-1-1','yyyy-mm-dd'));
insert into testspli values(seq.nextval,'stu001',1,to_date('2011-1-1','yyyy-mm-dd'));
insert into testspli values(seq.nextval,'stu001',1,to_date('2012-1-1','yyyy-mm-dd'));
insert into testspli values(seq.nextval,'stu001',1,to_date('2013-1-1','yyyy-mm-dd'));


SQL> commit;

Commit complete.

anbob@ANBOB> select * from testspli;

        ID STUNO                              STATUS STIME
---------- ------------------------------ ---------- -------------------
       119 stu001                                  1 2007-01-01 00:00:00
       138 stu001                                  1 2007-01-01 00:00:00
       157 stu001                                  1 2008-01-01 00:00:00
       176 stu001                                  1 2009-01-01 00:00:00
       195 stu001                                  1 2010-01-01 00:00:00
         1 stu001                                  1 2011-01-01 00:00:00
        20 stu001                                  1 2012-01-01 00:00:00
        39 stu001                                  1 2013-01-01 00:00:00

anbob@ANBOB> create unique index idx_un_id on testspli(id);

Index created.
anbob@ANBOB> create index idx_loc_stime on testspli(stime) local;

Index created.
anbob@ANBOB> col index_name for a20
anbob@ANBOB> select index_name,UNIQUENESS,status,PARTITIONed from user_indexes where table_name='TESTSPLI';

INDEX_NAME           UNIQUENESS                  STATUS                   PARTITION
-------------------- --------------------------- ------------------------ ---------
IDX_UN_ID            UNIQUE                      VALID                    NO
IDX_LOC_STIME        NONUNIQUE                   N/A                      YES
anbob@ANBOB> select index_name,partition_name,num_rows from user_ind_partitions;

INDEX_NAME           PARTITION_   NUM_ROWS
-------------------- ---------- ----------
IDX_LOC_STIME        P_2008              1
IDX_LOC_STIME        P_2007              2
IDX_LOC_STIME        P_2009              1
IDX_LOC_STIME        P_MORE              4

system@ANBOB> exec dbms_stats.gather_table_stats('ANBOB','TESTSPLI',estimate_percent=>100,granularity=>'ALL'); 

PL/SQL procedure successfully completed.

anbob@ANBOB> select table_name,partition_name,NUM_ROWS from user_tab_partitions;

TABLE_NAME           PARTITION_NAME         NUM_ROWS
-------------------- -------------------- ----------
TESTSPLI             P_2007                        2
TESTSPLI             P_2008                        1
TESTSPLI             P_2009                        1
TESTSPLI             P_MORE                        4

anbob@ANBOB> alter table testspli
  2  split partition  p_more
  3  at(to_date('2011-1-1','yyyy-mm-dd'))
  4  into(partition p_2010,partition p_more)
  5  update global indexes
  6  /

Table altered.

anbob@ANBOB> alter table testspli
  2  split partition  p_more
  3  at(to_date('2012-1-1','yyyy-mm-dd'))
  4  into(partition p_2011,partition p_more)
  5  update global indexes
  6  /

Table altered.

anbob@ANBOB> alter table testspli
  2  split partition  p_more
  3  at(to_date('2013-1-1','yyyy-mm-dd'))
  4  into(partition p_2012,partition p_more)
  5  update global indexes
  6  /

Table altered.

anbob@ANBOB> select partition_name,high_value from user_tab_partitions order by 1;

PARTITION_NAME       HIGH_VALUE
 ------------------ ---------------------------------------------------------------------------------------------
P_2007               TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
P_2008               TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
P_2009               TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
P_2010               TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
P_2011               TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
P_2012               TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
P_MORE               MAXVALUE

7 rows selected.

anbob@ANBOB> select partition_name,high_value,status from user_ind_partitions where index_name='IDX_LOC_STIME' ORDER BY 1;

PARTITION_NAME       HIGH_VALUE                                                                                           STATUS
-------------------- ----------------------------------------------------------------------------------------------    ----------------
P_2007               TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')                  USABLE
P_2008               TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')                  USABLE
P_2009               TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')                  USABLE
P_2010               TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')                  UNUSABLE
P_2011               TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')                  UNUSABLE
P_2012               TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')                  UNUSABLE
P_MORE               MAXVALUE                                                                                             UNUSABLE

7 rows selected.

anbob@ANBOB> select index_name,UNIQUENESS,status,PARTITIONed from user_indexes where table_name='TESTSPLI';

INDEX_NAME           UNIQUENESS                  STATUS                   PARTITION
-------------------- --------------------------- ------------------------ ---------
IDX_UN_ID            UNIQUE                      VALID                    NO
IDX_LOC_STIME        NONUNIQUE                   N/A                      YES

note:
alter table split partition是我指定了update global indexes,所以global索引状态是有效的,而分区索引为unusable,其实还区分在要拆分的分区包含数据;local index 自动维护新建分区,index分区名称如果未指定,沿用表分区名称.
官方解释
If the partition you are splitting contains any data, indexes may be marked UNUSABLE

Regular (Heap) Unless you specify UPDATE INDEXES as part of the ALTER TABLE statement:
The database marks UNUSABLE the new partitions (there are two) in each local index.
Any global indexes, or all partitions of partitioned global indexes, are marked UNUSABLE and must be rebuilt.
————-
Index-organized
The database marks UNUSABLE the new partitions (there are two) in each local index.
All global indexes remain usable.

anbob@ANBOB> alter index IDX_LOC_STIME rebuild partition p_2010;
Index altered.

anbob@ANBOB> alter index IDX_LOC_STIME rebuild partition p_2011;
Index altered.

anbob@ANBOB> alter index IDX_LOC_STIME rebuild partition p_2012;
Index altered.

anbob@ANBOB> alter index IDX_LOC_STIME rebuild partition p_more;
Index altered.

anbob@ANBOB> select partition_name pname,high_value,status from user_ind_partitions where index_name='IDX_LOC_STIME' ORDER BY 1;

PNAME      HIGH_VALUE                                                                               STATUS
---------- ---------------------------------------------------------------------------------------- ------------------------
P_2007     TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')      USABLE
P_2008     TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')      USABLE
P_2009     TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')      USABLE
P_2010     TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')      USABLE
P_2011     TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')      USABLE
P_2012     TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')      USABLE
P_MORE     MAXVALUE                                                                                 USABLE

7 rows selected.

anbob@ANBOB> select rowid r,t.* from testspli partition (p_more)t;
R                          ID STUNO                              STATUS STIME
------------------ ---------- ------------------------------ ---------- -------------------
AAAMnQAAGAAAAB0AAA         39 stu001                                  1 2013-01-01 00:00:00
anbob@ANBOB> alter table testspli merge partitions p_2012,p_more into partition p_more;
Table altered.
anbob@ANBOB> select partition_name,high_value from user_tab_partitions order by 1;

PARTITION_ HIGH_VALUE
---------- ----------------------------------------------------------------------------------------
P_2007     TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
P_2008     TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
P_2009     TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
P_2010     TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
P_2011     TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
P_MORE     MAXVALUE

6 rows selected.
anbob@ANBOB> select index_name,UNIQUENESS,status,PARTITIONed from user_indexes where table_name='TESTSPLI';
INDEX_NAME           UNIQUENESS                  STATUS                   PARTITION
-------------------- --------------------------- ------------------------ ---------
IDX_UN_ID            UNIQUE                      UNUSABLE                 NO
IDX_LOC_STIME        NONUNIQUE                   N/A                      YES

anbob@ANBOB> select partition_name,high_value,status from user_ind_partitions where index_name='IDX_LOC_STIME' ORDER BY 1;
PARTITION_ HIGH_VALUE                                                                               STATUS
---------- ---------------------------------------------------------------------------------------- ------------------------
P_2007     TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')      USABLE
P_2008     TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')      USABLE
P_2009     TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')      USABLE
P_2010     TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')      USABLE
P_2011     TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')      USABLE
P_MORE     MAXVALUE                                                                                 UNUSABLE

6 rows selected.
--再次验证上面所说索引状态unusable,需重建

anbob@ANBOB> select rowid r,t.* from testspli partition (p_more)t;

R                          ID STUNO                              STATUS STIME
------------------ ---------- ------------------------------ ---------- -------------------
AAAMnXAAGAAAACMAAA         20 stu001                                  1 2012-01-01 00:00:00
AAAMnXAAGAAAACMAAB         39 stu001                                  1 2013-01-01 00:00:00

--可以看到id=39在分区合并后rowid发生了变化
打赏

目前这篇文章有2条评论(Rss)评论关闭。

  1. Wally Snape | #1
    2011-12-21 at 07:13

    I’m having a small problem. I’m unable to subscribe to your rss feed for some reason. I’m using google reader by the way.

  2. 车载导航仪 | #2
    2011-09-06 at 16:40

    学以致用~