8.比较全局普通索引和本地非前缀索引性能：
CREATE INDEX IDX_HK_P_MER_CODE ON TIPS_TRD_HK_P(MER_CODE) LOCAL;
select * from ips2.TIPS_TRD_HK_P where MER_CODE='010870'
|
Plan
|
|
SELECT
STATEMENT ALL_ROWS Cost:
16 Bytes:
17,202 Cardinality: 6
|
|
|
3 PARTITION
RANGE ALL Cost:
16 Bytes: 17,202 Cardinality:
6 Partition
#: 1 Partitions accessed #1 - #7
|
|
|
|
2 TABLE
ACCESS BY LOCAL INDEX ROWID
TABLE IPS2.TIPS_TRD_HK_P Cost:
16 Bytes: 17,202 Cardinality: 6Partition #: 1
Partitions accessed #1 - #7
|
|
|
|
|
1 INDEX
RANGE SCAN INDEX IPS2.IDX_HK_P_MER_CODE Cost:
8 Cardinality: 6 Partition #: 1
Partitions accessed #1 - #7
|
78ms
create index idx_hk_p_mer_code_b ON TIPS_TRD_HK_P(MER_CODE) ;
select * from ips2.TIPS_TRD_HK_P where MER_CODE='010870'
|
Plan
|
|
SELECT
STATEMENT ALL_ROWS Cost:
11 Bytes:
17,202 Cardinality: 6
|
|
|
2 TABLE
ACCESS BY GLOBAL INDEX ROWID
TABLE IPS2.TIPS_TRD_HK_P Cost:
11 Bytes: 17,202 Cardinality: 6Partition #: 1
Partition access computed by row location
|
|
|
|
1 INDEX
RANGE SCAN INDEX IPS2.IDX_HK_P_MER_CODE_B Cost:
3 Cardinality: 6
|
78ms
CREATE INDEX IDX_HK_P_MER_CODE ON TIPS_TRD_HK_P(MER_CODE) LOCAL;
select * from ips2.TIPS_TRD_HK_P where HB_CREATETIME>SYSDATE-1900 AND MER_CODE='010870'
|
Plan
|
|
SELECT
STATEMENT ALL_ROWS Cost:
1,810 Bytes:
564,799 Cardinality: 197
|
|
|
9 PARTITION
RANGE ITERATOR Cost:
1,810 Bytes: 564,799 Cardinality:
197 Partition #: 1
Partitions determined by Key Values
|
|
|
|
8 TABLE
ACCESS BY LOCAL INDEX ROWID
TABLE IPS2.TIPS_TRD_HK_P Cost:
1,810 Bytes: 564,799 Cardinality: 197Partition #: 1
Partitions determined by Key Values
|
|
|
|
|
7 BITMAP
CONVERSION TO ROWIDS
|
|
|
|
|
|
6 BITMAP
AND
|
|
|
|
|
|
|
2 BITMAP
CONVERSION FROM ROWIDS
|
|
|
|
|
|
|
|
1 INDEX
RANGE SCAN INDEX IPS2.IDX_HK_P_MER_CODE Cost:
13 Cardinality: 1,574 Partition #: 1
Partitions determined by Key Values
|
|
|
|
|
|
|
5 BITMAP
CONVERSION FROM ROWIDS
|
|
|
|
|
|
|
|
4 SORT
ORDER BY
|
|
|
|
|
|
|
|
|
3 INDEX
RANGE SCAN INDEX IPS2.IDX_HK_P_CT_HB_ID Cost:
1,684 Cardinality: 1,574 Partition #: 1
Partitions determined by Key Values
|
220ms
create index idx_hk_p_mer_code_b ON TIPS_TRD_HK_P(MER_CODE) ;
select * from ips2.TIPS_TRD_HK_P where HB_CREATETIME>SYSDATE-1900 AND MER_CODE='010870'
|
Plan
|
|
SELECT
STATEMENT ALL_ROWS Cost:
1,894 Bytes:
564,799 Cardinality: 197
|
|
|
2 TABLE
ACCESS BY GLOBAL INDEX ROWID
TABLE IPS2.TIPS_TRD_HK_P Cost:
1,894 Bytes: 564,799 Cardinality: 197Partition #: 1
Partition access computed by row location
|
|
|
|
1 INDEX
RANGE SCAN INDEX IPS2.IDX_HK_P_MER_CODE_B Cost:
8 Cardinality: 1,574
|
87ms
通过以上比较，全局普通索引比本地非前缀索引要快一些！
9.删除分区使用UPDATE
GLOBAL INDEXES
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_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
|
ALTER TABLE IPS2.TIPS_TRD_HK_P DROP PARTITION TRD_HK_2009 UPDATE GLOBAL INDEXES;
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_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
|
通过查询以下SQL:
|
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
|
本地index没有变更，无需维护
。
10.删除/拆分索引分区：
无法显式的删除本地索引的分区，删除的唯一方式是本地索引分区基表的分区被删除时由Oracle自动的隐式删除。
如果全局索引分区是空的，则可以显式的删除它，使用的语句是ALTER
INDEX index_name DROP PARTITION
partition_name。但是，如果全局索引分区包含数据，删除则会引起更高级的分区（即下一个分区）变得不可用。如果非要这么做，则对更高级的分区需要重构，语法是：
ALTER INDEX
index_name REUBILT PARTITION nextpartition_name
本地索引分区无法显式的拆分，其拆分的唯一途径是基表的分区拆分时由Oracle隐式的进行拆分。全局索引分区可以拆分，拆分完成后需要重构。
|
CREATE INDEX IDX_HK_P_AMT ON IPS2.TIPS_TRD_HK_P(HB_AMT)
GLOBAL PARTITION BY RANGE(HB_AMT)
(PARTITION IDX_HK_P_AMT_P1 VALUES LESS THAN (2000),
PARTITION IDX_HK_P_AMT_P2 VALUES LESS THAN (10000),
PARTITION IDX_HK_P_AMT_P3 VALUES LESS THAN (50000),
PARTITION IDX_HK_P_AMT_P4 VALUES LESS THAN (200000),
PARTITION IDX_HK_P_AMT_P5 VALUES LESS THAN (MAXVALUE)
);
|
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' ANDINDEX_NAME='IDX_HK_P_AMT'
ORDER BY INDEX_NAME,PARTITION_NAME;
|
INDEX_NAME
|
PARTITION_NAME
|
STATUS
|
|
IDX_HK_P_AMT
|
IDX_HK_P_AMT_P1
|
USABLE
|
|
IDX_HK_P_AMT
|
IDX_HK_P_AMT_P2
|
USABLE
|
|
IDX_HK_P_AMT
|
IDX_HK_P_AMT_P3
|
USABLE
|
|
IDX_HK_P_AMT
|
IDX_HK_P_AMT_P4
|
USABLE
|
|
IDX_HK_P_AMT
|
IDX_HK_P_AMT_P5
|
USABLE
|
|
ALTER INDEX IDX_HK_P_AMT DROP PARTITION IDX_HK_P_AMT_P1;
|
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' ANDINDEX_NAME='IDX_HK_P_AMT'
ORDER BY INDEX_NAME,PARTITION_NAME;
|
INDEX_NAME
|
PARTITION_NAME
|
STATUS
|
|
IDX_HK_P_AMT
|
IDX_HK_P_AMT_P2
|
UNUSABLE
|
|
IDX_HK_P_AMT
|
IDX_HK_P_AMT_P3
|
USABLE
|
|
IDX_HK_P_AMT
|
IDX_HK_P_AMT_P4
|
USABLE
|
|
IDX_HK_P_AMT
|
IDX_HK_P_AMT_P5
|
USABLE
|
|
ALTER INDEX IDX_HK_P_AMT REBUILD PARTITION IDX_HK_P_AMT_P2;
|
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' ANDINDEX_NAME='IDX_HK_P_AMT'
|
INDEX_NAME
|
PARTITION_NAME
|
STATUS
|
|
IDX_HK_P_AMT
|
IDX_HK_P_AMT_P2
|
USABLE
|
|
IDX_HK_P_AMT
|
IDX_HK_P_AMT_P3
|
USABLE
|
|
IDX_HK_P_AMT
|
IDX_HK_P_AMT_P4
|
USABLE
|
|
IDX_HK_P_AMT
|
IDX_HK_P_AMT_P5
|
USABLE
|
|
ALTER INDEX IDX_HK_P_AMT SPLIT
PARTITION IDX_HK_P_AMT_P2 AT(2000) INTO (PARTITION IDX_HK_P_AMT_P1, PARTITIONIDX_HK_P_AMT_P2);
|
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' ANDINDEX_NAME='IDX_HK_P_AMT'
ORDER BY INDEX_NAME,PARTITION_NAME;
|
INDEX_NAME
|
PARTITION_NAME
|
STATUS
|
|
IDX_HK_P_AMT
|
IDX_HK_P_AMT_P1
|
USABLE
|
|
IDX_HK_P_AMT
|
IDX_HK_P_AMT_P2
|
USABLE
|
|
IDX_HK_P_AMT
|
IDX_HK_P_AMT_P3
|
USABLE
|
|
IDX_HK_P_AMT
|
IDX_HK_P_AMT_P4
|
USABLE
|
|
IDX_HK_P_AMT
|
IDX_HK_P_AMT_P5
|
USABLE
|
从以上可知，拆分后可能不需要重建涉及的索引分区：
|
ALTER INDEX IDX_HK_P_AMT REBUILD PARTITION IDX_HK_P_AMT_P1;
ALTER INDEX IDX_HK_P_AMT REBUILD PARTITION IDX_HK_P_AMT_P2;
|
