ORACLE 分区（Concepts） 中英对照

上面是英文版，下面是中文版！

Partitioning Concepts

Partitioning enhances the performance, manageability, and availability of a wide variety of applications and helps reduce the total cost of ownership for storing large amounts of data. Partitioning allows tables, indexes, and index-organized tables to be subdivided into smaller pieces, enabling these database objects to be managed and accessed at a finer level of granularity. Oracle provides a rich variety of partitioning strategies and extensions to address every business requirement. Moreover, since it is entirely transparent, partitioning can be applied to almost any application without the need for potentially expensive and time consuming application changes.

This chapter contains the following topics:

• Basics of Partitioning

• Benefits of Partitioning

• Partitioning Strategies

• Overview of Partitioned Indexes

Basics of Partitioning

Partitioning allows a table, index, or index-organized table to be subdivided into smaller pieces, where each piece of such a database object is called a partition. Each partition has its own name, and may optionally have its own storage characteristics.

From the perspective of a database administrator, a partitioned object has multiple pieces that can be managed either collectively or individually. This gives the administrator considerable flexibility in managing partitioned objects. However, from the perspective of the application, a partitioned table is identical to a non-partitioned table; no modifications are necessary when accessing a partitioned table using SQL queries and DML statements.

Figure 2-1 offers a graphical view of how partitioned tables differ from non-partitioned tables.

Figure 2-1 A View of Partitioned Tables

Description of "Figure 2-1 A View of Partitioned Tables"
 

Note:

All partitions of a partitioned object must reside in tablespaces of a single block size.

See Also:

Oracle Database Concepts for more information about multiple block sizes

Partitioning Key

Each row in a partitioned table is unambiguously assigned to a single partition. The partitioning key is comprised of one or more columns that determine the partition where each row will be stored. Oracle automatically directs insert, update, and delete operations to the appropriate partition through the use of the partitioning key.

Partitioned Tables

Any table can be partitioned into a million separate partitions except those tables containing columns with LONG or LONG RAW datatypes. You can, however, use tables containing columns with CLOB or BLOB datatypes.

Note:

To reduce disk usage and memory usage (specifically, the buffer cache), you can store tables and partitions of a partitioned table in a compressed format inside the database. This often leads to a better scaleup for read-only operations. Table compression can also speed up query execution. There is, however, a slight cost in CPU overhead.

See Also:

Oracle Database Concepts for more information about table compression

When to Partition a Table

Here are some suggestions for when to partition a table:

• Tables greater than 2 GB should always be considered as candidates for partitioning.

• Tables containing historical data, in which new data is added into the newest partition. A typical example is a historical table where only the current month's data is updatable and the other 11 months are read only.

• When the contents of a table need to be distributed across different types of storage devices.

When to Partition an Index

Here are some suggestions for when to consider partitioning an index:

• Avoid rebuilding the entire index when data is removed.

• Perform maintenance on parts of the data without invalidating the entire index.

• Reduce the impact of index skew caused by an index on a column with a monotonically increasing value.

Partitioned Index-Organized Tables

Partitioned index-organized tables are very useful for providing improved performance, manageability, and availability for index-organized tables.

For partitioning an index-organized table:

• Partition columns must be a subset of the primary key columns

• Secondary indexes can be partitioned (both locally and globally)

• OVERFLOW data segments are always equi-partitioned with the table partitions

See Also:

Oracle Database Concepts for more information about index-organized tables

System Partitioning

System partitioning enables application-controlled partitioning without having the database controlling the data placement. The database simply provides the ability to break down a table into partitions without knowing what the individual partitions are going to be used for. All aspects of partitioning have to be controlled by the application. For example, an insertion into a system partitioned table without the explicit specification of a partition will fail.

System partitioning provides the well-known benefits of partitioning (scalability, availability, and manageability), but the partitioning and actual data placement are controlled by the application.

See Also:

Oracle Database Data Cartridge Developer's Guide for more information about system partitioning

Partitioning for Information Lifecycle Management

Information Lifecycle Management (ILM) is concerned with managing data during its lifetime. Partitioning plays a key role in ILM because it enables groups of data (that is, partitions) to be distributed across different types of storage devices and managed individually.

See Also:

Chapter 5, "Using Partitioning for Information Lifecycle Management" for more information about Information Lifecycle Management

Partitioning and LOB Data

Unstructured data (such as images and documents) which is stored in a LOB column in the database can also be partitioned. When a table is partitioned, all the columns will reside in the tablespace for that partition, with the exception of LOB columns, which can be stored in their own tablespace.

This technique is very useful when a table is comprised of large LOBs because they can be stored separately from the main data. This can be beneficial if the main data is being frequently updated but the LOB data isn't. For example, an employee record may contain a photo which is unlikely to change frequently. However, the employee personnel details (such as address, department, manager, and so on) could change. This approach also means that cheaper storage can be used for storing the LOB data and more expensive, faster storage used for the employee record.

Benefits of Partitioning

Partitioning can provide tremendous benefit to a wide variety of applications by improving performance, manageability, and availability. It is not unusual for partitioning to improve the performance of certain queries or maintenance operations by an order of magnitude. Moreover, partitioning can greatly simplify common administration tasks.

