分区(partitioning)概述

--分区概述

/* 
分区使用了一种分而治之的方法，使用管理非常大的表和索引。
分区引用了一种分区键（partition key）的概念，数据会根据其他分区键值分到对应的分区。
划分分区的方法可以是基于键值的某个范围、或者是某个键值的一个列表，也可是分区键值的某个散列函数值。
 */
 
/* 分区优点 */
1.提高可用性
可用性的提高来源于每个分区的独立性。
如果表是分区的，那么查询优化器会意识到这点，并且在执行计划中去除不需要的分区。
比如说一个大对象中的一个分区不可用，而你的查询又需要这个分区，那么Oracle还是能成功处理这个查询。

--实验演示
EODA@PROD1> set echo on
EODA@PROD1> alter system set db_create_file_dest='/u01/app/oracle/oradata/PROD1/';

System altered.

EODA@PROD1> create tablespace p1 datafile size 1m autoextend on next 1m;

Tablespace created.

EODA@PROD1> create tablespace p2 datafile size 1m autoextend on next 1m;

Tablespace created.

EODA@PROD1> CREATE TABLE emp(empno int, ename varchar2(20)) partition by hash(empno) (partition part_1 tablespace p1, partition part_2 tablespace p2);

Table created.        --创建一个散列分区表

EODA@PROD1> insert into emp select empno, ename from scott.emp;  --插入数据

14 rows created.

EODA@PROD1> select * from emp partition(part_1);  --数据在分区内随机摆放

     EMPNO ENAME
---------- --------------------
      7369 SMITH
      7499 ALLEN
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7839 KING
      7876 ADAMS
      7934 MILLER

8 rows selected.

EODA@PROD1> select * from emp partition(part_2);

     EMPNO ENAME
---------- --------------------
      7521 WARD
      7566 JONES
      7788 SCOTT
      7844 TURNER
      7900 JAMES
      7902 FORD

6 rows selected.

EODA@PROD1> alter tablespace p1 offline;  --模拟一个表空间故障

Tablespace altered.

EODA@PROD1> select * from emp;  --不能查询
select * from emp
              *
ERROR at line 1:
ORA-00376: file 12 cannot be read at this time
ORA-01110: data file 12: '/u01/app/oracle/oradata/PROD1/PROD1/datafile/o1_mf_p1_d24hxlxk_.dbf'


EODA@PROD1> variable n number
EODA@PROD1> exec :n := 7844;

PL/SQL procedure successfully completed.

EODA@PROD1> select * from emp where empno = :n;  --查询到未损坏表空间的数据

     EMPNO ENAME
---------- --------------------
      7844 TURNER
	  
总结：分区机制从两个方面提高了可用性：(1)优化器能跳过对某些分区的访问，这代表很多用户可能意识不到某些数据是不可用的。
									  (2)出现错误时的停机时间会减少，因为恢复所需要的工作量大幅减少。


2.减少管理负担			
分区机制之所以能够减少管理负担，是因为在与一个大对象上执行操作相比，在小对象上执行同样的操作更为容易速度更快。

--实验演示
EODA@PROD1> create tablespace big1 datafile size 1200m;

Tablespace created.

EODA@PROD1> create tablespace big2 datafile size 1200m;

Tablespace created.

EODA@PROD1> 
EODA@PROD1> create table big_table1   --创建未分区的大表1
  2    ( ID, OWNER, OBJECT_NAME, SUBOBJECT_NAME,
  3  	 OBJECT_ID, DATA_OBJECT_ID,
  4  	 OBJECT_TYPE, CREATED, LAST_DDL_TIME,
  5  	 TIMESTAMP, STATUS, TEMPORARY,
  6  	 GENERATED, SECONDARY )
  7    tablespace big1
  8    as
  9    select ID, OWNER, OBJECT_NAME, SUBOBJECT_NAME,
 10  	      OBJECT_ID, DATA_OBJECT_ID,
 11  	      OBJECT_TYPE, CREATED, LAST_DDL_TIME,
 12  	      TIMESTAMP, STATUS, TEMPORARY,
 13  	      GENERATED, SECONDARY
 14  from big_table;

