oracle10g 创建分区表,Oracle 10g 11g分区表创建举例

1.3.3. 创建压缩分区表

SQL> create table aning_par_compress

2 (aning_id number,

3 aning_name varchar2(100),

4 aning_date date

5 )

6 partition by range (aning_date)

7 (partition par_compress2010

8 values less than (to_date('2011-01-01','yyyy-mm-dd')) compress,

9 partition par_compress2011

10 values less than (to_date('2012-01-01','yyyy-mm-dd')),

11 partition par_compressmax

12 values less than (maxvalue)

13 );

Table created.

1.3.4. 创建压缩分区索引

1.4.4.1.使用分区键创建本地分区索引

SQL> create index idx_aning_par_compress on aning_par_compress(aning_date) compress local

2 (partition par_compress2010,

3 partition par_compress2011,

4 partition par_compressmax nocompress

5 );

Index created.

1.4.4.2.使用非分区键创建本地分区索引

SQL> create index pk_aning_par_compress on aning_par_compress(aning_id) compress local

2 (partition pk_par_compress2010,

3 partition pk_par_compress2011,

4 partition pk_par_compressmax nocompress

5 );

Index created.

1.5.创建分区表需要表空间

create tablespace aningtbs1 datafile '/home/Oracle/oradata/lottery/aningtbs1.dbf' size 1m autoextend on maxsize unlimited extent management local segment space management auto;

create tablespace aningtbs2 datafile '/home/oracle/oradata/lottery/aningtbs2.dbf' size 1m autoextend on maxsize unlimited extent management local segment space management auto;

create tablespace aningtbs3 datafile '/home/oracle/oradata/lottery/aningtbs3.dbf' size 1m autoextend on maxsize unlimited extent management local segment space management auto;

create tablespace aningtbs4 datafile '/home/oracle/oradata/lottery/aningtbs4.dbf' size 1m autoextend on maxsize unlimited extent management local segment space management auto;

create tablespace aningtbs5 datafile '/home/oracle/oradata/lottery/aningtbs5.dbf' size 1m autoextend on maxsize unlimited extent management local segment space management auto;

create tablespace aningtbs6 datafile '/home/oracle/oradata/lottery/aningtbs6.dbf' size 1m autoextend on maxsize unlimited extent management local segment space management auto;

create tablespace aningtbs7 datafile '/home/oracle/oradata/lottery/aningtbs7.dbf' size 1m autoextend on maxsize unlimited extent management local segment space management auto;

create tablespace aningtbs8 datafile '/home/oracle/oradata/lottery/aningtbs8.dbf' size 1m autoextend on maxsize unlimited extent management local segment space management auto;