Partitioning also enables database designers and administrators to tackle some of the toughest problems posed by cutting-edge applications. Partitioning is a key tool for building multi-terabyte systems or systems with extremely high availability requirements.

Partitioning for Performance

By limiting the amount of data to be examined or operated on, and by providing data distribution for parallel execution, partitioning provides a number of performance benefits. These features include:

• Partition Pruning

• Partition-Wise Joins

Partition Pruning

Partition pruning is the simplest and also the most substantial means to improve performance using partitioning. Partition pruning can often improve query performance by several orders of magnitude. For example, suppose an application contains an Orders table containing a historical record of orders, and that this table has been partitioned by week. A query requesting orders for a single week would only access a single partition of the Orders table. If the Orders table had 2 years of historical data, then this query would access one partition instead of 104 partitions. This query could potentially execute 100 times faster simply because of partition pruning.

Partition pruning works with all of Oracle's other performance features. Oracle will utilize partition pruning in conjunction with any indexing technique, join technique, or parallel access method.

Partition-Wise Joins

Partitioning can also improve the performance of multi-table joins by using a technique known as partition-wise joins. Partition-wise joins can be applied when two tables are being joined together and both tables are partitioned on the join key, or when a reference partitioned table is joined with its parent table. Partition-wise joins break a large join into smaller joins that occur between each of the partitions, completing the overall join in less time. This offers significant performance benefits both for serial and parallel execution.

Partitioning for Manageability

Partitioning allows tables and indexes to be partitioned into smaller, more manageable units, providing database administrators with the ability to pursue a "divide and conquer" approach to data management. With partitioning, maintenance operations can be focused on particular portions of tables. For example, a database administrator could back up a single partition of a table, rather than backing up the entire table. For maintenance operations across an entire database object, it is possible to perform these operations on a per-partition basis, thus dividing the maintenance process into more manageable chunks.

A typical usage of partitioning for manageability is to support a "rolling window" load process in a data warehouse. Suppose that a DBA loads new data into a table on a weekly basis. That table could be partitioned so that each partition contains one week of data. The load process is simply the addition of a new partition using a partition exchange load. Adding a single partition is much more efficient than modifying the entire table, since the DBA does not need to modify any other partitions.

Partitioning for Availability

Partitioned database objects provide partition independence. This characteristic of partition independence can be an important part of a high-availability strategy. For example, if one partition of a partitioned table is unavailable, then all of the other partitions of the table remain online and available. The application can continue to execute queries and transactions against the available partitions for the table, and these database operations will run successfully, provided they do not need to access the unavailable partition.

The database administrator can specify that each partition be stored in a separate tablespace; the most common scenario is having these tablespaces stored on different storage tiers. Storing different partitions in different tablespaces allows the database administrator to do backup and recovery operations on each individual partition, independent of the other partitions in the table. Thus allowing the active parts of the database to be made available sooner so access to the system can continue, while the inactive data is still being restored. Moreover, partitioning can reduce scheduled downtime. The performance gains provided by partitioning may enable database administrators to complete maintenance operations on large database objects in relatively small batch windows.

Partitioning Strategies

Oracle Partitioning offers three fundamental data distribution methods as basic partitioning strategies that control how data is placed into individual partitions:

• Range

• Hash

• List

Using these data distribution methods, a table can either be partitioned as a single list or as a composite partitioned table:

• Single-Level Partitioning

• Composite Partitioning

Each partitioning strategy has different advantages and design considerations. Thus, each strategy is more appropriate for a particular situation.

Single-Level Partitioning

A table is defined by specifying one of the following data distribution methodologies, using one or more columns as the partitioning key:

• Range Partitioning

• Hash Partitioning

• List Partitioning

For example, consider a table with a column of type NUMBER as the partitioning key and two partitions less_than_five_hundred and less_than_one_thousand. The less_than_one_thousand partition contains rows where the following condition is true:

500 <= partitioning key < 1000

Figure 2-2 offers a graphical view of the basic partitioning strategies for a single-level partitioned table.

Figure 2-2 List, Range, and Hash Partitioning

Description of "Figure 2-2 List, Range, and Hash Partitioning"

Range Partitioning

Range partitioning maps data to partitions based on ranges of values of the partitioning key that you establish for each partition. It is the most common type of partitioning and is often used with dates. For a table with a date column as the partitioning key, the January-2005 partition would contain rows with partitioning key values from 01-Jan-2005 to 31-Jan-2005.

Each partition has a VALUES LESS THAN clause, which specifies a non-inclusive upper bound for the partitions. Any values of the partitioning key equal to or higher than this literal are added to the next higher partition. All partitions, except the first, have an implicit lower bound specified by the VALUES LESS THAN clause of the previous partition.

A MAXVALUE literal can be defined for the highest partition. MAXVALUE represents a virtual infinite value that sorts higher than any other possible value for the partitioning key, including the NULL value.

Hash Partitioning

Hash partitioning maps data to partitions based on a hashing algorithm that Oracle applies to the partitioning key that you identify. The hashing algorithm evenly distributes rows among partitions, giving partitions approximately the same size.

Hash partitioning is the ideal method for distributing data evenly across devices. Hash partitioning is also an easy-to-use alternative to range partitioning, especially when the data to be partitioned is not historical or has no obvious partitioning key.

