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

Requirements: a partitioned table with hundreds of millions of pieces of data, to separate some old data and put it on a new table. Divided by time, the data before the specified time is placed in the old data table, the data after the specified time is kept in the original table, and then the backup tablespace of the old data is truncate. Operating partition swapping according to partition is the best solution.

Partition exchange technology can achieve rapid data transfer, so it is especially useful in data loading speed up, historical data cleaning and so on. Partition switching technology actually only modifies the location of the physical segment of the data in the data dictionary, rather than the actual moving data, so it is very fast.

Create a partition table:

Create table t_exchange

(

Sno number not null primary key

Oitime date DEFAULT sysdate

Word varchar2 (100)

)

PARTITION BY range (sno)

(PARTITION p1 VALUES LESS THAN (10000) tablespace TBS_MING

PARTITION p2 VALUES LESS THAN (20000) tablespace TBS_MING

PARTITION p3 VALUES LESS THAN (maxvalue) tablespace TBS_MING

) tablespace TBS_MING

Historical Partition Table:

Create table t_exchange_his

(

Sno number not null primary key

Oitime date DEFAULT sysdate

Word varchar2 (100)

)

PARTITION BY range (sno)

(PARTITION p1 VALUES LESS THAN (10000) tablespace TBS_MING

PARTITION p2 VALUES LESS THAN (20000) tablespace TBS_MING

PARTITION p3 VALUES LESS THAN (maxvalue) tablespace TBS_MING

) tablespace TBS_MING

Insert data:

Begin

For i in 1..30000 loop

Insert into t_exchange values (iMagnum sysdatereiPad ('mingshuo',100,'x'))

End loop

Dbms_output.put_line ('roommates')

Commit

End

/

Create some different types of indexes to see if partition swapping affects the indexes:

Source table:

Create a global partition index:

Create index t_exchange_idx_01 on t_exchange (sno,oitime,word)

GLOBAL partition by range (sno)

(

Partition p01 values less than (10000) tablespace TBS_MING

Partition P02 values less than (20000) tablespace TBS_MING

Partition P03 values less than (MAXVALUE) tablespace TBS_MING

);

Create a local non-prefix index:

Create index t_exchange_idx_02 on t_exchange (word) local

Create a unique index:

Create unique index t_exchange_idx_03 on t_exchange (sno,oitime)

History table:

Create a global partition index:

Create index t_exchange_his_idx_01 on t_exchange_his (sno,oitime,word)

GLOBAL partition by range (sno)

(

Partition p01 values less than (10000) tablespace TBS_MING

Partition P02 values less than (20000) tablespace TBS_MING

Partition P03 values less than (MAXVALUE) tablespace TBS_MING

);

Create a local non-prefix partitioned index:

Create index t_exchange__his_idx_02 on t_exchange_his (word) local

The difference between a source table and a history table is that no unique index is created.

Create an intermediate table:

Create table t_exchange_tmp as select * from t_exchange where 1: 2; SQL >! ora ddl ming table t_exchange_tmp

\ n=Fri Jun 22 19:31:13 CST 2018 =\ n

