Partition : When to use ?

Note:-

·       Any table can be partitioned except those tables containing columns with LONG or LONG RAW data types. You can, however, use tables containing columns with CLOB or BLOB data types.

·       The concepts of splitting, dropping or merging partitions do not apply to hash partitions. Instead, hash partitions can be added and coalesced.

When to Partition a Table:

·       Tables greater than 2GB should always be considered for partitioning.

·       Tables containing historical data, in which new data is added into the newest partition. A typical example is a historical table where only the current month's data is updatable and the other 11 months are read only.

·       When the contents of a table need to be distributed across different types of storage devices.

·       When to Partition an Index:

Here are some suggestions for when to consider partitioning an index:

·       Avoid rebuilding the entire index when data is removed.

·       Perform maintenance on parts of the data without invalidating the entire index.

·       Reduce the impact of index skew caused by an index on a column with a monotonically increasing value.

Partitioning offers these advantages:

·       Partitioning enables data management operations such data loads, index creation and rebuilding, and backup/recovery at the partition level, rather than on the entire table. This results in significantly reduced times for these operations.

·       Partitioning improves query performance. In many cases, the results of a query can be achieved by accessing a subset of partitions, rather than the entire table. For some queries, this technique (called partition pruning) can provide order-of-magnitude gains in performance.

·       Partitioning can significantly reduce the impact of scheduled downtime for maintenance operations.

·       Partition independence for partition maintenance operations lets you perform concurrent maintenance operations on different partitions of the same table or index. You can also run concurrent SELECT and DML operations against partitions that are unaffected by maintenance operations.

·       Partitioning increases the availability of mission-critical databases if critical tables and indexes are divided into partitions to reduce the maintenance windows, recovery times, and impact of failures.

·       Partitioning can be implemented without requiring any modifications to your applications. For example, you could convert a no partitioned table to a partitioned table without needing to modify any of the SELECT statements or DML statements which access that table. You do not need to rewrite your application code to take advantage of partitioning.

When to Use Range or Interval Partitioning

Ø  Range partitioning is a convenient method for partitioning historical data.

Ø  Very large tables are frequently scanned by a range predicate on a good partitioning column, such as ORDER_DATE or PURCHASE_DATE. Partitioning the table on that column enables partition pruning

Ø  You cannot complete administrative operations, such as backup and restore, on large tables in an allotted time frame, but you can divide them into smaller logical pieces based on the partition range column

Ø  Range partitioning is also ideal when you periodically load new data and purge old data, because it is easy to add or drop partitions.

Ø  Ex: To add data from a new month, you load it into a separate table, clean it, index it, and then add it to the range-partitioned table using the EXCHANGE PARTITION statement, all while the original table remains online. After you add the new partition, you can drop the trailing month with the DROP PARTITION statement.

CREATE TABLE salestable

  (s_productid  NUMBER,

   s_saledate   DATE,

   s_custid     NUMBER,

   s_totalprice NUMBER)

PARTITION BY RANGE(s_saledate)

INTERVAL(NUMTOYMINTERVAL(1,'MONTH')) STORE IN (tbs1,tbs2,tbs3,tbs4)

 (PARTITION sal05q1 VALUES LESS THAN (TO_DATE('01-APR-2005', 'DD-MON-YYYY'))

   TABLESPACE tbs1,

  PARTITION sal05q2 VALUES LESS THAN (TO_DATE('01-JUL-2005', 'DD-MON-YYYY'))

   TABLESPACE tbs2,

  PARTITION sal05q3 VALUES LESS THAN (TO_DATE('01-OCT-2005', 'DD-MON-YYYY'))

   TABLESPACE tbs3,

  PARTITION sal05q4 VALUES LESS THAN (TO_DATE('01-JAN-2006', 'DD-MON-YYYY'))

   TABLESPACE tbs4,

  PARTITION sal06q1 VALUES LESS THAN (TO_DATE('01-APR-2006', 'DD-MON-YYYY'))

   TABLESPACE tbs1,

  PARTITION sal06q2 VALUES LESS THAN (TO_DATE('01-JUL-2006', 'DD-MON-YYYY'))

   TABLESPACE tbs2,

  PARTITION sal06q3 VALUES LESS THAN (TO_DATE('01-OCT-2006', 'DD-MON-YYYY'))

   TABLESPACE tbs3,

  PARTITION sal06q4 VALUES LESS THAN (TO_DATE('01-JAN-2007', 'DD-MON-YYYY'))

   TABLESPACE tbs4);

