It's All About ORACLE

Oracle - The number one Database Management System. Hope this Blog will teach a lot about oracle.

Various Partitioning Strategies

So now you know partitioning in oracle now the only thing that you need to know is little bit of syntax and that’s it, and you are a partitioning guru. 

Oracle introduced partitioning with 8.0. With this version only, " Range Partitioning" was supported. I will come to details later about what that means. Then with Oracle 8i " Hash and Composite Partitioning" was also introduced and with 9i " List Partitioning", it was introduced with lots of other features with each upgrade. 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. Also one can MODIFY , RENAME, MOVE, ADD, DROP, TRUNCATE, SPLIT partitions. We will go through the details now. 

When you create (or alter) a partitioned table, a row movement clause, either ENABLE ROW MOVEMENT or DISABLE ROW MOVEMENT can be specified. This clause either enables or disables the migration of a row to a new partition if its key is updated. The default is DISABLE ROW MOVEMENT.

RANGE Partitioning

This type of partitioning creates partitions based on the " Range of Column" values. 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. Lets say we have a table SAMPLE_ORDERS and it has a field ORDER_DATE. Also, lets say we have 5 years of history in this table. Then, we can create partitions by date for, lets say, every quarter. 
So Every Quarter Data becomes a partition in the SAMPLE_ORDER table. The first partition will be the one with the lowest bound and the last one will be the Partition with the highest bound. So if we have a query that want to look at the Data of first quarter of 1999 then instead of going through the complete data it will directly go to the Partition of first quarter 1999. 

This is example of the syntax needed for creating a RANGE PARTITION.
CREATE TABLE SAMPLE_ORDERS
(ORDER_NUMBER NUMBER,
ORDER_DATE DATE,
CUST_NUM NUMBER,
TOTAL_PRICE NUMBER,
TOTAL_TAX NUMBER,
TOTAL_SHIPPING NUMBER)
PARTITION BY RANGE(ORDER_DATE)
(
PARTITION SO99Q1 VALUES LESS THAN TO_DATE(‘01-APR-1999’, ‘DD-MON-YYYY’),
PARTITION SO99Q2 VALUES LESS THAN TO_DATE(‘01-JUL-1999’, ‘DD-MON-YYYY’),
PARTITION SO99Q3 VALUES LESS THAN TO_DATE(‘01-OCT-1999’, ‘DD-MON-YYYY’),
PARTITION SO99Q4 VALUES LESS THAN TO_DATE(‘01-JAN-2000’, ‘DD-MON-YYYY’),
PARTITION SO00Q1 VALUES LESS THAN TO_DATE(‘01-APR-2000’, ‘DD-MON-YYYY’),
PARTITION SO00Q2 VALUES LESS THAN TO_DATE(‘01-JUL-2000’, ‘DD-MON-YYYY’),
PARTITION SO00Q3 VALUES LESS THAN TO_DATE(‘01-OCT-2000’, ‘DD-MON-YYYY’),
PARTITION SO00Q4 VALUES LESS THAN TO_DATE(‘01-JAN-2001’, ‘DD-MON-YYYY’)
);

the above example basically created 8 partitions on the SAMPLE_ORDERS Table all these partitions correspond to one quarter. Partition SO99Q1 will contain the orders for only first quarter of 1999. 
MAXVALUE is provided as a catch-all for values that exceed all ranges specified. Note that Oracle sorts NULLs greater than all other values, except MAXVALUE.

Partition on a numeric value range:
CREATE TABLE emp (
   empno NUMBER(4), 
   ename VARCHAR2(30), 
   sal   NUMBER
) 
PARTITION BY RANGE(empno) (
  partition e1 values less than (1000)     tablespace ts1, 
  partition e2 values less than (2000)     tablespace ts2, 
  partition e3 values less than (MAXVALUE) tablespace ts3
);
Partition on a VARCHAR2 string:
CREATE TABLE emp
( id        NUMBER(5)    PRIMARY KEY,
  name      VARCHAR2(50) NOT NULL,
  phone     VARCHAR2(15),
  email     VARCHAR2(100) )
