Partitioning in oracle
Partitioning the Tables in Oracle database is an important aspect when it comes to Performance Management.As the number of rows in table increases,The performance impacts wil increase
Backup and recovery process may take longer time than usal and sql queries that affecting entire table will take loger time.
we can reduce the performance issue causing by large tables through separating the rows of a single table into
multiple parts. Dividing a table’s data in this manner is called partitioning the table.the table that is partitioned is called a partitioned table, and the parts are called partitions.
By splitting a large table’s rows across multiple smaller partitions,
- The performance of queries against the tables may improve because Oracle may have to search only one partition (one part of the table) instead of the entire table to resolve a query.
- The table may be easier to manage. Because the partitioned table’s data is stored in multiple parts, it may be easier to load and delete data in the partitions than in the large table.
- Backup and recovery operations may perform better. Because the partitions are smaller than the partitioned table, you may have more options for backing up and recovering the partitions than you would have for a single large table.
In Oracle we can partition a table by the following methods
Range Partitioning
Tables are partitioned by ranges of values.useful when dealing with data that has logical ranges into which it can be distributed.
eg:
create table Library
( Title VARCHAR2(100) primary key,
Publisher VARCHAR2(20),
CategoryName VARCHAR2(20),
Rating VARCHAR2(2),
references CATEGORY(CategoryName)
)
partition by range (CategoryName)
table will be partitioned based on the values in the CategoryName
Hash Partitioning
A hash partition determines the physical placement of data by performing a hash function on the values of the partition key.In hash partitioning, consecutive values of the partition key are not generally stored in the same
partition. Hash partitioning distributes a set of records over a greater set of partitions than range partitioning does, potentially decreasing the likelihood for I/O contention.
To create a hash partition, use the partition by hash clause in place of partition by range
eg:
create table Library
( Title VARCHAR2(100) primary key,
Publisher VARCHAR2(20),
CategoryName VARCHAR2(20),
Rating VARCHAR2(2),
references CATEGORY(CategoryName)
)
partition by hash (CategoryName)
partitions 16;
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.
List Partitioning
In list partitioning, you tell Oracle all the possible values and designate the partitions into which the corresponding rows should be inserted.
create table Library
( Title VARCHAR2(100) primary key,
Publisher VARCHAR2(20),
CategoryName VARCHAR2(20),
Rating VARCHAR2(2),
references CATEGORY(CategoryName)
)
partition by list (CategoryName)
Creating Subpartitions
You can create subpartitions—that is, partitions of partitions. You can use subpartitions to combine all types of partitions: range partitions, list partitions, and hash partitions. For example, you can use hash partitions in combination with range partitions, creating hash partitions of the range
partitions. For very large tables, this composite partitioning may be an effective way of separating the data into manageable and tunable divisions.
Indexing Partitions
When you create a partitioned table, you should create an index on the table. The index may be partitioned according to the same range values used to partition the table. In the following listing,
create index index_library
on library(CategoryName)
local
(partition PART1
tablespace PART1_NDX_TS,
partition PART2
tablespace PART2_NDX_TS);
Written by admin on March 26th, 2011 with
comments disabled.
Read more articles on Home.
- [+] Digg: Feature this article
- [+] Del.icio.us: Bookmark this article
- [+] Furl: Bookmark this article