| Team Fly | |
Other commands that would normally apply at the table level can also be applied to a particular partition of the table. These include, but are not limited to: delete, insert, select, truncate, and update.
Performance
One of the main reasons for partitioning a table is to improve I/O response time when selecting data from the table. Having a table's data partitioned into subsets can yield much faster query results when you are looking for data that is limited to one subset of the total. Let's look at an example to illustrate.
Assume the SALES table contains 100 million records representing daily sales revenue for the three years 2005 to 2007 inclusive. We want to know what the total revenue is for February 2006. The query might look something like this:
select sum(amount_sold)
from sales
where time_id between to_date('2006-02-01', 'YYYY-MM-DD') and
to_date('2006-02-28', 'YYYY-MM-DD');
Using a nonpartitioned table design, all 100 million rows would need to be scanned to determine if they belong to the date criteria. Using a partitioned table design based on monthly partitions with about 2.8 million rows for each month, only those rows in the February 2006 partition (and therefore only about 2.8 million rows) would be scanned. The process of eliminating data not belonging to the subset defined by the query criteria is referred to as partition pruning.
With the basic concepts of partitioning and why we use it under our belts, we can now discuss the finer details of how to implement partitioning.
| Team Fly | |