Note:

You cannot change the hashing algorithms used by partitioning.

List Partitioning

List partitioning enables you to explicitly control how rows map to partitions by specifying a list of discrete values for the partitioning key in the description for each partition. The advantage of list partitioning is that you can group and organize unordered and unrelated sets of data in a natural way. For a table with a region column as the partitioning key, the North America partition might contain values Canada, USA, and Mexico.

The DEFAULT partition enables you to avoid specifying all possible values for a list-partitioned table by using a default partition, so that all rows that do not map to any other partition do not generate an error.

Composite Partitioning

Composite partitioning is a combination of the basic data distribution methods; a table is partitioned by one data distribution method and then each partition is further subdivided into subpartitions using a second data distribution method. All subpartitions for a given partition together represent a logical subset of the data.

Composite partitioning supports historical operations, such as adding new range partitions, but also provides higher degrees of potential partition pruning and finer granularity of data placement through subpartitioning. Figure 2-3 offers a graphical view of range-hash and range-list composite partitioning, as an example.

Figure 2-3 Composite Partitioning

Description of "Figure 2-3 Composite Partitioning"
 

• Composite Range-Range Partitioning

• Composite Range-Hash Partitioning

• Composite Range-List Partitioning

• Composite List-Range Partitioning

• Composite List-Hash Partitioning

• Composite List-List Partitioning

Composite Range-Range Partitioning

Composite range-range partitioning enables logical range partitioning along two dimensions; for example, partition by order_date and range subpartition by shipping_date.

Composite Range-Hash Partitioning

Composite range-hash partitioning partitions data using the range method, and within each partition, subpartitions it using the hash method. Composite range-hash partitioning provides the improved manageability of range partitioning and the data placement, striping, and parallelism advantages of hash partitioning.

Composite Range-List Partitioning

Composite range-list partitioning partitions data using the range method, and within each partition, subpartitions it using the list method. Composite range-list partitioning provides the manageability of range partitioning and the explicit control of list partitioning for the subpartitions.

Composite List-Range Partitioning

Composite list-range partitioning enables logical range subpartitioning within a given list partitioning strategy; for example, list partition by country_id and range subpartition by order_date.

Composite List-Hash Partitioning

Composite list-hash partitioning enables hash subpartitioning of a list-partitioned object; for example, to enable partition-wise joins.

Composite List-List Partitioning

Composite list-list partitioning enables logical list partitioning along two dimensions; for example, list partition by country_id and list subpartition by sales_channel.

Partitioning Extensions

In addition to the basic partitioning strategies, Oracle Database provides partitioning extensions:

• Manageability Extensions

• Partitioning Key Extensions

Manageability Extensions

These extensions significantly enhance the manageability of partitioned tables:

• Interval Partitioning

• Partition Advisor

Interval Partitioning

Interval partitioning is an extension of range partitioning which instructs the database to automatically create partitions of a specified interval when data inserted into the table exceeds all of the existing range partitions. You must specify at least one range partition. The range partitioning key value determines the high value of the range partitions, which is called the transition point, and the database creates interval partitions for data beyond that transition point. The lower boundary of every interval partition is the non-inclusive upper boundary of the previous range or interval partition.

For example, if you create an interval partitioned table with monthly intervals and the transition point at January 1, 2007, then the lower boundary for the January 2007 interval is January 1, 2007. The lower boundary for the July 2007 interval is July 1, 2007, regardless of whether the June 2007 partition was already created.

When using interval partitioning, consider the following restrictions:

• You can only specify one partitioning key column, and it must be of NUMBER or DATE type.

• Interval partitioning is not supported for index-organized tables.

• You cannot create a domain index on an interval-partitioned table.

You can create single-level interval partitioned tables as well as the following composite partitioned tables:

• Interval-range

• Interval-hash

• Interval-list

Partition Advisor

The Partition Advisor is part of the SQL Access Advisor. The Partition Advisor can recommend a partitioning strategy for a table based on a supplied workload of SQL statements which can be supplied by the SQL Cache, a SQL Tuning set, or be defined by the user.

Partitioning Key Extensions

These extensions extend the flexibility in defining partitioning keys:

• Reference Partitioning

• Virtual Column-Based Partitioning

Reference Partitioning

Reference partitioning allows the partitioning of two tables related to one another by referential constraints. The partitioning key is resolved through an existing parent-child relationship, enforced by enabled and active primary key and foreign key constraints.

The benefit of this extension is that tables with a parent-child relationship can be logically equi-partitioned by inheriting the partitioning key from the parent table without duplicating the key columns. The logical dependency will also automatically cascade partition maintenance operations, thus making application development easier and less error-prone.

An example of reference partitioning is the Orders and OrderItems tables related to each other by a referential constraint orderid_refconstraint. Namely, OrderItems.OrderID references Orders.OrderID. The Orders table is range partitioned on OrderDate. Reference partitioning on orderid_refconstraint for OrderItems leads to creation of the following partitioned table, which is equi-partitioned with respect to the Orders table, as shown in Figure 2-4 and Figure 2-5.

Figure 2-4 Before Reference Partitioning

Description of "Figure 2-4 Before Reference Partitioning"
 

