Oracle Partitions : Divide and increase performance!Oracle partitions addresses the key problem of supporting very large tables and indexes by allowing you to decompose them into smaller and more manageable pieces called partitions.
Once partitions are defined, SQL statements can access and manipulate the partitions rather than entire tables or indexes.
Partitions are especially useful in data warehouse applications, which commonly store and analyze large amounts of historical data.
How do we create and manage partitions?
The following DML statements contain an optional partition specification for non-remote partitioned tables:
SELECT * FROM schema.table PARTITION(part_name);
This syntax provides a simple way of viewing individual partitions as tables: A view can be created which selects from just one partition using the partition-extended table name, and this view can be used in lieu of a table.
With such views you can also build partition-level access control mechanisms by granting (revoking) privileges on these views to (from) other users or roles.
The use of partition-extended table names has the following restrictions:
In order to provide partition independence for DDL and utility operations, Oracle supports DML partition locks.
Partition independence allows you to perform DDL and utility operations on selected partitions without disturbing activities on other partitions.
The purpose of a partition lock is to protect the data in an individual partition while multiple users are accessing that partition or other partitions in the table concurrently.
Managing Oracle partitions:
Create a partitioned table:
Creating Oracle partitions is very similar to creating a table or index. You must use the CREATE TABLE statement with the PARTITION CLAUSE.
The first step to create a partitioned table would be to identify the column(s) to partition on and the range of values which go to each partition. Then you determine the tablespaces where each partition should go.
Here is a script to create a simple partitioned table:
CREATE TABLE AA_GENERAL_ATTENDANCE
Moving Oracle partitions:
You can use the MOVE PARTITION clause to move a partition. For example, a DBA wishes to move the most active partition to a tablespace that resides on its own disk (in order to balance I/O).
The DBA can issue the following statement:
ALTER TABLE aaa MOVE PARTITION bbb
This statement always drops the partition's old segment and creates a new segment, even if you don't specify a new tablespace.
When the partition you are moving contains data, MOVE PARTITION marks the matching partition in each local index, and all global index partitions as unusable. You must rebuild these index partitions after issuing MOVE PARTITION.
Adding Oracle partitions:
You can use the ALTER TABLE ADD PARTITION statement to add a new partition to the "high" end.
If you wish to add a partition at the beginning or in the middle of a table, or if the partition bound on the highest partition is MAXVALUE, you should instead use the SPLIT PARTITION statement.
When the partition bound on the highest partition is anything other than MAXVALUE, you can add a partition using the ALTER TABLE ADD PARTITION statement.
ALTER TABLE edu
When there are local indexes defined on the table and you issue the ALTER TABLE ... ADD PARTITION statement, a matching partition is also added to each local index.
Since Oracle assigns names and default physical storage attributes to the new index partitions, you may wish to rename or alter them after the ADD operation is complete.
Dropping Oracle partitions:
You can use the ALTER TABLE DROP PARTITION statement to drop Oracle partitions.
If there are local indexes defined for the table, ALTER TABLE DROP PARTITION also drops the matching partition from each local index.
You cannot explicitly drop a partition for a local index. Instead, local index partitions are dropped only when you drop a partition from the underlying table.
If, however, the partition contains data and global indexes, and you leave the global indexes in place during the ALTER TABLE DROP PARTITION statement which marks all global index partitions unusable, you must rebuild them afterwards.
Truncating Partitioned Tables:
You can use the ALTER TABLE TRUNCATE PARTITION statement to remove all rows from a table partition with or without reclaiming space.
If there are local indexes defined for this table, ALTER TABLE TRUNCATE PARTITION also truncates the matching partition from each local index.
Splitting Oracle partitions:
You can split a table partition by issuing the ALTER TABLE SPLIT PARTITION statement.
If there are local indexes defined on the table, this statement also splits the matching partition in each local index.
Because Oracle assigns system-generated names and default storage attributes to the new index partitions, you may wish to rename or alter these index partitions after splitting them.
If the partition you are splitting contains data, the ALTER TABLE SPLIT PARTITION statement marks the matching partitions (there are two) in each local index, as well as all global index partitions, as unusable.
You must rebuild these index partitions after issuing the ALTER TABLE SPLIT PARTITION statement.
Exchanging Table Partitions:
You can convert a partition into a non-partitioned table, and a table into a partition of a partitioned table by exchanging their data and index segments.
Exchanging table partitions is most useful when you have an application using non-partitioned tables which you want to convert to partitions of a partitioned table.
Converting a Partition View into a Partitioned Table:
This part describes how to convert a partition view into a partitioned table. The partition view is defined as follows:
CREATE VIEW students
Initially, only the two most recent partitions, students_NOV95 and students_DEC95, will be migrated from the view to the table by creating the partition table.
Each partition gets a temporary segment of 2 blocks (as a placeholder).
CREATE TABLE accounts_new (...)
Use the EXCHANGE command to migrate the tables to the corresponding partitions.
ALTER TABLE students_new
ALTER TABLE students_new
So now the placeholder data segments associated with the NOV95 and DEC95 partitions have been exchanged with the data segments associated with the students_NOV95 and students_DEC95 tables.
Redefine the students view:
CREATE OR REPLACE VIEW accounts
Drop the students_NOV95 and students_DEC95 tables, which own the placeholder segments that were originally attached to the NOV95 and DEC95 partitions.
After all the tables in the UNIONALL view are converted into partitions, drop the view and rename the partitioned table as the view.
DROP VIEW students;
Rebuilding Index Partitions:
Some operations, such as ALTER TABLE DROP PARTITION, mark all Oracle partitions of a global index unusable. You can rebuild global index partitions in two ways:
Merging Oracle partitions:
Partition-level Export and Import provide a way to merge Oracle partitions in the same table, even though SQL does not explicitly support merging partitions.
A DBA can use partition-level Import to merge a table partition into the next highest partition on the same table. To merge partitions, do an export of the partition you would like to merge, delete the partition and do an import.
Free eBookSubscribe to my newsletter and get my ebook on Entity Relationship Modeling Principles as a free gift:
What visitors say...
"I just stumbled accross your site looking for some normalization theory and I have to say it is fantastic.Read more Testimonials
I have been in the database field for 10+ years and I have never before come across such a useful site. Thank you for taking the time to put this site together."
Theory & Practice
SQL Server DBA
Install SQL Server
Database Normalization eBook:
Copyright © www.databasedesign-resource.com / Alf A. Pedersen
All rights reserved.
All information contained on this website is for informational purposes only.
Disclaimer: www.databasedesign-resource.com does not warrant any company, product, service or any content contained herein.
Return to top
The name Oracle is a trademark of Oracle Corporation.