Session altered. DBMS_METADATA.GET_DDL (UPPER ('TABLE'), UPPER (' Tunable EXCHANGENG), UPPER ('MING'))

CREATE TABLE "MING". "T_EXCHANGE_TMP"

("SNO" NUMBER NOT NULL ENABLE

"OITIME" DATE

"WORD" VARCHAR2

) SEGMENT CREATION DEFERRED

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

TABLESPACE "TBS_MING"

Enable 10046 event tracking before inserting data:

EXEC SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION (50,419, true)

Load the data into the intermediate table:

Alter table t_exchange exchange partition p1 with table tasking exchangeholders tmp; data at this time:

SQL > select count (*) from t_exchange_tmp

COUNT (*)

-

9999

SQL > select count (*) from t_exchange partition (p1)

COUNT (*)

-

0 load the data into the history table:

Alter table t_exchange_his exchange partition p1 with table t_exchange_tmp

SQL > alter table t_exchange_his exchange partition p1 with table t_exchange_tmp

Alter table t_exchange_his exchange partition p1 with table t_exchange_tmp

*

ERROR at line 1:

ORA-14130: UNIQUE constraints mismatch in ALTER TABLE EXCHANGE PARTITION

Create a unique index:

Create unique index t_exchange_his_idx_03 on t_exchange_his (sno,oitime); loading data again is just as wrong.

If you look at the definition of the historical table and the intermediate table, you can see that the unique constraint can only be the primary key constraint of sno. This is not true, but it just means that the primary key constraint may be due to the primary key constraint in the intermediate table:

Alter table t_exchange_tmp add primary key (sno)

Load the data again:

SQL > alter table t_exchange_his exchange partition p1 with table t_exchange_tmp

Table altered.

Success! Close the 10046 event:

EXEC SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION (50,419, false); the data has successfully moved from the intermediate table to the history table:

SQL > select count (*) from t_exchange_tmp

COUNT (*)

-

0

SQL > select count (*) from t_exchange_his partition (p1)

COUNT (*)

-

9999 check the index:

SQL >! ora unusable

\ n=Fri Jun 22 19:55:13 CST 2018 =\ n

Session altered. UNUSABLE_INDEXES

ALTER INDEX MING.SYS_C0012382 REBUILD ONLINE;-Primary key index of intermediate table

ALTER INDEX MING.T_EXCHANGE_HIS_IDX_03 REBUILD ONLINE;-the unique index of the history table

ALTER INDEX MING.SYS_C0012380 REBUILD ONLINE;-History table primary key index

ALTER INDEX MING.T_EXCHANGE_IDX_03 REBUILD ONLINE;-the unique index of the source table

ALTER INDEX MING.SYS_C0012378 REBUILD ONLINE;-Source table primary key index

ALTER INDEX MING.T_EXCHANGE__HIS_IDX_02 REBUILD PARTITION P1 ONLINE;-Local non-prefix partitioned index of the history table

ALTER INDEX MING.T_EXCHANGE_HIS_IDX_01 REBUILD PARTITION P03 ONLINE;-Global partitioned index of the history table

ALTER INDEX MING.T_EXCHANGE_HIS_IDX_01 REBUILD PARTITION P02 ONLINE

ALTER INDEX MING.T_EXCHANGE_IDX_01 REBUILD PARTITION P01 ONLINE;-the global partitioned index of the source table

ALTER INDEX MING.T_EXCHANGE_IDX_02 REBUILD PARTITION P1 ONLINE;-Local non-prefix partitioned index of the source table

ALTER INDEX MING.T_EXCHANGE_HIS_IDX_01 REBUILD PARTITION P01 ONLINE

ALTER INDEX MING.T_EXCHANGE_IDX_01 REBUILD PARTITION P03 ONLINE

ALTER INDEX MING.T_EXCHANGE_IDX_01 REBUILD PARTITION P02 ONLINE

13 rows selected.

Summary:

1. You can see from 10046 that the exchange process locks two tables in exclusive mode (exclusive), but executes quickly and doesn't have to worry about blocking business dml statements.

The effects of ogg and dataguard are also considered in 2.10046, which are some modifications to the system tables.

3. When loading into a history table, you need to have consistent constraints on both ends of the table. In fact, it is also easy to understand that the physical location in the data dictionary modified by this process must meet the requirements of the constraint, otherwise loading the past data violates the primary key constraint or unique constraint on the table, then it is meaningless.

4. All the indexes are invalid, so the disadvantage of this method is that although the data loads quickly, the index needs to be rebuilt, which is fatal. If the table is large, the availability of partitions will deteriorate, the performance of daily transactions will decline, and recovery will take a long time.

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.