Figure 2-5 With Reference Partitioning

Description of "Figure 2-5 With Reference Partitioning"
 

All basic partitioning strategies are available for reference Partitioning. Interval partitioning cannot be used with reference partitioning.

Virtual Column-Based Partitioning

In previous releases of the Oracle Database, a table could only be partitioned if the partitioning key physically existed in the table. In Oracle Database 11g, virtual columns remove that restriction and allow the partitioning key to be defined by an expression, using one or more existing columns of a table. The expression is stored as metadata only.

Oracle Partitioning has been enhanced to allow a partitioning strategy to be defined on virtual columns. For example, a 10 digit account ID can include account branch information as the leading 3 digits. With the extension of virtual column based Partitioning, an ACCOUNTS table containing an ACCOUNT_ID column can be extended with a virtual (derived) column ACCOUNT_BRANCH that is derived from the first three digits of the ACCOUNT_ID column, which becomes the partitioning key for this table.

Virtual column-based Partitioning is supported with all basic partitioning strategies, including interval and interval-* composite partitioning.

Overview of Partitioned Indexes

Just like partitioned tables, partitioned indexes improve manageability, availability, performance, and scalability. They can either be partitioned independently (global indexes) or automatically linked to a table's partitioning method (local indexes). In general, you should use global indexes for OLTP applications and local indexes for data warehousing or DSS applications. Also, whenever possible, you should try to use local indexes because they are easier to manage. When deciding what kind of partitioned index to use, you should consider the following guidelines in order:

1. If the table partitioning column is a subset of the index keys, use a local index. If this is the case, you are finished. If this is not the case, continue to guideline 2.

2. If the index is unique and does not include the partitioning key columns, then use a global index. If this is the case, then you are finished. Otherwise, continue to guideline 3.

3. If your priority is manageability, use a local index. If this is the case, you are finished. If this is not the case, continue to guideline 4.

4. If the application is an OLTP one and users need quick response times, use a global index. If the application is a DSS one and users are more interested in throughput, use a local index.

See Also:

Chapter 6, "Using Partitioning in a Data Warehouse Environment" and Chapter 7, "Using Partitioning in an Online Transaction Processing Environment" for more information about partitioned indexes and how to decide which type to use

Local Partitioned Indexes

Local partitioned indexes are easier to manage than other types of partitioned indexes. They also offer greater availability and are common in DSS environments. The reason for this is equipartitioning: each partition of a local index is associated with exactly one partition of the table. This enables Oracle to automatically keep the index partitions in sync with the table partitions, and makes each table-index pair independent. Any actions that make one partition's data invalid or unavailable only affect a single partition.

Local partitioned indexes support more availability when there are partition or subpartition maintenance operations on the table. A type of index called a local nonprefixed index is very useful for historical databases. In this type of index, the partitioning is not on the left prefix of the index columns.

See Also:

Chapter 4 for more information about prefixed indexes

You cannot explicitly add a partition to a local index. Instead, new partitions are added to local indexes only when you add a partition to the underlying table. Likewise, you cannot explicitly drop a partition from a local index. Instead, local index partitions are dropped only when you drop a partition from the underlying table.

A local index can be unique. However, in order for a local index to be unique, the partitioning key of the table must be part of the index's key columns.

Figure 2-6 offers a graphical view of local partitioned indexes.

Figure 2-6 Local Partitioned Index

Description of "Figure 2-6 Local Partitioned Index"

Global Partitioned Indexes

Oracle offers two types of global partitioned indexes: range partitioned and hash partitioned.

Global Range Partitioned Indexes

Global range partitioned indexes are flexible in that the degree of partitioning and the partitioning key are independent from the table's partitioning method.

The highest partition of a global index must have a partition bound, all of whose values are MAXVALUE. This ensures that all rows in the underlying table can be represented in the index. Global prefixed indexes can be unique or nonunique.

You cannot add a partition to a global index because the highest partition always has a partition bound of MAXVALUE. If you wish to add a new highest partition, use the ALTERINDEX SPLIT PARTITION statement. If a global index partition is empty, you can explicitly drop it by issuing the ALTER INDEX DROP PARTITION statement. If a global index partition contains data, dropping the partition causes the next highest partition to be marked unusable. You cannot drop the highest partition in a global index.

Global Hash Partitioned Indexes

Global hash partitioned indexes improve performance by spreading out contention when the index is monotonically growing. In other words, most of the index insertions occur only on the right edge of an index.

Maintenance of Global Partitioned Indexes

By default, the following operations on partitions on a heap-organized table mark all global indexes as unusable:

ADD (HASH) 

COALESCE (HASH) 

DROP 

EXCHANGE 

MERGE 

MOVE 

SPLIT 

TRUNCATE 

These indexes can be maintained by appending the clause UPDATE INDEXES to the SQL statements for the operation. The two advantages to maintaining global indexes:

• The index remains available and online throughout the operation. Hence no other applications are affected by this operation.

• The index doesn't have to be rebuilt after the operation.

Note:

This feature is supported only for heap-organized tables.

Figure 2-7 offers a graphical view of global partitioned indexes.

Figure 2-7 Global Partitioned Index

Description of "Figure 2-7 Global Partitioned Index"

Global Non-Partitioned Indexes

