ITPub博客

首页 > 数据库 > Oracle > Oracle10g Database Table Partition Testcase-3 严于律己

Oracle10g Database Table Partition Testcase-3 严于律己

Oracle 作者:cy0001c 时间:2014-03-05 15:39:03 0 删除 编辑

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

 

PARTITION RANGE ALL Cost: 11,890 Bytes: 1,909,422 Cardinality: 666 Partition #: 1 Partitions accessed #1 - #8

 

 

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

 

PARTITION RANGE ALL Cost: 11,891 Bytes: 1,909,422 Cardinality: 666 Partition #: 1 Partitions accessed #1 - #8

 

 

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

 

PARTITION RANGE ALL Cost: 11,875 Bytes: 642,101,921 Cardinality: 223,963 Partition #: 1 Partitions accessed #1 - #8

 

 

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

 

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

 

 

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

 

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

 

 

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

 

PARTITION RANGE ITERATOR Cost: 2,288 Bytes: 7,167,500 Cardinality: 2,500 Partition #: 1 Partitions accessed #3 - #5

 

 

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

 

 

 

INDEX RANGE SCAN INDEX IPS2.IDX_HK_P_AMT Cost: 8 Cardinality: 2,500 Partition #: 1 Partitions accessed #3 - #5

 

 

 

6对分区表进行DDL操作:

ADDTRUNCATEDROPMOVESPLIT 

 

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 PARTTIONNULL 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 PARTITIONNULL 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 PARTTIONINCLUDING 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 PARTITIONINCLUDING 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博客 ” ,链接:http://blog.itpub.net/22341359/viewspace-1114729/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2009-08-14