SELECT INDEX_NAME,PARTITION_NAME,STATUS FROM DBA_IND_PARTITIONS WHERE INDEX_OWNER='IPS2' ORDERBY INDEX_NAME,PARTITION_NAME
--ADD PARTITION
ALTER TABLE IPS2.TIPS_TRD_HK_P ADD PARTITION TRD_HK_2015 VALUES LESS THAN(to_date('20160101','yyyymmdd'));
SELECT INDEX_NAME,TABLE_NAME,STATUS,PARTITIONED FROM DBA_INDEXES WHERE OWNER='IPS2' ANDTABLE_NAME='TIPS_TRD_HK_P'
|
INDEX_NAME
|
TABLE_NAME
|
STATUS
|
PARTITIONED
|
|
IDX_HK_P_HB_ID_CT
|
TIPS_TRD_HK_P
|
N/A
|
YES
|
|
IDX_HK_P_CT_HB_ID
|
TIPS_TRD_HK_P
|
N/A
|
YES
|
|
IDX_HK_P_MER_CODE
|
TIPS_TRD_HK_P
|
N/A
|
YES
|
|
IDX_HK_P_CREATETIME
|
TIPS_TRD_HK_P
|
N/A
|
YES
|
|
IDX_HK_P_HB_SDATE
|
TIPS_TRD_HK_P
|
VALID
|
NO
|
|
IDX_HK_P_HB_EDATE
|
TIPS_TRD_HK_P
|
VALID
|
NO
|
|
IDX_HK_P_AMT
|
TIPS_TRD_HK_P
|
N/A
|
YES
|
--SPLIT PARTITION(NULL DATA)
ALTER TABLE IPS2.TIPS_TRD_HK_P SPLIT PARTITION TRD_HK_2015 AT (to_date('20150701','yyyymmdd')) INTO (PARTITION TRD_HK_2015,PARTITION TRD_HK_2016);
SELECT INDEX_NAME,TABLE_NAME,STATUS,PARTITIONED FROM DBA_INDEXES WHERE OWNER='IPS2' ANDTABLE_NAME='TIPS_TRD_HK_P'
|
INDEX_NAME
|
TABLE_NAME
|
STATUS
|
PARTITIONED
|
|
IDX_HK_P_HB_ID_CT
|
TIPS_TRD_HK_P
|
N/A
|
YES
|
|
IDX_HK_P_CT_HB_ID
|
TIPS_TRD_HK_P
|
N/A
|
YES
|
|
IDX_HK_P_MER_CODE
|
TIPS_TRD_HK_P
|
N/A
|
YES
|
|
IDX_HK_P_CREATETIME
|
TIPS_TRD_HK_P
|
N/A
|
YES
|
|
IDX_HK_P_HB_SDATE
|
TIPS_TRD_HK_P
|
VALID
|
NO
|
|
IDX_HK_P_HB_EDATE
|
TIPS_TRD_HK_P
|
VALID
|
NO
|
|
IDX_HK_P_AMT
|
TIPS_TRD_HK_P
|
N/A
|
YES
|
SELECT INDEX_NAME,PARTITION_NAME,STATUS FROM DBA_IND_PARTITIONS WHERE INDEX_OWNER='IPS2' ORDERBY INDEX_NAME,PARTITION_NAME
--SPLIT PARTITION(Including data)
ALTER TABLE IPS2.TIPS_TRD_HK_P SPLIT PARTITION TRD_HK_2014 AT (to_date('20140127','yyyymmdd')) INTO (PARTITION TRD_HK_2014,PARTITION TRD_HK_2015);
SELECT INDEX_NAME,TABLE_NAME,STATUS,PARTITIONED FROM DBA_INDEXES WHERE OWNER='IPS2' ANDTABLE_NAME='TIPS_TRD_HK_P'
|
INDEX_NAME
|
TABLE_NAME
|
STATUS
|
PARTITIONED
|
|
IDX_HK_P_HB_ID_CT
|
TIPS_TRD_HK_P
|
N/A
|
YES
|
|
IDX_HK_P_CT_HB_ID
|
TIPS_TRD_HK_P
|
N/A
|
YES
|
|
IDX_HK_P_MER_CODE
|
TIPS_TRD_HK_P
|
N/A
|
YES
|
|
IDX_HK_P_CREATETIME
|
TIPS_TRD_HK_P
|
N/A
|
YES
|
|
IDX_HK_P_HB_SDATE
|
TIPS_TRD_HK_P
|
UNUSABLE
|
NO
|
|
IDX_HK_P_HB_EDATE
|
TIPS_TRD_HK_P
|
UNUSABLE
|
NO
|
|
IDX_HK_P_AMT
|
TIPS_TRD_HK_P
|
N/A
|
YES
|
--Rebuild
ALTER INDEX IDX_HK_P_HB_SDATE REBUILD ONLINE;
ALTER INDEX IDX_HK_P_HB_EDATE REBUILD ONLINE;
ALTER INDEX IDX_HK_P_AMT REBUILD PARTITION IDX_HK_P_AMT_P1 ONLINE;
ALTER INDEX IDX_HK_P_AMT REBUILD PARTITION IDX_HK_P_AMT_P2 ONLINE;
ALTER INDEX IDX_HK_P_AMT REBUILD PARTITION IDX_HK_P_AMT_P3 ONLINE;
ALTER INDEX IDX_HK_P_AMT REBUILD PARTITION IDX_HK_P_AMT_P4 ONLINE;
ALTER INDEX IDX_HK_P_AMT REBUILD PARTITION IDX_HK_P_AMT_P5 ONLINE;
SELECT INDEX_NAME,TABLE_NAME,STATUS,PARTITIONED FROM DBA_INDEXES WHERE OWNER='IPS2' ANDTABLE_NAME='TIPS_TRD_HK_P'
|
INDEX_NAME
|
TABLE_NAME
|
STATUS
|
PARTITIONED
|
|
IDX_HK_P_HB_ID_CT
|
TIPS_TRD_HK_P
|
N/A
|
YES
|
|
IDX_HK_P_CT_HB_ID
|
TIPS_TRD_HK_P
|
N/A
|
YES
|
|
IDX_HK_P_MER_CODE
|
TIPS_TRD_HK_P
|
N/A
|
YES
|
|
IDX_HK_P_CREATETIME
|
TIPS_TRD_HK_P
|
N/A
|
YES
|
|
IDX_HK_P_HB_SDATE
|
TIPS_TRD_HK_P
|
VALID
|
NO
|
|
IDX_HK_P_HB_EDATE
|
TIPS_TRD_HK_P
|
VALID
|
NO
|
|
IDX_HK_P_AMT
|
TIPS_TRD_HK_P
|
N/A
|
YES
|
SELECT INDEX_NAME,PARTITION_NAME,STATUS FROM DBA_IND_PARTITIONS WHERE INDEX_OWNER='IPS2' ORDERBY INDEX_NAME,PARTITION_NAME
---------Rebuild local index
alter index IDX_HK_P_CT_HB_ID rebuild partition trd_hk_2009 online;
alter index IDX_HK_P_CT_HB_ID rebuild partition trd_hk_2010 online;
alter index IDX_HK_P_CT_HB_ID rebuild partition trd_hk_2011 online;
alter index IDX_HK_P_CT_HB_ID rebuild partition trd_hk_2012 online;
alter index IDX_HK_P_CT_HB_ID rebuild partition trd_hk_2013 online;
alter index IDX_HK_P_CT_HB_ID rebuild partition trd_hk_2014 online;
alter index IDX_HK_P_CT_HB_ID rebuild partition trd_hk_2015 online;
alter index IDX_HK_P_HB_ID_CT rebuild partition trd_hk_2009 online;
alter index IDX_HK_P_HB_ID_CT rebuild partition trd_hk_2010 online;
alter index IDX_HK_P_HB_ID_CT rebuild partition trd_hk_2011 online;
alter index IDX_HK_P_HB_ID_CT rebuild partition trd_hk_2012 online;
alter index IDX_HK_P_HB_ID_CT rebuild partition trd_hk_2013 online;
alter index IDX_HK_P_HB_ID_CT rebuild partition trd_hk_2014 online;
alter index IDX_HK_P_HB_ID_CT rebuild partition trd_hk_2015 online;
alter index IDX_HK_P_MER_CODE rebuild partition trd_hk_2009 online;
alter index IDX_HK_P_MER_CODE rebuild partition trd_hk_2010 online;
alter index IDX_HK_P_MER_CODE rebuild partition trd_hk_2011 online;
alter index IDX_HK_P_MER_CODE rebuild partition trd_hk_2012 online;
alter index IDX_HK_P_MER_CODE rebuild partition trd_hk_2013 online;
alter index IDX_HK_P_MER_CODE rebuild partition trd_hk_2014 online;
alter index IDX_HK_P_MER_CODE rebuild partition trd_hk_2015 online;
---------Move partition
SELECT INDEX_NAME,TABLE_NAME,STATUS,PARTITIONED FROM DBA_INDEXES WHERE OWNER='IPS2' ANDTABLE_NAME='TIPS_TRD_HK_P'
SELECT INDEX_NAME,PARTITION_NAME,STATUS FROM DBA_IND_PARTITIONS WHERE INDEX_OWNER='IPS2' ORDERBY INDEX_NAME,PARTITION_NAME;
ALTER TABLE table_name MOVE PARTITION partition_name
[TABLESPACE new_tablespace_name][NOLOGGING|LOGGING][COMPRESS]
ALTER TABLE TIPS_TRD_HK_P MOVE PARTITION trd_hk_2010 TABLESPACE users1;
SELECT INDEX_NAME,TABLE_NAME,STATUS,PARTITIONED FROM DBA_INDEXES WHERE OWNER='IPS2' ANDTABLE_NAME='TIPS_TRD_HK_P'
SELECT INDEX_NAME,PARTITION_NAME,STATUS FROM DBA_IND_PARTITIONS WHERE INDEX_OWNER='IPS2' ORDERBY INDEX_NAME,PARTITION_NAME;
--Rebuild global index
ALTER INDEX IDX_HK_P_HB_SDATE REBUILD ONLINE;
ALTER INDEX IDX_HK_P_HB_EDATE REBUILD ONLINE;
ALTER INDEX IDX_HK_P_AMT REBUILD PARTITION IDX_HK_P_AMT_P1 ONLINE;
ALTER INDEX IDX_HK_P_AMT REBUILD PARTITION IDX_HK_P_AMT_P2 ONLINE;
ALTER INDEX IDX_HK_P_AMT REBUILD PARTITION IDX_HK_P_AMT_P3 ONLINE;
ALTER INDEX IDX_HK_P_AMT REBUILD PARTITION IDX_HK_P_AMT_P4 ONLINE;
ALTER INDEX IDX_HK_P_AMT REBUILD PARTITION IDX_HK_P_AMT_P5 ONLINE;
7. 重建本地索引
在对分区表进行一系列DDL操作之后，发现如下语句无法使用index：
|
select * from TIPS_TRD_HK_P where hb_createtime>sysdate-40
select * from TIPS_TRD_HK_P where hb_createtime>sysdate-640
select * from TIPS_TRD_HK_P where HB_ID>80
select * from TIPS_TRD_HK_P where HB_ID>8000
select * from TIPS_TRD_HK_P where HB_ID>8000
select * from TIPS_TRD_HK_P where hb_createtime>sysdate-40 and HB_ID>8000
select * from TIPS_TRD_HK_P where HB_ID>8000 and hb_createtime>sysdate-40
select * from TIPS_TRD_HK_P where hb_createtime>sysdate-640 and HB_ID>8000
select * from TIPS_TRD_HK_P where HB_ID>8000 and hb_createtime>sysdate-640
|
于是，重建本地索引：
--Rebuild local index
alter index IDX_HK_P_CT_HB_ID rebuild partition
trd_hk_2009 online;
alter index IDX_HK_P_CT_HB_ID rebuild partition
trd_hk_2010 online;
alter index IDX_HK_P_CT_HB_ID rebuild partition
trd_hk_2011 online;
alter index IDX_HK_P_CT_HB_ID rebuild partition
trd_hk_2012 online;
alter index IDX_HK_P_CT_HB_ID rebuild partition
trd_hk_2013 online;
alter index IDX_HK_P_CT_HB_ID rebuild partition
trd_hk_2014 online;
alter index IDX_HK_P_CT_HB_ID rebuild partition
trd_hk_2015 online;
alter index IDX_HK_P_HB_ID_CT rebuild partition
trd_hk_2009 online;
alter index IDX_HK_P_HB_ID_CT rebuild partition
trd_hk_2010 online;
alter index IDX_HK_P_HB_ID_CT rebuild partition
trd_hk_2011 online;
alter index IDX_HK_P_HB_ID_CT rebuild partition
trd_hk_2012 online;
alter index IDX_HK_P_HB_ID_CT rebuild partition
trd_hk_2013 online;
alter index IDX_HK_P_HB_ID_CT rebuild partition
trd_hk_2014 online;
alter index IDX_HK_P_HB_ID_CT rebuild partition
trd_hk_2015 online;
alter index IDX_HK_P_MER_CODE rebuild partition trd_hk_2009 online;
alter index IDX_HK_P_MER_CODE rebuild partition trd_hk_2010 online;
alter index IDX_HK_P_MER_CODE rebuild partition trd_hk_2011 online;
alter index IDX_HK_P_MER_CODE rebuild partition trd_hk_2012 online;
alter index IDX_HK_P_MER_CODE rebuild partition trd_hk_2013 online;
alter index IDX_HK_P_MER_CODE rebuild partition trd_hk_2014 online;
alter index IDX_HK_P_MER_CODE rebuild partition trd_hk_2015 online;
