Jun 28

Table Partitions
Partitioned tables allow your data to be broken down into smaller, more manageable pieces called Partitions, or even Sub Partitions. Indexes can be partitioned in similar fashion. Each partition is stored in its own segment and can be managed individually.
SQL queries and DML statements do not need to be modified in order to access partitioned tables.
However, after partitions are defined, DDL statements can access and manipulate individual partitions rather than entire tables or indexes. This is how partitioning can simplify the manageability of large database objects and enables faster data access within an Oracle database.

Partitioning allows tables and indexes to be partitioned into smaller, more manageable units, providing database administrators with the ability to pursue a “divide and conquer” approach to data management. With partitioning, maintenance operations can be focused on particular portions of tables. For example, a database administrator could back up a single partition of a table, rather than backing up the entire table.

Advantages of Partitioning:
• It enables data management operations such as data loads, index creation and rebuilding, and backup/recovery at the partition level, rather than on the entire table. This results in significantly reduced times for these operations.
• It improves query performance. In many cases, the results of a query can be achieved by accessing a subset of partitions, rather than the entire table.
• It increases the availability of mission-critical databases if critical tables and indexes are divided into partitions to reduce the maintenance windows, recovery times, and impact of failures.

Figure 1 List, Range, and Hash Partitioning

Types of Table Partitions

There are several partitioning methods offered by Oracle Database. Here, we have to discuss three basic partitions:

Range Partitioning
Range partitioning is used when 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.

Range Partitioning Example
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_jan2010 VALUES LESS THAN(TO_DATE(’02/01/2010′,’DD/MM/YYYY’)),
PARTITION sales_feb2010 VALUES LESS THAN(TO_DATE(’03/01/2010′,’DD/MM/YYYY’)),
PARTITION sales_mar2010 VALUES LESS THAN(TO_DATE(’04/01/2010′,’DD/MM/YYYY’)),
PARTITION sales_apr2010 VALUES LESS THAN(TO_DATE(’05/01/2010′,’DD/MM/YYYY’))
);

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.

Hash Partitioning Example
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 (data1, data2, data3, data4)

The preceding statement creates a table sales_hash, which is hash partitioned on salesman_id field. The tablespace names are data1, data2, data3, and data4.

List Partitioning
List partitioning is used 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.

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.

List Partitioning 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)
)
A row is mapped to a partition by checking whether the value of the partitioning column for a row falls within the set of values that describes the partition. For example, the rows are inserted as follows:
• (10, ‘Jones’, ‘Hawaii’, 100, ’05-JAN-2010′) maps to partition sales_west
• (21, ‘Smith’, ‘Florida’, 150, ’15-JAN-2010′) maps to partition sales_east
• (32, ‘Lee’, ‘Colorado’, 130, ’21-JAN-2010′) does not map to any partition in the table

In my view, this is the best time for proessaywriting.org/ cheap essay writing service reflection about what we can do to make next year even better than this year