When to Use Hash Partitioning

Ø  To enable partial or full parallel partition-wise joins with likely equalized partitions.

Ø  To distribute data evenly among the nodes of an MPP platform that uses Oracle Real Application Clusters. Consequently, you can minimize interconnect traffic when processing internodes parallel statements.

Ø  To use partition pruning and partition-wise joins according to a partitioning key that is mostly constrained by a distinct value or value list.

Ø  To randomly distribute data to avoid I/O bottlenecks if you do not use a storage management technique that stripes and mirrors across all available devices

CREATE TABLE sales_hash

  (s_productid  NUMBER,

   s_saledate   DATE,

   s_custid     NUMBER,

   s_totalprice NUMBER)

PARTITION BY HASH(s_productid)

( PARTITION p1 TABLESPACE tbs1

, PARTITION p2 TABLESPACE tbs2

, PARTITION p3 TABLESPACE tbs3

, PARTITION p4 TABLESPACE tbs4

);

When to Use List Partitioning

Ø  You should use list partitioning when you want to specifically map rows to partitions based on discrete values

CREATE TABLE accounts

( id             NUMBER

, account_number NUMBER

, customer_id    NUMBER

, branch_id      NUMBER

, region         VARCHAR(2)

, status         VARCHAR2(1)

)

PARTITION BY LIST (region)

( PARTITION p_northwest VALUES ('OR', 'WA')

, PARTITION p_southwest VALUES ('AZ', 'UT', 'NM')

, PARTITION p_northeast VALUES ('NY', 'VM', 'NJ')

, PARTITION p_southeast VALUES ('FL', 'GA')

, PARTITION p_northcentral VALUES ('SD', 'WI')

, PARTITION p_southcentral VALUES ('OK', 'TX')

);

When to Use Composite Range-List Partitioning

Ø  Composite range-list partitioning is commonly used for large tables that store historical data and are commonly accessed on multiple dimensions

CREATE TABLE call_detail_records

( id NUMBER

, from_number        VARCHAR2(20)

, to_number          VARCHAR2(20)

, date_of_call       DATE

, distance           VARCHAR2(1)

, call_duration_in_s NUMBER(4)

) PARTITION BY RANGE(date_of_call)

INTERVAL (NUMTODSINTERVAL(1,'DAY'))

SUBPARTITION BY LIST(distance)

SUBPARTITION TEMPLATE

( SUBPARTITION local VALUES('L') TABLESPACE tbs1

, SUBPARTITION medium_long VALUES ('M') TABLESPACE tbs2

, SUBPARTITION long_distance VALUES ('D') TABLESPACE tbs3

, SUBPARTITION international VALUES ('I') TABLESPACE tbs4

)

(PARTITION p0 VALUES LESS THAN (TO_DATE('01-JAN-2005','dd-MON-yyyy')))

PARALLEL;

CREATE INDEX from_number_ix ON call_detail_records(from_number)

LOCAL PARALLEL NOLOGGING;

CREATE INDEX to_number_ix ON call_detail_records(to_number)

LOCAL PARALLEL NOLOGGING;

For example, regional account managers are very interested in how many new customers they signed up in their region in a specific time period.

When to Use Composite Range-Range Partitioning

Ø  Composite range-range partitioning is useful for applications that store time-dependent data on multiple time dimensions.

For example, a web retailer wants to analyze its sales data based on when orders were placed, and when orders were shipped (handed over to the shipping company).

CREATE TABLE account_balance_history

( id                 NUMBER NOT NULL

, account_number     NUMBER NOT NULL

, customer_id        NUMBER NOT NULL

, transaction_date   DATE NOT NULL

, amount_credited    NUMBER

, amount_debited     NUMBER

, end_of_day_balance NUMBER NOT NULL

) PARTITION BY RANGE(transaction_date)

INTERVAL (NUMTODSINTERVAL(7,'DAY'))

SUBPARTITION BY RANGE(end_of_day_balance)

SUBPARTITION TEMPLATE

( SUBPARTITION unacceptable VALUES LESS THAN (-1000)

, SUBPARTITION credit VALUES LESS THAN (0)

, SUBPARTITION low VALUES LESS THAN (500)

, SUBPARTITION normal VALUES LESS THAN (5000)

, SUBPARTITION high VALUES LESS THAN (20000)

, SUBPARTITION extraordinary VALUES LESS THAN (MAXVALUE)

)

