Oracle Build partition Table

1. Create partitions on a monthly basis

CREATE TABLE T_MHDP

(

ID VARCHAR2 NOT NULL,

Flt_aircode VARCHAR2 (4),

Flt_number Number (7),

Flt_date DATE

) partition by range (flt_date)

(

Partition PART201105 values less than (To_date (' 2011-06-01 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS '), ' nls_calendar= Gregorian '))

Tablespace ts_mhdp_1105

Pctfree 10

Initrans 1

Maxtrans 255

Storage

(

Initial 64K

Next 1M

Minextents 1

Maxextents Unlimited

),

Partition PART201106 values less than (To_date (' 2011-07-01 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS '), ' nls_calendar= Gregorian '))

Tablespace ts_mhdp_1106

Pctfree 10

Initrans 1

Maxtrans 255

Storage

(

Initial 64K

Next 1M

Minextents 1

Maxextents Unlimited

),

Partition PART201107 values less than (To_date (' 2011-08-01 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS '), ' nls_calendar= Gregorian '))

Tablespace ts_mhdp_1107

Pctfree 10

Initrans 1

Maxtrans 255

Storage

(

Initial 64K

Next 1M

Minextents 1

Maxextents Unlimited

),

Partition Partdefault values less than (To_date (' 9999-12-30 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS '), ' nls_calendar= Gregorian '))

Tablespace TS_MHDP

Pctfree 10

Initrans 1

Maxtrans 255

Storage

(

Initial 64K

Next 1M

Minextents 1

Maxextents Unlimited

)

);

2. Create Global Index

Create INDEX INDEX_MHDP_PK on T_MHDP (ID)

Tablespace TS_INDEX_MHDP

Pctfree 10

Initrans 2

Maxtrans 255

Storage

(

Initial 64K

Next 1M

Minextents 1

Maxextents Unlimited

);

3. Create local Index

CREATE INDEX index_mhdp_pk on T_MHDP (ID) Local (

PARTITION PART201105 tablespace ts_mhdp_index_1105,

PARTITION PART201106 tablespace ts_mhdp_index_1106,

PARTITION PART201107 tablespace ts_mhdp_index_1107,

PARTITION Partdefault tablespace Ts_mhdp_index

);