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

There are four ways to convert a regular table to a partitioned table:

1) Export/import method

2) Insert with a subquery method

3) Partition exchange method (swap partition)

4) DBMS_REDEFINITION (online redefinition)

To swap partitions:

1) create a partition table. Suppose there are 2 partitions, P1 and P2.

2) create Table A to store the data of P1 rules

3) create Table B to store the data of P2 rules

4) use table An and P1 partition exchange, put the data of table A to P1 partition

5) use table B and p2 partition exchange to store the data of table B to P2 partition

Example:

a. Create a partition table:

Sql > create table p_dba

2 (id number,time date)

3 partition by range (time)

4 (

5 partition p1 values less than (to_date ('2010-09-1,' yyyy-mm-dd'))

6 partition p2 values less than (to_date ('2010-11-1,' yyyy-mm-dd'))

7)

b. Create 2 base tables corresponding to partitions:

SQL > CREATE TABLE dba_p1 as SELECT id,time_fee FROM dba_old WHERE

Time_fee CREATE TABLE dba_p2 as SELECT id,time_fee FROM dba_old WHERE

Time_feeTO_DATE ('2010-09-1th,' YYYY-MM-DD')

c. Swap 2 base tables with 2 partitions:

SQL > alter table p_dba exchange partition p1 with table dba_p1

The table has changed.

SQL > alter table p_dba exchange partition p2 with table dba_p2

The table has changed.

Online redefinition ensures data consistency, and tables can operate DML normally most of the time.

Lock the table only at the moment of switching, with high availability. This method has strong flexibility and can meet a variety of different needs.

Moreover, the corresponding authorization and various constraints can be established before the handover, so that no additional management operations are needed after the handover is completed.

This feature is available only in versions after 9.2.0.4, and the online redefinition table has the following features:

(1) modify the storage parameters of the table

(2) transfer the table to another tablespace

(3) add parallel query options.

(4) add or delete partitions

(5) rebuild the table to reduce fragmentation

(6) change the heap table into an index to organize the table or vice versa.

(7) add or delete a column

Use some of the constraints that are redefined online:

(1) There must be enough space to hold two copies of the table.

(2) Primary key columns cannot be modified.

(3) Tables must have primary keys.

(4) Redefinition must be done within the same schema.

(5) New columns added cannot be made NOT NULL until after the redefinition operation.

(6) Tables cannot contain LONGs, BFILEs or User Defined Types.

(7) Clustered tables cannot be redefined.

(8) Tables in the SYS or SYSTEM schema cannot be redefined.

(9) Tables with materialized view logs or materialized views defined on them cannot be redefined.

(10) Horizontal sub setting of data cannot be performed during the redefinition.

The general operation flow of online redefinition is as follows:

(1) create basic table A. If it exists, no operation is required.

(2) create temporary partition table B

(3) start redefining and import the data from base table An into temporary partition table B.

(4) end the redefinition. At this time, the two tables have been exchanged in the Name Directory of DB. That is, at this point, base table A becomes a partition table, and the temporary partition table B we created becomes a regular table. At this point we can delete the temporary table B we created.

Example:

a. Create basic tables and indexes

Sql > create table unpar_table (

2 id number (10) primary key

3 create_date date

4)

b. Collect statistics for the table

Sql > exec dbms_stats.gather_table_stats ('icd',' unpar_table', cascade = > true)

The pl/sql process has completed successfully

c. Create a temporary partition table

Sql > create table par_table (id number primary key, time date) partition by range

(time)

2 (partition p1 values less than (to_date ('2004-7-1),' yyyy-mm-dd'))

3 partition p2 values less than (to_date ('2005-1-1,' yyyy-mm-dd'))

4 partition p3 values less than (to_date ('2005-7-1,' yyyy-mm-dd'))

5 partition p4 values less than (maxvalue))

d. Perform a redefinition operation

D1. Check the reasonableness of redefinition

D2. If there is no problem with D1, start redefining, the process may take a while.

Note here: if the partitioning table has the same column name as the original table, it can be done as follows:

SQL > BEGIN

DBMS_REDEFINITION.start_redef_table (

Uname = > 'ICD'

Orig_table = > 'unpar_table'

Int_table = > 'par_table')

END

/

If the column name of the partitioned table is not the same as the original table, you need to respecify it when you start the redefinition

Mapping relationship:

SQL > EXEC DBMS_REDEFINITION.START_REDEF_TABLE (

'ICD'

'unpar_table'

'par_table'

'ID ID, create_date TIME',-- specify the new mapping relationship here

DBMS_REDEFINITION.CONS_USE_PK)

After this step, the data has been synchronized to the temporary partition table.

D3. Synchronize the new table, which is an optional operation

SQL > BEGIN

2 dbms_redefinition.sync_interim_table (

3 uname = > 'ICD'

4 orig_table = > 'unpar_table'

5 int_table = > 'par_table')

6 END

7 /

The PL/SQL process completed successfully.

D4. Create an index, redefine only the data online, and the index needs to be created separately

Sql > create index create_date_ind2 on par_table (time)

D5. Collect statistics for the new table

Sql > exec dbms_stats.gather_table_stats ('icd',' par_table', cascade = > true)

D6. End redefinition

SQL > BEGIN

2 dbms_redefinition.finish_redef_table (

3 uname = > 'ICD'

4 orig_table = > 'unpar_table'

5 int_table = > 'par_table')

6 END

7 /

The PL/SQL process has completed successfully

End the meaning of the redefinition:

The base table unpar_table and the temporary partition table par_table are exchanged. Temporary partition table at this time

Par_table becomes a regular table, and our base table, unpar_table, becomes a partition table.

When we redefine, the base table unpar_table can perform DML operations. Only in 2.

When a table is switched, there will be a temporary lock on the table.

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.