首页 » ORACLE 9i-23ai » 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)评论关闭。