你的位置:首页 > 数据库

[数据库]oracle 分区表


  1. range

  2. list分区
  3. hash分区
  4. range与list分区
  5. 分区表的导出导入
  6. 分区表的索引 - global local

Range

--创建分区表

create table emp3(id number(4) primary key,name varchar2(20),eff_dt date) partition by range(eff_dt)

(

         partition p1 values less than (to_date('2015-07-01','yyyy-mm-dd')) ,//tablespace users,

         partition p2 values less than (to_date('2016-01-01','yyyy-mm-dd')),

         partition p3 values less than (to_date('2016-07-01','yyyy-mm-dd'))

);

 

--多列分区表 列的顺序非常重要,只有第一列相等的时候,才比较第二列的值。

create table multicol (unit number(1), subunit char(1))
partition by range (unit,subunit)
(partition P_2b values less than (2,'B')
,partition P_2c values less than (2,'C')
,partition P_3b values less than (3,'B')
,partition P_4x values less than (4,'X'));

 

 

exp -help

imp -help

--导出p1分区

exp file=emp3.dmp tables=emp3:p1

 

imp file=emp3.dmp ignore=y

 

SQL> select * from emp3;

        ID NAME                 EFF_DT

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

         1 lilei                13-FEB-15

--查看分区情况

SQL> SELECT table_name,partition_name FROM ALL_TAB_PARTITIONS WHERE TABLE_NAME='EMP3';

TABLE_NAME                     PARTITION_NAME

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

EMP3                           P1

EMP3                           P2

EMP3                           P3

 

SQL>  insert into scott.emp3 values(2,'hanmei',to_date('2015-07-11','yyyy-mm-dd'));

SQL> insert into scott.emp3 values(3,'lily',to_date('2015-12-30','yyyy-mm-dd'));

SQL>  select * from emp3;

        ID NAME                 EFF_DT

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

         1 lilei                13-FEB-15

         2 hanmei               11-JUL-15

         3 lily                 30-DEC-15

SQL> select * from emp3 partition(p1);

        ID NAME                 EFF_DT

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

         1 lilei                13-FEB-15

--删除某个分区

SQL> alter table emp3 drop partition p3;

SQL> SELECT table_name,partition_name,tablespace_name FROM ALL_TAB_PARTITIONS WHERE TABLE_NAME='EMP3';

 

TABLE_NAME        PARTITION_NAME                 TABLESPACE_NAME

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

EMP3                           P1                             USERS

EMP3                           P2                             USERS

 

SQL> conn system as sysdba

SQL> select name from v$datafile;

 

NAME

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

/u01/app/oracle/oradata/PROD/disk3/system01.dbf

/u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf

/u01/app/oracle/oradata/PROD/disk5/undotbs01.dbf

/u01/app/oracle/oradata/PROD/disk3/users01.dbf

/u01/app/oracle/oradata/PROD/disk3/cuug01.dbf

 

SQL> conn scott/tiger

Connected.

--添加分区

SQL> alter table emp3 add partition p3 values less than (to_date('2016-07-01','yyyy-mm-dd'));

SQL> SELECT table_name,partition_name,tablespace_name FROM ALL_TAB_PARTITIONS WHERE TABLE_NAME='EMP3';

TABLE_NAME       PARTITION_NAME                 TABLESPACE_NAME

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

EMP3                           P1                             USERS

EMP3                           P2                             USERS

EMP3                           P3                             USERS

 

SQL> alter table emp3 drop partition p3;

SQL> alter table emp3 add partition p3 values less than (to_date('2016-07-01','yyyy-mm-dd')) tablespace cuug;

SQL> SELECT table_name,partition_name,tablespace_name FROM ALL_TAB_PARTITIONS WHERE TABLE_NAME='EMP3';

 

TABLE_NAME            PARTITION_NAME                 TABLESPACE_NAME

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

EMP3                           P1                             USERS

EMP3                           P2                             USERS

EMP3                           P3                             CUUG

 

SQL> insert into emp3 values(4,'lucy',to_date('2016-03-03','yyyy-mm-dd'));

 

SQL> select * from emp3;

 

        ID NAME                 EFF_DT

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

         1 lilei                13-FEB-15

         2 hanmei               11-JUL-15

         3 lily                 30-DEC-15

         4 lucy                 03-MAR-16

[oracle@gc1 ~]$ exp file=emp3.dmp tables=emp3

 

Export: Release 11.2.0.1.0 - Production on Thu Dec 10 15:36:58 2015

 

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

 

 

Username: scott

Password:

 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export done in US7ASCII character set and AL16UTF16 NCHAR character set

server uses ZHS16GBK character set (possible charset conversion)

 

About to export specified tables via Conventional Path ...

. . exporting table                           EMP3

. . exporting partition                             P1          1 rows exported

. . exporting partition                             P2          2 rows exported

. . exporting partition                             P3          1 rows exported

EXP-00091: Exporting questionable statistics.

Export terminated successfully with warnings.

