Oracle分区技术介绍

DSS queries on very large tables present special performance problems. An ad-hoc query that requires a table scan may take a long time, because it must inspect every row in the table; there is no way to identify and skip subsets of irrelevant rows. The problem is particularly important for historical tables, for which many queries concentrate access on rows that were generated recently. Partitions help solve this problem. An ad-hoc query that requires only rows that correspond to a single partition (or range of partitions) can be executed using a partition scan rather than a table scan. For example, a query that requests data generated in the month of October 1994 can scan just the rows stored in the October 1994 partition, rather than rows generated over years of activity. This improves response time. It may also substantially reduce the temporary disk space requirement for queries that require sorts. Load balancing Many DBAs want to be able to control how data is spread across physical devices. It is not sufficient to allow a table’s data to reside on multiple disks. The DBA must be able to specify where subsets of a table go, so that he can balance I/O utilization. With this level of control, the DBA can accommodate the special needs of applications requiring fast response time by reducing disk contention and utilizing faster devices. On the other hand data, that is accessed infrequently, such as old historical data, can be moved to slow disks or stored in subsystems that support a storage hierarchy. Partitioning satisfies this requirement by allowing partitions of a table or index to be stored in different tablespaces.