PARTITION BY RANGE ( name )
     ( PARTITION p1 VALUES LESS THAN ('L')      TABLESPACE ts1,
       PARTITION p2 VALUES LESS THAN (MAXVALUE) TABLESPACE ts2 )
Time based range partitioning:
CREATE TABLE t1 (id NUMBER, c1 DATE)
PARTITION BY RANGE (c1)
  (PARTITION t1p1 VALUES LESS THAN (TO_DATE('2007-11-01', 'YYYY-MM-DD')),
   PARTITION t1p2 VALUES LESS THAN (TO_DATE('2007-12-01', 'YYYY-MM-DD')),
   PARTITION t1p3 VALUES LESS THAN (TO_DATE('2008-01-01', 'YYYY-MM-DD')),
   PARTITION t1p4 VALUES LESS THAN (MAXVALUE)
  );

2. HASH Partitioning
Hash partitioning is a partitioning technique where a hash key is used to distribute rows evenly across the different partitions (sub-tables). This is typically used where ranges aren't appropriate, i.e. employee number, productID, etc.
 "Hash Partitioning" does not have any logical meaning to the partitions as do the range partitioning. Lets take one example. 
CREATE TABLE SAMPLE_ORDERS
(ORDER_NUMBER NUMBER,
ORDER_DATE DATE,
CUST_NUM NUMBER,
TOTAL_PRICE NUMBER,
TOTAL_TAX NUMBER,
TOTAL_SHIPPING NUMBER,
ORDER_ZIP_CODE)
PARTITION BY HASH (ORDER_ZIP_CODE)
(PARTITION P1_ZIP TABLESPACE TS01, 
PARTITION P2_ZIP TABLESPACE TS02,
PARTITION P3_ZIP TABLESPACE TS03, 
PARTITION P4_ZIP TABLESPACE TS04)
ENABLE ROW MOVEMENT;

The above example creates four hash partitions based on the zip codes from where the orders were placed.
create table emp2 (
   empno number(4), 
   ename varchar2(30), 
   sal   number
) 
partition by hash(empno) (
  partition e1 tablespace emp1, 
  partition e2 tablespace emp2, 
  partition e3 tablespace emp3,
  partition e4 tablespace emp4
); 
create table emp2 (
   empno number(4), 
   ename varchar2(30), 
   sal   number
) 
PARTITION BY HASH(empno)
PARTITIONS 3
STORE IN (empts1, empts2, empts3);

3. List Partitioning ( Only with 9i)
Under this type of partitioning the records in a table are partitioned based on the List of values for a table with say communities column as a defining key the partitions can be made based on that say in a table we have communities like ‘Government’ , ‘Asian’ , ‘Employees’ , ‘American’, ‘European’ then a List Partition can be created for individual or a group of communities lets say ‘American-partition’ will have all the records having the community as ‘American’

Lets take one example. In fact, we will modify the same example.
CREATE TABLE SAMPLE_ORDERS
(ORDER_NUMBER NUMBER,
ORDER_DATE DATE,
CUST_NUM NUMBER,
TOTAL_PRICE NUMBER,
TOTAL_TAX NUMBER,
TOTAL_SHIPPING NUMBER,
SHIP_TO_ZIP_CODE,
SHIP_TO_STATE)
PARTITION BY LIST (SHIP_TO_STATE)
(PARTITION SHIP_TO_ARIZONA VALUES (‘AZ’) TABLESPACE TS01, 
PARTITION SHIP_TO_CALIFORNIA VALUES (‘CA’) TABLESPACE TS02, 
PARTITION SHIP_TO_ILLINOIS VALUES (‘IL’) TABLESPACE TS03, 
PARTITION SHIP_TO_MASACHUSETTES VALUES (‘MA’) TABLESPACE TS04, 
PARTITION SHIP_TO_MICHIGAN VALUES (‘MI’) TABLESPACE TS05)
ENABLE ROW MOVEMENT;

