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。
