Oracle Partition table (Oracle partition Table) __linux

I. Theoretical knowledge of partitioned tables

Oracle provides partitioning technology to support VLDB (Very Large DataBase). The partitioned table puts different records of the partition columns into different partitions by judging the partitioning columns. Partitions are completely transparent to the application.

Oracle's partitioned tables can include multiple partitions, each of which is a separate segment (SEGMENT) that can be stored in a different table space. Queries can be queried by querying tables to access the data in each partition, or by specifying the partition directly at the time of the query.

When to Partition a table needs to be partitioned, the official website's 2 recommendations are as follows:

(1) Tables greater than 2GB should always is considered for partitioning.

(2) Tables containing historical data, in which new data are added into the newest partition. A typical example is a historical table where only the current month ' s data are updatable and the other months are read Only.

Maximum support in Oracle 10g: 1024K-1 Partitions:

Tables can be partitioned into the up to 1024k-1 separate partitions

Description of the partitioned tables and indexes on the online documentation:

Partitioned Tables and Indexes

http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/partconc.htm#sthref2604

Partitioning provides the following benefits:

(1) The possibility of data corruption is reduced because the data is dispersed into various partitions;

(2) A separate partition can be backed up and restored;

(3) The partition can be mapped to different physical disks to disperse IO;

(4) Improve manageability, availability, and performance.

Oracle 10g provides the following types of partitions:

(1) Range partition (range);

(2) hash partition (hash);

(3) List partitions (lists);

(4) Range-hash compound partition (Range-hash);

(5) Range-List composite partition (Range-list).

Range Partition:

A range partition is a table partitioning method that uses a wide range of values, as a partitioning condition of a column's value, and a record to the range partition in which the column value resides.

In terms of time, January 2010 data is placed in a partition, February data is placed in partition B, and when created, you need to specify the columns that are based on, and the range values of the partitions.

When partitioning by time, if some records are temporarily unpredictable, you can create maxvalue partitions, and all records that are not in the specified range are stored in the MaxValue partition.

Such as:

CREATE TABLE PDBA (ID number, time date) partition by range (time)

(

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

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

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

Partition P4 values less than (MaxValue)

)

Hash partition:

For tables that cannot be effectively partitioned, you can use a hash partition to help improve performance. The hash partition distributes the data in the table evenly to several partitions that you specify, and the columns are allocated automatically according to the hash value of the partition column, so you cannot control or know which records will be placed in which partition, and the hash partition can support multiple dependent columns.

Such as:

CREATE TABLE Test

(

TRANSACTION_ID Number primary Key,

ITEM_ID Number (8) NOT NULL

)

Partition by hash (transaction_id)

(

Partition part_01 tablespace tablespace01,

Partition part_02 tablespace TABLESPACE02,

Partition part_03 tablespace tablespace03

);

Here, we specify the table space for each partition.

List partition:

The list partition also needs to specify the value of the column whose partition value must be explicitly specified, which can only have one, and cannot specify multiple columns as a partition-dependent column as a range or a hash partition, but its individual partition corresponding value can be multiple.

When partitioning, you must determine what values the partitioning column might have. Insert/update fails once the inserted column value is not within the partition range, so when using the list partition, it is often recommended that you create a default partition that stores records that are not in the specified range, similar to the MaxValue partition in the range partition.

When based on a field, such as a city code partition, default can be specified, and the data for the unpartitioned rule is placed in the default partition.

Such as:

CREATE TABLE CustAddr
(

ID VARCHAR2 (byte) not NULL,

AreaCode varchar2 (4 byte)
)

Partition by list (AreaCode)
(Partition t_list025 values (' 025 '),
Partition t_list372 values (' 372 '),
Partition t_list510 values (' 510 '),

Partition P_other values (default)

)

Combined partitions:

If a table follows a column partition, still larger, or some other requirement, you can also partition the partition in the form of sub partitions within the partition, that is, the way to combine the partitions.

Combined partitions There are two kinds in 10g: range-hash,range-list. Note The order, the root partition can only be a range partition, and the child partition may be a hash partition or a list partition.

Such as:

CREATE TABLE Test

(

TRANSACTION_ID Number primary Key,

Transaction_date Date

)

Partition by Range (transaction_date) subpartition by hash (transaction_id)

Subpartitions 3 store in (TABLESPACE01,TABLESPACE02,TABLESPACE03)

(

Partition part_01 values less than (to_date (' 2009-01-01 ', ' yyyy-mm-dd ')),

Partition part_02 values less than (to_date (' 2010-01-01 ', ' yyyy-mm-dd ')),

Partition part_03 values less than (MaxValue)

);

CREATE TABLE Emp_sub_template (deptno number, empname varchar (), grade number)

