Partitioning in Oracle

Oracle Partitioning

Oracle introduced partitioning with Oracle8.

Partitioning allows tables or materialized views or indexes(based on table or materialized view) or index-organized tables to be subdivided into smaller pieces, enabling these database objects to be managed and accessed at a finer level of granularity. Each partition of a table or index must have the same logical attributes, such as column names, datatypes and constraints, but each partition can have separate physical/storage attributes such as pctfree, pctused, having table compression enabled or having partitions being stored in different tablespaces, potentially on different ASM diskgroups.

Oracle Partitioning, an option of Oracle Database Enterprise Edition (to be purchased separately), enhances the SPAM (Scalability, Performance, Availability, and Manageability) of a wide variety of applications. Oracle provides a comprehensive range of partitioning schemes to address every business requirement. Moreover, since it is entirely transparent in SQL statements, partitioning can be applied to any application, from OLTP to Data Warehousing.

From the perspective of a database administrator, a partitioned object has multiple pieces which can be managed either collectively or individually. This gives the administrator considerably flexibility in managing partitioned objects. From the perspective of the application development, a partitioned table is identical to a non-partitioned table. The application need not be modified to access partitioned tables if that application was initially written on non partitioned tables.

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 or M-View.

Oracle supports partitioning only for tables, indexes on tables/materialized views, and materialized views.

Oracle does not support

(i) Partitioning of clustered tables or indexes on clustered tables.

(ii) LONG & LONG RAW datatypes.

Partitions are especially useful in datawarehouse applications, which commonly store and analyze large amounts of historical data.

Partition Key: Each row in a partitioned table is unambiguously assigned to a single partition. The partition key is a set of from 1 to 16 columns that determines the partition for each row. Cannot contain a LEVEL, ROWID or MLSLABEL pseudo column or a column of type ROWID.

Subpartition: Partitions created within partitions. They are just partitions themselves and there is nothing special about them.

Oracle8i, supports up to 64,000 partitions per table or index.

The following DML statements contain an optional partition specification for non-remote partitioned tables:

INSERT

UPDATE

DELETE

LOCK TABLE

SELECT

