Shulou(Shulou.com)06/01 Report--

COALESCE PARTITION | SUBPARTITION (hash)

DROP PARTITION | SUBPARTITION

EXCHANGE PARTITION | SUBPARTITION

MERGE PARTITION | SUBPARTITION

MOVE PARTITION | SUBPARTITION

SPLIT PARTITION | SUBPARTITION

TRUNCATE PARTITION | SUBPARTITION

All of the above will cause the global index to fail.

The following actions will cause the local index to fail.

MERGE PARTITION

MOVE PARTITION

SPLIT PARTITION

COALESCE PARTITION

EXCHANGE PARTITION

Interval partition and time partition are tested here. Adding partition global and local partition will not fail. Deleting partition will cause global index to fail.

Create a test table

Create table FACT_MONTH1

(

Stat_date DATE

Name VARCHAR2 (6)

)

PARTITION BY RANGE (stat_date)

INTERVAL (NUMTOYMINTERVAL (1)

(

PARTITION P1 VALUES LESS THAN (TO_DATE ('2014-1-1,' YYYY-MM-DD'))

);

Insert test data

Insert into FACT_MONTH1TIME values (TO_DATE ('2013-11-1,' YYYY-MM-DD'), 'ABCDE')

Insert into FACT_MONTH1TIME values (TO_DATE ('2014-1-1),' YYYY-MM-DD'), 'ABCDE')

Insert into FACT_MONTH1TIME values (TO_DATE ('2014-1-15,' YYYY-MM-DD'), 'ABCDE')

Insert into FACT_MONTH1TIME values (TO_DATE ('2014-2-15,' YYYY-MM-DD'), 'ABCDE')

Insert into FACT_MONTH1TIME values (TO_DATE ('2014-3-15,' YYYY-MM-DD'), 'ABCDE')

Insert into FACT_MONTH1TIME values (TO_DATE ('2014-4-15,' YYYY-MM-DD'), 'ABCDE')

Insert into FACT_MONTH1TIME values (TO_DATE ('2014-5-15,' YYYY-MM-DD'), 'ABCDE')

Insert into FACT_MONTH1TIME values (TO_DATE ('2014-6-15,' YYYY-MM-DD'), 'ABCDE')

Insert into FACT_MONTH1TIME values (TO_DATE ('2014-7-15,' YYYY-MM-DD'), 'ABCDE')

Commit

View all kinds of information

Select * from FACT_MONTH1

Select * from user_tab_partitions

Select * from user_part_tables

Select * from user_ind_partitions

Select * from user_part_indexes

Create an index

Create index fa_idx_g on FACT_MONTH1 (stat_date)

Create index fa_idx_local on FACT_MONTH1 (name) local

View index information

Select * from user_tab_partitions

Select * from user_part_tables

Select * from user_ind_partitions

Select * from user_part_indexes

Select * from user_indexes

Add Partition:

Insert into FACT_MONTH1 values (TO_DATE ('2014-9-15,' YYYY-MM-DD'), 'ABCDE')

Commit

Check to see if the index is available:

Alter session set statistics_level=all

Select / * + index (FA_IDX_G) * / * from FACT_MONTH1 where stat_date=TO_DATE ('2014-9-15,' YYYY-MM-DD')

Select * from table (dbms_xplan.display_cursor (null,null,'allstats last'))

Time partition test

CREATE TABLE FACT_MONTH1TIME

(

Stat_date DATE

Name VARCHAR2 (6)

)

PARTITION BY RANGE (stat_date)

(

PARTITION p1 VALUES LESS THAN (TO_DATE ('2014-1-1,' yyyy-mm-dd'))

PARTITION p2 VALUES LESS THAN (TO_DATE ('2014-2-1,' yyyy-mm-dd'))

PARTITION p3 VALUES LESS THAN (TO_DATE ('2014-3-1,' yyyy-mm-dd'))

Partition p4 values less than (maxvalue)

);

Insert test data

Insert into FACT_MONTH1TIME values (TO_DATE ('2013-11-1,' YYYY-MM-DD'), 'ABCDE')

Insert into FACT_MONTH1TIME values (TO_DATE ('2014-1-1),' YYYY-MM-DD'), 'ABCDE')

Insert into FACT_MONTH1TIME values (TO_DATE ('2014-1-15,' YYYY-MM-DD'), 'ABCDE')

Insert into FACT_MONTH1TIME values (TO_DATE ('2014-2-15,' YYYY-MM-DD'), 'ABCDE')

Insert into FACT_MONTH1TIME values (TO_DATE ('2014-3-15,' YYYY-MM-DD'), 'ABCDE')

Insert into FACT_MONTH1TIME values (TO_DATE ('2014-4-15,' YYYY-MM-DD'), 'ABCDE')

Insert into FACT_MONTH1TIME values (TO_DATE ('2014-5-15,' YYYY-MM-DD'), 'ABCDE')

Insert into FACT_MONTH1TIME values (TO_DATE ('2014-6-15,' YYYY-MM-DD'), 'ABCDE')

Insert into FACT_MONTH1TIME values (TO_DATE ('2014-7-31,' YYYY-MM-DD'), 'ABCDE')

Commit

Create an index

Create index fa_idxtime_g on FACT_MONTH1TIME (stat_date)

Create index fa_idxtime_local on FACT_MONTH1TIME (name) local

Increase zoning

Alter table FACT_MONTH1TIME add partition p6 VALUES LESS THAN (TO_DATE ('2014-6-1,' yyyy-mm-dd'))

Alter table FACT_MONTH1TIME add partition p8 VALUES LESS THAN (TO_DATE ('2014-8-1),' yyyy-mm-dd'))

Alter table FACT_MONTH1TIME drop partition p8

Check if the index is available

Alter session set statistics_level=all

Select / * + index (FA_IDXTIME_G) * / * from FACT_MONTH1TIME where stat_date=TO_DATE ('2014-9-15,' YYYY-MM-DD')

Select * from table (dbms_xplan.display_cursor (null,null,'allstats last'))

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.