Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Thursday, 30 August 2012

Partitioning Tables

Partitioning is useful for many different types of applications, particularly applications that manage large volumes of data. OLTP systems often benefit from improvements in manageability and availability, while data warehousing systems benefit from performance and manageability.
Here are some suggestions for when to partition a table:
- Tables greater than 2GB should always be considered for partitioning.
- Tables containing historical data, in which new data are added into the newest partition where only the current month's data is updatable and the other 11 months are read only.

Partitioned Tables

Tables can be partitioned into up to 64,000 separate partitions. Any table can be partitioned except those tables containing columns with LONG or LONG RAW datatypes.

1.     Range Partitioning

2.     List Partitioning

3.     Hash Partitioning

4.     Composite Partitioning

Range Partitioning

This type of partitioning is useful when dealing with data that has logical ranges into which it can be distributed; for example, value of year. Performance is best when the data evenly distributes across the range
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

Unlike range and hash partitioning, multicolumn partition keys are not supported for list partitioning. If a table is partitioned by list, the partitioning key can only consist of a single column of the table.
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 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.
CREATE TABLE sales_hash
(salesman_id  NUMBER(5), 
salesman_name VARCHAR2(30), 
sales_amount  NUMBER(10), 
week_no       NUMBER(2)) 
PARTITION BY HASH(salesman_id) 
PARTITIONS 4 
STORE IN (ts1, ts2, ts3, ts4);

 Composite Partitioning

CREATE TABLE sales_composite 
(salesman_id  NUMBER(5), 
 salesman_name VARCHAR2(30), 
 sales_amount  NUMBER(10), 
 sales_date    DATE)
PARTITION BY RANGE(sales_date) 
SUBPARTITION BY HASH(salesman_id)
SUBPARTITION TEMPLATE(
SUBPARTITION sp1 TABLESPACE ts1,
SUBPARTITION sp2 TABLESPACE ts2,
SUBPARTITION sp3 TABLESPACE ts3,
SUBPARTITION sp4 TABLESPACE ts4)
(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')),
 PARTITION sales_may2000 VALUES LESS THAN(TO_DATE('06/01/2000','DD/MM/YYYY')));
This statement creates a table sales_composite that is range partitioned on the sales_date field and hash subpartitioned on salesman_id. Because the subpartitions are not named, system generated names are assigned

ALTERING PARTITION TABLES

To add a partition: Range partition:
SQL>Alter table sales add partition p6 values less than (1996);
To add a partition: Hash partition:
SQL>Alter table products add partition;
SQL>Alter table products add partition p5 tablespace u5;
To add a partition: List Partition:
SQL>Alter table customers add partition central_India values (‘BHOPAL’,’NAGPUR’);

Note: Any value in the set of literal values that describe the partition(s) being added must not exist in any of the other partitions 
of the table.

Coalescing Partitions

Coalescing partitions is a way of reducing the number of partitions in a hash-partitioned table, or the number of subpartitions in a composite-partitioned table. When a hash partition is coalesced, its contents are redistributed into one or more remaining partitions determined by the hash function. The specific partition that is coalesced is selected by Oracle, and is dropped after its contents have been redistributed.
SQL>Alter table products coalesce partition;

Note: This reduces by one the number of partitions in the table products each time the command run with coalesce partition;

DROPPING PARTITIONS

SQL>Alter table sales drop partition p5;
Once you have drop the partition and if you have created a global index on the table. Then you have to rebuild the global index.
SQL>Alter index sales_ind rebuild;
To avoid rebuilding of indexes after dropping of the partitions you can also first delete all the records and then drop the partition
Delete from sales where year=1994;
SQL>Alter table sales drop partition p4;
SQL>ALTER TABLE sales DROP PARTITION p5 UPDATE GLOBAL INDEXES;
This causes the global index to be updated at the time the partition is drope.

Exchanging a Range, Hash, or List Partition

Sql>ALTER TABLE stocks EXCHANGE PARTITION p3 with TABLE stocks1;

Merging Partitions

SQL>Alter table sales merge partition p2 and p3 into
partition p23;

Modifying Partitions: Adding Values

SQL>ALTER TABLE customers MODIFY PARTITION south_india
      ADD VALUES ('KOCHI', 'MANGALORE');

Modifying Partitions: Dropping Values

SQL>ALTER TABLE customers MODIFY PARTITION south_india
      DROP VALUES (‘KOCHI’,’MANGALORE’);

SPLITTING PARTITIONS

SQL>Alter table sales split partition p5 into 
  (Partition p6 values less than (1996),
   Partition p7 values less then (MAXVALUE));

 TRUNCATING PARTITON

SQL>Alter table sales truncate partition p5;

MONITORING  PARTITION TABLES OR INDEX

USER_PART_TABLES;USER_TAB_PARTITIONS;DBA_TAB_PARTITIONS;DBA_IND_PARTITIONS;DBA_PART_INDEXES;

 Local Prefixed Indexes

Assuming the INVOICES table is range partitioned on INVOICE_DATE
CREATE INDEX invoices_idx ON invoices (invoice_date) LOCAL;

Local Non-Prefixed Indexes

CREATE INDEX invoices_idx ON invoices (invoice_no) LOCAL;

Global Prefixed Indexes

CREATE INDEX invoices_idx ON invoices (invoice_date)
GLOBAL PARTITION BY RANGE (invoice_date)
 (PARTITION invoices_q1 VALUES LESS THAN (TO_DATE('01/04/2001', 'DD/MM/YYYY')) TABLESPACE users,
  PARTITION invoices_q2 VALUES LESS THAN (TO_DATE('01/07/2001', 'DD/MM/YYYY')) TABLESPACE users,
  PARTITION invoices_q3 VALUES LESS THAN (TO_DATE('01/09/2001', 'DD/MM/YYYY')) TABLESPACE users,
  PARTITION invoices_q4 VALUES LESS THAN (MAXVALUE) TABLESPACE users);

0 comments:

Post a Comment