The above example creates List partition based on the SHIP_TO_STATE each partition allocated to different table spaces.
CREATE TABLE myemp_work (
       emp#   NUMBER PRIMARY KEY,
       ename    VARCHAR2(30),
       salary   NUMBER(8,2),
       deptno   NUMBER)
  PARTITION BY LIST (deptno) (   -- Add list partitioning
       PARTITION p10 VALUES (10), 
       PARTITION p20 VALUES (20), 
       PARTITION p30 VALUES (30,40));

4. Composite Range-Hash Partitioning
This is basically a combination of range and hash partitions. So basically, the first step is that 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. 

Lets modify the above example again:
CREATE TABLE SAMPLE_ORDERS
(ORDER_NUMBER NUMBER,
ORDER_DATE DATE,
CUST_NUM NUMBER,
CUST_NAME VARCAHR2, 
TOTAL_PRICE NUMBER,
TOTAL_TAX NUMBER,
TOTAL_SHIPPING NUMBER,
SHIP_TO_ZIP_CODE,
SHIP_TO_STATE)
TABLESPACE USERS
PARTITION BY RANGE (ORDER_DATE) 
SUBPARTITION BY HASH(CUST_NAME) 
SUBPARTITION TEMPLATE(
(SUBPARTITION SHIP_TO_ARIZONA VALUES (‘AZ’) TABLESPACE TS01, 
SUBPARTITION SHIP_TO_CALIFORNIA VALUES (‘CA’) TABLESPACE TS02, 
SUBPARTITION SHIP_TO_ILLINOIS VALUES (‘IL’) TABLESPACE TS03, 
SUBPARTITION SHIP_TO_NORTHEAST VALUES (‘MA’, ‘NY’, ‘NJ’) TABLESPACE TS04, 
SUBPARTITION SHIP_TO_MICHIGAN VALUES (‘MI’) TABLESPACE TS05)
(
PARTITION SO99Q1 VALUES LESS THAN TO_DATE(‘01-APR-1999’, ‘DD-MON-YYYY’),
PARTITION SO99Q2 VALUES LESS THAN TO_DATE(‘01-JUL-1999’, ‘DD-MON-YYYY’),
PARTITION SO99Q3 VALUES LESS THAN TO_DATE(‘01-OCT-1999’, ‘DD-MON-YYYY’),
PARTITION SO99Q4 VALUES LESS THAN TO_DATE(‘01-JAN-2000’, ‘DD-MON-YYYY’),
PARTITION SO00Q1 VALUES LESS THAN TO_DATE(‘01-APR-2000’, ‘DD-MON-YYYY’),
PARTITION SO00Q2 VALUES LESS THAN TO_DATE(‘01-JUL-2000’, ‘DD-MON-YYYY’),
PARTITION SO00Q3 VALUES LESS THAN TO_DATE(‘01-OCT-2000’, ‘DD-MON-YYYY’),
PARTITION SO00Q4 VALUES LESS THAN TO_DATE(‘01-JAN-2001’, ‘DD-MON-YYYY’)
)
ENABLE ROW MOVEMENT;

The above example shows that each range partition has been further sub-partitioned into smaller partitions based on the list value specified. SHIP_TO_ARIZONA is a sub-partition by a List value AZ. This partition will be present in the main partitions by range SO99Q1 etc.

5. Composite Range-List Partitioning

This is basically a combination of range and list partitions. So basically, the first step is that the data is divided using the range partition and then each range partitioned data is further subdivided into a list partition using list of values. All sub partitions, together, represent a logical subset of the data. 

The following example illustrates how range-list partitioning might be used. The example tracks sales data of products by quarters and within each quarter, groups it by specified states.
CREATE TABLE quarterly_regional_sales
      (deptno number, item_no varchar2(20),
       txn_date date, txn_amount number, state varchar2(2))
  TABLESPACE ts4
  PARTITION BY RANGE (txn_date)
    SUBPARTITION BY LIST (state)
      (PARTITION q1_1999 VALUES LESS THAN (TO_DATE('1-APR-1999','DD-MON-YYYY'))
         (SUBPARTITION q1_1999_northwest VALUES ('OR', 'WA'),
          SUBPARTITION q1_1999_southwest VALUES ('AZ', 'UT', 'NM'),
          SUBPARTITION q1_1999_northeast VALUES ('NY', 'VM', 'NJ'),
          SUBPARTITION q1_1999_southeast VALUES ('FL', 'GA'),
          SUBPARTITION q1_1999_northcentral VALUES ('SD', 'WI'),
          SUBPARTITION q1_1999_southcentral VALUES ('OK', 'TX')
         ),
       PARTITION q2_1999 VALUES LESS THAN ( TO_DATE('1-JUL-1999','DD-MON-YYYY'))
         (SUBPARTITION q2_1999_northwest VALUES ('OR', 'WA'),
          SUBPARTITION q2_1999_southwest VALUES ('AZ', 'UT', 'NM'),
          SUBPARTITION q2_1999_northeast VALUES ('NY', 'VM', 'NJ'),
          SUBPARTITION q2_1999_southeast VALUES ('FL', 'GA'),
          SUBPARTITION q2_1999_northcentral VALUES ('SD', 'WI'),
          SUBPARTITION q2_1999_southcentral VALUES ('OK', 'TX')
         ),
       PARTITION q3_1999 VALUES LESS THAN (TO_DATE('1-OCT-1999','DD-MON-YYYY'))
         (SUBPARTITION q3_1999_northwest VALUES ('OR', 'WA'),
          SUBPARTITION q3_1999_southwest VALUES ('AZ', 'UT', 'NM'),
          SUBPARTITION q3_1999_northeast VALUES ('NY', 'VM', 'NJ'),
          SUBPARTITION q3_1999_southeast VALUES ('FL', 'GA'),
          SUBPARTITION q3_1999_northcentral VALUES ('SD', 'WI'),
          SUBPARTITION q3_1999_southcentral VALUES ('OK', 'TX')
         ),
       PARTITION q4_1999 VALUES LESS THAN ( TO_DATE('1-JAN-2000','DD-MON-YYYY'))
         (SUBPARTITION q4_1999_northwest VALUES ('OR', 'WA'),
          SUBPARTITION q4_1999_southwest VALUES ('AZ', 'UT', 'NM'),
          SUBPARTITION q4_1999_northeast VALUES ('NY', 'VM', 'NJ'),
          SUBPARTITION q4_1999_southeast VALUES ('FL', 'GA'),
          SUBPARTITION q4_1999_northcentral VALUES ('SD', 'WI'),
          SUBPARTITION q4_1999_southcentral VALUES ('OK', 'TX')
         )
      );


A row is mapped to a partition by checking whether the value of the partitioning column for a row falls within a specific partition range. The row is then mapped to a subpartition within that partition by identifying the subpartition whose descriptor value list contains a value matching the subpartition column value.
For example, some sample rows are inserted as follows:

(10, 4532130, '23-Jan-1999', 8934.10, 'WA') maps to subpartition q1_1999_northwest
(20, 5671621, '15-May-1999', 49021.21, 'OR') maps to subpartition q2_1999_northwest
(30, 9977612, '07-Sep-1999', 30987.90, 'FL') maps to subpartition q3_1999_southeast
(40, 9977612, '29-Nov-1999', 67891.45, 'TX') maps to subpartition q4_1999_southcentral
(40, 4532130, '5-Jan-2000', 897231.55, 'TX') does not map to any partition in the table and raises an error
(50, 5671621, '17-Dec-1999', 76123.35, 'CA') does not map to any subpartition in the table and raises an error

When to use which Partition Type

The following sections can help you decide on a partitioning method appropriate for your needs:

When to Use Range Partitioning
Use range partitioning to map rows to partitions based on ranges of column values. This type of partitioning is useful when dealing with data that has logical ranges into which it can be distributed; for example, months of the year. Performance is best when the data evenly distributes across the range. If partitioning by range causes partitions to vary dramatically in size because of unequal distribution, you may want to consider one of the other methods of partitioning.
When creating range partitions, you must specify:
  • Partitioning method: range
  • Partitioning column(s)
  • Partition descriptions identifying partition bounds
When to Use Hash Partitioning
Use hash partitioning if your data does not easily lend itself to range 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. Creating and using hash partitions gives you a highly tunable method of data placement, because you can influence availability and performance by spreading these evenly sized partitions across I/O devices (striping).
To create hash partitions you specify the following:
  • Partitioning method: hash
  • Partitioning column(s)
  • Number of partitions or individual partition descriptions
When to Use List Partitioning
Use list partitioning when you require explicit control over how rows map to partitions. You can specify a list of discrete values for the partitioning column in the description for each partition. This is different from range partitioning, where a range of values is associated with a partition, and from hash partitioning, where the user has no control of the row to partition mapping.
The list partitioning method is specifically designed for modeling data distributions that follow discrete values. This cannot be easily done by range or hash partitioning because:
  • Range partitioning assumes a natural range of values for the partitioning column. It is not possible to group together out-of-range values partitions.
  • Hash partitioning allows no control over the distribution of data because the data is distributed over the various partitions using the system hash function. Again, this makes it impossible to logically group together discrete values for the partitioning columns into partitions.
Further, list partitioning allows unordered and unrelated sets of data to be grouped and organized together very naturally.
Unlike the range and hash partitioning methods, multicolumn partitioning is not supported for list partitioning. If a table is partitioned by list, the partitioning key can consist only of a single column of the table. Otherwise all columns that can be partitioned by the range or hash methods can be partitioned by the list partitioning method.
When creating list partitions, you must specify:
  • Partitioning method: list
  • Partitioning column
  • Partition descriptions, each specifying a list of literal values (a value list), which are the discrete values of the partitioning column that qualify a row to be included in the partition
When to Use Composite Range Hash Partitioning
Range-hash partitioning partitions data using the range method, and within each partition, subpartitions it using the hash method. These composite partitions are ideal for both historical data and striping, and provide improved manageability of range partitioning and data placement, as well as the parallelism advantages of hash partitioning.
When creating range-hash partitions, you specify the following:
  • Partitioning method: range
  • Partitioning column(s)
  • Partition descriptions identifying partition bounds
  • Subpartitioning method: hash
  • Subpartitioning column(s)
  • Number of subpartitions for each partition or descriptions of subpartitions
When to Use Composite Range List Partitioning
Like the composite range-hash partitioning method, the composite range-list partitioning method provides for partitioning based on a two level hierarchy. The first level of partitioning is based on a range of values, as for range partitioning; the second level is based on discrete values, as for list partitioning. This form of composite partitioning is well suited for historical data, but lets you further group the rows of data based on unordered or unrelated column values.
When creating range-list partitions, you specify the following:
  • Partitioning method: range
  • Partitioning column(s)
  • Partition descriptions identifying partition bounds
  • Subpartitioning method: list
  • Subpartitioning column
  • Subpartition descriptions, each specifying a list of literal values (a value list), which are the discrete values of the subpartitioning column that qualify a row to be included in the subpartition
Source: 
1. http://www.devarticles.com/c/a/Oracle/Partitioning-in-Oracle/1/
2. http://docs.oracle.com/cd/B10501_01/server.920/a96521/partiti.htm

1 comments:

If you want your ex-girlfriend or ex-boyfriend to come crawling back to you on their knees (no matter why you broke up) you got to watch this video
right away...

(VIDEO) Get your ex CRAWLING back to you...?

 

You Might Also Like

Related Posts with Thumbnails

Pages