分区（partitioning） 将一个表或索引物理地分解为多个更小、更可管理的部分。就访问数据库的应用而言，逻辑上讲只有一个表或一个索引，但在物理上这个表或索引可能由数十个物理分区组成。每个分区都是一个独立的对象，可以独自处理，也可以作为一个更大对象的一部分进行处理。
1 分区概述
分区有利于管理非常大的表和索引，它使用了一种“分而治之”的逻辑。分区引入了分区键（partition key）的概念，分区键用于根据某个区间值（或范围值）、特定值列表或散列函数值执行数据的聚集。分区好处如下：
（1） 提高数据的可用性
（2） 由于从数据库中去除了大段，相应地减轻了管理的负担。在一个100GB的表上执行管理操作时（如重组来删除移植的行，或者在“净化”旧信息后回收表左边的“空白”空间），与在各个10GB的表分区上执行10次同样的操作相比，前者负担要大得多。另外，通过使用分区，可以让净化例程根本不留下空白空间，这就完全消除了重组的必要！
（3） 改善某些查询的性能：主要在大型仓库环境中有这个好处，通过使用分区，可以消除很大的数据区间，从而不必考虑它们，相应地根本不用访问这些数据。但这在事务性系统中并不适用，因为这种系统本身就只是访问少量的数据。
（4） 可以把修改分布到多个单独的分区上，从而减少大容量OLTP系统上的竞争：如果一个段遭遇激烈的竞争，可以把它分为多个段，这就可以得到一个副作用：能成比例地减少竞争。
1.1 提高可用性
可用性的提高源自于每个分区的独立性。对象中一个分区的可用性（或不可用）并不意味着对象本身是不可用的。优化器知道有这种分区机制，会相应地从查询计划中去除未引用的分区。在一个大对象中如果一个分区不可用，查询可以消除这个分区而不予考虑，这样Oracle就能成功地处理这个查询。
为了展示这种可用性的提高，我们将建立一个散列分区表，其中有两个分区，分别在单独的表空间中。这里将创建一个EMP表，它在EMPNO列上指定了一个分区键（EMPNO就是我们的分区键）。在这种情况下，这个结构意味着：对于插入到这个表中的每一行，会对EMPNO列的值计算散列，来确定这一行将置于哪个分区（及相应的表空间）中：
scott@ORCL>create tablespace P2 datafile 'D:\app\Administrator\oradata\orcl\P2.dbf'
2 size 40m autoextend on maxsize 4G;
表空间已创建。
scott@ORCL>create tablespace P1 datafile 'D:\app\Administrator\oradata\orcl\P1.dbf'
2 size 40m autoextend on maxsize 4G;
表空间已创建。
scott@ORCL>CREATE TABLE emp
2 ( empno int,
3 ename varchar2(20)
4 )
5 PARTITION BY HASH (empno)
6 ( partition part_1 tablespace p1,
7 partition part_2 tablespace p2
8 )
9 /
表已创建。接下来，我们向表中插入一些数据，然后使用带分区的扩展表名检查各个分区的内容：
scott@ORCL>insert into emp select empno, ename from emp_bak;
已创建14行。
scott@ORCL>select * from emp partition(part_1);
EMPNO ENAME
---------- ----------------------------------------
7782 CLARK
7839 KING
7934 MILLER
7369 SMITH
7876 ADAMS
7499 ALLEN
7654 MARTIN
7698 BLAKE
已选择8行。
scott@ORCL>select * from emp partition(part_2);
EMPNO ENAME
---------- ----------------------------------------
7566 JONES
7788 SCOTT
7902 FORD
7521 WARD
7844 TURNER
7900 JAMES
已选择6行。通过使用散列分区，我们让Oracle随机地（很可能均匀地）将数据分布到多个分区上。我们无法控制数据要分布到哪个分区上；Oracle会根据生成的散列键值来确定。
下面将其中一个表空间离线（例如，模拟一种磁盘出故障的情况），使这个分区中的数据不可用：
scott@ORCL>alter tablespace p1 offline;
表空间已更改。
接下来，运行一个查询，这个查询将命中每一个分区，可以看到这个查询失败了：
scott@ORCL>select * from emp;
select * from emp
*
第 1 行出现错误:
ORA-00376: 此时无法读取文件 6
ORA-01110: 数据文件 6: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\P1.DBF'
不过，如果查询不访问离线的表空间，这个查询就能正常工作；Oracle会消除离线分区而不予考虑。在这个特定的例子中，我使用了一个绑定变量，这只是为了展示Oracle肯定能消除离线分区：即使Oracle在查询优化时不知道会访问哪个分区，也能在运行是不考虑离线分区：
scott@ORCL>variable n number
scott@ORCL>exec :n := 7844;
PL/SQL 过程已成功完成。
scott@ORCL>select * from emp where empno = :n;
EMPNO ENAME
---------- ----------------------------------------
7844 TURNER
总之，只要优化器能从查询计划消除分区，它就会这么做。基于这一点，如果应用在查询中使用了分区键，就能提高这些应用的可用性。
分区还可以通过减少停机时间来提高可用性。例如，如果有一个100GB的表，它划分为50个2GB的分区，这样就能更快地从错误中恢复。如果某个2GB的分区遭到破坏，现在恢复的时间就只是恢复一个2GB分区所需的时间，而不是恢复一个100GB表的时间。所以从两个方面提高了可用性：
优化器能够消除分区，这意味着许多用户可能甚至从未注意到某些数据是不可用的。
出现错误时的停机时间会减少，因为恢复所需的工作量大幅减少。
1.2 减少管理负担
与在一个大对象上执行操作相比，在小对象上执行同样的操作从本质上讲更为容易、速度更快，而且占用的资源也更少。
例如，假设数据库中有一个10GB的索引。如果需要重建这个索引，而该索引未分区，你就必须将整个10GB的索引作为一个工作单元来重建。尽管可以在线地重建索引，但是要完全重建完整的10GB索引，还是需要占用大量的资源。至少需要在某处有10GB的空闲存储空间来存放两个索引的副本，还需要一个临时事务日志表来记录重建索引期间对基表所做的修改。另一方面，如果将索引本身划分为10个1GB的分区，就可以一个接一个地单独重建各个索引分区。现在只需要原先所需空闲空间的10%。另外，各个索引的重建也更快（可能是原来的10倍），需要向新索引合并的事务修改也更少（到此为止，在线索引重建期间发生的事务修改会更少）。
另外请考虑以下情况：10GB索引的重建即将完成之前，如果出现系统或软件故障会发生什么。我们所做的全部努力都会付诸东流。如果把问题分解，将索引划分为1GB的分区，你最多只会丢掉重建工作的10%。
或者，你可能只需要重建全部聚集索引的10%，例如，只是“最新”的数据（活动数据）需要重组，而所有“较旧”的数据（相当静态）不受影响。
最后，请考虑这样一种情况：你发现表中50%的行都是“移植”行，可能想进行修正。建立一个分区表将有利于这个操作。为了“修正”移植行，你往往必须重建对象，在这种情况下，就是要重建一个表。如果有一个100GB的表，就需要在一个非常大的“块”（chunk）上连续地使用ALTER TABLE MOVE来执行这个操作。另一方面，如果你有25个分区，每个分区的大小为4GB，就可以一个接一个地重建各个分区。或者，如果你在空余时间做这个工作，而且有充足的资源，甚至可以在单独的会话中并行地执行ALTER TABLE MOVE语句，这就很可能会减少整个操作所需的时间。对于一个未分区对象所能做的工作，分区对象中的单个分区几乎都能做到。你甚至可能发现，移植行都集中在一个很小的分区子集中，因此，可以只重建一两个分区，而不是重建整个表。
这里有一个小例子，展示了如何对一个有多个移植行的表进行重建。BIG_TABLE1和BIG_TABLE2都是从BIG_TABLE的一个10,000,000行的实例创建的。BIG_TABLE1是一个常规的未分区表，而BIG_TABLE2是一个散列分区表，有8个分区：
create tablespace big1 datafile 'D:\app\Administrator\oradata\orcl\big1.dbf'
size 20m autoextend on maxsize 2G;
表空间已创建。
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;
表已创建。
create tablespace big2 datafile 'D:\app\Administrator\oradata\orcl\big2.dbf'
size 20m autoextend on maxsize 2G;
表空间已创建。
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;
表已创建。
现在，每个表都在自己的表空间中，所以我们可以很容易地查询数据字典，来查看每个表空间中已分配的空间和空闲空间：
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 1362
17
BIG1 1345BIG1和BIG2的大小都大约是1.3GB，每个表空间都有344MB的空闲空间。我们想创建第一个表BIG_TABLE1：
scott@ORCL>alter table big_table1 move;
alter table big_table1 move
*
第 1 行出现错误:
ORA-01652: 无法通过 1024 (在表空间 BIG1 中) 扩展 temp 段但失败了，BIG 1表空间中要有足够的空闲空间来放下BIG_TABLE1的完整副本，同时它的原副本仍然保留，简单地说，我们需要一个很短的时间内有大约两倍的存储空间（可能多一点，也可能少移动，这取决于重建后表的大小）。现在试图对BIG_TABLE2执行同样的操作：
scott@ORCL>alter table big_table2 move;
alter table big_table2 move
*
第 1 行出现错误:
ORA-14511: 不能对分区对象进行操作这说明，Oracle在告诉我们：无法对这个“表”执行MOVE操作；我们必须在表的各个分区上执行这个操作。可以逐个地移动（相应地重建和重组）各个分区：
scott@ORCL>alter table big_table2 move partition part_1;
表已更改。
scott@ORCL>alter table big_table2 move partition part_2;
表已更改。
scott@ORCL>alter table big_table2 move partition part_3;
表已更改。
scott@ORCL>alter table big_table2 move partition part_4;
表已更改。
scott@ORCL>alter table big_table2 move partition part_5;
表已更改。
scott@ORCL>alter table big_table2 move partition part_6;
表已更改。
scott@ORCL>alter table big_table2 move partition part_7;
表已更改。
scott@ORCL>alter table big_table2 move partition part_8;
表已更改。对于每个移动，只需要有足够的空闲空间来存放原来数据的1/8的副本！因此，假设有先前同样多的空闲空间，这些命令就能成功。我们需要的临时资源将显著减少。不仅如此，如果在移动到PART_4后但在PART_5完成“移动”之前系统失败了（例如，掉电），并不会丢失以前所做的所有工作，这与执行一个MOVE语句的情况不同。前4个分区仍是“移动”后的状态，等系统恢复时，我们可以从分区PART_5继续处理。
如果有数百个分区（或者更多），可以编写一个脚本来解决这个问题，前面的语句则变成以下脚本：
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;
/需要的所有信息都能在Oracle数据字典中找到，而且大多数实现了分区的站点都有一系列存储过程，可用于简化大量分区的管理。另外，许多GUI工具（如Enterprise Manager）也有一种内置的功能，可以执行这种操作而无需你键入各条命令。
关于分区和管理，还有一个因素需要考虑，这就是在维护数据仓库和归档中使用数据“滑动窗口”。在许多情况下，需要保证数据在最后N个时间单位内一直在线。例如，假设需要保证最后12个月或最后5年的数据在线。如果没有分区，这通常是一个大规模的INSERT，其后是一个大规模的DELETE。为此有相对多的DML，并且会生成大量的redo和undo。如果进行了分区，则只需做下面的工作：
（1） 用新的月（或年，或者是其他）数据加载一个单独的表。
（2） 对这个表充分建立索引（这一步甚至可以在另一个实例中完成，然后传送到这个数据库中）。
（3） 将这个新加载（并建立了索引）的表附加到分区表的最后，这里使用一个快速DDL命令：ALTER TABLE EXCHANGE PARTITION。
（4） 从分区表另一端将最旧的分区去掉。
这样一来，现在就可以很容易地支持包含时间敏感信息的非常大的对象。旧数据很容易地从分区表中去除，如果不再需要它，可以简单地将其删除；或者也可以归档到某个地方。新数据可以加载到一个单独的表中，这样在加载、建索引等工作完成之前就不会影响分区表。
利用分区，原先让人畏惧的操作（有时甚至是不可行的操作）会变得像在小数据库中一样容易。
1.3改善语句性能
分区最后一个总的（潜在）好处体现在改进语句（SELECT、INSERT、UPDATE、DELETE、MERGE）的性能方面。
看两类语句，一种是修改信息的语句，另一种是只读取信息的语句，并讨论在这种情况下可以从分区得到哪些好处。
1. 并行DML
修改数据库中数据的语句有可能会执行并行DML（parallel DML，PDML）。采用PDML时，Oracle使用多个线程或进程来执行INSERT、UPDATE或DELETE， 而不是执行一个串行进程。在一个有充足I/O带宽的多CPU主机上，对于大规模的DML操作，速度的提升可能相当显著。在Oracle9i以前的版本中，PDML要求必须分区。如果表确实已经分区，Oracle会根据对象所有的物理分区数为对象指定一个最大并行度。从很大程度上讲，在Oracle9i及以后版本中这个限制已经放松，只有两个突出的例外；如果希望在一个表上执行PDML，而且这个表的一个LOB列上有一个位图索引，要并行执行操作就必须对这个表分区；另外并行度就限制为分区数。不过，总的说来，使用PDML并不一定要求进行分区。
2. 查询性能
在只读查询（SELECT语句）的性能方面，分区对两类特殊操作起作用：
分区消除（partition elimination）：处理查询时不考虑某些数据分区。
并行操作（parallel operation）：并行全表扫描和并行索引区间扫描就是这种操作的例子。
由此得到的好处很多程度上取决于使用何种类型的系统。
OLTP系统
在OLTP系统中，不应该把分区当作一种大幅改善查询性能的方法。实际上，在一个传统的OLTP系统中，你必须很小心地应用分区，提防着不要对运行时性能产生负面作用。在传统的OLTP系统中，大多数查询很可能几乎立即返回，而且大多数数据库获取可能都通过一个很小的索引区间扫描来完成。因此，以上所列分区性能方面可能的主要优点在OLTP系统中表现不出来。分区消除只在大对象全面扫描时才有用，因为通过分区消除，你可以避免对对象的很大部分做全面扫描。不过，在一个OLTP环境中，本来就不是对大对象全面扫描。即使对索引进行了分区，就算是真的能在速度上有所提高，通过扫描较小索引所得到的性能提升也是微乎其微的。如果某些查询使用了一个索引，而且它们根本无法消除任何分区，你可能会发现，完成分区之后查询实际上运行得反而更慢 了，因为你现在要扫描5、10或20个更小的索引，而不是一个较大的索引。
尽管如此，有分区的OLTP系统确实也有可能得到效率提示。例如，可以用分区来减少竞争，从而提高并发度。可以利用分区将一个表的修改分布到多个物理分区上。并不是只有一个表段和一个索引段，而是可以有10个表分区和20个索引分区。这就像有20个表而不是1个表，相应地，修改期间就能减少对这个共享资源的竞争。
至于并行操作，你可能不希望在一个OLTP系统中执行并行查询。你会慎用并行操作，而是交由DBA来完成重建、创建索引、收集统计信息等工作。事实上在一个OLTP系统中，查询已经有以下特点：即索引访问相当快，因此，分区不会让索引访问的速度有太大的提高（甚至根本没有任何提高）。这并不是说要绝对避免在OLTP系统中使用分区；而只是说不要指望通过分区来提供大幅的性能提升。尽管有效情况下分区能够改善查询的性能，但是这些情况在大多数OLTP应用中并不成立。不过在OLTP系统中，你还是可以得到另外两个可能的好处：减轻管理负担以及有更高的可用性。
数据仓库系统
在一个数据仓库/决策支持系统中，分区不仅是一个很强大的管理工具，还可以加快处理的速度。例如，有一个大表，需要在其中执行一个即席查询。总是按销售定额（sales quarter）执行即席查询，因为每个销售定额包含数十万条记录，而有数百万条在线记录。因此，你想查询整个数据集中相当小的一部分，但是基于销售定额来索引不太可行。这个索引会指向数十万条记录，以这种方式执行索引区间扫描会很糟糕。 处理许多查询时都要求执行一个全表扫描，但是最后却发现，一方面必须扫描数百万条记录，但另一方面其中大多数记录并不适用于我们的查询。如果使用一种明智的分区机制，就可以按销售定额来聚集数据，这样在查询某个给定销售定额的数据时，就可以只对这个销售定额的数据进行全面扫描。这在所有可能的解决方案中是 最佳的选择。
在一个数据仓库/决策支持环境中，会频繁地使用并行查询。因此，诸如并行索引区间扫描或并行快速全面索引扫描等操作不仅很有意义，而且对我们很有好处。我们希望充分地使用所有可用的资源，并行查询就提供了这样的一种途径。因此，在数据仓库环境中，分区就意味着很有可能会加快处理速度。
2 表分区机制
目前Oracle中有4种对表分区的方法：
区间分区：可以指定应当存储在一起的数据区间。例如，时间戳在Jan-2005内的所有记录都存储在分区1中，时间戳在Feb-2005内的所有记录都存储在分区2中，依此类推。这可能是Oracle中最常用的分区机制。
散列分区：这是指在一个列（或多个列）上应用一个散列函数，行会按这个散列值放在某个分区中。
列表分区：指定一个离散值集，来确定应当存储在一起的数据。例如，可以指定STATUS列值在（’A’,’M’,’Z’）中的行放在分区1中，STATUS值在（‘D’,’P’,’Q’）中的行放在分区2中，依此类推。
组合分区：这是区间分区和散列分区的一种组合，或者是区间分区与列表分区的组合。通过组合分区，你可以先对某些数据应用区间分区，再在区间中根据散列或列表来选择最后的分区。
2.1 区间分区
区间分区表（range partitioned table）。下面的CREATE TABLE语句创建了一个使用RANGE_KEY_COLUMN列的区间分区表。RANGE_KEY_COLUMN值严格小于01-JAN-2005的所有数据要放在分区PART_1中，RANGE_KEY_COLUMN值严格小于01-JAN-2006的所有数据则放在分区PART_2中。不满足这两个条件的所有数据（例如，RANGE_KEY_COLUMN值为01-JAN-2007的行）将不能插入，因为它们无法映射到任何分区：
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'))
)
/
表已创建。为了展示分区区间是严格小于某个值而不是小于或等于某个值，这里插入的行是特别选择的。我们首先插入值15-DEC-2004，它肯定要放在分区PART_1中。我们还插入了日期/时间为01-JAN-2005之前一秒（31-dec-2004 23：59：59）的行，这一行也会放到分区PART_1中，因为它小于01-JAN-2005。不过，插入的下一行日期/时间不是严格小于分区区间边界。最后一行显然应该放在分区PART_2中，因为它小于PART_2的分区区间边界。
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...' );
已创建 1 行。
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...' );
已创建 1 行。
insert into range_example
( range_key_column, data )
values
( to_date('31/12/2004 23:59:59',
'dd/mm/yyyy hh24:mi:ss' ),
'application data...' );
已创建 1 行。
可以从各个分区分别执行SELECT语句，来确认确实如此：
select to_char(range_key_column,'dd-mon-yyyy hh24:mi:ss')
from range_example partition (part_1);
TO_CHAR(RANGE_KEY_COLUMN,'DD-MON-YYYYHH24:MI:SS')
--------------------------------------------------
15-12月-2004 00:00:00
31-12月-2004 23:59:59
select to_char(range_key_column,'dd-mon-yyyy hh24:mi:ss')
from range_example partition (part_2);
TO_CHAR(RANGE_KEY_COLUMN,'DD-MON-YYYYHH24:MI:SS')
--------------------------------------------------
01-1月 -2005 00:00:00如果插入的日期超出上界会怎么样呢：
scott@ORCL>insert into range_example
2 ( range_key_column, data )
3 values
4 ( to_date('15/12/2007 00:00:00',
5 'dd/mm/yyyy hh24:mi:ss' ),
6 '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)
)
/现在，向这个表插入一个行时，这一行肯定会放入三个分区中的某一个分区中，而不会再拒绝任何行，因为分区PART_3可以接受不能放在PART_1或PART_2中的任何RANG_KEY_COLUMN值（即使RANGE_KEY_COLUMN值为null，也会插入到这个新分区中）。
2.2 散列分区
对一个表执行散列分区（hash partitioning）时，Oracle会对分区键应用一个散列函数，以此确定数据应当放在N个分区中的哪一个分区中。Oracle建议N是2的一个幂（2、4、8、16等），从而得到最佳的总体分布。
1. 散列分区如何工作
散列分区设计为能使数据很好地分布在多个不同设备（磁盘）上，或者只是将数据聚集到更可管理的块（chunk）上，为表选择的散列键应当是惟一的一个列或一组列，或者至少有足够多的相异值，以便行能在多个分区上很好地（均匀地）分布。
在这里，我们将创建一个有两个分区的散列表。在此使用名为HASH_KEY_COLUMN的列作为分区键。Oracle会取这个列中的值，并计算它的散列值，从而确定这一行将存储在哪个分区中：
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
)
/
表已创建。如果使用散列分区，将无从控制一行最终会放在哪个分区中。Oracle会应用散列函数，并根据散列的结果来确定行会放在哪里。行会按散列函数的“指示”放在某个分区中，也就是说，散列函数说这一行该放在哪个分区，它就会放在哪个分区中。如果改变散列分区的个数，数据会在所有分区中重新分布（向一个散列分区表增加或删除一个分区时，将导致所有数据都重写，因为现在每一行可能属于一个不同的分区）。
如果有一个大表，想对它“分而治之”，此时散列分区最有用。你不用管理一个大表，而只是管理8或16个 较小的“表”。从某种程度上讲，散列分区对于提高可用性也很有用；临时丢掉一个散列分区，就能访问所有余下的分区。 也许有些用户会受到影响，但是很有可能很多用户根本不受影响，但是很有可能很多用户根本不受影响。另外，恢复的单位现在也更小了。你不用恢复一个完整的大 表；而只需恢复表中的一小部分。最后一点，散列分区还有利于存在高度更新竞争的环境。我们可以不使一个段“很热”，而是可以将一个段散列分区为16个“部分”，这样一来，现在每一部分都可以接收修改。
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;
-- 现在，取这个查询，选出分区位置（PNAME）和该分区中的行数（CNT）。
-- 通过使用RPAD，可以构造一个相当基本但很有效的直方图：
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;
/
过程已创建。如果针对输入值4运行这个过程，这表示有4个散列分区，就会看到类似如下的输出：
scott@ORCL>variable x refcursor
scott@ORCL>set autoprint on
scott@ORCL>exec hash_proc( 4, :x );
PL/SQL 过程已成功完成。
PNAM CNT
---- ----------
HG
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------------------------
p1 17914
*****************************
p2 17923
*****************************
p3 18122
******************************
p4 17803
*****************************这个简单的直方图展示了数据很均匀地分布在这4个分区中。每个分区中的行数都很接近。不过，如果将4改成5，要求有5个散列分区，就会看到以下输出：
scott@ORCL>exec hash_proc( 5, :x );
PL/SQL 过程已成功完成。
PNAM CNT
---- ----------
HG
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------------------------
p1 9016
***************
p2 17925
*****************************
p3 18125
******************************
p4 17803
*****************************
p5 8898
**************
这个直方图指出，第一个和最后一个分区中的行数只是另外三个分区中行数的一半。数据根本没有得到均匀的分布。我们会看到，如果有6个和7个散列分区，这种趋势还会继续：
scott@ORCL>exec hash_proc( 6, :x );
PL/SQL 过程已成功完成。
PNAM CNT
---- ----------
HG
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------------------------
p1 9018
***************
p2 9088
***************
p3 18128
******************************
p4 17803
*****************************
p5 8898
**************
p6 8838
**************
已选择6行。
scott@ORCL>exec hash_proc( 7, :x );
PL/SQL 过程已成功完成。
PNAM CNT
---- ----------
HG
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------------------------
p1 9019
***************
p2 9089
***************
p3 9053
***************
p4 17805
******************************
p5 8899
***************
p6 8838
***************
p7 9077
***************
已选择7行。散列分区数再回到2的幂值（8）时，又能到达我们的目标，实现均匀分布：
scott@ORCL>exec hash_proc( 8, :x );
PL/SQL 过程已成功完成。
PNAM CNT
---- ----------
HG
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------------------------
p1 9019
*****************************
p2 9091
******************************
p3 9055
******************************
p4 8930
*****************************
p5 8900
*****************************
p6 8838
*****************************
p7 9079
******************************
p8 8876
*****************************
已选择8行。
再继续这个实验，分区最多达到16个，你会看到如果分区数为9～15，也存在同样的问题，中间的分区存放的数据多，而两头的分区中数据少，数据的分布是斜的；而达到16个分区时，你会再次看到数据分布是直的。再达到32个分区和64个分区时也是如此。这个例子只是要指出：散列分区数要使用2的幂，这一点非常重要。
2.3 列表分区
可以根据离散的值列表来指定一行位于哪个分区。如果能根据某个代码来进行分区（如州代码或区代码），这通常很有用。例如，你可能想把Maine州（ME）、New Hampshire州（NH）、Vermont州（VT）和Massachusetts州（MA）中所有人的记录都归至一个分区中，因为这些州相互之间挨得很近，而且你的应用按地理位置来查询数据。类似地，你可能希望将Connecticut州（CT）、Rhode Island州（RI）和New York州（NY）的数据分组在一起。
利用列表分区，我们可以很容易地完成这个定制分区机制：
create table list_example
( state_cd varchar2(2),
data varchar2(20)
)
partition by list(state_cd)
( partition part_1 values ( 'ME', 'NH', 'VT', 'MA' ),
partition part_2 values ( 'CT', 'RI', 'NY' )
)
/
表已创建。就像区间分区一样，如果我们想插入列表分区中未指定的一个值，Oracle会向客户应用返回一个合适的错误。换句话说，没有DEFAULT分区的列表分区表会隐含地施加一个约束（非常像表上的一个检查约束）：
scott@ORCL>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语句中使用这个子句）：
alter table list_example
add partition
part_3 values ( DEFAULT );
表已更改。
scott@ORCL>insert into list_example values ( 'VA', 'data' );
已创建 1 行。值列表中未显式列出的所有值都会放到这个（DEFAULT）分区中。
一旦列表分区表有一个 DEFAULT 分区，就不能再向这个表中增加更多的分区了：
scott@ORCL>alter table list_example
2 add partition
3 part_4 values( 'CA', 'NM' );
alter table list_example
*
第 1 行出现错误:
ORA-14323: 在 DEFAULT 分区已存在时无法添加分区
此时必须删除DEFAULT分区，增加PART_4，再加回DEFAULT分区。原因在于，原来DEFAULT分区可以有列表分区键值为CA或NM的行，但增加PART_4之后，这些行将不再属于DEFAULT分区。
2.4 组合分区
组合分区是区间分区和散列分区的组合，或者是区间分区与列表分区的组合。
在组合分区中，顶层分区机制总是区间分区。第二级分区机制可能是列表分区或散列分区。使用组合分区时，并没有分区段，而只有子分区段。分区本身并没有段（这就类似于分区表没有段）。数据物理的存储在子分区段上，分区成为一个逻辑容器，或者是一个指向实际子分区的容器。
在下面的例子中，我们将查看一个区间-散列组合分区，这两层分区也可以使用同样的列集：
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_2_sub_3 values ( 2, 4, 6, 8 )
)
)
/
表已创建。在此，最后总共有5个分区：分区PART_1有两个子分区，分区PART_2有3个子分区。
2.5 行移动
在前面所述的各种分区机制中，如果用于确定分区的列有修改会发生什么。需要考虑两种情况：
修改不会导致使用一个不同的分区；行仍属于原来的分区。这在所有情况下都得到支持。
修改会导致行跨分区移动。只有当表启用了行移动时才支持这种情况；否则，会产生一个错误。
在前面的例子中，我们向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...' );
已创建 1 行。
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...' );
已创建 1 行。
scott@ORCL>select * from range_example partition(part_1);
RANGE_KEY_COLU DATA
-------------- ----------------------------------------
15-12月-04 application data...
31-12月-04 application data...
15-12月-04 application data...
31-12月-04 application data...取其中一行，并更新其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' );
已更新2行。不出所料，这会成功：行仍在分区PART_1中。接下来，再把RANGE_KEY_COLUMN更新为另一个值，但这次更新后的值将导致它属于分区PART_2：
scott@ORCL>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');
update range_example
*
第 1 行出现错误:
ORA-14402: 更新分区关键字列将导致分区的更改这会立即产生一个错误，因为我们没有显式地启用行移动。
可以在这个表上启用行移动（row movement），以允许从一个分区移动到另一个分区。
这样做有一个小小的副作用；行的ROWID会由于更新而改变：
select rowid
from range_example
where range_key_column = to_date('31-12-2004','dd-MM-yyyy');
ROWID
------------------
AAAU6HAADAAAtjGAAB
AAAU6HAADAAAtjGAAD
alter table range_example enable row movement;
表已更改。
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');
已更新2行。
select rowid
from range_example
where range_key_column = to_date('02-01-2005','dd-MM-yyyy');
ROWID
------------------
AAAU6IAADAAAtjWAAB
AAAU6IAADAAAtjWAAC
既然知道执行这个更新时行的ROWID会改变，所以要启用行移动，这样才允许更新分区键。
执行行移动时，实际上在内部就好像先删除了这一行，然后再将其重新插入。这会更新这个表上的索引，删除旧的索引条目，再插入一个新条目。此时会完成DELETE再加一个INSERT的相应物理工作。不过，尽管在此执行了行的物理删除和插入，在Oracle看来却还是一个更新，因此，不会导致INSERT和DELETE触发器触发，只有UPDATE触发器会触发。另外，由于外键约束可能不允许DELETE的子表也不会触发DELETE触发器。不过，还是要对将完成的额外工作有所准备；行移动的开销比正常的UPDATE昂贵得多。
3 索引分区
索引与表类似，也可以分区。对索引进行分区有两种可能的方法：
随表对索引完成相应的分区：这也称为局部分区索引（locally pertitioned index）。每个表分区都有一个索引分区，而且只索引该表分区。一个给定索引分区中的所有条目都指向一个表分区，表分区中的所有行都表示在一个索引分区中。
按区间对索引分区：这也称为全局分区索引（globally partitioned index）。在此，索引按区间分区，一个索引分区可能指向任何（和所有）表分区。
对于全局分区索引，索引分区数可能不同于表分区数。
由于全局索引只按区间或散列分区，如果希望有一个列表或组合分区索引，就必须使用局部索引。局部索引会使用底层表相同的机制分区。
3.1 局部索引
Oracle 划分了以下两类局部索引：
D 局部前缀索引（local prefixed index）：在这些索引中，分区键在索引定义的前几列上。例如， 一个表在名为 LOAD_DATE 的列上进行区间分区，该表上的局部前缀索引就是 LOAD_DATE 作为其索引列列表中的第一列。
D 局部非前缀索引（local nonprefixed index）：这些索引不以分区键作为其列列表的前几列。 索引可能包含分区键列，也可能不包含。
这两类索引都可以利用分区消除，它们都支持惟一性（只有非前缀索引包含分区键）等。事实上，使用局部前缀索引的查询总允许索引分区消除，而使用局部非前缀索引的查询可能不允许。
3.1.1 分区消除行为
如果查询首先访问索引，它是否能消除分区完全取决于查询中的谓词。
下面的代码创建了一个表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 ：
scott@ORCL>create index local_prefixed on partitioned_table (a,b) local;
索引已创建。
scott@ORCL>create index local_nonprefixed on partitioned_table (b) local;
索引已创建。接下来，向一个分区中插入一些数据，并收集统计信息：
insert into partitioned_table
select mod(rownum-1,2)+1, rownum, 'x'
from all_objects;
已创建71816行。
begin
dbms_stats.gather_table_stats
( user,
'PARTITIONED_TABLE',
cascade=>TRUE );
end;
/
PL/SQL 过程已成功完成。将表空间P2离线，其中包含用于表和索引的PART_2分区：
scott@ORCL>alter tablespace p2 offline;
表空间已更改。表空间P2离线后，Oracle就无法访问这些特定的索引分区。
现在查询这个表，来看看不同的查询需要哪些索引分区。第一个查询编写为允许使用局部前缀索引：
scott@ORCL>select * from partitioned_table where a = 1 and b = 1;
A B DATA
---------- ---------- ----------------------------------------
1 1 x
这个查询成功了，查看解释计划，将使用内置包DBMS_XPLAN来查看这个查询访问了哪些分区。输出中的PSTART (分区开始)和PSTOP（分区结束）这两列准确地显示出，这个查询要想成功需要哪些分区必须在线而且可用：
scott@ORCL>delete from plan_table;
已删除0行。
scott@ORCL>explain plan for
2 select * from partitioned_table where a = 1 and b = 1;
已解释。
scott@ORCL>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 | 29 |
2 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE | | 1 | 29 |
2 (0)| 00:00:01 | 1 | 1 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| PARTITIONED_TABLE | 1 | 29 |
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。分区消除帮助了我们。不过，第二个查询却失败了：
scott@ORCL>select * from partitioned_table where b = 1;
ERROR:
ORA-00376: 此时无法读取文件 8
ORA-01110: 数据文件 8: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\P2.DBF'
通过使用同样的技术，可以看到这是为什么：
scott@ORCL>delete from plan_table;
已删除4行。
scott@ORCL>explain plan for
2 select * from partitioned_table where b = 1;
已解释。
scott@ORCL>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 | 29 |
4 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE ALL | | 1 | 29 |
4 (0)| 00:00:01 | 1 | 2 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| PARTITIONED_TABLE | 1 | 29 |
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都必须检查。在此，局部非前缀索引存在一个性能问题：它不能像前缀索引那样，在谓词中使用分区键；要使用非前缀索引，必须使用一个允许分区消除的查询。
scott@ORCL>drop index local_prefixed;
索引已删除。
scott@ORCL>select * from partitioned_table where a = 1 and b = 1;
A B DATA
---------- ---------- ----------------------------------------
1 1 x
它会成功，但是正如我们所见，这里使用了先前失败的索引。该计划显示出，在此Oracle能利用分区消除，有了谓词A=1，就有了足够的信息可以让数据库消除索引分区PART_2而不予考虑：
scott@ORCL>delete from plan_table;
已删除4行。
scott@ORCL>explain plan for
2 select * from partitioned_table where a = 1 and b = 1;
已解释。
scott@ORCL>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 | 29 |
2 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE | | 1 | 29 |
2 (0)| 00:00:01 | 1 | 1 |
|* 2 | TABLE ACCESS BY LOCAL INDEX ROWID| PARTITIONED_TABLE | 1 | 29 |
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）上的局部前缀索引只对第一个查询有用。
要尽可能保证查询包含的谓词允许索引分区消除。
3.1.2 局部索引和惟一约束
为了保证惟一性（这包括UNIQUE约束或PRIMARY KEY约束），如果你想使用一个局部索引来保证这个约束，那么分区键必须包括在约束本身中。Oracle只保证索引分区内部的惟一性，而不能跨分区。Oracle会利用全局索引来保证惟一性。
在下面的例子中，我们将创建一个区间分区表，它按一个名为LOAD_TYPE的列分区，却在ID列上有一个主键。为此，可以在一个没有任何其他对象的模式中执行以下CREATE TABLE语句，所以通过查看这个用户所拥有的每一个段，就能很容易地看出到底创建了哪些对象：
scott@ORCL> create user test identified by test;
用户已创建。
scott@ORCL> grant connect, resource to test;
授权成功。
scott@ORCL>connect test/test
已连接。
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') )
)
/
表已创建。
select segment_name, partition_name, segment_type
from user_segments;
SEGMENT_NAME
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--
PARTITION_NAME SEGMENT_TYPE
------------------------------------------------------------ -------------------
-----------------
PARTITIONED
PART_1 TABLE PARTITION
PARTITIONED
PART_2 TABLE PARTITION
PARTITIONED_PK
INDEX
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
/
select segment_name, partition_name, segment_type
from user_segments;
SEGMENT_NAME
--------------------------------------------------------------------------------
PARTITION_NAME SEGMENT_TYPE
------------------------------------------------------------ -------------------
PARTITIONED
PART_1 TABLE PARTITION
PARTITIONED
PART_2 TABLE PARTITION
PARTITIONED_IDX
PART_1 INDEX PARTITION
PARTITIONED_IDX
PART_2 INDEX PARTITION
alter table partitioned
add constraint
partitioned_pk
primary key(id)
/
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保证），以确保数据的完整性。这意味着，应用的逻辑模型会对物理设计产生影响。惟一性约束能决定底层的表分区机制，影响分区键的选择，或者指示你应该使用全局索引。
3.2 全局索引
全局索引使用一种有别于底层表的机制进行分区。表可以按一个TIMESTAMP列划分为10个分区，而这个表上的一个全局索引可以按REGION列划分为5个分区。与局部索引不同，全局索引 只能是 前缀全局索引（prefixed global index）。不论用什么属性对索引分区，这些属性都必须是索引键的前几列。
全局分区索引可以用于保证主键的惟一性，即使不包括表的分区键，也可以有能保证惟一性的分区索引。
下面的例子创建了一个按TIMESTAMP分区的表，它有一个按ID分区的索引：
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_index
on partitioned(id)
GLOBAL
partition by range(id)
(
partition part_1 values less than(1000),
partition part_2 values less than (MAXVALUE)
)
/
索引已创建。MAXVALUE可以不仅可以用于索引中，还可以用于任何区间分区表中。全局索引有一个需求，即最高分区（最后一个分区）必须有一个值为MAXVALUE的分区上界。这可以确保底层表中的所有行都能放在这个索引中。
下面，在这个例子的最后，我们将向表增加主键：
alter table partitioned add constraint
partitioned_pk
primary key(id)
/
表已更改。Oracle在使用我们创建的索引来保证主键，可以试着删除这个索引来证明这一点：
test@ORCL>drop index partitioned_index;
drop index partitioned_index
*
第 1 行出现错误:
ORA-02429: 无法删除用于强制唯一/主键的索引为了显示Oracle不允许创建一个非前缀全局索引，只需执行下面的语句：
test@ORCL>create index partitioned_index2
2 on partitioned(timestamp,id)
3 GLOBAL
4 partition by range(id)
5 (
6 partition part_1 values less than(1000),
7 partition part_2 values less than (MAXVALUE)
8 )
9 /
partition by range(id)
*
第 4 行出现错误:
ORA-14038: GLOBAL 分区索引必须加上前缀全局索引必须是前缀索引。
要在什么时候使用全局索引呢？分析两种不同类型的系统（数据仓库和OLTP）。
3.2.1 数据仓库和全局索引
3.2.1.1 数据仓库和全局索引
原先数据仓库和全局索引是相当互斥的。数据仓库就意味着系统有某些性质，如有大量的数据出入。许多数据仓库都实现了一种滑动窗口（sliding window）方法来管理数据，也就是说，删除表中最旧的分区，并为新加载的数据增加一个新分区。
滑动窗口和索引
在许多实现中，会随着时间的推移向仓库中增加数据，而最旧的数据会老化。在很多时候，这个数据会按一个日期属性进行区间分区，所以最旧的数据多存储在一个分区中，新加载的数据很可能都存储在一个新分区中。每月的加载过程涉及：
去除老数据：最旧的分区要么被删除，要么与一个空表交换（将最旧的分区变为一个表），从而允许对旧数据进行归档。
加载新数据并建立索引：将新数据加载到一个“工作”表中，建立索引并进行验证。
关联新数据：一旦加载并处理了新数据，数据所在的表会与分区表中的一个空分区交换，将表中的这些新加载的数据变成分区表中的一个分区（分区表会变得更大）。
这个过程会没有重复，或者执行加载过程的任何周期重复；可以是每天或每周。
在这个例子中，我们将处理每年的数据，并加载2004和2005财政年度的数据。这个表按TIMESTAMP列分区，并创建了两个索引，一个是ID列上的局部分区索引，另一个是TIMESTAMP列上的全局索引（这里为分区）：
CREATE TABLE partitioned
( timestamp date,
id int
)
PARTITION BY RANGE (timestamp)
(
PARTITION fy_2004 VALUES LESS THAN ( to_date('01-01-2005','dd-MM-yyyy') ) ,
PARTITION fy_2005 VALUES LESS THAN ( to_date('01-01-2006','dd-MM-yyyy') )
)
/
insert into partitioned partition(fy_2004)
select to_date('31-12-2004','dd-MM-yyyy')-mod(rownum,360), object_id
from all_objects
/
insert into partitioned partition(fy_2005)
select to_date('31-12-2005','dd-MM-yyyy')-mod(rownum,360), object_id
from all_objects
/
create index partitioned_idx_local
on partitioned(id)
LOCAL
/
create index partitioned_idx_global
on partitioned(timestamp)
GLOBAL
/这就建立了我们的“仓库”表。数据按财政年度分区，而且最后两年的数据在线。这个表有两个索引：一个是LOCAL索引，另一个是GLOBAL索引。我们想做下面的工作：
（1） 删除最旧的财政年度数据。我们不想永远地丢掉这个数据，而只是希望它老化，并将其归档。
（2） 增加最新的财政年度数据。加载、转换、建索引等工作需要一定的时间。我们想做这个工作，但是希望尽可能不影响当前数据的可用性。
第一步是为2004财政年度建立一个看上去就像分区表的空表。我们将使用这个表与分区表中的FY_2004分区交换，将这个分区转变成一个表，相应地是分区表中的分区为空。这样做的效果就是分区表中最旧的数据（实际上）会在交换之后被删除：
test@ORCL>create table fy_2004 ( timestamp date, id int );
表已创建。
test@ORCL>create index fy_2004_idx on fy_2004(id) ;
索引已创建。对要加载的新数据做同样的工作。我们将创建并加载一个表，其结构就像是现在的分区表（但是它本身并不是分区表）：
test@ORCL>create table fy_2006 ( timestamp date, id int );
表已创建。
insert into fy_2006
select to_date('31-12-2006','dd-MM-yyyy')-mod(rownum,360), object_id
from all_objects
/
已创建55717行。
test@ORCL>create index fy_2006_idx on fy_2006(id) nologging ;
索引已创建。我们将当前的满分区变成一个空分区，并创建了一个包含FY_2004数据的“慢”表。而且，我们完成了使用FY_2006数据的所有必要工作，这包括验证数据、进行转换以及准备这些数据所需完成的所有复杂任务。
现在可以使用一个交换分区来更新“活动”数据：
alter table partitioned
exchange partition fy_2004
with table fy_2004
including indexes
without validation
/
alter table partitioned
drop partition fy_2004
/要把旧数据“老化”，所要做的仅此而已。我们将分区变成一个满表，而将空表变成一个分区。这是一个简单的数据字典更新，瞬时就会完成，而不会发生大量的I/O。现在可以将FY_2004表从数据库中导出（可能要使用一个可移植的表空间）来实现归档。如果需要，还可以很快地重新关联这些数据。
接下来，我们想“滑入”（即增加）新数据：
alter table partitioned
add partition fy_2006
values less than ( to_date('01-01-2007','dd-MM-yyyy') )
/
alter table partitioned
exchange partition fy_2006
with table fy_2006
including indexes
without validation
/同样，这个工作也会立即完成；这是通过简单的数据字典更新实现的。增加空分区几乎不需要多少时间来处理。然后，将新创建的空分区与满表交换（满表与空分区交换），这个操作也会很快完成。新数据是在线的。
不过，通过查看索引，可以看到下面的结果：
test@ORCL>select index_name, status from user_indexes;
INDEX_NAME STATUS
------------------------------------------------------------ ----------------
FY_2006_IDX VALID
FY_2004_IDX VALID
PARTITIONED_IDX_LOCAL N/A
PARTITIONED_IDX_GLOBAL UNUSABLE当 然，在这个操作之后，全局索引是不可用的。由于每个索引分区可能指向任何表分区，而我们刚才取走了一个分区，并增加了一个分区，所以这个索引已经无效了。 其中有些条目指向我们已经生成的分区，却没有任何条目指向刚增加的分区。使用了这个索引的任何查询可能会失败而无法执行，或者如果我们跳过不可用的索引， 尽管查询能执行，但查询的性能会受到负面影响（因为无法使用这个索引）：
test@ORCL>set autotrace on explain
test@ORCL>select /*+ index( partitioned PARTITIONED_IDX_GLOBAL ) */ count(*)
2 from partitioned
3 where timestamp between sysdate-50 and sysdate ;
select /*+ index( partitioned PARTITIONED_IDX_GLOBAL ) */ count(*)
*
第 1 行出现错误:
ORA-01502: 索引 'TEST.PARTITIONED_IDX_GLOBAL' 或这类索引的分区处于不可用状态
select count(*)
from partitioned
where timestamp between sysdate-50 and sysdate;因此，执行这个分区操作后，对于全局索引，我们有以下选择：
跳过索引，设置会话参数SKIP_UNUSABLE_INDEXES=TRUE来跳过索引。但是这样一来，就丢失了索引所提供的性能提升。
让查询接收到一个错误，就像9i中一样（SKIP_UNUSABLE_INDEX设置为FALSE），在10g中，显式地请求使用提示的任何查询都会接收到错误。要想让数据再次真正可用，必须重建这个索引。
到此为止滑动窗口过程几乎不会带来任何停机时间，但是在我们重建全局索引时，需要相当长的时间才能完成。如果查询依赖于这些索引，在此期间它们的运行时查询 性能就会受到负面影响，可能根本不会运行，也可能运行时得不到索引提供的好处。所有数据都必须扫描，而且要根据数据重建整个索引。如果表的大小为数百DB，这会占用相当多的资源。
“活动”全局索引维护
可以在分区操作期间使用UPDATE GLOBAL INEXES子句来维护全局索引。这意味着，在你删除一个分区、分解一个分区以及在分区上执行任何必要的操作时，Oracle会对全局索引执行必要的修改，保证它是最新的。由于大多数分区操作都会导致全局索引无效，这个特征对于需要提供数据连续访问的系统来说是一个大福音。你会发现，通过牺牲分区操作的速度，可以换取100%的数据可用性（尽管分区操作的总体响应时间会更慢）。简单地说，如果数据仓库不允许有停机时间，而且必须支持数据的滑入滑出等数据仓库技术，这个特性就再合适不过了。
再来看前面的例子，如果分区操作在必要时使用了UPDATE GLOBAL INDEXES子句（在这个例子中，在ADD PARTITION语句上就没有必要使用这个子句，因为新增加的分区中没有任何行）：
create table fy_2018 ( timestamp date, id int );
insert into fy_2018
select to_date('31-12-2018','dd-MM-yyyy')-mod(rownum,360), object_id
from all_objects
/
create index fy_2018_idx on fy_2018(id) nologging ;
alter table partitioned
add partition fy_2018 values less than ( to_date('01-01-2018','dd-MM-yyyy'))
/
alter table partitioned
exchange partition fy_2018
with table fy_2018
including indexes
without validation
UPDATE GLOBAL INDEXES
/
alter table partitioned
drop partition fy_2018
UPDATE GLOBAL INDEXES
/就会发现索引完全有效，不论在操作期间还是操作之后这个索引都是可用的：
test@ORCL>select index_name, status from user_indexes;
INDEX_NAME STATUS
------------------------------------------------------------ ----------------
FY_2018_IDX VALID
FY_2006_IDX VALID
FY_2004_IDX VALID
PARTITIONED_IDX_LOCAL N/A
PARTITIONED_IDX_GLOBAL UNUSABLE
test@ORCL>set autotrace on explain
test@ORCL>select count(*)
2 from partitioned
3 where timestamp between sysdate-50 and sysdate;
COUNT(*)
----------
0
执行计划
----------------------------------------------------------
Plan hash value: 2869581836
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time | Pstart| Pstop |
--------------------------------------------------------------------------------
--------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 84 (6)|
00:00:02 | | |
| 1 | SORT AGGREGATE | | 1 | 9 | |
| | |
|* 2 | FILTER | | | | |
| | |
| 3 | PARTITION RANGE ITERATOR| | 3 | 27 | 84 (6)|
00:00:02 | KEY | KEY |
|* 4 | TABLE ACCESS FULL | PARTITIONED | 3 | 27 | 84 (6)|
00:00:02 | KEY | KEY |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(SYSDATE@!-50<=SYSDATE@!)
4 - filter("TIMESTAMP"<=SYSDATE@! AND "TIMESTAMP">=SYSDATE@!-50)
Note
-----
- dynamic sampling used for this statement (level=2)但是这里要做一个权衡：我们要在全局索引结构上执行INSERT和DELETE操作的相应逻辑操作。删除一个分区时，必须删除可能指向该分区的所有全局索引条目。执行表与分区的交换时，必须删除指向原数据的所有全局索引条目，再插入指向刚滑入的数据的新条目。所以ALTER命令执行的工作量会大幅增加。
通过使用runstats，能测量出分区操作期间维护全局索引所执行的“额外”工作量。
我们将滑出FY_2004，并滑入FY_2006，这就必须加入索引重建。由于需要重建全局索引，因此滑动窗口实现将导致数据变得不可用。然后我们再滑出FY_2005，并滑入FY_2007，不过这一次将使用UPDATE GLOBAL INDEXES子 句，来模拟提供完全数据可用性的滑动窗口实现。这样一来，即使在分区操作期间，数据也是可用的。采用这种方式，我们就能测量出使用不同技术实现相同操作的性能，并对它们进行比较。我们期望的结果是，第一种方法占用的数据库资源更少，因此会完成得“更快”，但是会带来显著的“停机时间”。第二种方法尽管会占用更多的资源，而且总的来说可能需要花费更长的时间才能完成，但是不会带来任何停机时间。
因此，如果用前面的例子，不过另外创建一个类似FY_2004的空FY_2005表，并创建一个类似FY_2006的满FY_2007表，这样就可以测量索引重建方法之间有什么差别，先来看“不太可用的方法”：
exec runStats_pkg.rs_start;
CREATE TABLE partitioned
( timestamp date,
id int
)
PARTITION BY RANGE (timestamp)
(
PARTITION fy_2004 VALUES LESS THAN ( to_date('01-01-2005','dd-MM-yyyy')) ,
PARTITION fy_2005 VALUES LESS THAN ( to_date('01-01-2006','dd-MM-yyyy'))
)
/
insert into partitioned partition(fy_2004)
select to_date('31-12-2004','dd-MM-yyyy')-mod(rownum,360), object_id
from all_objects
/
insert into partitioned partition(fy_2005)
select to_date('31-12-2005','dd-MM-yyyy')-mod(rownum,360), object_id
from all_objects
/
create index partitioned_idx_local
on partitioned(id)
LOCAL
/
create index partitioned_idx_global
on partitioned(timestamp)
GLOBAL
/
*********************************************************
create table fy_2004 ( timestamp date, id int );
create index fy_2004_idx on fy_2004(id) ;
alter table partitioned exchange partition fy_2004
with table fy_2004 including indexes without validation
/
alter table partitioned drop partition fy_2004;
*********************************************************
alter table partitioned add partition fy_2006
values less than ( to_date('01-01-2007','dd-MM-yyyy') );
create table fy_2006 ( timestamp date, id int );
insert into fy_2006
select to_date('31-12-2006','dd-MM-yyyy')-mod(rownum,360), object_id
from all_objects
/
create index fy_2006_idx on fy_2006(id) nologging ;
alter table partitioned exchange partition fy_2006
with table fy_2006 including indexes without validation;
*********************************************************
alter index partitioned_idx_global rebuild;
exec runStats_pkg.rs_middle;
下面是可以提供高度可用性的UPDATE GLOBAL INDEXES方法：
create table fy_2005 ( timestamp date, id int );
create index fy_2005_idx on fy_2005(id) ;
alter table partitioned exchange partition fy_2005
with table fy_2005 including indexes without validation
update global indexes;
alter table partitioned drop partition fy_2005
update global indexes;
*******************************************************
alter table partitioned add partition fy_2007
values less than ( to_date('01-01-2008','dd-mm-yyyy') );
create table fy_2007 ( timestamp date, id int );
insert into fy_2007
select to_date('31-12-2007','dd-MM-yyyy')-mod(rownum,360), object_id
from all_objects
/
create index fy_2007_idx on fy_2007(id) nologging ;
alter table partitioned exchange partition fy_2007
with table fy_2007 including indexes without validation
update global indexes;
exec runStats_pkg.rs_stop;可以观察到以下结果：
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
1,437,419 540,149 -897,270 266.12%
索引重建方法确实运行得更快一些，从观察到的耗用时间和CPU时间可见一斑。
查看这种方法生成的redo时，可以看到UPDATE GLOBAL INDEXES生成的redo会多出许多，它是索引创建方法的230%，而且可以想见，随着为表增加越来越多的全局索引，UPDATE GLOBAL INDEXES生成的redo数量还会进一步增加。UPDATE GLOBAL INDEXES生成的redo是不可避免的，不能通过NOLOGGING去掉，因为全局索引的维护不是其结构的完全重建，而应该算是一种增量式“维护”。另外，由于我们维护着活动索引结构，必须为之生成undo，万一分区操作失败，必须准备好将索引置回到它原来的样子。而且要记住，undo受redo本身的保护，因此你看到的所生成的redo中，有些来自索引更新，有些来自回滚。如果增加另一个（或两个）全局索引，可以很自然地想见这些数据量会增加。
UPDATE GLOBAL INDEXES是一种允许用资源耗费的增加来换取可用性的选项，可以提供连续的可用性。
3.2.1.2 OLTP和全局索引
OLTP系统的特点是会频繁出现许多小的读写事务，一般来讲，在OLTP系统中，首要的是需要快速访问所需的行，而且数据完整性很关键，另外可用性也非常重要。
在OLTP系统中，许多情况下全局索引很有意义。表数据可以按一个键（一个列键）分区。不过，可能需要以多种不同的方式访问数据。
这里需要按多种不同的键来访问应用中不同位置的EMPLOYEE数据，而且速度至上。
快速访问
数据完整性
可用性
在一个OLTP系统中，可以通过全局索引实现这些目标。
以下例子 显示了如何用全局索引来达到以上所列的3个目标。这里使用简单的“单分区”全局索引，但是这与多个分区情况下的全局索引也没有不同（只有一点除外，增加索引分区时，可用性和可管理性会提高）。先创建一个表，它按位置LOC执行区间分区，根据我们的规则，这会把所有小于‘C’的LOC值放在分区P1中，小于’D‘的LOC值则放在分区P2中，依此类推：
alter tablespace p1 online;
alter tablespace p2 online;
create tablespace P3 datafile 'D:\app\Administrator\oradata\orcl\P3.dbf'
size 40m autoextend on maxsize 4G;
create tablespace P4 datafile 'D:\app\Administrator\oradata\orcl\P4.dbf'
size 40m autoextend on maxsize 4G;
***********************************************************************
create table emp
(EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) NOT NULL,
LOC VARCHAR2(13) NOT NULL
)
partition by range(loc)
(
partition p1 values less than('C') tablespace p1,
partition p2 values less than('D') tablespace p2,
partition p3 values less than('N') tablespace p3,
partition p4 values less than('Z') tablespace p4
)
/接下来修改这个表，在主键列上增加一个约束：
alter table emp add constraint emp_pk
primary key(empno)
/EMPNO列上将有一个惟一索引，可以支持和保证数据完整性。
在DEPTNO和JOB上创建另外两个全局索引，以便通过这些属性快速地访问记录：
create index emp_job_idx on emp(job)
GLOBAL
/
create index emp_dept_idx on emp(deptno)
GLOBAL
/
insert into emp
select e.EMPNO,e.ENAME,e.JOB,e.MGR,e.HIREDATE,e.SAL,e.COMM,e.DEPTNO,d.LOC
from scott.emp_bak e, scott.dept_bak d
where e.deptno = d.deptno
/现在来看每个分区中有什么：
select 'p1' pname, empno, job, loc from emp partition(p1)
union all
select 'p2' pname, empno, job, loc from emp partition(p2)
union all
select 'p3' pname, empno, job, loc from emp partition(p3)
union all
select 'p4' pname, empno, job, loc from emp partition(p4)
/
PNAM EMPNO JOB LOC
---- ---------- ------------------ --------------------------
p2 7499 SALESMAN CHICAGO
7521 SALESMAN CHICAGO
7654 SALESMAN CHICAGO
7698 MANAGER CHICAGO
7844 SALESMAN CHICAGO
7900 CLERK CHICAGO
p3 7369 CLERK DALLAS
7566 MANAGER DALLAS
7788 ANALYST DALLAS
7876 CLERK DALLAS
7902 ANALYST DALLAS
p4 7782 MANAGER NEW YORK
7839 PRESIDENT NEW YORK
7934 CLERK NEW YORK
已选择14行。这显示了数据按位置在各个分区中的分布。现在可以检查一些查询计划，来查看会有怎样的性能：
variable x varchar2(30);
begin
dbms_stats.set_table_stats
( user, 'EMP', numrows=>100000, numblks => 10000 );
end;
/
*******************************************************
delete from plan_table;
explain plan for
select empno, job, loc from emp where empno = :x;
*******************************************************
abc@ORCL>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
----------------------------------------
Plan hash value: 3656192650
--------------------------------------------------------------------------------
-----------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU
)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------
-----------------------------
| 0 | SELECT STATEMENT | | 1 | 27 | 0 (0
)| 00:00:01 | | |
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| EMP | 1 | 27 | 0 (0
)| 00:00:01 | ROWID | ROWID |
|* 2 | INDEX UNIQUE SCAN | EMP_PK | 1 | | 0 (0
)| 00:00:01 | | |
--------------------------------------------------------------------------------
-----------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=TO_NUMBER(:X))
已选择14行。这里的计划显示出对未分区索引EMP_PK（为支持主键所创建）有一个INDEX UNIQUE SCAN。然后还有一个TABLE ACCESS GLOBAL INDEX ROWID，其PSTART和PSTOP为ROWID/ROWID，这说明从索引得到ROWID时，它会准确地告诉我们读哪个索引分区来得到这一行。这个索引访问与未分区表上的访问同样有效，而且为此会执行同样数量的I/O。这只是一个简单的单索引惟一扫描，其后是“根据ROWID来得到这一行”。现在，我们来看一个全局索引，即JOB上的全局索引：
abc@ORCL>delete from plan_table;
已删除3行。
abc@ORCL>explain plan for
2 select empno, job, loc from emp where job = :x;
已解释。
abc@ORCL>select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------------------------
Plan hash value: 475001586
--------------------------------------------------------------------------------
----------------------------------
| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------
----------------------------------
| 0 | SELECT STATEMENT | | 1000 | 27000 | 1
(0)| 00:00:01 | | |
| 1 | TABLE ACCESS BY GLOBAL INDEX ROWID| EMP | 1000 | 27000 | 1
(0)| 00:00:01 | ROWID | ROWID |
|* 2 | INDEX RANGE SCAN | EMP_JOB_IDX | 1 | | 1
(0)| 00:00:01 | | |
--------------------------------------------------------------------------------
----------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("JOB"=:X)
已选择14行。当然，对于INDEX RANGE SCAN，可以看到类似的结果。在此使用了我们的索引，而且可以对底层数据提供高速的OLTP访问。如果索引进行了分区，则必须是前缀索引，并保证索引分区消除；因此，这些索引也是可扩缩的，这说明我们可以对其分区，而且能观察到类似的行为。
最后，下面来看可用性方面，全局分区索引与局部分区索引有着同样的高度可用性。考虑以下例子：
abc@ORCL>alter tablespace p1 offline;
表空间已更改。
abc@ORCL>alter tablespace p2 offline;
表空间已更改。
abc@ORCL>alter tablespace p3 offline;
表空间已更改。
abc@ORCL>select empno, job, loc from emp where empno = 7782;
EMPNO JOB LOC
---------- ------------------ --------------------------
7782 MANAGER NEW YORK
即使表中大多数底层数据都不可用，还是可以通过索引访问任何可用的数据。只要我们想要的EMPNO在可用的表空间中，而且GLOBAL索引可用，就可以利用GLOBAL索引来访问数据。
不过，在这种情况下，其他类型的查询不会（而且不能）工作：
abc@ORCL>select empno, job, loc from emp where job = 'CLERK';
select empno, job, loc from emp where job = 'CLERK'
*
第 1 行出现错误:
ORA-00376: 此时无法读取文件 8
ORA-01110: 数据文件 8: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\P2.DBF'所有分区中都有CLERK数据，由于3个表空间离线，这一点确实会对我们带来影响。
如果可以由索引来回答查询，就要避免TABLE ACCESS BY ROWID，数据不可用的事实并不重要：
abc@ORCL>select count(*) from emp where job = 'CLERK';
COUNT(*)
----------
4
在这种情况下，由于Oracle并不需要表，大多数分区离线的事实也不会影响这个查询。由于OLTP系统中这种优化（即只使用索引来回答查询）很常见，所以很多应用都不会因为数据离线而受到影响。现在所要做的只是尽快地让离线数据可用（将其恢复）。
4 再论分区和性能
在一个数据仓库中，如果查询频繁地全面扫描很大的数据表，通过消除大段的数据，分区能够对这些查询有很好的影响。假设你有一个100万行的表，其中有一个时间戳属性。你的查询要从这个表中获取一年的数据（其中有10年的数据）。查询使用了一个全表扫描来获取这个数据。如果按时间戳分区，例如每个月一个分区，就可以只对1/10的数据进行全面扫描（假设各年的数据是均匀分布的）。通过分区消除，90%的数据都可以不考虑，查询往往会运行得更快。
现在，再来看如果OLTP系统中有一个类似的表。在这种应用中，你肯定不会获取100万行表中10%的数据，因此，尽管数据仓库中可以得到大幅的速度提升，但这种提升在事务性系统中得不到。因此，一般来说，在OLTP系统中达不到第一种情况（不会是查询更快），你不会主要因为提供性能而应用分区。就算是要应用分区，也往往是为了提供可用性以及得到管理上的易用性。在一个OLTP系统中，即使是要确保达到第二点（也就是说，对查询的性能没有影响，而不论是负面影响还是正面影响），也并非轻而易举，而需要付出努力。很多时候，你的目标可能只是应用分区而不影响查询响应时间。
例如有1000万行，决定将数据分区。但是通过查看数据，却发现没有哪个属性可以用于区间分区（RANGE partitioning）。根本没有合适的属性来执行区间分区。同样，列表分区（LIST partitioning）也不可行。所以，想对主键执行散列分区，而主键恰好填充为一个Oracle序号，主键是惟一的，而且易于散列，另外很多查询都有以下形式：SELECT * FROM T WHERE PRIMARY_KEY = :X。
但是，对这个对象还有另外一些并非这种形式的查询，假设当前表实际上是ALL_OBJECTS字典视图，尽管在内部许多查询的形式都是WHERE OBJECT_ID = :X，但最终用户还会频繁地对应用发出以下请求：
显示SCOTT中EMP表的详细信息（WHERE OWNER=:0 AND OBJECT_TYPE=:T AND OBJECT_NAME=:N）。
显示SCOTT所拥有的所有表（WHERE OWNER=:0 AND OBJECT_TYPE=:T）。
显示SCOTT所拥有的所有对象（WHERE OWNER=:0）.
为了支持这些查询，在（OWNER.OBJECT_TYPE.OBJECT_NAME）上有一个局部索引。
最后将表重建如下，它有16个散列分区：
create table t
partition by hash(object_id)
partitions 16
as
select * from all_objects;
create index t_idx
on t(owner,object_type,object_name)
LOCAL
/
begin
dbms_stats.gather_table_stats
( user, 'T', cascade=>true);
end;
/接下来执行经典的OLTP查询（你知道这些查询会频繁地运行）：
variable o varchar2(30)
variable t varchar2(30)
variable n varchar2(30)
exec :o := 'SCOTT'; :t := 'TABLE'; :n := 'EMP';
select *
from t
where owner = :o
and object_type = :t
and object_name = :n
/
select *
from t
where owner = :o
and object_type = :t
/
select *
from t
where owner = :o
/但是可以注意到，运行以上代码时，查看所得到的TKPROF报告会有以下性能特征：
select *
from t
where owner = :o
and object_type = :t
and object_name = :n
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.02 0 32 0 0
Rows Row Source Operation
------- ---------------------------------------------------
0 PARTITION HASH ALL PARTITION: 1 16 (cr=32 pr=0 pw=0 time=0 us cost=18 size=97 card=1)
0 TABLE ACCESS BY LOCAL INDEX ROWID T PARTITION: 1 16 (cr=32 pr=0 pw=0 time=0 us cost=18 size=97 card=1)
0 INDEX RANGE SCAN T_IDX PARTITION: 1 16 (cr=32 pr=0 pw=0 time=0 us cost=17 size=0 card=1)(object id 85931)
********************************************************************************
select *
from t
where owner = :o
and object_type = :t
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.00 0.00 0 54 0 39
Rows Row Source Operation
------- ---------------------------------------------------
39 PARTITION HASH ALL PARTITION: 1 16 (cr=54 pr=0 pw=0 time=0 us cost=59 size=5820 card=60)
39 TABLE ACCESS BY LOCAL INDEX ROWID T PARTITION: 1 16 (cr=54 pr=0 pw=0 time=34 us cost=59 size=5820 card=60)
39 INDEX RANGE SCAN T_IDX PARTITION: 1 16 (cr=35 pr=0 pw=0 time=4 us cost=17 size=0 card=60)(object id 85931)
********************************************************************************
select *
from t
where owner = :o
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 15 0.00 0.00 0 1091 0 175
Rows Row Source Operation
------- ---------------------------------------------------
175 PARTITION HASH ALL PARTITION: 1 16 (cr=1091 pr=0 pw=0 time=0 us cost=313 size=232315 card=2395)
175 TABLE ACCESS FULL T PARTITION: 1 16 (cr=1091 pr=0 pw=0 time=69 us cost=313 size=232315 card=2395)这个查询必须查看每一个索引分区，因为对应SCOTT的条目可以在每一个索引分区中。索引按OBJECT_ID执行逻辑散列分区，所以如果查询使用了这个索引，但在谓词中没有引用OBJECT_ID，所有这样的查询都必须考虑每一个索引分区！
与未实现分区的同一个表相比较，会发现以下结果：
select *
from t
where owner = :o
and object_type = :t
and object_name = :n
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.02 0 1033 0 0
Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS FULL T (cr=1033 pr=0 pw=0 time=0 us cost=287 size=97 card=1)
********************************************************************************
select *
from t
where owner = :o
and object_type = :t
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.01 0.00 0 1036 0 38
Rows Row Source Operation
------- ---------------------------------------------------
38 TABLE ACCESS FULL T (cr=1036 pr=0 pw=0 time=0 us cost=287 size=5820 card=60)
********************************************************************************
select *
from t
where owner = :o
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 16 0.00 0.00 0 1046 0 189
Rows Row Source Operation
------- ---------------------------------------------------
189 TABLE ACCESS FULL T (cr=1046 pr=0 pw=0 time=0 us cost=287 size=232412 card=2396)是对索引执行全局分区。例如，继续看这个T_IDX例子，可以选择对索引进行散列分区，Oracle会取OWNER值，将其散列到1～16之间的一个分区，并把索引条目放在其中。现在，再次查看这3个查询的TKPROF信息【表未分区--索引分区】：
create index t_idx
on t(owner,object_type,object_name)
global
partition by hash(owner)
partitions 16
/
select *
from t
where owner = :o
and object_type = :t
and object_name = :n
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.01 0.03 1 2 0 0
Rows Row Source Operation
------- ---------------------------------------------------
0 PARTITION HASH SINGLE PARTITION: KEY KEY (cr=2 pr=1 pw=0 time=0 us cost=2 size=97 card=1)
0 TABLE ACCESS BY INDEX ROWID T (cr=2 pr=1 pw=0 time=0 us cost=2 size=97 card=1)
0 INDEX RANGE SCAN T_IDX PARTITION: KEY KEY (cr=2 pr=1 pw=0 time=0 us cost=1 size=0 card=1)(object id 85949)
********************************************************************************
select *
from t
where owner = :o
and object_type = :t
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.00 0.00 0 29 0 38
Rows Row Source Operation
------- ---------------------------------------------------
38 PARTITION HASH SINGLE PARTITION: KEY KEY (cr=29 pr=0 pw=0 time=0 us cost=45 size=5820 card=60)
38 TABLE ACCESS BY INDEX ROWID T (cr=29 pr=0 pw=0 time=0 us cost=45 size=5820 card=60)
38 INDEX RANGE SCAN T_IDX PARTITION: KEY KEY (cr=5 pr=0 pw=0 time=185 us cost=2 size=0 card=60)(object id 85949)
********************************************************************************
select *
from t
where owner = :o
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 16 0.01 0.00 0 1046 0 189
Rows Row Source Operation
------- ---------------------------------------------------
189 TABLE ACCESS FULL T (cr=1046 pr=0 pw=0 time=0 us cost=287 size=232412 card=2396)【表分区--索引分区】
create table t
partition by hash(object_id)
partitions 16
as
select * from all_objects;
create index t_idx
on t(owner,object_type,object_name)
global
partition by hash(owner)
partitions 16
/select *
from t
where owner = :o
and object_type = :t
and object_name = :n
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.00 0.00 0 2 0 0
Rows Row Source Operation
------- ---------------------------------------------------
0 PARTITION HASH SINGLE PARTITION: KEY KEY (cr=2 pr=0 pw=0 time=0 us cost=1 size=158 card=1)
0 TABLE ACCESS BY GLOBAL INDEX ROWID T PARTITION: ROW LOCATION ROW LOCATION (cr=2 pr=0 pw=0 time=0 us cost=1 size=158 card=1)
0 INDEX RANGE SCAN T_IDX PARTITION: KEY KEY (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 85983)
********************************************************************************
select *
from t
where owner = :o
and object_type = :t
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.00 0.00 0 41 0 38
Rows Row Source Operation
------- ---------------------------------------------------
38 PARTITION HASH SINGLE PARTITION: KEY KEY (cr=41 pr=0 pw=0 time=0 us cost=33 size=6004 card=38)
38 TABLE ACCESS BY GLOBAL INDEX ROWID T PARTITION: ROW LOCATION ROW LOCATION (cr=41 pr=0 pw=0 time=0 us cost=33 size=6004 card=38)
38 INDEX RANGE SCAN T_IDX PARTITION: KEY KEY (cr=5 pr=0 pw=0 time=0 us cost=2 size=0 card=38)(object id 85983)
********************************************************************************
select *
from t
where owner = :o
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 18 0.00 0.00 0 204 0 221
Rows Row Source Operation
------- ---------------------------------------------------
221 PARTITION HASH SINGLE PARTITION: KEY KEY (cr=204 pr=0 pw=0 time=660 us cost=178 size=34918 card=221)
221 TABLE ACCESS BY GLOBAL INDEX ROWID T PARTITION: ROW LOCATION ROW LOCATION (cr=204 pr=0 pw=0 time=440 us cost=178 size=34918 card=221)
221 INDEX RANGE SCAN T_IDX PARTITION: KEY KEY (cr=18 pr=0 pw=0 time=0 us cost=3 size=0 card=221)(object id 85983)散列分区索引无法执行区间扫描。一般来说，它最适于完全相等性比较（是否相等或是否在列表中）。如果想使用前面的索引来查询WHERE OWNER > :X，就无法使用分区消除来执行一个简单的区间扫描，你必须退回去检查全部的16个散列分区。
一般来讲，对于OLTP中的数据获取，分区确实没有正面的影响。但是对于高度并发环境中的数据修改，分区则可能提供显著的好处。
考虑一个相当简单的例子，有一个表，而且只有一个索引，在这个表中再增加一个主键。如果没有分区，实际上这里只有一个表：所有插入都会插入到这个表中。对这个表的freelist可能存在竞争。另外，OBJECT_ID列上的主键索引是一个相当“重”的右侧索引。假设主键列由一个序列来填充；因此，所有插入都会放到最右边的块中，这就会导致缓冲区等待。另外只有一个要竞争的索引结构T_IDX。目前看来，“单个”的项目太多了（只有一个表，一个索引等）。
再来看分区的情况。按OBJECT_ID将表散列分区为16个分区。现在就会竞争16个“表”，而且只会有1/16个“右侧”，每个索引结构只会接收以前1/16的工作负载，等等。也就是说，在一个高度并发环境中可以使用分区来减少竞争。与没有分区相比，数据的分区处理本身会占用更多的CPU时间。也就是说，如果没有分区，数只有一个去处，但有了分区后，则需要用更多的CPU时间来查明要把数据放在哪里。
使用ORDER BY
使用索引来获取数据时，并不会自动地获取有序的数据。要以某种有序顺序来获取数据，惟一的办法就是在查询上使用ORDER BY。如果查询不包含ORDER BY语句，就不能对数据的有序顺序做任何假设。
可以用一个小例子来说明。创建了一个小表（ALL_USERS的一个副本），并创建一个散列分区索引，在USER_ID列上有4个分区：
create table t
as
select *
from all_users
/
create index t_idx
on t(user_id)
global
partition by hash(user_id)
partitions 4
/现在，我们要查询这个表，要求Oracle使用这个索引。注意数据的顺序：
scott@ORCL>set autotrace on explain
scott@ORCL>select /*+ index( t t_idx ) */ user_id
2 from t
3 where user_id > 0
4 /
USER_ID
----------
30
46
53
54
55
61
74
76
79
88
129
2147483638
9
31
32
42
43
67
70
83
86
90
94
5
21
56
65
72
78
85
89
112
14
45
57
75
84
87
已选择38行。
执行计划
----------------------------------------------------------
Plan hash value: 3357014883
--------------------------------------------------------------------------------
------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pst
art| Pstop |
--------------------------------------------------------------------------------
------------
| 0 | SELECT STATEMENT | | 38 | 494 | 4 (0)| 00:00:01 |
| |
| 1 | PARTITION HASH ALL| | 38 | 494 | 4 (0)| 00:00:01 |
1 | 4 |
|* 2 | INDEX RANGE SCAN | T_IDX | 38 | 494 | 4 (0)| 00:00:01 |
1 | 4 |
--------------------------------------------------------------------------------
------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("USER_ID">0)
Note
-----
- dynamic sampling used for this statement (level=2)
scott@ORCL>set autotrace off所以，即使Oracle在区间扫描中使用了索引，数据显然也不是有序的。实际上，可以观察到这个数据存在一个模式。这里有“4个有序”的结果。我们观察到的结果是：Oracle会从4个散列分区一个接一个地返回“有序的数据”。
在此只是一个警告：除非你的查询中有一个ORDER BY，否则不要指望返回的数据会按某种顺序排序。（另外，GROUP BY也不会执行排序！ORDER BY 是无可替代的）。
5 审计和段空间压缩
在数据库中可能只是插入审计跟踪信息，而这部分数据在正常操作期间从不获取。审计跟踪信息主要作为一种证据，这是一种事后证据。这些证据是必要的，但是从很 多方面来讲，这些数据只是放在磁盘上，占用着空间，而且所占的空间相当大。然后必须每个月或每年（或者每隔一段时间）对其净化或归档。如果审计从一开始就 设计不当，最后很可能置你于“死地”。从现在算起，如果7年后需要第一次对旧数据进行净化或归档时你才开始考虑如何来完成这一工作，那就太迟了。除非你做了适当的设计，否则取出旧信息实在是件痛苦的事情。
下面来看两种技术：分区和段空间压缩。 利用这些技术，审计不仅是可以忍受的，而且很容易管理，并且将占用更少的空间。第二个技术可能不那么明显，因为段空间压缩只适用于诸如直接路径加载之类的 大批量操作，而审计跟踪通常一次只插入一行，也就是事件发生时才插入。这里的技巧是要将滑动窗口分区与段空间压缩结合起来。
假设我们决定按月对审计跟踪信息分区。在第一个业务月中，我们只是向分区表中插入信息；这些插入使用的是“传统路径”，而不是直接路径，因此没有压缩。在这 个月结束之前，现在我们要向表中增加一个新的分区，以容纳下个月的审计活动。下个月开始后不久，我们会对上个月的审计跟踪信息执行一个大批量操作，具体来 讲，我们将使用ALTER TABLE命令来移动上个月的分区，这还有压缩数据的作用。实际上，如 果再进一步，可以将这个分区从一个可读写表空间（现在它必然在一个可读写表空间中）移动到一个通常只读的表空间中（其中包含对应这个审计跟踪信息的其他分 区）。采用这种方式，就可以一个月备份一次表空间（将分区移动到这个表空间之后才备份）；这就能确保有一个正确、干净的当前表空间只读副本；然后在这个月 不再对其备份。审计跟踪信息可以有以下表空间：
一个当前在线的读写表空间，它会像系统中每一个其他的正常表空间一样得到备份。这个表空间中的审计跟踪信息不会被压缩，我们只是向其中插入信息。
一个只读表空间，其中包含“当前这一年”的审计跟踪信息分区，在此采用一种压缩格式。在每个月的月初，置这个表空间为可读写，向这个表空间中移入上个月的审计信息，并进行压缩，再使之成为只读表空间，并完成备份。
用于去年、前年等的一系列表空间。这些都是只读表空间，甚至可以放在很慢的廉价存储介质上。如果出现介质故障，我们只需要从备份恢复。有时可以随机地从备份集中选择每一年的信息，确保这些信息是可恢复的（有时磁带会出故障）。
采用这种方式，就能很容易地完成净化（即删除一个分区）。同样，归档也很轻松，只需先传送一个表空间，以后再恢复。通过实现压缩可以减少空间的占用。备份的 工作量会减少，因为在许多系统中，单个最大的数据集就是审计跟踪数据。如果可以从每天的备份中去掉某些或全部审计跟踪数据，可能会带来显著的差别。
简单地说，审计跟踪需求和分区这两个方面是紧密相关的，而不论底层系统是何种类型（数据仓库或是OLTP系统）。
扫一扫
6083
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