Global non-partitioned indexes behave just like a non-partitioned index.

Figure 2-8 offers a graphical view of global non-partitioned indexes.

Figure 2-8 Global Non-Partitioned Index

Description of "Figure 2-8 Global Non-Partitioned Index"

Miscellaneous Information about Creating Indexes on Partitioned Tables

You can create bitmap indexes on partitioned tables, with the restriction that the bitmap indexes must be local to the partitioned table. They cannot be global indexes.

Global indexes can be unique. Local indexes can only be unique if the partitioning key is a part of the index key.

Partitioned Indexes on Composite Partitions

Here are a few points to remember when using partitioned indexes on composite partitions:

• Subpartitioned indexes are always local and stored with the table subpartition by default.

• Tablespaces can be specified at either index or index subpartition levels.

中文翻译：

分区概念

分区可以增强各种应用程序的性能，可管理性和可用性，并有助于降低存储大量数据的总体拥有成本。分区允许将表，索引和索引组织表细分为更小的部分，从而使这些数据库对象能够以更精细的粒度进行管理和访问。Oracle提供了丰富的分区策略和扩展，以满足每个业务需求。此外，由于它是完全透明的，因此分区可以应用于几乎任何应用程序，而无需潜在的昂贵和耗时的应用程序更改。

本章包含以下主题：

• 分区基础知识

• 分区的好处

• 分区策略

• 分区索引概述

分区基础知识

分区允许将表，索引或索引组织表细分为更小的部分，其中每个这样的数据库对象称为分区。每个分区都有自己的名称，并且可以选择具有自己的存储特性。

从数据库管理员的角度来看，分区对象具有多个可以集体或单独管理的部分。这为管理员提供了管理分区对象的极大灵活性。但是，从应用程序的角度来看，分区表与非分区表相同; 使用SQL查询和DML语句访问分区表时，无需进行任何修改。

图2-1提供了分区表与非分区表的区别的图形视图。

图2-1分区表视图

“图2-1分区表视图”的描述

注意：

分区对象的所有分区必须驻留在单个块大小的表空间中。

也可以看看：

有关多个块大小的更多信息，请参见Oracle数据库概念

分区键

分区表中的每一行都明确地分配给单个分区。分区键由一个或多个列组成，这些列确定将存储每行的分区。Oracle通过使用分区键自动将插入，更新和删除操作定向到适当的分区。

分区表

除了那些包含带LONG或LONG RAW数据类型的列的表外，任何表都可以划分为一百万个单独的分区。但是，您可以使用包含带CLOB或BLOB数据类型的列的表。

注意：

要减少磁盘使用和内存使用（特别是缓冲区缓存），可以在数据库中以压缩格式存储分区表的表和分区。这通常会导致只读操作的更好的扩展。表压缩还可以加快查询执行速度。但是，CPU开销略有成本。

也可以看看：

有关表压缩的更多信息，请参阅 Oracle数据库概念

何时分区表

以下是有关何时对表进行分区的一些建议：

• 应始终将大于2 GB的表视为分区的候选对象。

• 包含历史数据的表，其中新数据被添加到最新分区中。一个典型的例子是历史表，其中只有当前月份的数据是可更新的，而其他11个月是只读的。

• 当表的内容需要分布在不同类型的存储设备上时。

何时分区索引

以下是关于何时考虑对索引进行分区的一些建议：

• 删除数据时避免重建整个索引。

• 对部分数据执行维护而不会使整个索引无效。

• 减少索引引起的索引偏斜对具有单调递增值的列的影响。

分区索引组织表

分区索引组织表对于为索引组织表提供改进的性能，可管理性和可用性非常有用。

对于索引组织表的分区：

• 分区列必须是主键列的子集

• 可以对二级索引进行分区（本地和全局）

• OVERFLOW 数据段始终与表分区等分

也可以看看：

有关索引组织表的更多信息，请参阅 Oracle数据库概念

系统分区

系统分区支持应用程序控制的分区，而无需数据库控制数据放置。数据库只是提供了将表分解为分区的功能，而无需知道将使用哪些分区。分区的所有方面都必须由应用程序控制。例如，在没有明确指定分区的情况下插入系统分区表将失败。

系统分区提供了分区（可伸缩性，可用性和可管理性）的众所周知的好处，但分区和实际数据放置由应用程序控制。

也可以看看：

有关系统分区的详细信息，请参见“Oracle数据库数据盒式磁带开发人员指南”

信息生命周期管理的分区

信息生命周期管理（ILM）关注的是在其生命周期内管理数据。分区在ILM中起着关键作用，因为它使数据组（即分区）能够分布在不同类型的存储设备上并单独管理。

也可以看看：

有关信息生命周期管理的更多信息，请参见第5章“使用信息生命周期管理分区”

分区和LOB数据

存储在数据库中的LOB列中的非结构化数据（例如图像和文档）也可以被分区。对表进行分区时，所有列都将驻留在该分区的表空间中，但LOB列除外，它们可以存储在自己的表空间中。

当表由大LOB组成时，该技术非常有用，因为它们可以与主数据分开存储。如果主数据经常更新但LOB数据不经常更新，这可能是有益的。例如，员工记录可能包含不太可能经常更改的照片。但是，员工人员详细信息（例如地址，部门，经理等）可能会发生变化。这种方法还意味着可以使用更便宜的存储来存储LOB数据，以及用于员工记录的更昂贵，更快的存储。

