CREATE TABLE range_example ( range_key_column date, data varchar2(20) ) PARTITION BY RANGE (range_key_column) ( PARTITION part_2011_ls VALUES LESS THAN (to_date('01/01/2011','dd/mm/yyyy')), PARTITION part_201101 VALUES LESS THAN (to_date('01/02/2011','dd/mm/yyyy')), PARTITION part_201102 VALUES LESS THAN (to_date('01/03/2011','dd/mm/yyyy')), PARTITION part_201103 VALUES LESS THAN (to_date('01/04/2011','dd/mm/yyyy')), PARTITION part_201104 VALUES LESS THAN (to_date('01/05/2011','dd/mm/yyyy')), PARTITION part_201105 VALUES LESS THAN (to_date('01/06/2011','dd/mm/yyyy')), PARTITION part_201106 VALUES LESS THAN (to_date('01/07/2011','dd/mm/yyyy')), PARTITION part_201107 VALUES LESS THAN (to_date('01/08/2011','dd/mm/yyyy')), PARTITION part_201108 VALUES LESS THAN (to_date('01/09/2011','dd/mm/yyyy')), PARTITION part_201109 VALUES LESS THAN (to_date('01/10/2011','dd/mm/yyyy')), PARTITION part_201110 VALUES LESS THAN (to_date('01/11/2011','dd/mm/yyyy')), PARTITION part_201111 VALUES LESS THAN (to_date('01/12/2011','dd/mm/yyyy')), PARTITION part_201112 VALUES LESS THAN (to_date('01/01/2012','dd/mm/yyyy')), partition part_2012_up VALUES LESS THAN (MAXVALUE) ) /
-- 分区(partitioning)最早在Oracle 8.0中引入，这个过程是将一个表或索引物理地分解为多个更小、更可管理的部分。就访问数据库的应用而言， -- 逻辑上讲只有一个表或索引，但在物理上这个表或索引可能由数十个物理分区组成。每个分区都是一个独立的对象，可以独自处理， -- 也可以作为一个更大对象的一部分进行处理。-- 13.1 分区概述 ( P561 ) ......-- 13.1.1 提高可用性 ( P562 ) -- 可用性的提高源自于每个分区的独立性。对象中一个分区的可用性(或不可用)并不意味着对象本身是不可用的。优化器知道有这种分区机制， -- 会相应地从查询计划中去除未引用的分区。在一个大对象中如果一个分区不可用，你的查询可以消除这个分区而不予考虑，这样Oracle就能成功地处理这个查询。 create tablespace p1 datafile 'E:\Oracle_data\p1_01.dbf' size 512M; create tablespace p2 datafile 'E:\Oracle_data\p2_01.dbf' size 512M;alter user eygle quota unlimited on p1; alter user eygle quota unlimited on p2;CREATE TABLE emp ( empno int, ename varchar2(20) ) PARTITION BY HASH (empno) ( partition part_1 tablespace p1, partition part_2 tablespace p2 ) /insert into emp select empno, ename from scott.emp;eygle@SZTYORA> 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行。eygle@SZTYORA> select * from emp partition(part_2); EMPNO ENAME ---------- ---------------------------------------- 7521 WARD 7566 JONES 7788 SCOTT 7844 TURNER 7900 JAMES 7902 FORD已选择6行。alter tablespace p1 offline;eygle@SZTYORA> select * from emp; select * from emp * 第 1 行出现错误: ORA-00376: 此时无法读取文件 9 ORA-01110: 数据文件 9: 'E:\ORACLE_DATA\P1_01.DBF'variable n number exec :n := 7844; select * from emp where empno = :n;-- 总之，只要优化器能从查询计划消除分区，它就会这么做。基于这一点，如果应用在查询中使用了分区键，就能提高这些应用的可用性。-- 分区还可以通过减少停机时间来提高可用性。例如，如果有一个100GB的表，它划分为50个2GB的分区，这样就能更快地从错误中恢复。如果某个2GB的分区遭到破坏， -- 现在恢复的时间就只是恢复一个2GB分区所需要的时间，而不是恢复一个100GB表的时间。所以从两个方面提高了可用性： -- *(1) 优化器能够消除分区，这意味着许多用户可能甚至从未注意到某些数据是不可用的。 -- *(2) 出现错误时的停机时间会减少，因为恢复所需的工作量大幅减少。
-- 13.1.2 减少管理负担 ( P564 )......create tablespace big1 datafile 'D:\oracle\product\10.2.0\oradata\sztyora\big1_01.dbf' size 4096M; create tablespace big2 datafile 'D:\oracle\product\10.2.0\oradata\sztyora\big2_01.dbf' size 4096M;create table big_table1 ( ID, OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY, GENERATED, SECONDARY ) tablespace big1 as select ID, OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY, GENERATED, SECONDARY from big_table.big_table;create table big_table2 ( ID, OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY, GENERATED, SECONDARY ) partition by hash(id) ( partition part_1 tablespace big2, partition part_2 tablespace big2, partition part_3 tablespace big2, partition part_4 tablespace big2, partition part_5 tablespace big2, partition part_6 tablespace big2, partition part_7 tablespace big2, partition part_8 tablespace big2 ) as select ID, OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY, GENERATED, SECONDARY from big_table.big_table;-- 现在，每个表都在自己的表空间中，所以我们可以很容易地查询数据字典，来查看每个表空间中已分配的空间和空闲空间： select b.tablespace_name, mbytes_alloc, mbytes_free from (select round(sum(bytes)/1024/1024) mbytes_free, tablespace_name from dba_free_space group by tablespace_name ) a, (select round(sum(bytes)/1024/1024) mbytes_alloc, tablespace_name from dba_data_files group by tablespace_name ) b where a.tablespace_name (+) = b.tablespace_name and b.tablespace_name in ('BIG1','BIG2');TABLESPACE_NAME MBYTES_ALLOC MBYTES_FREE ------------------------------------------------------------ ------------ ----------- BIG2 4096 2880 BIG1 4096 2744-- BIG1和BIG2的大小都大约是4GB，每个表空间都有1200MB的空闲空间。我们想重建第一个表BIG_TABLE1： alter table big_table1 move;-- 但是失败了，BIG1表空间中要有足够的空闲空间来放下BIG_TABLE1的完整副本，同时它的原副本仍然保留，简单地说， -- 我们需要在一个很短的时间内有大约两倍的存储空间(可能多一点，也可能少一点，这取决于重建后表的大小)。现在试图对BIG_TABLE2执行同样的操作： alter table big_table2 move;-- 这说明，Oracle在告诉我们：无法对这个“表”执行MOVE操作；我们必须在表的各个分区上执行这个操作。可以逐个地移动(相应地重建和重组)各个分区： alter table big_table2 move partition part_1; alter table big_table2 move partition part_2; alter table big_table2 move partition part_3; alter table big_table2 move partition part_4; alter table big_table2 move partition part_5; alter table big_table2 move partition part_6; alter table big_table2 move partition part_7; alter table big_table2 move partition part_8;begin for x in ( select partition_name from user_tab_partitions where table_name = 'BIG_TABLE2' ) loop execute immediate 'alter table big_table2 move partition ' || x.partition_name; end loop; end; /......-- 简单地说，利用分区，原先让人畏惧的操作(有时甚至是不可行的操作)会变得像在小数据库中一样容易。-- 13.1.3 改善语句性能 ( P569 ) -- 分区最后一个总的(潜在)好处体现在改进语句(SELECT、INSERT、UPDATE、DELETE、MERGE)的性能方面。我们来看两类语句，一种是修改信息的语句， -- 另一种是只读信息的语句，并讨论在这两种情况下可以从分区得到哪些好处。-- *1) 并行DDL ( P569 ) -- 修改数据库中数据的语句有可能会执行并行DML(parallel DML, PDML)。采用PDML时，Oracle使用多个线程来执行INSERT、UPDATE或DELETE，而不是执行一个串行进程。 -- 在一个有充足I/O带宽的多CPU主机上，对于大规模的DML操作，速度的提升可能相当显著。在Oracle 9i以前的版本中，PDML要求必须分区。如果你的表没有分区， -- 在先前的版本中就不能并行地执行这些操作。如果表确实已经分区，Oracle会根据对象所有的物理分区数为对象指定一个最大并行度。从很大程度上讲， -- 在Oracle 9i及以后版本中这个限制已经放松，只有两个突出的例外：如果希望在一个表上执行PDML，而且这个表的一个LOB列上有一个位图索引， -- 要并行执行操作就必须对这个表分区；另外并行度就限制为分区数。不过，总的来说，使用PDML并不一定要求进行分区。
-- *2) 查询性能 -- 在只读查询(SELECT语句)的性能方面，分区对两类特殊操作起作用： -- *(01) 分区消除(partition elimination)：处理查询时不考虑某些数据分区。我们已经看到了一个分区消除的例子。 -- *(02) 并行操作(parallel operation)：并行全表扫描和并行索引区间扫描就是这种操作的例子。-- 不过，由此得到的好处很大程度上取决于你使用何种类型的系统。-- *(1) OLTP系统 ......-- 13.2 表分区机制 ( P571 ) -- 目前Oracle中有4种对表分区的方法： -- *(01) 区间分区：可以指定应当存储在一起的数据区间。例如，时间戳在Jan-2005内的所有记录都存储在分区1中，时间戳在Feb-2005内的所有记录都存储在分区2中， -- 依次类推。这可能是Oracle中最常用的分区机制。 -- *(02) 散列分区：我们在第一章第一个例子中就已经看到了散列分区。这是指在一个列(或多个列)上应用一个散列函数，行会按这个散列值放在某个分区中。 -- *(03) 列表分区：指定一个离散值集，来确定应当存储在一起的数据。例如，可以指定STATUS列值在('A','M','Z')中的行放在分区1中， -- STATUS值在('D','P','Q')中的行放在分区2中，依次类推。 -- *(04) 组合分区：这是区间分区和散列分区的一种组合，或者是区间分区与列表分区的组合。你可以先对某些数据应用区间分区， -- 再在区间中根据列或表来选择最后的分区。-- 13.2.1 区间分区......CREATE TABLE range_example ( range_key_column date, data varchar2(20) ) PARTITION BY RANGE (range_key_column) ( PARTITION part_1 VALUES LESS THAN (to_date('01/01/2005','dd/mm/yyyy')), PARTITION part_2 VALUES LESS THAN (to_date('01/01/2006','dd/mm/yyyy')) ) /insert into range_example ( range_key_column, data ) values ( to_date( '15-12-2004 00:00:00','dd-mm-yyyy hh24:mi:ss'),'application data...' );insert into range_example ( range_key_column, data ) values ( to_date( '01-01-2005 00:00:00','dd-mm-yyyy hh24:mi:ss')-1/24/60/60,'application data...' );insert into range_example ( range_key_column, data ) values ( to_date( '01-01-2005 00:00:00','dd-mm-yyyy hh24:mi:ss'),'application data...' );insert into range_example ( range_key_column, data ) values ( to_date( '15-12-2005 00:00:00','dd-mm-yyyy hh24:mi:ss'),'application data...' );commit;select to_char(range_key_column,'dd-mm-yyyy hh24:mi:ss') from range_example partition (part_1);select to_char(range_key_column,'dd-mm-yyyy hh24:mi:ss') from range_example partition (part_2);-- 你可能想知道，如果插入的日期超出上界会怎么样呢？答案是Oracle会产生一个错误： insert into range_example ( range_key_column, data ) values ( to_date( '15/12/2007 00:00:00','dd/mm/yyyy hh24:mi:ss' ), 'application data...' ); insert into range_example * 第 1 行出现错误: ORA-14400: 插入的分区关键字未映射到任何分区-- 假设你想像刚才一样，将2005年和2006年的日期分别聚集到各自的分区，但是另外你还希望将所有其他日期都归入第三个分区。利用区间分区， -- 这可以使用MAXVALUE子句做到这一点，如下所示： CREATE TABLE range_example ( range_key_column date, data varchar2(20) ) PARTITION BY RANGE (range_key_column) ( PARTITION part_1 VALUES LESS THAN (to_date('01/01/2005','dd/mm/yyyy')), PARTITION part_2 VALUES LESS THAN (to_date('01/01/2006','dd/mm/yyyy')), partition part_3 VALUES LESS THAN (MAXVALUE) ) /-- alter table range_example add partition part_3 values less than(maxvalue);-- 现在，向这个表插入一行时，这一行肯定会放入三个分区中的某一个分区中，而不会拒绝任何行， -- 因为分区PART_3可以接受不能放在PART_1或PART_2中的任何RANGE_KEY_COLUMN值(即使RANGE_KEY_COLUMN值为null，也会插入到这个新分区中)。insert into range_example ( range_key_column, data ) values ( null, 'application data...' );select to_char(range_key_column,'dd-mm-yyyy hh24:mi:ss') from range_example partition (part_3);
-- 13.2.2 散列分区 ( P574 ) -- 对一个表执行散列分区(hash partitioning)时，Oracle会对分区键应用一个散列函数，以此确定数据应当在N个分区中的哪一个分区中。 -- Oracle建议N是2的一个幂(2、4、8、16等)，从而得到最佳的总体分布，稍后会看到这确实是一个很好的建议。-- *1 散列分区如何工作 -- 散列分区设计为能使数据很好地分布在多个不同的设备(磁盘)上，或者只是将数据聚集到更可管理的块(chunk)上。为表选择的散列键应当是惟一的一个列或一组列， -- 或者至少有足够多的相异值，以便行能在多个分区上很好地(均匀地)分布。如果你选择一个只有4个相异值的列，并使用两个分区， -- 那么最后可能把所有行都散列到同一个分区上，这就有悖于分区的最初目标!CREATE TABLE hash_example ( hash_key_column date, data varchar2(20) ) PARTITION BY HASH (hash_key_column) ( partition part_1 tablespace p1, partition part_2 tablespace p2 ) /insert into hash_example ( hash_key_column, data ) values ( to_date('01-01-2005','dd-mm-yyyy'), 'application data...');insert into hash_example ( hash_key_column, data ) values ( to_date('02-01-2005','dd-mm-yyyy'), 'application data...');select * from hash_example partition(part_1);select * from hash_example partition(part_2);-- 前面已经提到过，如果使用散列分区，你将无从控制一行最终会放在哪个分区中。Oracle会应用散列函数，并根据散列的结果来确定行会放在哪里。 -- 如果你由于某种原因希望将某个特定行放在分区PART_1中，就不应该使用散列分区，实际上，此时也不能使用散列分区。行会按散列函数的“指示”放在某个分区中， -- 也就是说，散列函数说这一行该放到哪个分区，它就会放到哪个分区中。如果改变散列分区的个数，数据会在所有分区中重新分布 -- (向一个散列分区表增加或删除一个分区时，将导致所有数据都重写，因为现在每一行可能属于一个不同的分区)。-- *2) 散列分区数使用2的幂 -- 我在前面提到过，分区数应该是2的幂，这很容易观察。为了便于说明，我们建立了一个存储过程，它会自动创建一个有N个分区的散列分区表(N是一个参数)。 -- 这个过程会构造一个动态查询，按分区获取其中的行数，再按分区显示行数，并给出行数的一个简单直方图。最后，它会打开这个查询，以便我们看到结果。 -- 这个过程首选创建散列表。我们将使用一个名为T的表： create or replace procedure hash_proc ( P_nhash in number, p_cursor out sys_refcursor ) authid current_user as l_text long; l_template long := 'select $POS$ oc, ''p$POS$'' pname, count(*) cnt ' || ' from t partition ( $PNAME$ ) union all '; begin begin execute immediate 'drop table t'; exception when others then null; end;
( range_key_column date,
data varchar2(20)
)
PARTITION BY RANGE (range_key_column)
( PARTITION part_2011_ls VALUES LESS THAN (to_date('01/01/2011','dd/mm/yyyy')),
PARTITION part_201101 VALUES LESS THAN (to_date('01/02/2011','dd/mm/yyyy')),
PARTITION part_201102 VALUES LESS THAN (to_date('01/03/2011','dd/mm/yyyy')),
PARTITION part_201103 VALUES LESS THAN (to_date('01/04/2011','dd/mm/yyyy')),
PARTITION part_201104 VALUES LESS THAN (to_date('01/05/2011','dd/mm/yyyy')),
PARTITION part_201105 VALUES LESS THAN (to_date('01/06/2011','dd/mm/yyyy')),
PARTITION part_201106 VALUES LESS THAN (to_date('01/07/2011','dd/mm/yyyy')),
PARTITION part_201107 VALUES LESS THAN (to_date('01/08/2011','dd/mm/yyyy')),
PARTITION part_201108 VALUES LESS THAN (to_date('01/09/2011','dd/mm/yyyy')),
PARTITION part_201109 VALUES LESS THAN (to_date('01/10/2011','dd/mm/yyyy')),
PARTITION part_201110 VALUES LESS THAN (to_date('01/11/2011','dd/mm/yyyy')),
PARTITION part_201111 VALUES LESS THAN (to_date('01/12/2011','dd/mm/yyyy')),
PARTITION part_201112 VALUES LESS THAN (to_date('01/01/2012','dd/mm/yyyy')),
partition part_2012_up VALUES LESS THAN (MAXVALUE)
)
/
-- 逻辑上讲只有一个表或索引，但在物理上这个表或索引可能由数十个物理分区组成。每个分区都是一个独立的对象，可以独自处理，
-- 也可以作为一个更大对象的一部分进行处理。-- 13.1 分区概述 ( P561 )
......-- 13.1.1 提高可用性 ( P562 )
-- 可用性的提高源自于每个分区的独立性。对象中一个分区的可用性(或不可用)并不意味着对象本身是不可用的。优化器知道有这种分区机制，
-- 会相应地从查询计划中去除未引用的分区。在一个大对象中如果一个分区不可用，你的查询可以消除这个分区而不予考虑，这样Oracle就能成功地处理这个查询。
create tablespace p1 datafile 'E:\Oracle_data\p1_01.dbf' size 512M;
create tablespace p2 datafile 'E:\Oracle_data\p2_01.dbf' size 512M;alter user eygle quota unlimited on p1;
alter user eygle quota unlimited on p2;CREATE TABLE emp
( empno int,
ename varchar2(20)
)
PARTITION BY HASH (empno)
( partition part_1 tablespace p1,
partition part_2 tablespace p2
)
/insert into emp select empno, ename from scott.emp;eygle@SZTYORA> 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行。eygle@SZTYORA> select * from emp partition(part_2); EMPNO ENAME
---------- ----------------------------------------
7521 WARD
7566 JONES
7788 SCOTT
7844 TURNER
7900 JAMES
7902 FORD已选择6行。alter tablespace p1 offline;eygle@SZTYORA> select * from emp;
select * from emp
*
第 1 行出现错误:
ORA-00376: 此时无法读取文件 9
ORA-01110: 数据文件 9: 'E:\ORACLE_DATA\P1_01.DBF'variable n number
exec :n := 7844;
select * from emp where empno = :n;-- 总之，只要优化器能从查询计划消除分区，它就会这么做。基于这一点，如果应用在查询中使用了分区键，就能提高这些应用的可用性。-- 分区还可以通过减少停机时间来提高可用性。例如，如果有一个100GB的表，它划分为50个2GB的分区，这样就能更快地从错误中恢复。如果某个2GB的分区遭到破坏，
-- 现在恢复的时间就只是恢复一个2GB分区所需要的时间，而不是恢复一个100GB表的时间。所以从两个方面提高了可用性：
-- *(1) 优化器能够消除分区，这意味着许多用户可能甚至从未注意到某些数据是不可用的。
-- *(2) 出现错误时的停机时间会减少，因为恢复所需的工作量大幅减少。
create tablespace big2 datafile 'D:\oracle\product\10.2.0\oradata\sztyora\big2_01.dbf' size 4096M;create table big_table1
( ID, OWNER, OBJECT_NAME, SUBOBJECT_NAME,
OBJECT_ID, DATA_OBJECT_ID,
OBJECT_TYPE, CREATED, LAST_DDL_TIME,
TIMESTAMP, STATUS, TEMPORARY,
GENERATED, SECONDARY )
tablespace big1
as
select ID, OWNER, OBJECT_NAME, SUBOBJECT_NAME,
OBJECT_ID, DATA_OBJECT_ID,
OBJECT_TYPE, CREATED, LAST_DDL_TIME,
TIMESTAMP, STATUS, TEMPORARY,
GENERATED, SECONDARY
from big_table.big_table;create table big_table2
( ID, OWNER, OBJECT_NAME, SUBOBJECT_NAME,
OBJECT_ID, DATA_OBJECT_ID,
OBJECT_TYPE, CREATED, LAST_DDL_TIME,
TIMESTAMP, STATUS, TEMPORARY,
GENERATED, SECONDARY )
partition by hash(id)
( partition part_1 tablespace big2,
partition part_2 tablespace big2,
partition part_3 tablespace big2,
partition part_4 tablespace big2,
partition part_5 tablespace big2,
partition part_6 tablespace big2,
partition part_7 tablespace big2,
partition part_8 tablespace big2
)
as
select ID, OWNER, OBJECT_NAME, SUBOBJECT_NAME,
OBJECT_ID, DATA_OBJECT_ID,
OBJECT_TYPE, CREATED, LAST_DDL_TIME,
TIMESTAMP, STATUS, TEMPORARY,
GENERATED, SECONDARY
from big_table.big_table;-- 现在，每个表都在自己的表空间中，所以我们可以很容易地查询数据字典，来查看每个表空间中已分配的空间和空闲空间：
select b.tablespace_name,
mbytes_alloc,
mbytes_free
from (select round(sum(bytes)/1024/1024) mbytes_free, tablespace_name from dba_free_space group by tablespace_name ) a,
(select round(sum(bytes)/1024/1024) mbytes_alloc, tablespace_name from dba_data_files group by tablespace_name ) b
where a.tablespace_name (+) = b.tablespace_name
and b.tablespace_name in ('BIG1','BIG2');TABLESPACE_NAME MBYTES_ALLOC MBYTES_FREE
------------------------------------------------------------ ------------ -----------
BIG2 4096 2880
BIG1 4096 2744-- BIG1和BIG2的大小都大约是4GB，每个表空间都有1200MB的空闲空间。我们想重建第一个表BIG_TABLE1：
alter table big_table1 move;-- 但是失败了，BIG1表空间中要有足够的空闲空间来放下BIG_TABLE1的完整副本，同时它的原副本仍然保留，简单地说，
-- 我们需要在一个很短的时间内有大约两倍的存储空间(可能多一点，也可能少一点，这取决于重建后表的大小)。现在试图对BIG_TABLE2执行同样的操作：
alter table big_table2 move;-- 这说明，Oracle在告诉我们：无法对这个“表”执行MOVE操作；我们必须在表的各个分区上执行这个操作。可以逐个地移动(相应地重建和重组)各个分区：
alter table big_table2 move partition part_1;
alter table big_table2 move partition part_2;
alter table big_table2 move partition part_3;
alter table big_table2 move partition part_4;
alter table big_table2 move partition part_5;
alter table big_table2 move partition part_6;
alter table big_table2 move partition part_7;
alter table big_table2 move partition part_8;begin
for x in ( select partition_name from user_tab_partitions where table_name = 'BIG_TABLE2' ) loop
execute immediate 'alter table big_table2 move partition ' || x.partition_name;
end loop;
end;
/......-- 简单地说，利用分区，原先让人畏惧的操作(有时甚至是不可行的操作)会变得像在小数据库中一样容易。-- 13.1.3 改善语句性能 ( P569 )
-- 分区最后一个总的(潜在)好处体现在改进语句(SELECT、INSERT、UPDATE、DELETE、MERGE)的性能方面。我们来看两类语句，一种是修改信息的语句，
-- 另一种是只读信息的语句，并讨论在这两种情况下可以从分区得到哪些好处。-- *1) 并行DDL ( P569 )
-- 修改数据库中数据的语句有可能会执行并行DML(parallel DML, PDML)。采用PDML时，Oracle使用多个线程来执行INSERT、UPDATE或DELETE，而不是执行一个串行进程。
-- 在一个有充足I/O带宽的多CPU主机上，对于大规模的DML操作，速度的提升可能相当显著。在Oracle 9i以前的版本中，PDML要求必须分区。如果你的表没有分区，
-- 在先前的版本中就不能并行地执行这些操作。如果表确实已经分区，Oracle会根据对象所有的物理分区数为对象指定一个最大并行度。从很大程度上讲，
-- 在Oracle 9i及以后版本中这个限制已经放松，只有两个突出的例外：如果希望在一个表上执行PDML，而且这个表的一个LOB列上有一个位图索引，
-- 要并行执行操作就必须对这个表分区；另外并行度就限制为分区数。不过，总的来说，使用PDML并不一定要求进行分区。
-- 在只读查询(SELECT语句)的性能方面，分区对两类特殊操作起作用：
-- *(01) 分区消除(partition elimination)：处理查询时不考虑某些数据分区。我们已经看到了一个分区消除的例子。
-- *(02) 并行操作(parallel operation)：并行全表扫描和并行索引区间扫描就是这种操作的例子。-- 不过，由此得到的好处很大程度上取决于你使用何种类型的系统。-- *(1) OLTP系统 ......-- 13.2 表分区机制 ( P571 )
-- 目前Oracle中有4种对表分区的方法：
-- *(01) 区间分区：可以指定应当存储在一起的数据区间。例如，时间戳在Jan-2005内的所有记录都存储在分区1中，时间戳在Feb-2005内的所有记录都存储在分区2中，
-- 依次类推。这可能是Oracle中最常用的分区机制。
-- *(02) 散列分区：我们在第一章第一个例子中就已经看到了散列分区。这是指在一个列(或多个列)上应用一个散列函数，行会按这个散列值放在某个分区中。
-- *(03) 列表分区：指定一个离散值集，来确定应当存储在一起的数据。例如，可以指定STATUS列值在('A','M','Z')中的行放在分区1中，
-- STATUS值在('D','P','Q')中的行放在分区2中，依次类推。
-- *(04) 组合分区：这是区间分区和散列分区的一种组合，或者是区间分区与列表分区的组合。你可以先对某些数据应用区间分区，
-- 再在区间中根据列或表来选择最后的分区。-- 13.2.1 区间分区......CREATE TABLE range_example
( range_key_column date,
data varchar2(20)
)
PARTITION BY RANGE (range_key_column)
( PARTITION part_1 VALUES LESS THAN (to_date('01/01/2005','dd/mm/yyyy')),
PARTITION part_2 VALUES LESS THAN (to_date('01/01/2006','dd/mm/yyyy'))
)
/insert into range_example
( range_key_column, data )
values
( to_date( '15-12-2004 00:00:00','dd-mm-yyyy hh24:mi:ss'),'application data...' );insert into range_example
( range_key_column, data )
values
( to_date( '01-01-2005 00:00:00','dd-mm-yyyy hh24:mi:ss')-1/24/60/60,'application data...' );insert into range_example
( range_key_column, data )
values
( to_date( '01-01-2005 00:00:00','dd-mm-yyyy hh24:mi:ss'),'application data...' );insert into range_example
( range_key_column, data )
values
( to_date( '15-12-2005 00:00:00','dd-mm-yyyy hh24:mi:ss'),'application data...' );commit;select to_char(range_key_column,'dd-mm-yyyy hh24:mi:ss') from range_example partition (part_1);select to_char(range_key_column,'dd-mm-yyyy hh24:mi:ss') from range_example partition (part_2);-- 你可能想知道，如果插入的日期超出上界会怎么样呢？答案是Oracle会产生一个错误：
insert into range_example
( range_key_column, data )
values
( to_date( '15/12/2007 00:00:00','dd/mm/yyyy hh24:mi:ss' ), 'application data...' );
insert into range_example
*
第 1 行出现错误:
ORA-14400: 插入的分区关键字未映射到任何分区-- 假设你想像刚才一样，将2005年和2006年的日期分别聚集到各自的分区，但是另外你还希望将所有其他日期都归入第三个分区。利用区间分区，
-- 这可以使用MAXVALUE子句做到这一点，如下所示：
CREATE TABLE range_example
( range_key_column date,
data varchar2(20)
)
PARTITION BY RANGE (range_key_column)
( PARTITION part_1 VALUES LESS THAN (to_date('01/01/2005','dd/mm/yyyy')),
PARTITION part_2 VALUES LESS THAN (to_date('01/01/2006','dd/mm/yyyy')),
partition part_3 VALUES LESS THAN (MAXVALUE)
)
/-- alter table range_example add partition part_3 values less than(maxvalue);-- 现在，向这个表插入一行时，这一行肯定会放入三个分区中的某一个分区中，而不会拒绝任何行，
-- 因为分区PART_3可以接受不能放在PART_1或PART_2中的任何RANGE_KEY_COLUMN值(即使RANGE_KEY_COLUMN值为null，也会插入到这个新分区中)。insert into range_example
( range_key_column, data )
values
( null, 'application data...' );select to_char(range_key_column,'dd-mm-yyyy hh24:mi:ss') from range_example partition (part_3);
-- 对一个表执行散列分区(hash partitioning)时，Oracle会对分区键应用一个散列函数，以此确定数据应当在N个分区中的哪一个分区中。
-- Oracle建议N是2的一个幂(2、4、8、16等)，从而得到最佳的总体分布，稍后会看到这确实是一个很好的建议。-- *1 散列分区如何工作
-- 散列分区设计为能使数据很好地分布在多个不同的设备(磁盘)上，或者只是将数据聚集到更可管理的块(chunk)上。为表选择的散列键应当是惟一的一个列或一组列，
-- 或者至少有足够多的相异值，以便行能在多个分区上很好地(均匀地)分布。如果你选择一个只有4个相异值的列，并使用两个分区，
-- 那么最后可能把所有行都散列到同一个分区上，这就有悖于分区的最初目标!CREATE TABLE hash_example
( hash_key_column date,
data varchar2(20)
)
PARTITION BY HASH (hash_key_column)
( partition part_1 tablespace p1,
partition part_2 tablespace p2
)
/insert into hash_example
( hash_key_column, data )
values
( to_date('01-01-2005','dd-mm-yyyy'), 'application data...');insert into hash_example
( hash_key_column, data )
values
( to_date('02-01-2005','dd-mm-yyyy'), 'application data...');select * from hash_example partition(part_1);select * from hash_example partition(part_2);-- 前面已经提到过，如果使用散列分区，你将无从控制一行最终会放在哪个分区中。Oracle会应用散列函数，并根据散列的结果来确定行会放在哪里。
-- 如果你由于某种原因希望将某个特定行放在分区PART_1中，就不应该使用散列分区，实际上，此时也不能使用散列分区。行会按散列函数的“指示”放在某个分区中，
-- 也就是说，散列函数说这一行该放到哪个分区，它就会放到哪个分区中。如果改变散列分区的个数，数据会在所有分区中重新分布
-- (向一个散列分区表增加或删除一个分区时，将导致所有数据都重写，因为现在每一行可能属于一个不同的分区)。-- *2) 散列分区数使用2的幂
-- 我在前面提到过，分区数应该是2的幂，这很容易观察。为了便于说明，我们建立了一个存储过程，它会自动创建一个有N个分区的散列分区表(N是一个参数)。
-- 这个过程会构造一个动态查询，按分区获取其中的行数，再按分区显示行数，并给出行数的一个简单直方图。最后，它会打开这个查询，以便我们看到结果。
-- 这个过程首选创建散列表。我们将使用一个名为T的表：
create or replace procedure hash_proc
( P_nhash in number,
p_cursor out sys_refcursor )
authid current_user
as
l_text long;
l_template long := 'select $POS$ oc, ''p$POS$'' pname, count(*) cnt ' ||
' from t partition ( $PNAME$ ) union all ';
begin
begin
execute immediate 'drop table t';
exception when others
then null;
end;
execute immediate '
CREATE TABLE t ( id )
partition by hash(id)
partitions ' || p_nhash || '
as
select rownum
from all_objects'; for x in ( select partition_name pname,
PARTITION_POSITION pos
from user_tab_partitions
where table_name = 'T'
order by partition_position )
loop
l_text := l_text || replace(replace(l_template, '$POS$', x.pos), '$PNAME$', x.pname );
end loop; open p_cursor for
'select pname, cnt,
substr( rpad(''*'',30*round( cnt/max(cnt)over(),2),''*''),1,30) hg
from (' || substr( l_text, 1, length(l_text)-11 ) || ')
order by oc';end;
/variable x refcursor
set autoprint on
eygle@SZTYORA> exec hash_proc(4,:x);PL/SQL 过程已成功完成。
PNAM CNT HG
---- ---------- -----------------------------------
p1 10088 *****************************
p2 10112 *****************************
p3 10411 ******************************
p4 10126 *****************************-- 这个简单的直方图展示了数据很均匀地分布在这4个分区中。每个分区中的行数很接近。不过，如果将4改成5，要求有5个散列分区，就会看到以下输出：
eygle@SZTYORA> exec hash_proc(5,:x);PL/SQL 过程已成功完成。
PNAM CNT HG
---- ---------- -----------------------------------
p1 5080 **************
p2 10112 *****************************
p3 10413 ******************************
p4 10127 *****************************
p5 5011 **************-- 这个直方图指出，第一个和最后一个分区中的行数只是另外三个分区中行数的一半。数据根本没有得到均匀的分布。我们会看到，如果有6个和6个散列分区，
-- 这种趋势还会继续：
eygle@SZTYORA> exec hash_proc(6, :x);PL/SQL 过程已成功完成。
PNAM CNT HG
---- ---------- -----------------------------------
p1 5082 **************
p2 5136 **************
p3 10417 ******************************
p4 10129 *****************************
p5 5011 **************
p6 4981 **************eygle@SZTYORA> exec hash_proc(7, :x);PL/SQL 过程已成功完成。
PNAM CNT HG
---- ---------- -----------------------------------
p1 5083 ***************
p2 5136 ***************
p3 5181 ***************
p4 10134 ******************************
p5 5011 **************
p6 4982 **************
p7 5237 ***************-- 散列分区再回到2的幂值(8)时，又能达到我们的目标，实现均匀分布：
eygle@SZTYORA> exec hash_proc(8, :x);PL/SQL 过程已成功完成。
PNAM CNT HG
---- ---------- -----------------------------------
p1 5085 *****************************
p2 5142 *****************************
p3 5181 *****************************
p4 5029 ****************************
p5 5013 ****************************
p6 4985 ****************************
p7 5240 ******************************
p8 5107 *****************************已选择8行。-- 再继续这个实验，分区最多达到16个，你会看到如果分区数为9~15，也存在同样的问题，中间的分区存放的数据多，而两头的分区中数据少，数据的分布是斜的；
-- 而达到16个分区时，你会再次看到数据分布是直的。再达到32个分区和64个分区时也是如此。这个例子只是要指出：散列分区数要使用2的幂，这一点非常重要。
-- 列表分区(list partitioning)是Oracle 9i Release 1的一个新特性。它提供了这样一种功能，可以根据离散的值列来指定一行位于哪个分区。
-- 如果如果能根据某个代码来进行分区(如州代码或区代码)，这通常很有用。例如，你可能想把Maine州(ME)、New Hampshire州(NH)、
-- Vermount州(VT)和Massachusetts州(MA)中所有人的记录都归至一个分区中，因为这些洲相互之间挨的很近，而且你的应用按牧师位置来查询数据。
-- 类似地，你可能希望将Connecticut州(CT)、Rhode Island州(RI)和New York州(NY)的数据分组在一起。-- 对此不能使用区间分区，因为第一个分区的区间是ME到VT，第二个区间是CT到RI。这两个区间有重叠。而且也不能使用散列分区，
-- 因为这样你就无法控制给定行要放到哪个分区中；而要由Oracle提供的内置散列函数来控制。-- 利用列表分区，我们可以很容易地完成这个定制分区机制：create table list_example
( state_cd varchar2(2),
data varchar2(10)
)
partition by list(state_cd)
( partition part_1 values ( 'ME', 'NH', 'VT', 'MA' ),
partition part_2 values ( 'CT', 'RI', 'NY' )
)
/-- 就像区间分区一样，如果我们想插入列表分区中未指定的一个值，Oracle会向客户应用返回一个合适的错误。换句话说，
-- 没有DEFAULT分区的列表分区表会隐含地施加一个约束(非常像表上的一个检查约束)：
eygle@SZTYORA> insert into list_example values ( 'VA', 'data' );
insert into list_example values ( 'VA', 'data' )
*
第 1 行出现错误:
ORA-14400: 插入的分区关键字未映射到任何分区-- 如果想像前面一样把这7个州分别聚集到各自的分区中，另外把其余的所有州代码放在第三个分区中(或者，实际上对于所插入的任何其他行，
-- 如果STATE_CD列值不是以上7个州代码之一，就要放在第三个分区中)，可以使用VALUES( DEFAULT ) 子句。在此，我们将修改表，
-- 增加这个分区(也可以在CREATE TABLE语句中使用这个子句)：
eygle@SZTYORA> alter table list_example
2 add partition
3 part_3 values ( DEFAULT );表已更改。eygle@SZTYORA> insert into list_example values ( 'VA', 'data' );已创建 1 行。-- 值列表中未显式列出的所有值都会放到这个(DEFAULT)分区中。关于DEFAULT的使用，
-- 有一点要注意：一旦列表分区表有一个DEFAULT分区，就不能再向这个表中增加更多的分区了：
eygle@SZTYORA> alter table list_example
2 add partition
3 part_4 values ( 'CA', 'NM' );
alter table list_example
*
第 1 行出现错误:
ORA-14323: 在 DEFAULT 分区已存在时无法添加分区-- 13.2.4 组合分区
-- 最后我们会看到组合分区(composite partitioning)的一些例子，组合分区是区间分区和散列分区的组合，或者是区间分区与列表分区的组合。-- 在组合分区中，顶层分区机制总是区间分区。第二级分区机制可能是列表分区或散列分区(在Oracle 9i Release 1及以前的版本中，只支持散列分区，而没有列表分区)。
-- 有意思的是，使用组合分区时，并没有分区段，而只有子分区段。分区本身并没有段(这就类似于分区表没有段)。数据物理也存储在子分区段上，
-- 分区成为一个逻辑容器，或者是一个指向实际子分区的容器。-- 在下面的例子中，我们将看到一个区间-散列组合分区。在此对区间分区使用的列集不同于散列分区使用的列集。并不是非得如此，这两层分区也可以使用同样的列集：CREATE TABLE composite_example
( range_key_column date,
hash_key_column int,
data varchar2(20)
)
PARTITION BY RANGE (range_key_column)
subpartition by hash(hash_key_column) subpartitions 2
(
PARTITION part_1
VALUES LESS THAN(to_date('01/01/2005','dd/mm/yyyy'))
(subpartition part_1_sub_1,
subpartition part_1_sub_2
),
PARTITION part_2
VALUES LESS THAN(to_date('01/01/2006','dd/mm/yyyy'))
(subpartition part_2_sub_1,
subpartition part_2_sub_2
)
)
/-- 在区间-散列组合分区中，Oracle首先会应用区间分区规则，得出数据属于哪个区间。然后再应用散列函数，来确定数据最后要放在哪个物理分区中。......CREATE TABLE composite_range_list_example
( range_key_column date,
code_key_column int,
data varchar2(20)
)
PARTITION BY RANGE(range_key_column)
subpartition by list(code_key_column)
(
PARTITION part_1
VALUES LESS THAN(to_date('01/01/2005','dd/mm/yyyy'))
(subpartition part_1_sub_1 values( 1, 3, 5, 7 ),
subpartition part_1_sub_2 values( 2, 4, 6, 8 )
),
PARTITION part_2
VALUES LESS THAN(to_date('01/01/2006','dd/mm/yyyy'))
(subpartition part_2_sub_1 values( 1, 3 ),
subpartition part_2_sub_2 values( 5, 7 ),
subpartition part_3_sub_3 values( 2, 4, 6, 8 )
)
)
/-- 加上MAXVALUE、DEFAULT值子分区，使其任何数值都能插入表中
CREATE TABLE composite_range_list_example
( range_key_column date,
code_key_column int,
data varchar2(20)
)
PARTITION BY RANGE(range_key_column)
subpartition by list(code_key_column)
(
PARTITION part_1
VALUES LESS THAN(to_date('01/01/2005','dd/mm/yyyy'))
(subpartition part_1_sub_1 values( 1, 3, 5, 7 ),
subpartition part_1_sub_2 values( 2, 4, 6, 8 ),
subpartition part_1_sub_def values( default )
),
PARTITION part_2
VALUES LESS THAN(to_date('01/01/2006','dd/mm/yyyy'))
(subpartition part_2_sub_1 values( 1, 3 ),
subpartition part_2_sub_2 values( 5, 7 ),
subpartition part_3_sub_3 values( 2, 4, 6, 8 ),
subpartition part_3_sub_def values( default )
),
PARTITION part_def
VALUES LESS THAN( MAXVALUE )
(subpartition part_def_1 values( 1, 3 ),
subpartition part_def_2 values( 5, 7 ),
subpartition part_def_3 values( 2, 4, 6, 8 ),
subpartition part_def_def values( default )
)
)
/-- 13.2.5 行移动
-- 你可能想知道，在前面所述的各种分区机制中，如果用于确定分区的列有修改会发生什么。需要考虑两种情况：
-- *(01) 修改不会导致使用一个不同的分区；行仍属于原来的分区。这在所有情况下都得到支持。
-- *(02) 修改会导致行跨分区移动。只有当表启用了行移动时才支持这种情况；否则，会产生一个错误。-- 这些行为很容易观察。在前面的例子中，我们向RANGE_EXAMPLE表的PART_1插入两行：
insert into range_example
( range_key_column, data )
values
( to_date( '15-12-2004 00:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'application data...' );insert into range_example
( range_key_column, data )
values
( to_date( '01-01-2005 00:00:00', 'dd-mm-yyyy hh24:mi:ss')-1/24/60/60, 'application data...' );select * from range_example partition(part_1);-- 取其中一行，并更新其RANGE_KEY_COLUMN值，不过更新后它还能放在PART_1中：
update range_example
set range_key_column = trunc(range_key_column)
where range_key_column = to_date( '31-12-2004 23:59:59','dd-mm-yyyy hh24:mi:ss');-- 不出所料，这会成功：行仍然在分区PART_1中。接下来，再把RANGE_KEY_COLUMN更新为另一个值，但这次更新后的值将导致它属于分区PART_2：
update range_example
set range_key_column = to_date('02-01-2005','dd-mm-yyyy')
where range_key_column = to_date( '31-12-2004','dd-mm-yyyy');第 1 行出现错误:
ORA-14402: 更新分区关键字列将导致分区的更改-- 这会立即产生一个错误，因为我们没有显式地启用行移动。在Oracle 8i及以后的版本中，可以在这个表上启用行移动(row movement)，
-- 以允许行从一个分区移动到另一个分区。---------------------------------------------------------------------------------------------------------------------
-- 注意 Oracle 8.0中没有行移动功能；在这个版本中，你必须先删除行，再重新将其插入。
---------------------------------------------------------------------------------------------------------------------
eygle@SZTYORA> select rowid
2 from range_example
3 where range_key_column = to_date('31-12-2004','dd-mm-yyyy');ROWID
------------------
AAAMuwAAIAAAAQKAABeygle@SZTYORA> alter table range_example enable row movement;表已更改。
eygle@SZTYORA> update range_example
2 set range_key_column = to_date('02-01-2005','dd-mm-yyyy')
3 where range_key_column = to_date( '31-12-2004','dd-mm-yyyy');已更新 1 行。eygle@SZTYORA> select rowid
2 from range_example
3 where range_key_column = to_date('02-01-2005','dd-mm-yyyy');ROWID
------------------
AAAMuxAAIAAAAQSAAA-- 既然知道执行这个更新时行的ROWID会改变，所以要启用行移动，这样才允许更新分区键。---------------------------------------------------------------------------------------------------------------------
-- 注意 在其他一些情况下，ROWID也有可能因为更新而改变。更新IOT的主键可能导致ROWID改变，该行的通用ROWID(UROWID)也会改变。
-- Oracle 10g的ALTER TABLE SHRINK命令也可能使行的ROWID改变。
----------------------------------------------------------------------------------------------------------------------- 要知道，执行行移动时，实际上在内部就好像先删除了这一行，然后再将其重新插入。这会更新这个表上的索引，删除旧的索引条目，再插入一个新条目。
-- 此时会完成DELETE再加一个INSERT的相应物理工作。不过，尽管在此执行了行的物理删除和插入，在Oracle看来却还是一个更新，因此，不会导致INSERT和DELETE触发器触发，
-- 只有UPDATE触发器会触发。另外，由于外键约束可能不允许DELETE的子表也不会触发DELETE触发器。不过，还是要对将完成的额外工作有所准备；
-- 行移动的开销比正常的UPDATE昂贵得多。因此，如果构建的系统会频繁修改分区键，而且这种修改会导致分区移动，这实在是一个糟糕的设计决策。-- 13.2.6 表分区机制小结
-- 一般来讲，如果将数据按某个(某些)值逻辑聚集，区间分区就很有用。基于时间的数据就是这方面的经典例子，如按“销售金额”、“财政年度”或“月份”分区。
-- 在许多情况下，区间分区都能利用分区消除，这包括使用完全相等性和区间(小于、大于、介于......之间等)。-- 如果不能按自然的区间进行分区，散列分区就很合适。例如，如果必须加载一个表，其中装满与人口普查相关的数据，可能无法找到一个合适的属性来按这个属性
-- 完成区间分区。不过，你可能还是想得到分区提供的管理、性能和可用性提升等诸多好处。在此，只需选择惟一的一个列或几乎惟一的一个列集，对其计算散列。
-- 这样一来，无论有多少个分区，都能得到均匀的数据分布。使用完全相等性或IN(value,value,...)时，散列分区对象可以利用分区消除，但是使用数据区间时，
-- 散列分区则无法利用分区消除。-- 如果数据中的一列有一组离散值，而且根据应用使用这一列的方式来看，按这一列进行分区很有意义(例如，这样一来，查询中可以轻松地利用分区消除)，
-- 这种数据就很适合采用列表分区。列表分区的经典例子包括按州或区域代码分区，实际上，一般来讲许多“代码”型属性都很适合应用列表分区。-- 如果某些数据逻辑上可以进行区间分区，但是得到的区间分区还是太大，不能有效地管理，就可以使用组合分区。可以先应用区间分区，再进一步划分各个区间，
-- 按一个散列函数或使用列表来分区。这样就能将I/O请求分布到任何给定大分区中的多个磁盘上。另外，现在可以得到3个层次的分区消除。如果在区间分区键上查询，
-- Oracle就能消除任何不满足条件的区间分区。如果向查询增加散列或列表键，Oracle可以消除该区间中其他的散列或列表分区。
-- 如果只是在散列或列表键上查询(而不使用区间分区键)，Oracle就只会查询各个区间分区中的这些散列或列表子分区。-- 我们建议，如果可以按某个属性自然地对数据完成区间分区，就应该使用区间分区，而不是散列分区或列表分区。散列和列表分区能提供分区的许多突出优点，
-- 但是在分区消除方面都不如区间分区有用。如果所得到的区间分区太大，不能很好地管理；或者如果你想使用所有PDML功能或对一个区间分区使用并行索引扫描，
-- 则建议在区间分区中再使用散列或列表分区。-- 13.3. 索引分区 ( P586 )
-- 索引与表类似，也可以分区。对索引进行分区有两种可能的方法：
-- *(01) 随表对索引完成相应的分区：这也称为局部分区索引(locally partitioned index)。每个表分区都有一个索引分区，而且只索引该表分区。
-- 一个给定索引分区中的所有条目都指向一个表分区，表分区中的所有行都表示在一个索引分区中。
-- *(02) 按区间对索引分区：这也称为全局索引分区(globally partitioned index)。在此，索引按区间分区(或者在Oracle 10g中还可以按散列分区)，
-- 一个索引分区可能指向任何(和所有)表分区。-- 对于全局分区索引，要注意实际上索引分区数可能不同于表分区数。-- 由于全局索引只按区间或散列分区，如果希望有一个列表或组合分区索引，就必须使用局部索引。局部索引会使用与底层表相同的机制分区。---------------------------------------------------------------------------------------------------------------------
-- 注意 全局索引的散列分区是Oracle 10g Release 1及以后的版本中才有的新特性。在Oracle 9i及以前的版本中，只能按区间进行全局分区。
----------------------------------------------------------------------------------------------------------------------- 13.3.1 局部索引与全局索引 ( P587 )
-- 根据我的经验，数据仓库系统中大多数分区实现使用的都是局部索引。而在OLTP系统中，全局索引则更为常见，稍后我们将解释为什么是这样，
-- 这取决于是否需要在索引结构上执行分区消除来维持分区后与分区前有同样的查询响应时间。---------------------------------------------------------------------------------------------------------------------
-- 注意 在过去几年中，OLTP使用局部索引的情况越来越常见，因为这种系统的规模已经飞速增长。
----------------------------------------------------------------------------------------------------------------------- 局部索引有一些特有的性质，这使得局部索引对于大多数数据仓库实现来说是最佳的选择。局部索引支持一种更可用的环境(停机时间更少)，
-- 因为问题会隔离到一个区间或数据散列上。而另一方面，由于全局索引可以指向多个表分区，因此可能会成为一个故障点，对于某些查询来说，
-- 一旦全局索引出现故障，则所有分区都不可访问。-- 对于分区维护操作，局部索引更为灵活。如果DBA决定移动一个表分区，只有相关的局部索引分区需要重建或维护。如果是全局索引，
-- 那么所有索引分区都必须实时重建或维护。滑动窗口实现也是一样，在此要从分区中使旧数据老化，而补入新数据。此时无需重建任何局部索引，
-- 但是在分区操作中所有全局索引都要么需要重建，要么需要维护。在某些情况下，Oracle可以利用索引随表进行局部分区这一事实，开发最优的查询计划。
-- 而对于全局索引，索引和表分区之间就没有这种关系。-- 局部索引还有利于分区时间点恢复操作。如果由于某种原因，一个分区要恢复到比其余表更早的某个时间点，所有局部分区索引都可以恢复到同样的那个时间点，
-- 而所有全局索引则必须在该对象上重建。这并不是说完全“避免全局索引”，实际上，从性能原因考虑，全局索引是非常重要的(有关内容稍后就会介绍)，
-- 我们只是要注意使用全局索引带来的影响。-- 13.3.2 局部索引 ( P587 )
-- Oracle划分了以下两类局部索引：
-- *(01) 局部前缀索引 (local prefixed index)：在这些索引中，分区键在索引定义的前几列上。例如，一个表在名为LOAD_DATE的列上进行区间分区，
-- 该表上的局部前缀索引就以LOAD_DATE作为其索引列列表中的第一列。
-- *(02) 局部非前缀索引 (local nonprefixed index)：这些索引不以分区键作为其列列表的前几列。索引可能包含分区键列，也可能不包含。-- 这两类索引都可以利用分区消除，它们都支持惟一性(只要非前缀索引包含分区键)等。事实上，使用局部前缀索引的查询总允许索引分区消除，
-- 而使用局部非前缀索引的查询可能不允许。正是由于这个原因，所以在某些人看来局部非前缀索引“更慢”，它们不能保证分区消除(但确实可以支持分区消除)。-- 如果查询中将索引用作访问表的初始路径，那么从本质来讲，局部前缀索引并不比局部非前缀索引更好。我的意思是说，如果查询把“扫描一个索引”作为第一步，
-- 那么前缀索引和非前缀索引之间并没有太大的差别。
-- 如果查询首先访问索引，它是否能消除分区完全取决于查询中的谓词。要说明这一点，举一个小例子会很有帮助。下面的代码创建了一个表PARTITIONED_TABLE，
-- 它在一个数字列A上进行区间分区，使得小于2的值在分区PART_1中，小于3的值则都在分区PART_2中：
CREATE TABLE partitioned_table
( a int,
b int,
data char(20)
)
PARTITION BY RANGE (a)
(
PARTITION part_1 VALUES LESS THAN(2) tablespace p1,
PARTITION part_2 VALUES LESS THAN(3) tablespace p2
)
/-- 然后我们创建一个局部前缀索引LOCAL_PREFIXED和一个局部非前缀索引LOCAL_NONPREFIXED。注意，非前缀索引在其定义中没有以A作为其最前列，
-- 正是这一点使之成为一个非前缀索引：
create index local_prefixed on partitioned_table(a,b) local;create index local_nonprefixed on partitioned_table(b) local;-- 接下来，我们向一个分区中插入一些数据，并收集统计信息：
insert into partitioned_table
select mod(rownum-1,2)+1, rownum, 'x'
from all_objects;begin
dbms_stats.gather_table_stats
( user,
'PARTITIONED_TABLE',
cascade=>TRUE );
end;
/-- 将表空间P2离线，其中包含用于表和索引的PART_2分区：
alter tablespace p2 offline;-- 表空间P2离线后，Oracle就无法访问这些特定的索引分区。这就好像是我们遭遇了“介质故障”，导致分区不可用。现在我们查询这个表，
-- 来看看不同的查询需要哪些索引分区。第一个查询编写为允许使用局部前缀索引：
select * from partitioned_table where a=1 and b=1;-- 这个查询成功了，通过查看解释计划，可以看到这个查询为什么能成功。我们将使用内置包DBMS_XPLAN来查看这个查询访问了哪些分区。
-- 输出的PSTART(分区开始)和PSTOP(分区结束)这两列准确地显示出，这个查询要想成功需要哪些分区必须在线而且可用：
delete from plan_table;eygle@SZTYORA> explain plan for
2 select * from partitioned_table where a = 1 and b = 1;已解释。eygle@SZTYORA> select * from table(dbms_xplan.display);PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1622054381------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 2 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE | | 1 | 28 | 2 (0)| 00:00:01 | 1 | 1 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| PARTITIONED_TABLE | 1 | 28 | 2 (0)| 00:00:01 | 1 | 1 |
|* 3 | INDEX RANGE SCAN | LOCAL_PREFIXED | 1 | | 1 (0)| 00:00:01 | 1 | 1 |
------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------- 3 - access("A"=1 AND "B"=1)已选择15行。-- 因此，使用LOCAL_PREFIXED的查询成功了。优化器能消除LOCAL_PREFIXED的PART_2不予考虑，因为我们在查询中指定了A=1，
-- 而且在计划中可以清楚地看到PSTART和PSTOP都等于1。分区消除帮助了我们。不过，第二个查询却失败了：
eygle@SZTYORA> select * from partitioned_table where b=1;
ERROR:
ORA-00376: 此时无法读取文件 10
ORA-01110: 数据文件 10: 'E:\ORACLE_DATA\P2_01.DBF'未选定行-- 通过同样的技术，可以看到这是为什么：
eygle@SZTYORA> delete from plan_table;已删除8行。eygle@SZTYORA>
eygle@SZTYORA> explain plan for
2 select * from partitioned_table where b = 1;已解释。eygle@SZTYORA>
eygle@SZTYORA> select * from table(dbms_xplan.display);PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 440752652------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 4 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE ALL | | 1 | 28 | 4 (0)| 00:00:01 | 1 | 2 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| PARTITIONED_TABLE | 1 | 28 | 4 (0)| 00:00:01 | 1 | 2 |
|* 3 | INDEX RANGE SCAN | LOCAL_NONPREFIXED | 1 | | 3 (0)| 00:00:01 | 1 | 2 |
------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------- 3 - access("B"=1)已选择15行。-- 在此优化器不能不考虑LOCAL_NONPREFIXED的PART_2，为了查看是否有B=1，索引的PART_1和PART_2都必须检查。在此，局部非前缀索引存在一个性能问题：
-- 它不能像前缀索引那样，在谓词中使用分区键。并不是说前缀索引更好，我们的意思是：要使用非前缀索引，必须使用一个允许分区消除的查询。-- 如果删除LOCAL_PREFIXED索引，重新运行原先成功的查询，如下：
eygle@SZTYORA> drop index local_prefixed;索引已删除。eygle@SZTYORA> select * from partitioned_table where a = 1 and b = 1; A B DATA
---------- ---------- ----------------------------------------
1 1 x-- 它会成功，但是正如我们所见，这里使用了先前失败的索引。该计划显示出，在此Oracle能利用分区消除，有了谓词A=1，
-- 就有了足够的信息可以让数据库消除索引分区PART_2而不予考虑：
eygle@SZTYORA> delete from plan_table;已删除0行。eygle@SZTYORA> explain plan for
2 select * from partitioned_table where a = 1 and b = 1;已解释。eygle@SZTYORA> select * from table(dbms_xplan.display);PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 904532382------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 2 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE | | 1 | 28 | 2 (0)| 00:00:01 | 1 | 1 |
|* 2 | TABLE ACCESS BY LOCAL INDEX ROWID| PARTITIONED_TABLE | 1 | 28 | 2 (0)| 00:00:01 | 1 | 1 |
|* 3 | INDEX RANGE SCAN | LOCAL_NONPREFIXED | 1 | | 1 (0)| 00:00:01 | 1 | 1 |
------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------- 2 - filter("A"=1)
3 - access("B"=1)已选择16行。-- 注意PSTART和PSTOP列值为1和1。这就证明，优化器甚至对非前缀局部索引也能执行分区消除。-- 如果你频繁地使用以下查询来查询先前的表：
select ... from partitioned_table where a = :a and b = :b;
select ... from partitioned_table where b = :b;-- 可以考虑在(b,a)上使用一个局部非前缀索引，这个索引对于前面的两个查询都是有用的。(a,b)上的局部前缀索引只对第一个查询有用。-- 这里的关键是，不必对非前缀索引退避三舍，也不要认为非前缀索引是主要的性能障碍。如果你有多个如前所列的查询(可以得益于非前缀索引)，
-- 就应该考虑使用一个非前缀索引。重点是，要尽可能保证查询包含的谓词允许索引分区消除。使用前缀局部索引可以保证这一点，使用非前缀索引则不能保证。
-- 还要考虑如何使用索引。如果将索引用作查询计划中的第一步，那么这两种类型的索引没有多少差别。
-- 为了保证惟一性(这包括UNIQUE约束或PRIMARY KEY约束)，如果你想使用一个局部索引来保证这个约束，那么分区键必须 包括在约束本身中。在我看来，
-- 这是局部索引的最大限制。Oracle只保证索引分区内部的惟一性，而不能跨分区。这说明什么呢？例如，这意味着不能一方面在一个TIMESTAMP字段上执行行区间分区，
-- 而另一方面在ID上有一个主键(使用一个局部分区索引来保证)。Oracle会利用全局索引来保证惟一性。-- 在下面的例子中，我们将创建一个区间分区表，它按一个名为LOAD_TYPE的列分区，却在ID列上有一个主键。为此，可以在一个没有任何其他对象的模式中执行以下
-- CREATE TABLE语句，所以通过查看这个用户所拥有的每一个段，就能很容易地看出到底创建了哪些对象：CREATE TABLE partitioned
( load_date date,
id int,
constraint partitioned_pk primary key(id)
)
PARTITION BY RANGE (load_date)
(
PARTITION part_1 VALUES LESS THAN ( to_date('01/01/2000','dd/mm/yyyy')),
PARTITION part_2 VALUES LESS THAN ( to_date('01/01/2001','dd/mm/yyyy'))
)
/eygle1@SZTYORA> select segment_name, partition_name, segment_type
from user_segments;SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE
------------------------------ ------------------------------ ------------------------------------
PARTITIONED_PK INDEX
PARTITIONED PART_2 TABLE PARTITION
PARTITIONED PART_1 TABLE PARTITION-- PARTITIONED_PK索引甚至没有分区，更不用说局部分区了。而且我们将会看到，它根本无法进行局部分区。由于认识到非惟一索引也能像惟一索引一样保证主键，
-- 我们想以此骗过Oracle，但是可以看到这种方法也不能奏效：
CREATE TABLE partitioned
( timestamp date,
id int
)
PARTITION BY RANGE (timestamp)
(
PARTITION part_1 VALUES LESS THAN ( to_date('01/01/2000','dd/mm/yyyy')),
PARTITION part_2 VALUES LESS THAN ( to_date('01/01/2001','dd/mm/yyyy'))
)
/create index partitioned_idx
on partitioned(id) local
/eygle2@SZTYORA> select segment_name, partition_name, segment_type
from user_segments;SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE
------------------------------ ------------------------------ ------------------------------------
PARTITIONED_IDX PART_2 INDEX PARTITION
PARTITIONED_IDX PART_1 INDEX PARTITION
PARTITIONED PART_2 TABLE PARTITION
PARTITIONED PART_1 TABLE PARTITIONeygle2@SZTYORA> alter table partitioned
2 add constraint
3 partitioned_pk
4 primary key(id)
5 /
alter table partitioned
*
第 1 行出现错误:
ORA-01408: 此列列表已索引-- 在此，Oracle试图在ID上创建一个全局索引，却发现办不到，这是因为ID上已经存在一个索引。如果已创建的索引没有分区，前面的语句就能工作，
-- Oracle会使用这个索引来保证约束。-- 为什么局部分区索引不能保证惟一性(除非分区键是约束的一部分)，原因有两方面。首先，如果Oracle允许如此，就会丧失分区的大多数好处。
-- 可用性和可扩展性都会丧失殆尽，因为对于任何插入和更新，总是要求所有分区都一定可用，而且要扫描每一个分区。你的分区越多，数据就变得越不可用。
-- 另外分区越多，要扫描的索引分区就越多，分区也会变得越发不可扩缩。这样做不仅不能提供可用性和可扩缩性，相反，实际上反倒会削弱可用性和可扩缩性。-- 另外，倘若局部分区索引能保证惟一性，Oracle就必须在事务级对这个表的插入和更新有效地进行串行化。这是因为，如果向PART_1增加ID=1，
-- Oracle就必须以某种方式防止其他人向PART_2增加ID=1。对此惟一的做法是防止别人修改索引分区PART_2，因为无法通过对这个分区中的内容“锁定”来做到
-- (找不出什么可以锁定)。-- 在一个OLTP系统中，惟一性约束必须由系统保证(也就是说，由Oracle保证)，以确保数据的完整性。这意味着，应用的逻辑模型会对物理设计产生影响。
-- 惟一性约束给决定底层的表分区机制，影响分区键的选择，或者指示你应该使用全局索引。下面将更深入地介绍全局索引。