(PARTITION p0 VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy')));

When to Use Composite List-Hash Partitioning

Ø  Composite list-hash partitioning is useful for large tables that are usually accessed on one dimension, but (due to their size) still must take advantage of parallel full or partial partition-wise joins on another dimension in joins with other large tables.

CREATE TABLE credit_card_accounts

( account_number  NUMBER(16) NOT NULL

, customer_id     NUMBER NOT NULL

, customer_region VARCHAR2(2) NOT NULL

, is_active       VARCHAR2(1) NOT NULL

, date_opened     DATE NOT NULL

) PARTITION BY LIST (customer_region)

SUBPARTITION BY HASH (customer_id)

SUBPARTITIONS 16

( PARTITION emea VALUES ('EU','ME','AF')

, PARTITION amer VALUES ('NA','LA')

, PARTITION apac VALUES ('SA','AU','NZ','IN','CH')

) PARALLEL;

CREATE BITMAP INDEX is_active_bix ON credit_card_accounts(is_active)

LOCAL PARALLEL NOLOGGING;

When to Use Composite List-List Partitioning

Ø  Composite list-list partitioning is useful for large tables that are often accessed on different dimensions

CREATE TABLE current_inventory

( warehouse_id      NUMBER

, warehouse_region  VARCHAR2(2)

, product_id        NUMBER

, product_category  VARCHAR2(12)

, amount_in_stock   NUMBER

, unit_of_shipping  VARCHAR2(20)

, products_per_unit NUMBER

, last_updated      DATE

) PARTITION BY LIST (warehouse_region)

SUBPARTITION BY LIST (product_category)

SUBPARTITION TEMPLATE

( SUBPARTITION perishable VALUES ('DAIRY','PRODUCE','MEAT','BREAD')

, SUBPARTITION non_perishable VALUES ('CANNED','PACKAGED')

, SUBPARTITION durable VALUES ('TOYS','KITCHENWARE')

)

( PARTITION p_northwest VALUES ('OR', 'WA')

, PARTITION p_southwest VALUES ('AZ', 'UT', 'NM')

, PARTITION p_northeast VALUES ('NY', 'VM', 'NJ')

, PARTITION p_southeast VALUES ('FL', 'GA')

, PARTITION p_northcentral VALUES ('SD', 'WI')

, PARTITION p_southcentral VALUES ('OK', 'TX')

);

CREATE INDEX warehouse_id_ix ON current_inventory(warehouse_id)

LOCAL PARALLEL NOLOGGING;

CREATE INDEX product_id_ix ON current_inventory(product_id)

LOCAL PARALLEL NOLOGGING;

When to Use Composite List-Range Partitioning

Ø  Composite list-range partitioning is useful for large tables that are accessed on different dimensions.

Ø  List-range partitioning is commonly used for tables that use range values within a list partition, whereas range-list partitioning is commonly used for a discrete list values within a range partition

When to Use Interval Partitioning

Ø  Interval partitioning can be used for every table that is range partitioned and uses fixed intervals for new partitions.

When to Use Reference Partitioning

Ø  If you have denormalized, or would denormalize, a column from a master table into a child table to get partition pruning benefits on both tables.

Ø  If two large tables are joined frequently, then the tables are not partitioned on the join key, but you want to take advantage of partition-wise joins

When to Partition on Virtual Columns

Ø  Virtual column partitioning enables you to partition on an expression, which may use data from other columns, and perform calculations with these columns.

Ø  To get partition pruning benefits, consider using virtual columns if tables are frequently accessed using a predicate that is not directly captured in a column, but can be derived

CREATE TABLE car_rentals

( id                  NUMBER NOT NULL

 , customer_id         NUMBER NOT NULL

 , confirmation_number VARCHAR2(12) NOT NULL

 , car_id              NUMBER

 , car_type            VARCHAR2(10)

 , requested_car_type  VARCHAR2(10) NOT NULL

 , reservation_date    DATE NOT NULL

 , start_date          DATE NOT NULL

 , end_date            DATE

 , country as (substr(confirmation_number,9,2))

) PARTITION BY LIST (country)

SUBPARTITION BY HASH (customer_id)

SUBPARTITIONS 16

( PARTITION north_america VALUES ('US','CA','MX')

 , PARTITION south_america VALUES ('BR','AR','PE')

 , PARTITION europe VALUES ('GB','DE','NL','BE','FR','ES','IT','CH')

 , PARTITION apac VALUES ('NZ','AU','IN','CN')

) ENABLE ROW MOVEMENT;

In this example, the column country is defined as a virtual column derived from the confirmation number.