Table created.

EODA@PROD1> 
EODA@PROD1> create table big_table2   --创建分区的大表2
  2    ( ID, OWNER, OBJECT_NAME, SUBOBJECT_NAME,
  3  	 OBJECT_ID, DATA_OBJECT_ID,
  4  	 OBJECT_TYPE, CREATED, LAST_DDL_TIME,
  5  	 TIMESTAMP, STATUS, TEMPORARY,
  6  	 GENERATED, SECONDARY )
  7    partition by hash(id)
  8    (partition part_1 tablespace big2,
  9  	partition part_2 tablespace big2,
 10  	partition part_3 tablespace big2,
 11  	partition part_4 tablespace big2,
 12  	partition part_5 tablespace big2,
 13  	partition part_6 tablespace big2,
 14  	partition part_7 tablespace big2,
 15  	partition part_8 tablespace big2
 16    )
 17    as
 18    select ID, OWNER, OBJECT_NAME, SUBOBJECT_NAME,
 19  	      OBJECT_ID, DATA_OBJECT_ID,
 20  	      OBJECT_TYPE, CREATED, LAST_DDL_TIME,
 21  	      TIMESTAMP, STATUS, TEMPORARY,
 22  	      GENERATED, SECONDARY
 23  from big_table;

Table created.


EODA@PROD1> 
EODA@PROD1> select b.tablespace_name,
  2  	      mbytes_alloc,
  3  	      mbytes_free
  4  	 from ( select round(sum(bytes)/1024/1024) mbytes_free,
  5  		       tablespace_name
  6  		  from dba_free_space
  7  		 group by tablespace_name ) a,
  8  	      ( select round(sum(bytes)/1024/1024) mbytes_alloc,
  9  		       tablespace_name
 10  		  from dba_data_files
 11  		 group by tablespace_name ) b
 12  	where a.tablespace_name (+) = b.tablespace_name
 13  	  and b.tablespace_name in ('BIG1','BIG2')
 14  /

TABLESPACE_NAME 	       MBYTES_ALLOC MBYTES_FREE
------------------------------ ------------ -----------
BIG2				       1200	    303
BIG1				       1200	     47

EODA@PROD1> 
EODA@PROD1> -- should throw an error, not enough space
EODA@PROD1> alter table big_table1 move;  --大表1表空间无足够空间进行MOVE操作。
alter table big_table1 move
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace BIG1


EODA@PROD1> -- should throw an error, have to move each partition
EODA@PROD1> alter table big_table2 move;
alter table big_table2 move
			*
ERROR at line 1:
ORA-14511：cannot perform operation on a partitioned object


EODA@PROD1> 
EODA@PROD1> -- should succeed
EODA@PROD1> alter table big_table2 move partition part_1;  --大表2需要在分区上执行MOVE操作
Table altered.
EODA@PROD1> alter table big_table2 move partition part_2;
Table altered.
EODA@PROD1> alter table big_table2 move partition part_3;
Table altered.
EODA@PROD1> alter table big_table2 move partition part_4;
Table altered.
EODA@PROD1> alter table big_table2 move partition part_5;
Table altered.
EODA@PROD1> alter table big_table2 move partition part_6;
Table altered.
EODA@PROD1> alter table big_table2 move partition part_7;
Table altered.
EODA@PROD1> alter table big_table2 move partition part_8;
Table altered.
--原先每个移动MOVE操作，现在只需要原数据的1/8大小的空闲空间就可以完成。

3.增强语句性能
可以增强两类SQL语句的性能，一类是修改信息的语句，另一类是只读取信息的语句。

--参考来源《Oracle编程艺术深入理解 数据库 体系结构（第三版）》