Oracle partition Table partitioning and partitioning index

Introduced:

For 10GR2, it can be divided into several categories:

Range (range) partition

Hash (hash) partition

List (lists) partition

and combined partitions: Range-hash,range-list.

Preparation environment:

--1, building three table spaces

sql> Create tablespace par01 datafile ' e:\oracle\test\par01.dbf ' size 10m;

sql> Create tablespace par02 datafile ' e:\oracle\test\par02.dbf ' size 10m;

sql> Create tablespace par03 datafile ' e:\oracle\test\par03.dbf ' size 10m;

--2, plus permissions

Alter user Fmismain quota unlimited on Par01;

Alter user Fmismain quota unlimited on par02;

Alter user Fmismain quota unlimited on par03;

--3, create a partitioned table in three tablespaces (rang partitions):

CREATE TABLE P_table_par

(

GID number NOT NULL,

IID Number (10),

Flid Number (10),

Pzxmname VARCHAR2 (20),

DYLX Number (10),

DYXM VARCHAR2 (100),

AMENDBZ number (a) default 0

)

Partition by range (GID)

(

Partition par_01 values less than (50000) tablespace Par01,

Partition par_02 values less than (100000) tablespace Par02,

Partition par_03 values less than (MaxValue) tablespace par03

);

---or directly create TABLE partitions with data

CREATE TABLE P_table_par (GID,IID,FLID,PZXMNAME,DYLX,DYXM,AMENDBZ)

Partition by range (GID)

(

Partition par_01 values less than (50000) tablespace Par01,

Partition par_02 values less than (100000) tablespace Par02,

Partition par_03 values less than (MaxValue) tablespace par03

)

As

Select GID,IID,FLID,PZXMNAME,DYLX,DYXM,AMENDBZ from p_table_2;

----Create a local index rang partition

Create INDEX Idx_local_p_gid on P_table_par (GID) local;

or custom

But the name of the partition, and the table space where the partition is located, can be customized, for example:

Sql> CREATE index idx_part_range_id on T_partition_range (ID) Local (

2 partition I_RANGE_P1 tablespace tbspart01,

3 partition I_RANGE_P2 tablespace tbspart01,

4 partition I_RANGE_P3 tablespace tbspart02,

5 Partition I_range_pmax Tablespace tbspart02

6);

----Create a global index rang partition

Create INDEX Idx_p_global_gid on P_table_par (GID)

Global partition by Range (GID) (

Partition i_range_par_01 values less than (50000) tablespace Par01,

Partition i_range_par_02 values less than (100000) tablespace Par02,

Partition i_range_par_03 values less than (MaxValue) tablespace par03

);

--4, inquiry

Select Table_name,partitioning_type,partition_count from User_part_tables;

Select Partition_name,high_value,tablespace_name from User_tab_partitions order by partition_position;

Select Index_name, Partitioning_type, Partition_count from user_part_indexes

User_part_tables: Records the table of the partition information;

User_tab_partitions: Information about the partition of the record table

User_part_indexes: Querying user index information.

--5, hash partition

CREATE TABLE P_table_par (GID,IID,FLID,PZXMNAME,DYLX,DYXM,AMENDBZ)

Partition by hash (GID)

(

Partition par_01 tablespace Par01,

Partition par_02 tablespace Par02,

Partition par_03 tablespace par03

)

As

Select GID,IID,FLID,PZXMNAME,DYLX,DYXM,AMENDBZ from p_table_2;

or use the same statement below to achieve the same effect

CREATE TABLE P_table_par (GID,IID,FLID,PZXMNAME,DYLX,DYXM,AMENDBZ)

Partition by hash (GID)

Partitions 3 store in (PAR01,PAR02,PAR03)

As

Select GID,IID,FLID,PZXMNAME,DYLX,DYXM,AMENDBZ from p_table_2;

--Create a global index hash partition

Create INDEX Idx_part_hash_gid on P_table_par (GID)

Global partition by hash (GID)

Partitions 3 store in (PAR01,PAR02,PAR03);

----Create a local index rang partition

Same as Range

--5, List partition

CREATE TABLE P_table_par (GID,IID,FLID,PZXMNAME,DYLX,DYXM,AMENDBZ)

Partition by list (GID)

(

Partition par_01 values (1,2) tablespace Par01,

Partition par_02 values (3,4) tablespace Par02,

Partition par_03 values (default) tablespace par03

)

As

Select GID,IID,FLID,PZXMNAME,DYLX,DYXM,AMENDBZ from p_table_2;

--6, creating Range-hash combined partitions

--Create 3 hash sub partitions for all partitions

CREATE TABLE P_table_par (GID,IID,FLID,PZXMNAME,DYLX,DYXM,AMENDBZ)

Partition by Range (GID) subpartition by hash (pzxmname)

Subpartitions 3 store in (PAR01,PAR02,PAR03)

(

Partition par_01 values less than (50000) tablespace Par01,

Partition par_02 values less than (100000) tablespace Par02,

Partition par_03 values less than (MaxValue) tablespace par03

)

As

Select GID,IID,FLID,PZXMNAME,DYLX,DYXM,AMENDBZ from p_table_2;

---query:

A dictionary table of query sub partitions learned here: user_tab_subpartitions

Select Partitioning_type,subpartitioning_type,partition_count,def_subpartition_count from User_part_tables where Table_name= ' P_table_par ';

The results are as follows:

Partitioning_type Subpartitioning_type Partition_count Def_subpartition_count

1 RANGE HASH 3 3

Select Partition_name,subpartition_count,high_value from user_tab_partitions where table_name= ' P_table_par ';

The results are as follows:

Partition_name Subpartition_count High_value

1 par_01 3 50000

2 par_02 3 100000

3 PAR_03 3 MAXVALUE

Select Partition_name,subpartition_name,tablespace_name from user_tab_subpartitions where table_name= ' P_TABLE_PAR ';

This column more highlights: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/