[oracle@gc1 ~]$ sqlplus "/as sysdba"

 

SQL> drop tablespace cuug including contents and datafiles;

drop tablespace cuug including contents and datafiles

*

ERROR at line 1:

ORA-14404: partitioned table contains partitions in a different tablespace

 

SQL> drop table scott.emp3;

 

Table dropped.

 

SQL> drop tablespace cuug including contents and datafiles;

 

Tablespace dropped.

 

SQL>

SQL>

SQL> conn scott/tiger

Connected.

SQL> select * from tab;

 

TNAME                          TABTYPE  CLUSTERID

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

BIN$JoX+/znoIsfgUB6sMhl2Kw==$0 TABLE

BONUS                          TABLE

DEPT                           TABLE

EMP                            TABLE

EMP2                           TABLE

MLOG$_EMP2                     TABLE

SALGRADE                       TABLE

 

7 rows selected.

 

SQL> quit       

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@gc1 ~]$ imp file=emp3.dmp ignore=y

 

Import: Release 11.2.0.1.0 - Production on Thu Dec 10 15:40:45 2015

 

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

 

Username: scott

Password:

 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

Export file created by EXPORT:V11.02.00 via conventional path

import done in US7ASCII character set and AL16UTF16 NCHAR character set

import server uses ZHS16GBK character set (possible charset conversion)

. importing SCOTT's objects into SCOTT

. importing SCOTT's objects into SCOTT

IMP-00017: following statement failed with ORACLE error 959:

 "CREATE TABLE "EMP3" ("ID" NUMBER(4, 0), "NAME" VARCHAR2(20), "EFF_DT" DATE)"

 "  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 TABLESPACE "USERS"  PARTITI"

 "ON BY RANGE ("EFF_DT" )  (PARTITION "P1" VALUES LESS THAN (TO_DATE(' 2015-0"

 "7-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  PCTFR"

 "EE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576"

 " MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE"

 " "USERS" LOGGING NOCOMPRESS, PARTITION "P2" VALUES LESS THAN (TO_DATE(' 201"

 "6-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))  PC"

 "TFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048"

 "576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESP"

 "ACE "USERS" LOGGING NOCOMPRESS, PARTITION "P3" VALUES LESS THAN (TO_DATE(' "

 "2016-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) "

 " PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1"

 "048576 MINEXTENTS 1 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABL"

 "ESPACE "CUUG" LOGGING NOCOMPRESS )"

IMP-00003: ORACLE error 959 encountered

ORA-00959: tablespace 'CUUG' does not exist

Import terminated successfully with warnings.

[oracle@gc1 ~]$ sqlplus "/as sysdba"

 

SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 10 15:42:15 2015

 

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> create tablespace cuug datafile '/u01/app/oracle/oradata/PROD/disk3/cuug01.dbf' size 50m;

 

Tablespace created.

 

SQL> quit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@gc1 ~]$ sqlplus scott/tiger

 

SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 10 15:43:10 2015

 

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> quit   

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@gc1 ~]$ imp file=emp3.dmp ignore=y

 

Import: Release 11.2.0.1.0 - Production on Thu Dec 10 15:43:32 2015

 

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

 

Username: scott

Password:

 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

Export file created by EXPORT:V11.02.00 via conventional path

import done in US7ASCII character set and AL16UTF16 NCHAR character set

import server uses ZHS16GBK character set (possible charset conversion)

. importing SCOTT's objects into SCOTT

. importing SCOTT's objects into SCOTT

. . importing partition                    "EMP3":"P1"          1 rows imported

. . importing partition                    "EMP3":"P2"          2 rows imported

. . importing partition                    "EMP3":"P3"          1 rows imported

Import terminated successfully without warnings.

[oracle@gc1 ~]$ sqlplus scott/tiger

 

SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 10 15:44:01 2015

 

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SQL> select * from emp3;

 

        ID NAME                 EFF_DT

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

         1 lilei                13-FEB-15

         2 hanmei               11-JUL-15

         3 lily                 30-DEC-15

         4 lucy                 03-MAR-16

 

SQL>

注:导入分区表时,如果分区所在的表空间被删除,导入时会报错。

分区表的导入导出:

http://blog.163.com/yanenshun@126/blog/static/1283881692013672149452/

 

合并分区表

Alter table emp3 merge partitions p1,p2 into partition p3;

分区表合并后,原分区表空间被释放,原数据均转移到新的表空间下。

 

分割分区表

Alter table emp3 split partition p3 at (to_date(‘2015-07-01’,’yyyy-mm-dd’)) to (partition p1,partition p2);

以2015-07-01位分界点将数据分别插入分区表中。

 

更改分区表名

Alter table emp3 rename partition p3 to p31;

交换表分区:

alter table sales_range exchange partition sales_2000 with table sales_range_temp;

 

List分区

create table test3 (id number,city varchar2(20))

partition by list(city)(

          partition p1 values ('SH','JS','ZJ'),

          partition p2 values ('BJ','TJ','HB'),

          partition p3 values ('GZ','SZ'),

          partition p_others values (default)

);

