Network Admin   «Prev  Next»

Lesson 4 Partitioning enhancements
Objective Identify the features for partitioned tables.

Oracle Partioned Tables

There are many features for partitioned tables[1] in Oracle that we have dedicated an entire module on the subject later in this course. For an introduction to these new features, navigate through the Carousel below.

1) Sometimes a row's data is updated and the data no longer falls in teh partitioning criteria for the partition that the row was inserted into.
1) Sometimes a row's data is updated and the data no longer falls in the partitioning criteria for the partition that the row was inserted into. Oracle allows you an option to automatically move rows from one partition to another when this happens. In this Illustration, a row in Partition 1 is moved to partition 2. The advantage of this is that your partitions stay intact according to the partition definition, allowing Oracle to optimize queries within a single partition whenever possible. Partition only queries save overhead by reducing the volume of the data that must be processed.

2) Occasionally, your original plan for partitioning a table results in an imbalance of distribution of data across the partitions.
2) Occasionally, your original plan for partitioning a table results in an imbalance of distribution of data across the partitions. One new feature that can help resolve this problem is Oracle's capability to combine, or coalesce, two partitions into a single partition. In this illustration, Partitions 2 and 3 are coalesced into a new partition called Partition 4.

3) Once that is done, the old partitions (2 and 3) are automatically removed.
3) Once that is done, the old partitions (2 and 3) are automatically removed.

4) How do you load data into one partition without having to redbuild all the partitions in the table?
4) How do you load data into one partition without having to rebuild all the partitions in the table? Another feature that can let you do this is Oracle's capability to exchange partitions. By exchanging partitions, you can trade a normal table with one of the partitions in the database. In the example shown here, Partition 2's data is exchanged for the data in Table X.

  1. Sometimes a row's data is updated and the data no longer falls in the partitioning criteria for the partition that the row was inserted into.
  2. Occasionally, your original plan for partitioning a table results in an imbalance of distribution of data across the partitions.
  3. Once that is done, the old partitions (2 and 3) are automatically removed.
  4. How do you load data into one partition without having to rebuild all the partitions in the table? (See Table Partitioning Improvements below)

Oracle Block Size

The block size of the SYSTEM tablespace is the standard block size. This is set when the database is created and can be any valid size.
You can specify up to four block sizes, in addition to a standard block size. In the initialization file, you can configure subcaches within the buffer cache for each of these block sizes. Subcaches can also be configured while an instance is running. You can create tablespaces having any of these block sizes. The standard block size is used for the system tablespace and most other tablespaces. Multiple block sizes are useful primarily when transporting a tablespace from an OLTP database to an enterprise data warehouse. This facilitates transport between databases of different block sizes. The next lesson introduces the new features for connectivity.

[1]Partitioned table: A table containing data in multiple physical tables. This divides large amounts of data into smaller segments.