分区(partitioning)概述

--分区概述/* 分区使用了一种分而治之的方法，使用管理非常大的表和索引。分区引用了一种分区键（partition key）的概念，数据会根据其他分区键值分到对应的分区。划分分区的方法可以是基于键值的某个范围、或者是某个键值的一个列表，也可是分区键值的某个散列函数值。 */ /* 分区优点 */1.提高可用性可用性的提高来源于每个分区的独立性。如果表是分区的，那么查询优化器会意识到这点，并且在执行计划中去除不需要的分区。比如说一个大对象中的一个分区不可用，而你的查询又需要这个分区，那么Oracle还是能成功处理这个查询。--实验演示EODA@PROD1> set echo onEODA@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 MILLER8 rows selected.EODA@PROD1> select * from emp partition(part_2);     EMPNO ENAME---------- --------------------      7521 WARD      7566 JONES      7788 SCOTT      7844 TURNER      7900 JAMES      7902 FORD6 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 timeORA-01110: data file 12: '/u01/app/oracle/oradata/PROD1/PROD1/datafile/o1_mf_p1_d24hxlxk_.dbf'EODA@PROD1> variable n numberEODA@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    303BIG1       1200     47EODA@PROD1> EODA@PROD1> -- should throw an error, not enough spaceEODA@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 BIG1EODA@PROD1> -- should throw an error, have to move each partitionEODA@PROD1> alter table big_table2 move;alter table big_table2 move*ERROR at line 1:ORA-14511：cannot perform operation on a partitioned objectEODA@PROD1> EODA@PROD1> -- should succeedEODA@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编程艺术深入理解数据库体系结构（第三版）》