分区的好处

通过提高性能，可管理性和可用性，分区可以为各种应用程序带来巨大的好处。分区将某些查询或维护操作的性能提高一个数量级并不罕见。此外，分区可以大大简化常见的管理任务。

分区还使数据库设计人员和管理员能够解决尖端应用程序带来的一些最棘手的问题。分区是构建具有极高可用性要求的多TB系统或系统的关键工具。

为性能划分

通过限制要检查或操作的数据量，并通过提供并行执行的数据分发，分区提供了许多性能优势。这些功能包括：

• 分区修剪

• 分区 - 明智的加入

分区修剪

分区修剪是使用分区提高性能的最简单也是最实质的方法。分区修剪通常可以将查询性能提高几个数量级。例如，假设一个应用程序包含一个Orders包含订单历史记录的表，并且该表已按周分区。请求一周订单的查询只能访问该Orders表的单个分区。如果Orders表具有2年的历史数据，则此查询将访问一个分区而不是104个分区。由于分区修剪，此查询可能执行速度提高100倍。

分区修剪与Oracle的所有其他性能功能一起使用。Oracle将结合使用任何索引技术，连接技术或并行访问方法来使用分区修剪。

分区 - 明智的加入

分区还可以通过使用称为分区连接的技术来提高多表连接的性能。当两个表连接在一起并且两个表在连接键上分区时，或者引用分区表与其父表连接时，可以应用分区连接。分区连接将大型连接分解为在每个分区之间发生的较小连接，从而在更短的时间内完成整个连接。这为串行和并行执行提供了显着的性能优势。

可管理性的分区

分区允许将表和索引划分为更小，更易于管理的单元，从而为数据库管理员提供了一种“分而治之”的数据管理方法。通过分区，维护操作可以集中在表的特定部分。例如，数据库管理员可以备份表的单个分区，而不是备份整个表。对于跨整个数据库对象的维护操作，可以基于每个分区执行这些操作，从而将维护过程分成更易于管理的块。

用于可管理性的分区的典型用法是支持数据仓库中的“滚动窗口”加载过程。假设DBA每周将新数据加载到表中。可以对该表进行分区，以便每个分区包含一周的数据。加载过程只是使用分区交换负载添加新分区。添加单个分区比修改整个表更有效，因为DBA不需要修改任何其他分区。

可用性分区

分区数据库对象提供分区独立性。分区独立性的这一特性可以成为高可用性策略的重要组成部分。例如，如果分区表的一个分区不可用，则表的所有其他分区保持联机且可用。应用程序可以继续针对表的可用分区执行查询和事务，并且这些数据库操作将成功运行，前提是它们不需要访问不可用的分区。

数据库管理员可以指定将每个分区存储在单独的表空间中; 最常见的情况是将这些表空间存储在不同的存储层上。在不同的表空间中存储不同的分区允许数据库管理员在每个单独的分区上执行备份和恢复操作，而与表中的其他分区无关。因此，允许更快地使数据库的活动部分可用，从而可以继续访问系统，同时仍然恢复非活动数据。此外，分区可以减少计划停机时间。分区提供的性能提升可以使数据库管理员在相对较小的批处理窗口中完成对大型数据库对象的维护操作。

分区策略

Oracle Partitioning提供了三种基本数据分发方法作为基本分区策略，用于控制如何将数据放入单个分区：

• 范围

• 哈希

• 名单

使用这些数据分发方法，可以将表分区为单个列表或复合分区表：

• 单级分区

• 复合分区

每种分区策略都有不同的优点和设计考虑因素。因此，每种策略都更适合特定情况。

单级分区

通过使用一个或多个列作为分区键指定以下数据分发方法之一来定义表：

• 范围分区

• 哈希分区

• 列表分区

例如，考虑类型的列的表NUMBER作为分区键和两个分区less_than_five_hundred和less_than_one_thousand。该less_than_one_thousand分区包含行，其中下列条件为真：

500 <=分区键<1000

图2-2提供了单级分区表的基本分区策略的图形视图。

图2-2列表，范围和散列分区

“图2-2列表，范围和散列分区”的说明

范围分区

范围分区根据您为每个分区建立的分区键的值范围将数据映射到分区。它是最常见的分区类型，通常与日期一起使用。对于具有日期列作为分区键的表，该January-2005分区将包含具有从2005年1月1日到2005年1月31日的分区键值的行。

每个分区都有一个VALUES LESS THAN子句，它指定分区的非包含上限。分区键的任何等于或高于此文字的值都将添加到下一个更高的分区。除第一个分区外，所有分区都具有VALUES LESS THAN前一分区的子句指定的隐式下限。

甲MAXVALUE字面可以为最高分区来定义。MAXVALUE表示一个虚拟无限值，其排序高于分区键的任何其他可能值，包括NULL值。

哈希分区

散列分区基于Oracle应用于您标识的分区键的散列算法将数据映射到分区。散列算法在分区之间均匀分布行，使分区大小大致相同。

