Partitioning Tables  «Prev  Next»

Arrange SQL command in order

You have completed the following SQL command that creates the SOLAR_SYSTEM table, a partitioned, index-oriented table.

CREATE TABLE SOLAR_SYSTEM
(STAR_ID NUMBER, GALAXY_ID NUMBER, 
TOTAL_PLANETS NUMBER,  DISCOVERY_DATE DATE,
CONSTRAINT SOLAR_SYSTEM_PK PRIMARY KEY (STAR_ID))
ORGANIZATION INDEX PCTTHRESHOLD 33
TABLESPACE USERS PCTFREE 10
PARTITION BY RANGE (STAR_ID)
(PARTITION LOW_PART VALUES LESS THAN (19999),
PARTITION MED_PART VALUES LESS THAN (999999),
PARTITION HIGH_PART VALUES LESS THAN (MAXVALUE))

Reduces Scope of Maintenance Operations

Equally important is the fact that partitioning substantially reduces the scope of maintenance operations and increases the availability of your data. You can perform all maintenance operations, such as backup, recovery, and loading, on a single partition. This flexibility makes it possible to handle extremely large data structures while still performing those maintenance operations in a reasonable amount of time. In addition, if you must recover one partition in a table for some reason, the other partitions in the table can remain online during the recovery operation.
If you have been working with other databases that don’t offer the same type of partitioning, you may have tried to implement a similar functionality by dividing a table into several separate tables and then using a UNION SQL command to view the data in several tables at once. Partitioned tables give you all the advantages of having several identical tables joined by a UNION command without the complexity that implementation requires.

Partition Table and index identically

To maximize the benefits of partitioning, it sometimes makes sense to partition a table and an index identically so that both the table partition and the index partition map to the same set of rows. You can automatically implement this type of partitioning, which is called equipartitioning, by specifying an index for a partitioned table as a LOCAL index. Local indexes simplify maintenance, since standard operations, such as dropping a partition, will work transparently with both the index partition and the table partition.

Partitioning Features 10g, 11g, 12c

Oracle has continued to increase the functionality of partitioning features. Since Oracle Database 10g Release 2, you can reorganize individual partitions online, the maximum number of partitions increased from
  1. (64 KB - 1) to (128 KB - 1), and
  2. query optimization using partition pruning improved.
Oracle Database 11g further improved partition pruning, enabled applications to control partitioning, and added a Partition Advisor that can help you to understand when partitioning might improve the performance of your Oracle Database.
Oracle Database 12c has added the ability to use partial indexes for a partitioned table. This capability means that you do not have to index all partitions in a table. You can indicate that a particular partition should not have an index, which means that there will not be a local index, or that partition will be excluded from the global index. You can turn indexing on or off for any individual partition.