Table Partitioning



What is Partitioning?


-  Partitioning allows a table, index, or index-organized table to be subdivided into smaller pieces, where each piece of such a database object is called a partition.
-  Partitioning enhances the performance, manageability, and availability of a wide variety of applications and helps reduce the total cost of ownership for storing large amounts of data.
-  Any table can be partitioned except those tables containing columns with LONG or LONG RAW datatypes. You can, however, use tables containing columns with CLOB or BLOB datatypes.
-  Tables can be partitioned into up to 64,000 separate partitions.




Partitioning Key


-  Comprised of one or more columns that determine the partition where each row will be stored. 
-   Oracle automatically directs insert, update, and delete operations to the appropriate partition through the use of the partitioning key.




When to Partition a Table


-   Tables greater than 2 GB should always be considered as candidates 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.




Partitioning Methods


-   Range Partitioning
-   List Partitioning
-   Hash Partitioning
-   Composite Partitioning


Range Partitioning

-   Range partitioning maps data to partitions based on ranges of partition key values that you establish for each partition. 
-   It is the most common type of partitioning and is often used with dates. For example, you might want to partition sales data into monthly partitions.


When using range partitioning, consider the following rules:
  • Each partition has a VALUES LESS THAN clause, which specifies a noninclusive upper bound for the partitions. Any binary values of the partition key equal to or higher than this literal are added to the next higher partition.
  • All partitions, except the first, have an implicit lower bound specified by the VALUES LESS THAN clause on the previous partition.
  • A MAXVALUE literal can be defined for the highest partition. MAXVALUE represents a virtual infinite value that sorts higher than any other possible value for the partition key, including the null value.

Ex:

CREATE TABLE sales_range 
(salesman_id  NUMBER(5), 
salesman_name VARCHAR2(30), 
sales_amount  NUMBER(10), 
sales_date    DATE)
PARTITION BY RANGE(sales_date) 
(
PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','DD/MM/YYYY')),
PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','DD/MM/YYYY')),
PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','DD/MM/YYYY')),
PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','DD/MM/YYYY'))
);

List Partitioning

-   enables you to explicitly control 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. 
-   The advantage of list partitioning is that you can group and organize unordered and unrelated sets of data in a natural way.
-   Unlike range and hash partitioning, multicolumn partition keys are not supported for list partitioning.


Ex: let's say you want to partition a sales table by region. That means grouping states together according to their geographical location as in the following example.

CREATE TABLE sales_list
(salesman_id  NUMBER(5), 
salesman_name VARCHAR2(30),
sales_state   VARCHAR2(20),
sales_amount  NUMBER(10), 
sales_date    DATE)
PARTITION BY LIST(sales_state)
(
PARTITION sales_west VALUES('California', 'Hawaii'),
PARTITION sales_east VALUES ('New York', 'Virginia', 'Florida'),
PARTITION sales_central VALUES('Texas', 'Illinois')
PARTITION sales_other VALUES(DEFAULT)
);





















Hash Partitioning





-   Hash partitioning enables easy partitioning of data that does not lend itself to range or list partitioning, but you would like to partition for performance and manageability reasons. 



-   Hash partitioning provides a method of evenly distributing data across a 



specified number of partitions. Rows are mapped into partitions based on a hash value of the partitioning key 



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







It is a better choice than range partitioning when:


    

  • You do not know beforehand how much data maps into a given range
    • 

  • The sizes of range partitions would differ quite substantially or would be difficult to balance manually
    • 

  • Range partitioning would cause the data to be undesirably clustered
    • 

  • Performance features such as parallel DML, partition pruning, and partition-wise joins are important
    • 






Ex:







The following example creates a hash-partitioned table. The partitioning column is partno, four partitions are created and assigned system generated names, and they are placed in four named tablespaces (tab1,tab2, ...).







CREATE TABLE products



(partno NUMBER,



 description VARCHAR2 (60))



 PARTITION BY HASH (partno)



 PARTITIONS 4 STORE IN (tab1, tab2, tab3, tab4);









Composite Partitioning







-   Composite partitioning partitions data using the range method, and within each partition, subpartitions it using the hash or list method. 



-   Composite range-hash partitioning provides the improved manageability of range partitioning and the data placement, striping, and parallelism advantages of hash partitioning. -   -   Composite range-list partitioning provides the manageability of range partitioning and the explicit control of list partitioning for the subpartitions.





-   Composite partitioning supports historical operations, such as adding new range partitions, but also provides higher degrees of parallelism for DML operations and finer granularity of data placement through subpartitioning.







Ex: Composite range-hash partitioning










CREATE TABLE PRODUCTS (partno NUMBER, 
description VARCHAR(32),
costprice NUMBER)
PARTITION BY RANGE (partno)
SUBPARTITION BY HASH(description)
SUBPARTITIONS 4 STORE IN (tab1, tab2, tab3, tab4)
(PARTITION p1 VALUES LESS THAN (100),
 PARTITION p2 VALUES LESS THAN (200),
 PARTITION p3 VALUES LESS THAN (MAXVALUE));






Ex: Composite range-list partitioning








CREATE TABLE bimonthly_regional_sales
(deptno NUMBER, 
 item_no VARCHAR2(20),
 txn_date DATE, 
 txn_amount NUMBER, 
 state VARCHAR2(2))
PARTITION BY RANGE (txn_date)
SUBPARTITION BY LIST (state)
SUBPARTITION TEMPLATE(
SUBPARTITION east VALUES('NY', 'VA', 'FL') TABLESPACE ts1,
SUBPARTITION west VALUES('CA', 'OR', 'HI') TABLESPACE ts2,
SUBPARTITION central VALUES('IL', 'TX', 'MO') TABLESPACE ts3)
( PARTITION janfeb_2000 VALUES LESS THAN (TO_DATE('1-MAR-2000','DD-MON-YYYY')),
  PARTITION marapr_2000 VALUES LESS THAN (TO_DATE('1-MAY-2000','DD-MON-YYYY')),
  PARTITION mayjun_2000 VALUES LESS THAN (TO_DATE('1-JUL-2000','DD-MON-YYYY')));




















































No comments:















Post a Comment


















        

      









Subscribe to:
Posts (Atom)