Partition by Range (DEPTNO) subpartition by hash (empname)

Subpartition Template

(Subpartition a tablespace ts1,

Subpartition b tablespace Ts2,

Subpartition c tablespace Ts3,

Subpartition d tablespace TS4

)

(partition P1 values less than (1000),

Partition P2 values less than (2000),

Partition P3 values less than (MaxValue)

);

CREATE TABLE Quarterly_regional_sales

(deptno number, item_no varchar2 (20),

Txn_date date, txn_amount number, state varchar2 (2))

Tablespace TS4

Partition by range (txn_date)

Subpartition by list (state)

(partition q1_1999 values less than (to_date (' 1-apr-1999 ', ' dd-mon-yyyy '))

(Subpartition q1_1999_northwest values (' or ', ' wa '),

Subpartition q1_1999_southwest values (' AZ ', ' ut ', ' nm '),

Subpartition q1_1999_northeast values (' NY ', ' VM ', ' NJ '),

Subpartition q1_1999_southeast VALUES (' fl ', ' GA '),

Subpartition q1_1999_northcentral values (' SD ', ' wi '),

Subpartition q1_1999_southcentral values (' OK ', ' TX ')

),

Partition q2_1999 values less than (to_date (' 1-jul-1999 ', ' dd-mon-yyyy '))

(Subpartition q2_1999_northwest values (' or ', ' wa '),

Subpartition q2_1999_southwest values (' AZ ', ' ut ', ' nm '),

Subpartition q2_1999_northeast values (' NY ', ' VM ', ' NJ '),

Subpartition q2_1999_southeast VALUES (' fl ', ' GA '),

Subpartition q2_1999_northcentral values (' SD ', ' wi '),

Subpartition q2_1999_southcentral values (' OK ', ' TX ')

),

Partition q3_1999 values less than (to_date (' 1-oct-1999 ', ' dd-mon-yyyy '))

(Subpartition q3_1999_northwest values (' or ', ' wa '),

Subpartition q3_1999_southwest values (' AZ ', ' ut ', ' nm '),

Subpartition q3_1999_northeast values (' NY ', ' VM ', ' NJ '),

Subpartition q3_1999_southeast VALUES (' fl ', ' GA '),

Subpartition q3_1999_northcentral values (' SD ', ' wi '),

Subpartition q3_1999_southcentral values (' OK ', ' TX ')

),

Partition q4_1999 values less than (to_date (' 1-jan-2000 ', ' dd-mon-yyyy '))

(Subpartition q4_1999_northwest values (' or ', ' wa '),

Subpartition q4_1999_southwest values (' AZ ', ' ut ', ' nm '),

Subpartition q4_1999_northeast values (' NY ', ' VM ', ' NJ '),

Subpartition q4_1999_southeast VALUES (' fl ', ' GA '),

Subpartition q4_1999_northcentral values (' SD ', ' wi '),

Subpartition q4_1999_southcentral values (' OK ', ' TX ')

)

);

In Oracle 11g, the combined partitioning feature has been enhanced, adding range-range,list-range,

List-list,list-hash and interval partitions and virtual column partitions are also supported inside the 11g.

This piece can be referred to blog:

Introduction to Oracle 11g new features

Http://blog.csdn.net/tianlesoftware/archive/2010/01/06/5134819.aspx

Partition table interval and virtual columns by week partition table

Http://blog.csdn.net/tianlesoftware/archive/2010/06/10/5662337.aspx

Two. normal table to partition table method

There are 4 ways to convert a common table to a composition area table:

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 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) * *

CreateTable Intervaldave

Partitionbyrange (Time_fee)

INTERVAL (Numtoyminterval (1, ' MONTH '))

(PARTITION part1

Valueslessthan (to_date (' 01/12/2010 ', ' mm/dd/yyyy '))

As

SELECT ID, Time_fee fromdave;

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 10g, 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.

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

TABLE_NAME Partition_name

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

PDBA P1

PDBA P2

PDBA P3

PDBA P4

Sql> Select COUNT (*) from PDBA partition (P1);

COUNT (*)

----------

1718285

Sql> Select COUNT (*) from PDBA partition (P2);

COUNT (*)

----------

183667

Sql> Select COUNT (*) from PDBA partition (p3);

COUNT (*)

----------

188701

Sql> Select COUNT (*) from PDBA partition (P4);

COUNT (*)

----------

622582

Sql>

Now that the partition table has been built, but the table name is not the same, you need to rename the table with rename:

sql> Rename dba to Dba_old;

The table has been renamed.

sql> rename Pdba to DBA;

The table has been renamed.

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

TABLE_NAME Partition_name

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

DBA P1

DBA P2

DBA P3

DBA P4