在oracle 10g世界里面，分区表主要分range,hash,list,range-hash,range-list五种类型，在oracle 11g中，则发展到了3*3的分区组合类型，以满足更多的应用场景！但无论在什么情况下，范围分区都是最常见的一种表分区方式，尤其在需要对过期的数据进行整理归档，只保留一定时期内的数据的条件下，几乎都会优先选择使用范围分区的方式！分区表可以说是一项百利而无一害的技术，当数据量达到一定的级别后(通常是超过100G后)，就算使用了ASM技术，数据库中一样会产生严重的I/O等待事件！
下面来简要介绍下范围分区，范围分区的主要优点主要如下：
1：分区表可以将表存储在多个表空间内，进而离散I/O；
2：同时各个分区维护各自的本地索引(一般使用local索引，而不是global索引)；
3：select语句可以根据索引进行分区范围扫描，减少查询语句所带来的一致性读；
4：可以对单个分区进行备份或者truncate，归档或者清除过期的数据；
5: 可以方便的对表的分区进行添加，删除，truncate，拆分和合并操作
一：创建一张分区表，分区的条件是以销售日期来界定，同时分区的索引为本地索引，每个分区的对应一个单独的表空间，基于离散I/O和方便管理的双重需要
SQL> create table sale_data
2 (sale_id number(5), salesman_name varchar2(30),sales_date date)
3 partition by range(sales_date)
4 (
5 partition sales_01 values less than (to_date('01/02/2012','DD/MM/YYYY')) tablespace tbs_sale01,
6 partition sales_02 values less than (to_date('01/03/2012','DD/MM/YYYY')) tablespace tbs_sale02,
7 partition sales_03 values less than (to_date('01/04/2012','DD/MM/YYYY')) tablespace tbs_sale03,
8 partition sales_04 values less than (to_date('01/05/2012','DD/MM/YYYY')) tablespace tbs_sale04,
9 partition sales_05 values less than (to_date('01/06/2012','DD/MM/YYYY')) tablespace tbs_sale05,
10 partition sales_06 values less than (to_date('01/07/2012','DD/MM/YYYY')) tablespace tbs_sale06,
11 partition sales_07 values less than (to_date('01/08/2012','DD/MM/YYYY')) tablespace tbs_sale07,
12 partition sales_08 values less than (to_date('01/09/2012','DD/MM/YYYY')) tablespace tbs_sale08,
13 partition sales_09 values less than (to_date('01/10/2012','DD/MM/YYYY')) tablespace tbs_sale09,
14 partition sales_10 values less than (to_date('01/11/2012','DD/MM/YYYY')) tablespace tbs_sale10,
15 partition sales_11 values less than (to_date('01/12/2012','DD/MM/YYYY')) tablespace tbs_sale11,
16* partition sales_12 values less than (to_date('31/12/2012','DD/MM/YYYY')) tablespace tbs_sale12)
Table created.
SQL> select owner,partitioning_type,partition_count,status from dba_part_tables where table_name='SALE_DATE';
OWNER PARTITI PARTITION_COUNT STATUS
------------------------------ ------- --------------- --------
SALE RANGE 12 VALID
SQL> create index ind_sale_data_date on sale_data(sale_id) local
2 (
3 partition sales_01 tablespace tbs_sale01,
4 partition sales_02 tablespace tbs_sale02,
5 partition sales_03 tablespace tbs_sale03,
6 partition sales_04 tablespace tbs_sale04,
7 partition sales_05 tablespace tbs_sale05,
8 partition sales_06 tablespace tbs_sale06,
9 partition sales_07 tablespace tbs_sale07,
10 partition sales_08 tablespace tbs_sale08,
11 partition sales_09 tablespace tbs_sale09,
12 partition sales_10 tablespace tbs_sale10,
13 partition sales_11 tablespace tbs_sale11,
14* partition sales_12 tablespace tbs_sale12)
Index created.
SQL> select segment_name,partition_name,tablespace_name from user_segments where segment_name in ('SALE_DATA','IND_SALE_DATA_DATE');
SEGMENT_NAME PARTITION_NAME TABLESPACE_NAME
-------------------- ------------------------------ --------------------
SALE_DATA SALES_01 TBS_SALE01
SALE_DATA SALES_02 TBS_SALE02
SALE_DATA SALES_03 TBS_SALE03
SALE_DATA SALES_04 TBS_SALE04
SALE_DATA SALES_05 TBS_SALE05
SALE_DATA SALES_06 TBS_SALE06
SALE_DATA SALES_07 TBS_SALE07
SALE_DATA SALES_08 TBS_SALE08
SALE_DATA SALES_09 TBS_SALE09
SALE_DATA SALES_10 TBS_SALE10
SALE_DATA SALES_11 TBS_SALE11
SEGMENT_NAME PARTITION_NAME TABLESPACE_NAME
-------------------- ------------------------------ --------------------
SALE_DATA SALES_12 TBS_SALE12
IND_SALE_DATA_DATE SALES_01 TBS_SALE01
IND_SALE_DATA_DATE SALES_02 TBS_SALE02
IND_SALE_DATA_DATE SALES_03 TBS_SALE03
IND_SALE_DATA_DATE SALES_04 TBS_SALE04
IND_SALE_DATA_DATE SALES_05 TBS_SALE05
IND_SALE_DATA_DATE SALES_06 TBS_SALE06
IND_SALE_DATA_DATE SALES_07 TBS_SALE07
IND_SALE_DATA_DATE SALES_08 TBS_SALE08
IND_SALE_DATA_DATE SALES_09 TBS_SALE09
IND_SALE_DATA_DATE SALES_10 TBS_SALE10
SEGMENT_NAME PARTITION_NAME TABLESPACE_NAME
-------------------- ------------------------------ --------------------
IND_SALE_DATA_DATE SALES_11 TBS_SALE11
IND_SALE_DATA_DATE SALES_12 TBS_SALE12
二：插入测试数据，收集优化器的统计信息(包括索引)，当前的系统时间为2012年3月12日(传说中的植树节哦！)，因而数据应当在sales_03和sales_05两个分区
SQL> begin
2 for i in 1..10000
3 loop
4 insert into sale_data values (i,'yang',sysdate);
5 commit;
6 end loop;
7* end;
PL/SQL procedure successfully completed.
SQL> begin
2 for i in 1..10000
3 loop
4 insert into sale_data values (i,'yang',sysdate+60);
5 commit;
6 end loop;
7* end;
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats('SALE','SALE_DATA',CASCADE => TRUE);
PL/SQL procedure successfully completed.
三：以sys用户运行，将plustrace权限赋予用户sale
SQL> @?/sqlplus/admin/plustrce.sql;
SQL>
SQL> drop role plustrace;
drop role plustrace
*
ERROR at line 1:
ORA-01919: role 'PLUSTRACE' does not exist
SQL> create role plustrace;
Role created.
SQL> grant select on v_$sesstat to plustrace;
Grant succeeded.
SQL> grant select on v_$statname to plustrace;
Grant succeeded.
SQL> grant select on v_$mystat to plustrace;
Grant succeeded.
SQL> grant plustrace to dba with admin option;
Grant succeeded.
SQL> set echo off
SQL> grant plustrace to sale;
Grant succeeded.
四：查看在分区表上的查询语句的执行计划
SQL> set autot trace exp stat
SQL> select count(*) from sale_data;
Execution Plan
----------------------------------------------------------
Plan hash value: 268098023
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 (0)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | | | | |
| 2 | PARTITION RANGE ALL| | 20000 | 17 (0)| 00:00:01 | 1| 12 |
| 3 | TABLE ACCESS FULL | SALE_DATA | 20000 | 17 (0)| 00:00:01 | | 12 |
--------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
106 consistent gets
0 physical reads
0 redo size
515 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
可以看到，统计整个分区表的行数的时候，先走了全表扫描，而后是全部的分区范围扫描，总共有106个一致性读！
SQL> select count(*) from sale_data partition(sales_03);
Execution Plan
----------------------------------------------------------
Plan hash value: 2733649240
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 (0)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | | | | |
| 2 | PARTITION RANGE SINGLE| | 10000 | 9 (0)| 00:00:01 | 3 | 3 |
| 3 | TABLE ACCESS FULL | SALE_DATA | 10000 | 9 (0)| 00:00:01 |3 | 3 |
--------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
38 consistent gets
0 physical reads
0 redo size
515 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
可以看到，统计整个分区表的行数的时候，先走了全表扫描，而后是单个的分区范围扫描，总共有38个一致性读！
SQL> set autot trace exp stat
SQL> select * from sale_data partition(sales_03) where sale_id=100;
SALE_ID SALESMAN_NAME SALES_DATE
---------- ------------------------------ -------------------
100 yang 2012-03-12:20:04:31
Execution Plan
----------------------------------------------------------
Plan hash value: 4229050284
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes| Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 16| 2 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE | | 1 | 16| 2 (0)| 00:00:01 | 3 | 3 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| SALE_DATA | 1 | 162| 2 (0)| 00:00:01 | 3 | 3 |
|* 3 | INDEX RANGE SCAN | IND_SALE_DATA_DATE | 1 |
| 1 (0)| 00:00:01 | 3 | 3 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("SALE_ID"=100)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
666 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
可以看到，当进行等值查询的时候，先走了范围索引扫描，而后是通过本地索引定位rowid，然后访问单个分区，总共有4个一致性读！
五：对单个分区的truncate操作
SQL> select count(*) from sale_data partition(sales_03);
COUNT(*)
----------
10000
SQL> select count(*) from sale_data partition(sales_05);
COUNT(*)
----------
10000
SQL> select * from sale_data partition(sales_03) where rownum=1;
SALE_ID SALESMAN_NAME SALES_DATE
---------- ------------------------------ -------------------
1 yang 2012-03-12:20:04:31
SQL> select * from sale_data partition(sales_05) where rownum=1;
SALE_ID SALESMAN_NAME SALES_DATE
---------- ------------------------------ -------------------
1 yang 2012-05-11:20:08:45
SQL> alter table sale_data truncate partition sales_03;
Table truncated.
SQL> select count(*) from sale_data partition(sales_03);
COUNT(*)
----------
0
六：添加分区(非默认)以及默认分区(对应范围分区的最大值)，本地索引会自动创建，删除分区的时候，索引也会自动维护！
SQL> alter table sale_data add partition sales_13 values less than (to_date('01/02/2013','DD/MM/YYYY')) tablespace tbs_sale12;
Table altered.
SQL> alter table sale_data add partition sales_14 values less than (maxvalue) tablespace tbs_sale12;
Table altered.
SQL> select segment_name,partition_name,tablespace_name from user_segments where segment_name='IND_SALE_DATA_DATE' and partition_name in ('SALES_13','SALES_14');
SEGMENT_NAME PARTITION_NAME TABLESPACE_NAME
-------------------- -------------------- ------------------------------
IND_SALE_DATA_DATE SALES_13 TBS_SALE12
IND_SALE_DATA_DATE SALES_14 TBS_SALE12
SQL> alter table sale_data drop partition sales_13;
Table altered.
SQL> alter table sale_data drop partition sales_14;
Table altered.
SQL> select segment_name,partition_name,tablespace_name from user_segments where segment_name='IND_SALE_DATA_DATE' and partition_name in ('SALES_13','SALES_14');
no rows selected.
七：下面来讨论下表分区的拆分与合并，创建一张结构简单的分区表来说明，根据用户id进行分区；
SQL> create table emp (id number,first_name char(10))
2 partition by range(id)
3 (
4 partition p_1 values less than (10000) tablespace tbs_sale01,
5 partition p_2 values less than (20000) tablespace tbs_sale02,
6* partition p_3 values less than (30000) tablespace tbs_sale03)
Table created.
SQL> begin
2 for i in 1..20000
3 loop
4 insert into emp values (i,'t_i');
5 commit;
6 end loop;
7* end;
PL/SQL procedure successfully completed.
1:首先，当创建分区表的时候未指定maxvalue值所在的分区的情况下，插入分区以外的值，将会报ORA-14400错误
SQL> insert into emp values (30001,'t');
insert into emp values (30001,'t')
*
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition
SQL> alter table emp add partition p_max values less than (maxvalue) tablespace tbs_sale04;
Table altered.
SQL> insert into emp values (30001,'t');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from emp partition(p_max);
ID FIRST_NAME
---------- ----------
30001 t
2：将p_1分区进行拆分操作
SQL> select partition_name,tablespace_name from user_segments where segment_name='EMP';
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
P_1 TBS_SALE01
P_2 TBS_SALE02
P_3 TBS_SALE03
P_MAX TBS_SALE04
SQL> alter table emp split partition p_1 at('9999') into (partition p_1_01 tablespace tbs_sale02,partition p_1_02 tablespace tbs_sale03);
Table altered.
SQL> select partition_name,tablespace_name from user_segments where segment_name='EMP';
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
P_2 TBS_SALE02
P_3 TBS_SALE03
P_MAX TBS_SALE04
P_1_01 TBS_SALE02
P_1_02 TBS_SALE03
SQL> select count(*) from emp partition(p_1_01);
COUNT(*)
----------
9998
SQL> select count(*) from emp partition(p_1_02);
COUNT(*)
----------
1
由此可见，at关键字指的是在这个点进行拆分，且包含这个点！
3：将p_1_01和p_1_02分区进行合并操作，若不指定表空间，将使用用户的默认表空间
SQL> alter table emp merge partitions p_1_01,p_1_02 into partition p_01;
Table altered.
SQL> select partition_name,tablespace_name from user_segments where segment_name='EMP';
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
P_01 USERS
P_2 TBS_SALE02
P_3 TBS_SALE03
P_MAX TBS_SALE04
八：针对单个分区的导出和导入，使用expdp和impdp实现
1：查看表各个分区的情况，创建目录对象并授权，导出分区P_1
SQL> select PARTITION_NAME ,TABLESPACE_NAME from user_segments where segment_name='EMP';
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
P_1 TBS_SALE01
P_2 TBS_SALE02
P_3 TBS_SALE03
P_MAX TBS_SALE04
SQL> select count(*) from emp partition (p_1);
COUNT(*)
----------
9999
SQL> select count(*) from emp partition (p_2);
COUNT(*)
----------
10000
SQL> select count(*) from emp partition (p_3);
COUNT(*)
----------
1
SQL> select count(*) from emp partition (p_max);
COUNT(*)
----------
1
SQL> conn /as sysdba
Connected.
SQL> create directory dir01 as '/home/oracle/dir01';
Directory created.
SQL> grant read,write on directory dir01 to sale;
Grant succeeded.
[oracle@rhel6 ~]$ expdp help=y
Example: expdp scott/tiger DUMPFILE=scott.dmp DIRECTORY=dmpdir SCHEMAS=scott
or TABLES=(T1:P1,T1:P2), if T1 is partitioned table
[oracle@rhel6 ~]$ expdp sale/123456 directory=dir01 dumpfile=emp_p1.dmp logfile=emp_p1.log tables=emp:p_1
Export: Release 10.2.0.1.0 - 64bit Production on Tuesday, 13 March, 2012 21:06:13
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Starting "SALE"."SYS_EXPORT_TABLE_01": sale/******** directory=dir01 dumpfile=emp_p1.dmp logfile=emp_p1.log tables=emp:p_1
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 256 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SALE"."EMP":"P_1" 190.5 KB 9999 rows
Master table "SALE"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SALE.SYS_EXPORT_TABLE_01 is:
/home/oracle/dir01/emp_p1.dmp
Job "SALE"."SYS_EXPORT_TABLE_01" successfully completed at 21:06:17
2.导出完成后，截断P_1分区
[oracle@rhel6 ~]$ sqlplus sale/123456
SQL*Plus: Release 10.2.0.1.0 - Production on Tue Mar 13 21:07:18 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> alter table emp truncate partition p_1;
Table truncated.
SQL> select count(*) from emp partition (p_1);
COUNT(*)
----------
0
3：导入分区P_1的备份，需要设置table_exists_action参数为append，否则将导入失败
[oracle@rhel6 ~]$ impdp sale/123456 directory=dir01 dumpfile=emp_p1.dmp logfile=emp_p1.log2 table_exists_action=append tables=emp:p_1
Import: Release 10.2.0.1.0 - 64bit Production on Tuesday, 13 March, 2012 21:29:58
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Master table "SALE"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SALE"."SYS_IMPORT_TABLE_01": sale/******** directory=dir01 dumpfile=emp_p1.dmp logfile=emp_p1.log2 table_exists_action=append tables=emp:p_1
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39152: Table "SALE"."EMP" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SALE"."EMP":"P_1" 190.5 KB 9999 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SALE"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 21:30:01
SQL> conn sale/123456
Connected.
SQL> select count(*) from emp partition (p_1);
COUNT(*)
----------
9999
九：分区交换，这是一项非常实用的技术，即可以将字段完全相同的分区表分区和普通表进行存储空间的交换，从而达到数据交换的目的。就类似于将两个用户的名字对调，而原本用户用户的对象都没有改变。并且，当仅有local分区索引，且和普通表的索引相对应时，还可以将索引一起作交换。exchange partition的语法如下：
ALTER TABLE table1
EXCHANGE PARTITION partition
WITH TABLE table2
[{ INCLUDING | EXCLUDING } INDEXES]
[{ WITH | WITHOUT } VALIDATION]
[EXCEPTIONS INTO [schema.]table]
[UPDATE/NVALIDATE GLOBAL INDEXES
[NOPARALLEL/PARALLEL[int]]]
SQL> create table emp_exchange as select * from emp where 1=0;
Table created.
SQL> select count(*) from emp_exchange;
COUNT(*)
----------
0
SQL> select count(*) from emp partition(p_1);
COUNT(*)
----------
9999
SQL> alter table emp exchange partition p_1 with table emp_exchange;
Table altered.
SQL> select count(*) from emp partition(p_1);
COUNT(*)
----------
0
SQL> select count(*) from emp_exchange;
COUNT(*)
----------
9999
SQL> select tablespace_name from user_segments where segment_name='EMP_EXCHANGE';
TABLESPACE_NAME
------------------------------
TBS_SALE01
SQL> select tablespace_name from user_segments where segment_name='EMP';
TABLESPACE_NAME
------------------------------
USERS
TBS_SALE02
TBS_SALE03
TBS_SALE04
©著作权归作者所有：来自51CTO博客作者ylw6006的原创作品，谢绝转载，否则将追究法律责任
oracle分区表partitionSQL/PER Tuning
共同学习，写下你的评论
评论加载中...
作者其他优质文章
