Basic Partitioning Concepts

Basic Partitioning Concepts

Benefits of Table and Index Partitioning and General Partition Rules
Improved query performance due to distributed data and less I/O contention.
Better manageability for large tables. Can be deleted or loaded in smaller pieces.
Better backup and recovery performance with more options available than having to backup or recover an entire table.
Better availability of data because partitions are available independently of each other.
Use local partition indexes in a DSS environment to limit the impact of regularly scheduled drop partition operations
Partitioned Indexes
Partitioned indexes can either be local or global and either prefixed or non-prefixed.
64,000 partitions maximum for a partitioned index.
A local index is an index whose partition boundaries are the same as the corresponding table and is created with the LOCAL keyword. Local indexes are equipartitioned with a table, meaning they have the same partition key and range values.
Local index equipartioning is beneficial because Oracle will automatically maintain the index partitions and keep them in line with their corresponding table partitions. As the DBA you don't have to worry about them. Also, this scheme provides partition independence since you can take down a partitioned table and its corresponding local index partition and not affect any other partition. It is also good for limiting queries to a single partition.
A global index is an index whose values refer to data in more than one table partition. They are not equipartitioned and must be maintained by the DBA as a separate entity. If a single partition is somehow corrupt all the partitions of a global index will require REBUILDing. You might want to use a global index if the table is not partitioned or if you want a unique index on a column that is not part of the partition key.
While global indexes can be created with the same partition range as their corresponding tables (e.g., equipartitioned), there is no benefit to this since Oracle will not automatically maintain them. If you find this is the case, use a local index instead.
A prefixed index is one whose leftmost columns are the same as the columns of the partition key of the index. You can have both local and global prefixed indexes.
A non-prefixed index is an index whose leftmost columns are not the same as the partition key.
Example: You partition an index on the hire_date column but you base the actual index on the salary column.
While you can have local non-prefixed indexes in Oracle 8, global non-prefixed indexes are not allowed. This means that while you can partition on a column that is not a part of the index for a local index, for a global index you must partition on the same columns you are indexing.
Unique non-prefixed local indexes are NOT supported. The only exception to this is if the index is a subset of the partition key. Otherwise in order to have a unique index on columns not in the partition key, the index must be global.
Local non-prefixed indexes are ideal for DSS systems where you need to partition by a date range but want to query often on another column.
Global indexes and local prefixed indexes are better for OLTP systems because they minimize index probes.
You can drop partitioned indexes (global only) using the ALTER INDEX index_name DROP PARTITION partition_name statement.
You cannot drop the highest partition of a partitioned index.
All partitions for a given table or index must have the same logical definition. However each partition can reside on different tablespaces and have different storage parameters.
You can have any combination of a partitioned table only, a partitioned index only, or both index and table partitioned.
MAXVALUE is a keyword that specifies a value, which nothing else can be equal to or higher than. It is always a good idea to specify MAXVALUE on the last partition to catch NULL and other data types whose values are higher than the range specified.
If you do not specify MAXVALUE and try to INSERT a row, which contains a value in a partition key column, which is higher than the range specified in the PARTITION BY RANGE clause, the INSERT statement will fail!

Range and Hash Partitioning
Example of RANGE partitioning:
CREATE TABLE usergroup
( member_id NUMBER,
lastname VARCHAR2,
firstname VARCHAR2,
member_since DATE, )
PARTITION BY RANGE ( lastname )
( PARTITION P1 VALUES LESS THAN ( 'F' )
TABLESPACE t1,
( PARTITION P2 VALUES LESS THAN ( 'L' )
TABLESPACE t2,
( PARTITION P3 VALUES LESS THAN ( 'N' )
TABLESPACE t3,
( PARTITION P4 VALUES LESS THAN ( 'R' )
TABLESPACE t4,
( PARTITION P5 VALUES LESS THAN ( MAXVALUE )
TABLESPACE t5 );
Examples of HASH partitioning:
CREATE TABLE usergroup
( member_id NUMBER,
lastname VARCHAR2,
firstname VARCHAR2,
member_since DATE, )
PARTITION BY HASH ( lastname )
PARTITIONS 5
STORE IN (t1, t2, t3, t4, t5);
In the above example, partitions will be named by the system in the format SYS_Pnnn. At a minimum you must specify a partition key, the partition method, and the number of partitions when creating a hash partition.
CREATE TABLE usergroup
( member_id NUMBER,
lastname VARCHAR2,
firstname VARCHAR2,
member_since DATE, )
PARTITION BY HASH ( lastname )
(PARTITIONS p1 tablespace t1,
PARTITIONS p2 tablespace t2,
PARTITIONS p3 tablespace t3,
PARTITIONS p4 tablespace t4,
PARTITIONS p5 tablespace t5);
DBA_TAB_PARTITIONS data dictionary view contains table partition storage parameters, partition levels, and partition statistics.
DBA_IND_PARTITIONS data dictionary view contains index partition storage parameters, partition levels, and partition statistics.