Example of creating an Oracle10g11g Partition Table

In Oracle9iR2 and later versions, you can partition tables by range, then partition them by list or hash in each interval partition. Starting from 11gR1, there have been two combined machines

In Oracle 9iR2 and later versions, you can partition tables by range, then partition them by list or hash in each interval partition. Starting from 11gR1, there have been two combined machines

Hello everyone!

Today, we have compiled examples of how to create 10g 11g all partition tables. Here we will share with you:

Partitions supported by 1.1 9i, 10g, 11gR1, and 11gR2

Interval list hash

Interval 11gR1 9iR2 9iR2

List 11gR1 11gR1 11gR1

Hash 11gR2 11gR2 11gR2

Note: in Oracle 9iR2 and later versions, you can partition tables by range and then partition them by list or hash in each interval partition. Since 11gR1, it has grown from two combination mechanisms to six. After 11gR2, Nine combinations are available.

1.2. Example of creating a single Partition

1.2.1. Create a range partition table and a global index

SQL> create table aning_range

2 (aning_id number,

3 aning_name varchar2 (100 ),

4 aning_date date

5)

6 partition by range (aning_date)

7 (

8 partition aning_p1_2010 values less

9 (to_date ('1970-01-01 00:00:00 ', 'yyyy-mm-dd hh24: mi: ss') tablespace aningtbs1,

10 partition aning_p2_2011 values less

11 (to_date ('1970-01-01 00:00:00 ', 'yyyy-mm-dd hh24: mi: ss') tablespace aningtbs2,

12 partition aning_max values less than (maxvalue)

13 );

Table created.

Create a range Partition Table for enable row movement

SQL> create table aning_range_en_rowmove

2 (aning_id number,

3 aning_name varchar2 (100 ),

4 aning_date date

5)

6 storage (initialization 100 k next 50 k) logging

7 partition by range (aning_date)

8 (partition aning_p1_2010 values less

9 (to_date ('1970-01-01 ', 'yyyy-mm-dd') tablespace aningtbs1 storage (initial 20 k next 10 k ),

10 partition aning_p2_2011 values less

11 (to_date ('1970-01-01 ', 'yyyy-mm-dd') tablespace aningtbs2,

12 partition aning_max values less than (maxvalue)

13)

14 enable row movement;

Table created.

Create a global index for a range Partition Table

SQL> create index idx_aning_range on aning_range (aning_date) global;

Index created.

SQL> create index idx_aning_range_1 on aning_range (aning_id) global;

Index created.

When creating an index, adding global to the index is not a global partition index.

Create a global partition Index

SQL> create index idx_aning_range_g on aning_range (aning_date, aning_id)

2 global partition by range (aning_date)

3 (partition aning_p1_2010 values less

4 (to_date ('1970-01-01 ', 'yyyy-mm-dd') tablespace aningtbs1,

5 partition aning_p2_2011 values less

6 (to_date ('1970-01-01 ', 'yyyy-mm-dd') tablespace aningtbs2,

7 partition aning_max values less than (maxvalue)

8 );

Index created.

Test Oracle does not support non-Prefix global partition Indexes

SQL> create index idx_aning_range_g on aning_range (aning_id)

2 global partition by range (aning_date)

3 (partition aning_p1_2010 values less

4 (to_date ('1970-01-01 ', 'yyyy-mm-dd') tablespace aningtbs1,

5 partition aning_p2_2011 values less

6 (to_date ('1970-01-01 ', 'yyyy-mm-dd') tablespace aningtbs2,

7 partition aning_max values less than (maxvalue)

8 );

Global partition by range (aning_date)

*

ERROR at line 2:

ORA-14038: GLOBAL partitioned index must be prefixed

The prefix here refers to the index partition key when the partition index is created.