散列分区是在设备之间均匀分布数据的理想方法。散列分区也是一种易于使用的范围分区替代方法，尤其是当要分区的数据不是历史数据或没有明显的分区键时。

注意：

您无法更改分区使用的散列算法。

列表分区

通过列表分区，您可以通过在每个分区的描述中指定分区键的离散值列表来显式控制行映射到分区的方式。列表分区的优点是您可以以自然的方式对无序和无关的数据集进行分组和组织。对于一个地区列作为分区键的表，该North America分区可能包含值Canada，USA和Mexico。

通过该DEFAULT分区，您可以避免使用默认分区为列表分区表指定所有可能的值，以便不映射到任何其他分区的所有行都不会生成错误。

复合分区

复合分区是基本数据分配方法的组合; 通过一种数据分配方法对表进行分区，然后使用第二种数据分配方法将每个分区进一步细分为子分区。给定分区的所有子分区一起表示数据的逻辑子集。

复合分区支持历史操作，例如添加新的范围分区，但也通过子分区提供更高程度的潜在分区修剪和更精细的数据放置粒度。图2-3提供了范围哈希和范围列表复合分区的图形视图，作为示例。

图2-3复合分区

“图2-3复合分区”的描述

• 复合范围 - 范围分区

• 复合范围哈希分区

• Composite Range-List Partitioning

• Composite List-Range Partitioning

• Composite List-Hash Partitioning

• Composite List-List Partitioning

Composite Range-Range Partitioning

Composite range-range partitioning enables logical range partitioning along two dimensions; for example, partition by order_date and range subpartition by shipping_date.

Composite Range-Hash Partitioning

Composite range-hash partitioning partitions data using the range method, and within each partition, subpartitions it using the hash method. Composite range-hash partitioning provides the improved manageability of range partitioning and the data placement, striping, and parallelism advantages of hash partitioning.

Composite Range-List Partitioning

复合范围列表分区使用range方法对数据进行分区，并在每个分区内使用list方法对其进行子分区。复合范围列表分区提供范围分区的可管理性以及子分区的列表分区的显式控制。

复合列表范围分区

复合列表范围分区在给定列表分区策略中启用逻辑范围子分区; 例如，列出分区依据country_id和范围子分区依据order_date。

复合列表 - 散列分区

复合列表 - 散列分区启用列表分区对象的散列子分区; 例如，启用分区智能连接。

复合列表 - 列表分区

复合列表 - 列表分区允许沿两个维度进行逻辑列表分区; 例如，list by by country_id和list subpartition by sales_channel。

分区扩展

除基本分区策略外，Oracle数据库还提供分区扩展：

• 可管理性扩展

• 分区键扩展名

可管理性扩展

这些扩展显着增强了分区表的可管理性：

• 区间分区

• 分区顾问

区间分区

间隔分区是范围分区的扩展，它指示数据库在插入表中的数据超过所有现有范围分区时自动创建指定间隔的分区。您必须至少指定一个范围分区。范围分区键值确定范围分区的高值（称为转换点），数据库为超出该转换点的数据创建间隔分区。每个间隔分区的下边界是前一个范围或间隔分区的非包含上边界。

例如，如果创建具有月间隔和2007年1月1日转换点的间隔分区表，则2007年1月间隔的下限为2007年1月1日。2007年7月间隔的下限为7月1日， 2007年，无论2007年6月的分区是否已经创建。

使用间隔分区时，请考虑以下限制：

• 您只能指定一个分区键列，它必须是NUMBER或DATE键入。

• 索引组织表不支持间隔分区。

• 您无法在间隔分区表上创建域索引。

您可以创建单级间隔分区表以及以下复合分区表：

• 间隔距离

• 间隔 - 哈希

• 间隔列表

分区顾问

Partition Advisor是SQL Access Advisor的一部分。Partition Advisor可以根据SQL语句提供的工作负载为表推荐分区策略，SQL语句可以由SQL Cache，SQL Tuning集提供，也可以由用户定义。

分区键扩展名

这些扩展扩展了定义分区键的灵活性：

• 参考分区

• 基于虚拟列的分区

参考分区

引用分区允许通过引用约束对彼此相关的两个表进行分区。分区键通过现有的父子关系解析，由启用和活动的主键和外键约束强制执行。

此扩展的好处是具有父子关系的表可以通过从父表继承分区键而不重复键列来在逻辑上进行等分。逻辑依赖性还将自动级联分区维护操作，从而使应用程序开发更容易，并且不易出错。

引用分区的示例是通过引用约束彼此相关的Orders和OrderItems表orderid_refconstraint。即，OrderItems.OrderID参考文献Orders.OrderID。该Orders表是范围分区的OrderDate。对参考分区orderid_refconstraint为OrderItems导致创建以下分区表，这是等值分区相对于所述的Orders表，如图图2-4和图2-5。

图2-4参考分区前

“图2-4参考分区前”的说明

图2-5带参考分区

“图2-5带参考分区”的描述

所有基本分区策略均可用于参考分区。间隔分区不能与引用分区一起使用。

基于虚拟列的分区

在以前版本的Oracle数据库中，只有在表中物理存在分区键时才能对表进行分区。在Oracle Database 11g中，虚拟列删除了该限制，并允许使用表的一个或多个现有列通过表达式定义分区键。表达式仅存储为元数据。