Hash分区

需要指定分区列和分区的数量,分区的数量应该是2的幂,这样可以保证的数据均匀的分布在所有的分区里。

create table test4 (id number,name varchar2(10)) partition by hash(id) partitions 4;

 

或者

create table test4(id number,name varchar2(10)) partition by hash(id)

(

       partition p1, partition p2, partition p3, partition p4

);

Range与List分区

create table test6(id varchar2(20),grade number(4),city varchar2(20))

partition by range(grade)

subpartition by list(city)

(

             partition p1 values less than(10)

             (

                       subpartition p1a values('BJ'),

                       subpartition p1b values(default)

             ),

             partition p2 values less than(20)

             (

                       subpartition p2a values('SH'),

                       subpartition p2b values(default)

             )

            

);

select * from user_tab_partitions t where t.table_name='TEST6';

SELECT * from user_tab_subpartitions u where u.table_name='TEST6' and u.partition_name='P1';

 

CREATE index id_test6 on test6(id) local;

select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from user_ind_partitions

where index_name=upper('id_test6');

 

select * from user_ind_subpartitions where index_name='ID_TEST6';

 

分区表的导入导出

exp imp方式:导入时如果不存在导出时所在的表空间会报错,可以先创建分区表再导入数据。

expdp impdp方式:导入时通过remap_tablespace对应表空间。

impdp scott/tiger directory=oracle_dir dumpfile=test.dmp remap_tablespace=cuug01:users remap_schema=scott2:scott

分区表的索引

Global,它必定是prefix的,不存在non-prefix的。

Local,分为2类:

1>     prefix:索引的第一列等于表的分区列。

2>     non-prefix:索引的第一列不等于表的分区列。

local

Local索引只能是表的分区方式(与分区表一致),不能自己写分区方式。

--创建分区表

create table test(id number,data varchar2(100)) partition by range(id)

(

       partition p1 values less than (10000),

       partition p2 values less than (20000),

       partition p3 values less than (maxvalue)

);

--id列上创建一个local索引

create index id_local on test(id) local;

select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from user_ind_partitions where index_name='ID_LOCAL';

 

如果我在表上增加个分区,则oracle自动维护分区的索引,注意此时加分区必须是用split,直接加会出错的。

alter table test add partition p4 values less than (30000);

alter table test split partition p3 at (30000) into (partition p3,partition p4); 

select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from user_ind_partitions where index_name='ID_LOCAL';

select index_name,index_type,table_name from user_indexes where index_name='ID_LOCAL';

删除索引

drop index id_local;

global

重新在ID列上创建一个GLOBAL的索引

create index id_global on test(id) global;

select INDEX_NAME,PARTITION_NAME,HIGH_VALUE,STATUS from user_ind_partitions where index_name='ID_GLOBAL';

 

select INDEX_NAME,INDEX_TYPE,TABLE_NAME from user_indexes where index_name='ID_GLOBAL';

从上面可以看出,它此时是个普通索引。dba_ind_partitions里根本就没有记录。

 

create index i_id_global on test(data) global

partition by range(id)(

       partition p1 values less than (10000),

       partition p2 values less than (maxvalue)  

);

Global索引必须是prefix,即索引分区的列,必须是其基表的分区列。

create index i_id_global on test(id) global

partition by range(id)(

       partition p1 values less than (10000),

       partition p2 values less than (maxvalue)  

);

此时可以创建。

select index_name,partition_name,high_value,status from user_ind_partitions where

index_name='I_ID_GLOBAL'。

Global和local索引都是针对分区表的,如果不加分区,就是一个普通索引。

到底如何判断建立怎样的分区索引(GLOBAL 还是LOCAL)

示例表:

create table TT(id number,createdate date)

partition by range(createdate)

(

  partition Q1 VALUES LESS THAN (TO_DATE('2012-03-30','YYYY-MM-DD')),

  partition Q2 VALUES LESS THAN (TO_DATE('2012-06-30','YYYY-MM-DD')),

  partition Q3 VALUES LESS THAN (TO_DATE('2012-09-30','YYYY-MM-DD')),

  partition Q4 VALUES LESS THAN (TO_DATE('2012-12-31','YYYY-MM-DD')),

  partition Q_OTHERS VALUES LESS THAN (MAXVALUE)

);

第一种情况:

where createdate='2012-10-19' and id>100

--查询的是4号分区,假设他有10万条记录。在扫描这10万条记录的时候,

--可以使用id列上的索引。这个时候可以在ID列上建立个local nonprofiex索引

 

第二种情况:

where createdate='2010-10-19'

这种情况在createdate上建立一个local prefix索引。

 

第二种情况:

where id>100

则就只能在ID列上建立GLOBAL索引了

一般建议建立LOCAL的索引,因为GLOBAL的容易所有的都失效,而LOCAL的最多只在某个分区上失效。索引失效必须一个分区的一个分区的REBUILD。