5.创建全局索引：

SELECT * FROM IPS2.TIPS_TRD_HK_P WHERE HB_SDATE>SYSDATE-10

Plan
SELECT STATEMENT ALL_ROWS Cost: 11,890 Bytes: 1,909,422 Cardinality: 666
	2 PARTITION RANGE ALL Cost: 11,890 Bytes: 1,909,422 Cardinality: 666 Partition #: 1 Partitions accessed #1 - #8
		1 TABLE ACCESS FULL TABLE IPS2.TIPS_TRD_HK_P Cost: 11,890 Bytes: 1,909,422 Cardinality: 666 Partition #: 1 Partitions accessed #1 - #8

SELECT * FROM IPS2.TIPS_TRD_HK_P WHERE HB_EDATE>SYSDATE-10

Plan
SELECT STATEMENT ALL_ROWS Cost: 11,891 Bytes: 1,909,422 Cardinality: 666
	2 PARTITION RANGE ALL Cost: 11,891 Bytes: 1,909,422 Cardinality: 666 Partition #: 1 Partitions accessed #1 - #8
		1 TABLE ACCESS FULL TABLE IPS2.TIPS_TRD_HK_P Cost: 11,891 Bytes: 1,909,422 Cardinality: 666 Partition #: 1 Partitions accessed #1 - #8

SELECT * FROM IPS2.TIPS_TRD_HK_P WHERE HB_AMT >20000

Plan
SELECT STATEMENT ALL_ROWS Cost: 11,875 Bytes: 642,101,921 Cardinality: 223,963
	2 PARTITION RANGE ALL Cost: 11,875 Bytes: 642,101,921 Cardinality: 223,963 Partition #: 1 Partitions accessed #1 - #8
		1 TABLE ACCESS FULL TABLE IPS2.TIPS_TRD_HK_P Cost: 11,875 Bytes: 642,101,921 Cardinality: 223,963 Partition #: 1 Partitions accessed #1 - #8

CREATE INDEX IDX_HK_P_HB_SDATE ON IPS2.TIPS_TRD_HK_P(HB_SDATE);

CREATE INDEX IDX_HK_P_HB_EDATE ON IPS2.TIPS_TRD_HK_P(HB_EDATE);

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 * FROM IPS2.TIPS_TRD_HK_P WHERE HB_SDATE>SYSDATE-10

Plan
SELECT STATEMENT ALL_ROWS Cost: 496 Bytes: 3,477,671 Cardinality: 1,213
	2 TABLE ACCESS BY GLOBAL INDEX ROWID TABLE IPS2.TIPS_TRD_HK_P Cost: 496 Bytes: 3,477,671 Cardinality: 1,213 Partition #: 1 Partition access computed by row location
		1 INDEX RANGE SCAN INDEX IPS2.IDX_HK_P_HB_SDATE Cost: 6 Cardinality: 1,213

SELECT * FROM IPS2.TIPS_TRD_HK_P WHERE HB_EDATE>SYSDATE-10

Plan
SELECT STATEMENT ALL_ROWS Cost: 389 Bytes: 5,195,004 Cardinality: 1,812
	2 TABLE ACCESS BY GLOBAL INDEX ROWID TABLE IPS2.TIPS_TRD_HK_P Cost: 389 Bytes: 5,195,004 Cardinality: 1,812 Partition #: 1 Partition access computed by row location
		1 INDEX RANGE SCAN INDEX IPS2.IDX_HK_P_HB_EDATE Cost: 8 Cardinality: 1,812

SELECT * FROM IPS2.TIPS_TRD_HK_P WHERE HB_AMT >20000

Plan
SELECT STATEMENT ALL_ROWS Cost: 2,288 Bytes: 7,167,500 Cardinality: 2,500
	3 PARTITION RANGE ITERATOR Cost: 2,288 Bytes: 7,167,500 Cardinality: 2,500 Partition #: 1 Partitions accessed #3 - #5
		2 TABLE ACCESS BY GLOBAL INDEX ROWID TABLE IPS2.TIPS_TRD_HK_P Cost: 2,288 Bytes: 7,167,500 Cardinality: 2,500 Partition #: 2 Partition access computed by row location
			1 INDEX RANGE SCAN INDEX IPS2.IDX_HK_P_AMT Cost: 8 Cardinality: 2,500 Partition #: 1 Partitions accessed #3 - #5

6. 对分区表进行DDL操作：

ADD、TRUNCATE、DROP、MOVE、SPLIT 等

SELECT INDEX_NAME,PARTITION_NAME,STATUS FROM DBA_IND_PARTITIONS WHERE INDEX_OWNER='IPS2' ORDERBY INDEX_NAME,PARTITION_NAME;

select index_name,table_name,partitioning_type,partition_count, locality,alignment from user_part_indexes WHERETABLE_NAME='TIPS_TRD_HK_P'

ORDER BY ALIGNMENT DESC;

INDEX_NAME	TABLE_NAME	PARTITIONING_TYPE	PARTITION_COUNT	LOCALITY	ALIGNMENT
IDX_HK_P_CREATETIME	TIPS_TRD_HK_P	RANGE	8	LOCAL	PREFIXED
IDX_HK_P_CT_HB_ID	TIPS_TRD_HK_P	RANGE	8	LOCAL	PREFIXED
IDX_HK_P_AMT	TIPS_TRD_HK_P	RANGE	5	GLOBAL	PREFIXED
IDX_HK_P_HB_ID_CT	TIPS_TRD_HK_P	RANGE	8	LOCAL	NON_PREFIXED
IDX_HK_P_MER_CODE	TIPS_TRD_HK_P	RANGE	8	LOCAL	NON_PREFIXED

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

--TRUNCATE PARTTION（NULL RECORDS）

ALTER TABLE IPS2.TIPS_TRD_HK_P TRUNCATE 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	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

--DROP PARTITION（NULL RECORDS）

ALTER TABLE IPS2.TIPS_TRD_HK_P DROP 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	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

--TRUNCATE PARTTION（INCLUDING DATA）

ALTER TABLE IPS2.TIPS_TRD_HK_P TRUNCATE PARTITION TRD_HK_2008;

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

SELECT INDEX_NAME,PARTITION_NAME,STATUS FROM DBA_IND_PARTITIONS WHERE INDEX_OWNER='IPS2' ORDERBY INDEX_NAME,PARTITION_NAME

SELECT * FROM IPS2.TIPS_TRD_HK_P WHERE HB_SDATE>SYSDATE-10

成功，但是全表扫描;

SELECT * FROM IPS2.TIPS_TRD_HK_P WHERE HB_EDATE>SYSDATE-10

成功，但是全表扫描;

SELECT * FROM IPS2.TIPS_TRD_HK_P WHERE HB_AMT >20000

成功，但是全表扫描;

--DROP PARTITION（INCLUDING DATA）

ALTER TABLE IPS2.TIPS_TRD_HK_P DROP PARTITION TRD_HK_2008 ;

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

ITPub博客

Oracle10g Database Table Partition Testcase-3 严于律己

5.创建全局索引：

6. 对分区表进行DDL操作：