Oracle Non-partitioned table, __oracle table

The original source of this article:

Http://blog.csdn.net/tianlesoftware/archive/2009/10/23/4717318.aspx

There are 4 ways to convert plain tables into partitioned tables :

1. Export/import method

2. Insert with a subquery method

3. Partition Exchange method

4. Dbms_redefinition

Specific reference:

How to Partition a non-partitioned Table [ID 1070693.6]

Http://blog.csdn.net/tianlesoftware/archive/2011/03/02/6218704.aspx

Logical export Import Here's no explanation, let's look at the other three ways.

2.1 Insert: Insert with a subquery method

This approach is implemented using inserts. Of course, when you create a partitioned table, you can insert the data together, or you can create it and insert it. This method uses DDL statements, does not produce undo, only produces a small number of redo, the completion of the table after the data has been distributed to the various partitions.

Sql> Select COUNT (*) from DBA;

COUNT (*)

----------

2713235

Sql> alter session set nls_date_format= ' Yyyy-mm-dd hh24:mi:ss ';

The session has changed.

Sql> Select Time_fee from DBA where rownum<5;

Time_fee

-------------------

2011-02-17 19:29:09

2011-02-17 19:29:15

2011-02-17 19:29:18

2011-02-17 19:29:20

Sql>

2.1.1 Oracle 11g of interval

Created in the 11g interval, this method is automatically created for partitions that are not fully written. For example, I only write a January date here, if the inserted data has other months, will automatically generate the corresponding partition.

/* Formatted on 2011/03/02 15:41:09 (QP5 v5.115.810.9015) * *

CREATE TABLE Intervaldave

PARTITION by RANGE (Time_fee)

INTERVAL (Numtoyminterval (1, ' MONTH '))

(PARTITION part1

VALUES less THAN (to_date (' 01/12/2010 ', ' mm/dd/yyyy '))

As

SELECT ID, time_fee from DAVE;

Sql> Select Table_name,partition_name from user_tab_partitions where table_name= ' intervaldave ';

TABLE_NAME Partition_name

------------------------------ ------------------------------

Intervaldave PART1

Intervaldave SYS_P24

Intervaldave Sys_p25

Intervaldave sys_p26

Intervaldave SYS_P33

Intervaldave sys_p27

Intervaldave SYS_P28

2.1.2 Oracle 10g version

in the 10g inside, I need to write all the partitions.

Sql> CREATE TABLE Pdba (ID, time) partition by range (time)

2 (partition P1 values less than (to_date (' 2010-10-1 ', ' yyyy-mm-dd ')),

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

4 partition P3 values less than (to_date (' 2010-12-1 ', ' yyyy-mm-dd ')),

5 partition P4 values less than (MaxValue))

6 as Select ID, time_fee from DBA;

Table has been created.