Oracle分区已得到增强，允许在虚拟列上定义分区策略。例如，10位数帐户ID可以包括帐户分支信息作为前3位数字。通过基于虚拟列的分区扩展，可以使用从列的前三个数字派生的虚拟（派生）列扩展ACCOUNTS包含ACCOUNT_ID列的表，该列成为此表的分区键。ACCOUNT_BRANCHACCOUNT_ID

所有基本分区策略都支持基于虚拟列的分区，包括interval和interval- * composite partitioning。

分区索引概述

与分区表一样，分区索引可提高可管理性，可用性，性能和可伸缩性。它们可以独立分区（全局索引），也可以自动链接到表的分区方法（本地索引）。通常，您应该为数据仓库或DSS应用程序使用OLTP应用程序的全局索引和本地索引。此外，只要有可能，您应该尝试使用本地索引，因为它们更易于管理。在决定使用何种分区索引时，应按顺序考虑以下准则：

1. 如果表分区列是索引键的子集，请使用本地索引。如果是这种情况，那么你就完成了。如果不是这种情况，请继续执行准则2。

2. 如果索引是唯一的并且不包括分区键列，则使用全局索引。如果是这种情况，那么你就完成了。否则，继续遵守准则3。

3. 如果您的优先级是可管理性，请使用本地索引。如果是这种情况，那么你就完成了。如果情况并非如此，请继续执行准则4。

4. 如果应用程序是OLTP，并且用户需要快速响应时间，请使用全局索引。如果应用程序是DSS，并且用户对吞吐量更感兴趣，请使用本地索引。

也可以看看：

第6章“在数据仓库环境中使用分区”和第7章“在联机事务处理环境中使用分区”，以获取有关分区索引以及如何确定使用哪种类型的更多信息

本地分区索引

与其他类型的分区索引相比，本地分区索引更易于管理。它们还提供更高的可用性，并且在DSS环境中很常见。其原因是均分：本地索引的每个分区都与表的一个分区相关联。这使Oracle能够自动保持索引分区与表分区同步，并使每个表索引对独立。使一个分区的数据无效或不可用的任何操作仅影响单个分区。

当表上有分区或子分区维护操作时，本地分区索引支持更多可用性。一种称为本地非前缀索引的索引对历史数据库非常有用。在这种类型的索引中，分区不在索引列的左前缀上。

也可以看看：

有关前缀索引的详细信息，请参阅第4章

您无法将分区显式添加到本地索引。相反，只有在将分区添加到基础表时，才会将新分区添加到本地索引。同样，您无法从本地索引显式删除分区。相反，仅当从基础表中删除分区时，才会删除本地索引分区。

本地索引可以是唯一的。但是，为了使本地索引唯一，表的分区键必须是索引键列的一部分。

图2-6提供了本地分区索引的图形视图。

图2-6本地分区索引

“图2-6局部分区索引”的描述

全局分区索引

Oracle提供两种类型的全局分区索引：范围分区和散列分区。

全局范围分区索引

全局范围分区索引是灵活的，因为分区的程度和分区键独立于表的分区方法。

全局索引的最高分区必须具有分区绑定，其所有值都是MAXVALUE。这可确保基础表中的所有行都可以在索引中表示。全局前缀索引可以是唯一的也可以是非唯一的。

您无法将分区添加到全局索引，因为最高分区始终具有分区范围MAXVALUE。如果要添加新的最高分区，请使用该ALTER INDEX SPLIT PARTITION语句。如果全局索引分区为空，则可以通过发出ALTER INDEX DROP PARTITION语句显式删除它。如果全局索引分区包含数据，则删除分区会导致下一个最高分区被标记为不可用。您不能删除全局索引中的最高分区。

全局哈希分区索引

全局散列分区索引通过在索引单调增长时分散争用来提高性能。换句话说，大多数索引插入仅出现在索引的右边缘。

维护全球分区索引

默认情况下，对堆组织表上的分区执行以下操作会将所有全局索引标记为不可用：

ADD（哈希） 

COALESCE（哈希） 

下降 

交换 

合并 

移动 

分裂 

截短 

可以通过将子句附加UPDATE INDEXES到操作的SQL语句来维护这些索引。维护全局索引的两个优点：

• 该指数在整个运营过程中仍然可用并在线。因此，此操作不会影响其他应用程序。

• 操作后不必重建索引。

注意：

仅对堆组织表支持此功能。

图2-7提供了全局分区索引的图形视图。

图2-7全局分区索引

“图2-7全局分区索引”的描述

非全局分区指数

非全局分区索引的行为就像非分区索引一样。

图2-8提供了全局非分区索引的图形视图。

图2-8全局非分区索引

“图2-8全局非分区索引”的描述

有关在分区表上创建索引的其他信息

您可以在分区表上创建位图索引，但限制是位图索引必须是分区表的本地索引。它们不能是全局索引。

全局索引可以是唯一的。如果分区键是索引键的一部分，则本地索引只能是唯一的。

复合分区的分区索引

在复合分区上使用分区索引时，需要记住以下几点：

• 子分区索引始终是本地的，默认情况下与表子分区一起存储。

• 可以在索引或索引子分区级别指定表空间。