Oracle provides partitioning of Tables, Indexes and IOTs to split their contents into different partitions. Each partition could be on a separate physical drive. This provides faster performance for read / write operations, improves manageability, and increases availability of the database.
In data warehouse this has great significance - when we want to load / refresh huge data, the cost of loading data as well as dropping / creating indexes can be huge. This can be reduced by partitioning the tables and indexes.
Tables are partitioned using a 'partitioning key', a set of columns which determine in which partition a given row will reside. Oracle9i provides five techniques for partitioning tables:
Range Partitioning: Each partition is specified by a range of values of the partitioning key (e.g. a trade table can be partitioned by the year of the trade date')
List Partitioning: Each partition is specified by a list of values of the partitioning key (sales data can be partitioned by region in which countries falling under NA, EMEA etc can be grouped under separate lists.)
Hash Partitioning: A hash algorithm is applied to the partitioning key to determine the partition for a given row
Composite Range-Hash Partitioning: A combination of the Range and Hash partitioning technique. The table is first range-partitioned, and then each individual range-partition is further sub-partitioned using the hash partitioning technique. All sub-partitions for a given range partition together represent a logical subset of the data.
Composite Range-List Partitioning: A combination of the Range and List partitioning technique. The table is first range-partitioned, and then each individual range-partition is further sub-partitioned using a list partitioning technique.
Above answer was rated as good by the following members: Vamshidhar
RE: What is partitioning? What are the types of partitioning?
Partitioning is a part of physical data warehouse design that is carried out to improve performance and simplify stored-data management. Partitioning is done to break up a large table into smaller independently-manageable components because it: 1. reduces work involved with addition of new data. 2. reduces work involved with purging of old data.
Two types of partitioning are: 1. Horizontal partitioning. 2. Vertical partitioning (reduces efficiency in the context of a data warehouse).
RE: What is partitioning? What are the types of partitioning?
Oracle provides partitioning of Tables Indexes and IOTs to split their contents into different partitions. Each partition could be on a separate physical drive. This provides faster performance for read / write operations improves manageability and increases availability of the database.
In data warehouse this has great significance - when we want to load / refresh huge data the cost of loading data as well as dropping / creating indexes can be huge. This can be reduced by partitioning the tables and indexes.
Tables are partitioned using a 'partitioning key' a set of columns which determine in which partition a given row will reside. Oracle9i provides five techniques for partitioning tables:
Range Partitioning: Each partition is specified by a range of values of the partitioning key (e.g. a trade table can be partitioned by the year of the trade date')
List Partitioning: Each partition is specified by a list of values of the partitioning key (sales data can be partitioned by region in which countries falling under NA EMEA etc can be grouped under separate lists.)
Hash Partitioning: A hash algorithm is applied to the partitioning key to determine the partition for a given row
Composite Range-Hash Partitioning: A combination of the Range and Hash partitioning technique. The table is first range-partitioned and then each individual range-partition is further sub-partitioned using the hash partitioning technique. All sub-partitions for a given range partition together represent a logical subset of the data.
Composite Range-List Partitioning: A combination of the Range and List partitioning technique. The table is first range-partitioned and then each individual range-partition is further sub-partitioned using a list partitioning technique.
1 
0