e.g. SQL> SELECT * FROM schema.table PARTITION(partition-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:

• A partition-extended table name cannot refer to a remote schema object.
• The partition-extended table name syntax is not supported by PL/SQL.
• A partition extension must be specified with a base table. No synonyms, views, or any other schema objects are allowed.

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.

Advantages of using partitions in table

1. Smaller and more manageable pieces of data (partitions).

2. Reduced recovery time.

3. Failure impact is less.

4. Export/Import can be done at the partition level.

5. Faster access of data.

6. Partitions work independent of the other partitions.

7. Very easy to use.

As partitioning converts one single table into many physical segments, more physical segments require more resources from the Oracle SGA. Oracle initialization parameter DML_LOCKS must be set to accommodate partitioning. If a table as 1000 partitions, then DML_LOCKS must be set to at least 1000 or the table cannot be created.

From Oracle 11g, Partition Advisor, part of the SQL Access Advisor, is available which will recommend how to partition a table based on how the data will be accessed. 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.

Types of Partitioning Methods

Oracle Database 11g provides the most comprehensive partitioning offering in the market, with a broad variety of partitioning techniques and advanced mechanisms.

With Oracle8, Range partitioning (on single column) was introduced.

With Oracle8i, Hash and Composite(Range-Hash) partitioning was introduced.

With Oracle9i, List partitioning and Composite(Range-List) partitioning was introduced.

With Oracle 11g, Interval partitioning, REFerence partitioning, Virtual column based partitioning, System partitioning and Composite partitionings [Range-Range, List-List, List-Range, List-Hash, Interval-Range, Interval-List, Interval-Interval] was introduced.

With Oracle 12c, Automatic List-Partitioning introduced.

Each method of partitioning has its own advantages and disadvantages and the decision which one to use will depend on the data and type of application.

1. RANGE Partitioning

Range partitioning based on consecutive ranges of column values. Range partitioning maps data to partitions based on ranges of partition key values that you establish for each partition.

e.g. Orders table range partitioned by order_date.

Each partition is defined by a partition bound (non inclusive) that basically limits the scope of partition. Most commonly used values for range partition is the date field in a table, we can create partitions for every quarter or month.

The syntax for creating RANGE PARTITION:

CREATE TABLE table-name

(...col-list...) ...

PARTITION BY RANGE (col-names)

(

PARTITION part-name VALUES LESS THAN (value) [TABLESPACE tbs-name],

PARTITION part-name VALUES LESS THAN (value) [TABLESPACE tbs-name],

... ... ... ...

PARTITION part-name VALUES LESS THAN (MAXVALUE) [TABLESPACE tbs-name]

);

e.g. 1:
CREATE TABLE DEPT (DEPTNO NUMBER(2), DEPT_NAME VARCHAR2(30))
PARTITION BY RANGE(DEPTNO)
(PARTITION P1 VALUES
 PARTITION P2 VALUES
 PARTITION P3 VALUES DEPT3);

e.g. 2:
create table cust_sales (acct_no number(5), cust_name char(30), sale_day integer not null, sale_mth integer not null, sale_yr integer not null)
partition by range (sale_yr, sale_mth, sale_day)
(partition cust_sales_q1 values less than (2010, 04, 01) tablespace users1,
 partition cust_sales_q2 values less than (2010, 07, 01) tablespace users2,
 partition cust_sales_q3 values less than (2010, 10, 01) tablespace users3,
 partition cust_sales_q4 values less than (2011, 01, 01) tablespace users4,
 partition cust_sales_qx values less than (maxvalue, maxvalue, maxvalue)
tablespace users4);

In Range partitioning,

• Can't have gaps in the range.
• VALUES LESS THAN keyword must be specified as a literal, which specifies a non-inclusive upper bound for the partitions. Any values of the partition key equal to or higher than this literal are added to the next higher partition.
• Except first partition, all partitions have an implicit lower bound specified by the VALUES LESS THAN clause on the previous partition.
• MAXVALUE to allow all possible values given to the highest partition in the table. MAXVALUE represents a virtual infinite value that sorts higher than any other possible value for the partition key, including the null value.
• Best suited when we know beforehand how much data will be mapped into a given range.
• The sizes of range partitions would differ quite substantially or would be difficult to balance manually.

2. LIST Partitioning (From Oracle9i)

List partitioning based on unordered lists of values. Explicitly controls how rows map to partitions. You do this by specifying a list of discrete values for the partitioning key in the description for each partition. Under this type of partitioning the records in a table are partitioned based on the list of values for a table column as a defining key the partitions can be made based on.

e.g. Orders table list partitioned by country.

The syntax for creating LIST PARTITION:

CREATE TABLE table-name

(...col-list...) ....

PARTITION BY LIST (col-name)

(

PARTITION part-name VALUES (list-of-values) [TABLESPACE tbs-name],

PARTITION part-name VALUES (list-of-values) [TABLESPACE tbs-name],

... ... ... ...

PARTITION part-name VALUES (DEFAULT) [TABLESPACE tbs-name]

);

e.g. 1:
create table dept_part (deptno number(2), dname varchar2(14), loc varchar2(13))
partition by list (dname)
(partition d1_east values ('BOSTON', 'NEW YORK'),
 partition d2_west values ('SAN FRANCISCO', 'LOS ANGELES'),
 partition d3_south values ('ATLANTA', 'DALLAS'),
 partition d4_north values ('CHICAGO', 'DETROIT'));

In List partitioning,

• The partitioning key can only consist of a single column of the table.
• 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.

3. HASH Partitioning

Hash partitioning based on a hash algorithm. Hash partitioning enables partitioning of data that does not lend itself to range or list partitioning. The records in a table, are partitions based on Hash value found in the value of the column, which is used for partitioning. Hash partitioning does not have any logical meaning to the partitions as do the range partitioning.

e.g. Orders table hash partitioned by customer_id.

The syntax for creating HASH PARTITION:

CREATE TABLE table-name

(...col-list...) ....

PARTITION BY HASH (col-names)

PARTITIONS number-of-partitions STORE IN (tbs-name1,tbs-name2,...);

or

CREATE TABLE table-name

(...col-list...) ....

PARTITION BY HASH (col-names)

(

PARTITION part-name [TABLESPACE tbs-name],

PARTITION part-name [TABLESPACE tbs-name],

... ... ... ...

PARTITION part-name [TABLESPACE tbs-name]

);

In Hash partitioning,

• No manageability, only availability and performance.
• Controls physical placement of data across fixed number of partitions.
• Easy to implement, any type of data can be easily partitioned.
• Best suited when you do not know beforehand how much data maps into a given range.
• Performance features such as parallel DML, partition pruning, and partition-wise joins are important.

Index organized tables can be partitioned using range or list or hash partitions.

4. INTERVAL Partitioning (from Oracle 11g)

Interval partitioning is an extension to range partitioning in which, beyond a point in time, partitions are defined by an interval. Interval partitions are automatically created by the database when data is inserted into the partition.

Interval partitioning defined by an interval, providing equi-width ranges. With the exception of the first partition all partitions are automatically created as-needed when matching data arrives.

e.g. Orders table partitioned by order_date with a predefined daily interval, starting with '01-Jan-2015'.

The syntax for creating INTERVAL PARTITION:

CREATE TABLE table-name

(...col-list...) ...

PARTITION BY RANGE (col-name)

INTERVAL (value) [STORE In (tbs-name)]

(

PARTITION part-name VALUES LESS THAN (value) [TABLESPACE tbs-name],

PARTITION part-name VALUES LESS THAN (value) [TABLESPACE tbs-name],

... ... ... ...

);

In Interval partitioning,

• 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.
• At least one partition must be defined when the table is created.
• A MAXVALUE partition cannot be defined for an interval partitioned table.
• NULL values are not allowed in the partition column.

5. REFERENCE Partitioning (from Oracle 11g)

REF (Reference) partitioning is for a child table is inherited from the parent table through a primary key – foreign key relationship. The partitioning keys are not stored in actual columns in the child table.

e.g. (Parent) Orders table range partitioned by order_date and inherits the partitioning technique to (child) order_lines table. Column order_date is only present in the parent orders table.

The benefit of this partitioning 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.

Data is mapped to partitions based on values defined in a referential constraint (foreign key).

6. Virtual column based Partitioning (from Oracle 11g)

Partitioning is defined by one of the partition techniques and the partitioning key is based on a virtual column. Virtual columns are not stored on disk and only exist as metadata.

e.g. Orders table has a virtual column that derives the sales region based on the customer account number. The orders table is then list partitioned by sales region.

7. Composite Partitioning

Composite partitioning is a combination of the basic partitioning techniques of Range, List, Hash, and Interval Partitionings.

e.g. Orders table is range partitioned by order_date and sub-partitioned by hash on customer_id.

e.g. Orders table is range partitioned by order_date and sub-partitioned by range on shipment_date.

Composite Partitionings

Ø Range-Hash (from 8i)

Ø Range-List (from 9i)

Ø Range-Range (from 11g)

Ø List-Hash (from 11g)

Ø List-List (from 11g)

Ø List-Range (from 11g)

Ø Interval-Range (from 11g)

Ø Interval-List (from 11g)

Ø Interval-Interval (from 11g)

7.1 Range-Hash Partitioning

This is basically a combination of range and hash partitions. The data is divided using the range partition and then each range partitioned data is further subdivided into a hash partition using hash key values. All sub partitions, together, represent a logical subset of the data.

The syntax for creating RANGE-HASH PARTITION:

CREATE TABLE table-name

(...col-list...) .... [TABLESPACE tbs-name]

PARTITION BY RANGE (col-names)

SUBPARTITION BY HASH (col-names)

SUBPARTITION TEMPLATE

(

SUBPARTITION subpart-name [TABLESPACE tbs-name],

SUBPARTITION subpart-name [TABLESPACE tbs-name],

......

)

(

PARTITION part-name VALUES LESS THAN (value) [TABLESPACE tbs-name],

PARTITION part-name VALUES LESS THAN (value) [TABLESPACE tbs-name],

....

PARTITION part-name VALUES LESS THAN (MAXVALUE) [TABLESPACE tbs-name]

);

Composite range-hash partitioning provides the improved manageability of range partitioning and the data placement, striping, and parallelism advantages of hash partitioning.

7.2 Range-List Partitioning

This is a combination of Range and List partitions, first the data is divided using the Range partition and then each Range partitioned data is further subdivided into List partitions using list key values. Each subpartition individually represents logical subset of the data not like composite Range-Hash Partition.

The syntax for creating RANGE-LIST PARTITION:

CREATE TABLE table-name

(...col-list...) .... [TABLESPACE tbs-name]

PARTITION BY RANGE (col-names)

SUBPARTITION BY LIST (col-name)

SUBPARTITION TEMPLATE

(

SUBPARTITION subpart-name VALUES (‘value-list’) [TABLESPACE tbs-name],

SUBPARTITION subpart-name VALUES (‘value-list’) [TABLESPACE tbs-name],

......

SUBPARTITION subpart-name VALUES (DEFAULT) [TABLESPACE tbs-name]

)

(

PARTITION part-name VALUES LESS THAN (value) [TABLESPACE tbs-name],

PARTITION part-name VALUES LESS THAN (value) [TABLESPACE tbs-name],

....

PARTITION part-name VALUES LESS THAN (MAXVALUE) [TABLESPACE tbs-name]

);

Composite range-list partitioning provides the manageability of range partitioning and the explicit control of list partitioning for the subpartitions.

7.3 Range-Range Partitioning

Composite range-range partitioning enables logical range partitioning along two dimensions.

e.g. partition by order_date and range subpartition by shipping_date.

The syntax for creating RANGE-RANGE PARTITION:

CREATE TABLE table-name

(...col-list...) .... [TABLESPACE tbs-name]

PARTITION BY RANGE (col-names)

SUBPARTITION BY RANGE (col-names)

SUBPARTITION TEMPLATE

(

SUBPARTITION subpart-name VALUES LESS THAN (value) [TABLESPACE tbs-name],

SUBPARTITION subpart-name VALUES LESS THAN (value) [TABLESPACE tbs-name],

......

SUBPARTITION subpart-name VALUES LESS THAN (MAXVALUE) [TABLESPACE tbs-name]

)

(

PARTITION part-name VALUES LESS THAN (value) [TABLESPACE tbs-name],

PARTITION part-name VALUES LESS THAN (value) [TABLESPACE tbs-name],

....

PARTITION part-name VALUES LESS THAN (MAXVALUE) [TABLESPACE tbs-name]

);

7.4 List-Range Partitioning

Composite list-range partitioning enables logical range subpartitioning within a given list partitioning strategy.

e.g. list partition by country_id and range subpartition by order_date.

The syntax for creating LIST-RANGE PARTITION:

CREATE TABLE table-name

(...col-list...) .... [TABLESPACE tbs-name]

PARTITION BY LIST (col-name)

SUBPARTITION BY RANGE (col-names)

SUBPARTITION TEMPLATE

(

SUBPARTITION subpart-name VALUES LESS THAN (value) [TABLESPACE tbs-name],

SUBPARTITION subpart-name VALUES LESS THAN (value) [TABLESPACE tbs-name],

......

SUBPARTITION subpart-name VALUES LESS THAN (MAXVALUE) [TABLESPACE tbs-name]

)

(

PARTITION part-name VALUES (‘value-list’) [TABLESPACE tbs-name],

PARTITION part-name VALUES (‘value-list’) [TABLESPACE tbs-name],

......

PARTITION part-name VALUES (DEFAULT) [TABLESPACE tbs-name]

);

7.5 List-Hash Partitioning

Composite list-hash partitioning enables hash sub partitioning of a list-partitioned object.

e.g. to enable partition-wise joins.

The syntax for creating LIST-HASH PARTITION:

CREATE TABLE table-name

(...col-list...) .... [TABLESPACE tbs-name]

PARTITION BY LIST (col-name)

SUBPARTITION BY HASH (col-names)

SUBPARTITION TEMPLATE

(

SUBPARTITION subpart-name [TABLESPACE tbs-name],

SUBPARTITION subpart-name [TABLESPACE tbs-name],

......

)

(

PARTITION part-name VALUES (‘value-list’) [TABLESPACE tbs-name],

PARTITION part-name VALUES (‘value-list’) [TABLESPACE tbs-name],

......

PARTITION part-name VALUES (DEFAULT) [TABLESPACE tbs-name]

);

7.6 List-List Partitioning

Composite list-list partitioning enables logical list partitioning along two dimensions.

e.g. list partition by country_id and list subpartition by sales_channel.

The syntax for creating LIST-LIST PARTITION:

CREATE TABLE table-name

(...col-list...) .... [TABLESPACE tbs-name]

PARTITION BY LIST (col-name)

SUBPARTITION BY LIST (col-name)

SUBPARTITION TEMPLATE

(

SUBPARTITION subpart-name VALUES (‘value-list’) [TABLESPACE tbs-name],

SUBPARTITION subpart-name VALUES (‘value-list’) [TABLESPACE tbs-name],

......

SUBPARTITION subpart-name VALUES (DEFAULT) [TABLESPACE tbs-name]

)

(

PARTITION part-name VALUES (‘value-list’) [TABLESPACE tbs-name],

PARTITION part-name VALUES (‘value-list’) [TABLESPACE tbs-name],

......

PARTITION part-name VALUES (DEFAULT) [TABLESPACE tbs-name]

);

7.7 Interval-Range Partitioning

This is a combination of Interval and Range partitions, first the data is divided using the Interval partition and then each Interval partitioned data is further subdivided into Range partitions.

7.8 Interval-List Partitioning

This is a combination of Interval and List partitions, first the data is divided using the Interval partition and then each Interval partitioned data is further subdivided into List partitions.

7.9 Interval-Interval Partitioning

First the data is divided using the Interval partition and then each Interval partitioned data is further subdivided into Interval partitions.

8. SYSTEM Partitioning (from Oracle 11g)

System partitions offer tremendous advantages when a table can't be partitioned in any logical way. The result is a huge, monolithic table, which poses problems such as the need to extended index maintenance and other operations. They let you take advantage of the benefits of partitioning while allowing free rein to the developers in deciding to which partition a record should go.

The syntax for creating SYSTEM PARTITION:

CREATE TABLE table-name

(...col-list...) ....

PARTITION BY SYSTEM

(

PARTITION part-name [TABLESPACE tbs-name],

PARTITION part-name [TABLESPACE tbs-name],

... ... ... ...

PARTITION part-name [TABLESPACE tbs-name]

);

The database has no control over the placement of rows during insert operations.

Note that there is no partition key or boundary.

Simple INSERT statements will give error (even if you use bind variable or partition number). You have to use PARTITION clause in INSERT statement.

SQL> INSERT INTO tab-name PARTITION (partition-name) VALUES (...values...);

In System partitioning,

• If you specify the PARTITION BY SYSTEM clause, but don't define partitions, a single partition is created with the name in the format of "SYS_Pn".
• If you specify PARTITION BY SYSTEM PARTITIONS n clause, the database creates "n" partitions with the name in the format of "SYS_Pn". The range of allowable values for "n" is from 1 to 1024K-1.
• System partitioning is not available for index-organized tables.
• System partitioning can play no part in composite partitioning.
• You cannot split a system partition.
• System partitioning cannot be specified in a CREATE TABLE ... AS SELECT statement.
• To insert data into a system-partitioned table using an INSERT INTO ... AS subquery statement, you must use partition-extended syntax to specify the partition into which the values returned by the subquery will be inserted.

Managing Oracle partitions

Indexes on Partitions

Benefits of Oracle Partitioning

Partitioning can provide tremendous benefits to a wide variety of applications by improving SPAM (Scalability, Performance, Availability and Manageability). Oracle Partitioning is proven key functionality for building multi-terabyte systems or systems with extremely high availability requirements.

For example, 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 reduce the total cost of ownership, using a "tiered archiving" approach of keeping older relevant information still online on low cost storage devices. Thus Oracle Partitioning enables an efficient and simple, yet very powerful approach to the Information Lifecycle Management for large environments.

Oracle Partitioning for Manageability

The Oracle Partitioning option provides 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 datawarehouse. Suppose that a DBA loads new data into a table on weekly basis. That table could be range-partitioned so that each partition contains one week of data. The load process is then simply the addition of a new partition. Adding a single partition is much more efficient than modifying the entire table, since the DBA does not need to modify any other partitions. The same is true for purging data from a partitioned table. You simply drop a partition, a very cheap and quick data dictionary operation, rather than issuing a DELETE command, using lots of resources and touching all the data to be deleted.

Oracle Partitioning for Performance

When data volumes increase, a common concern, is that system performance will degrade because of all the extra data that has to be examined. Oracle Partitioning eliminates this problem, by limiting the amount of data to be examined or operated on, thus significantly improving performance beyond what is possible with a non partitioned table. Oracle partitioning option provides a number of performance benefits, including the following:

• Partition Pruning
• Partition-wise joins
• Parallel DML

Partitioning Pruning: Partition pruning is the skipping of unnecessary index and data partitions or subpartitions by a query. Partitioning pruning is the simplest and also the most substantial means to improve performance using partitioning.

For example, suppose an application contains a Shipment table containing a historical record of shipments, and that this table has been partitioned by day. A query requesting shipments for a single day would only access a single partition of the Shipments table. If the Shipments table had 2 years of historical data, this query would access one partition instead of 730 partitions. This query could potentially execute appr. 700x 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. Oracle optimizes SQL statements to mark the partitions or subpartitions that need to be accessed and eliminates (prunes) unnecessary partitions or subpartitions from access.

Partition-wise joins: Partitioning can also improve the performance of multi-table joins, by using a technique known as partition-wise join. Partition-wise joins can be applied with two tables are being joined together, and both of these tables are partitioned on the join key. Partition-wise joins break large joins 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.

Parallel DML: Parallel execution dramatically reduces response time for data-intensive operations on large databases typically associated with decision support systems (DSS) and data warehouses. In addition to conventional tables, you can use parallel query and parallel DML with range and hash-partitioned tables. By doing so, you can enhance scalability and performance for batch operations.

Oracle Partitioning for Availability

Partitioned database objects provide partition independence, which is an important part of a high-availability strategy. For example, if one partition in a table is unavailable, all of the other partitions of the table remain online and available; the application can continue to execute queries and transactions against this partitioned table, and these database operations will run successfully if they do not need to access the unavailable partition. 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.

Summarizing the advantages of partitioning.

1. Partitions can be independently managed.
2. Backup & recovery can be done on individual partitions. Hence maintenance windows will be reduced.
3. Partitions that may be unavailable (say if one disk goes down, that doesn't stop the show).
4. Partitions can be moved from one tablespace to another.
5. Can be added, dropped and truncated at the partition level.
6. Select, insert, update and delete can be done at the partition level, instead of table level and can be done in parallel.
7. Oracle optimizer eliminates partitions that need not be scanned. This is called partition pruning.
8. Partitions can be scanned in parallel (OPQO - Oracle Parallel Query Option).
9. The size of the temporary segments used for sorting can be reduced.
10. we can load balance partitions across physical devices.
11. We can do Export/Import data from/to at partition level, by using normal export/import or datapump export/import.
    $ exp ... table=emp:partition2
    $ imp ... table=emp:partition2
    $ expdp ... tables=test.test_tab:part_2007
    $ impdp ... partition_options=departition dumpfile=part_2007.dmp
12. SQL*Loader can load data into a specified partition.

Related Base Tables

tabpart$

tabsubpart$

indpart$

indsubpart$

partobj$

indpart_param$

partlob$

subpartcol$

Related Oracle Views

dba_part_tables

dba_tab_partitions

dba_tab_subpartitions

dba_tab_cols

dba_part_indexes

dba_ind_partitions

dba_ind_subpartitions

dba_part_lobs

dba_lob_partitions

dba_lob_subpartitions

dba_subpartition_templates

dba_subpart_key_columns

Related Oracle Articles: Managing Oracle Partitions   Indexes on Oracle Partitions