Data Warehouse Systems-Partitioning
In a data warehouse/decision support system, partitioning is not only a great administrative tool but something that will speed up processing. For example, you may have a large table on which you need to perform an ad hoc query.
You always do the ad hoc query by sales quarter, as each sales quarter contains hundreds of thousands of records, and you have millions of online records. So, you want to query a relatively small slice of the entire data set, but it is not really feasible to index it based on the sales quarter.
This index would point to hundreds of thousands of records, and doing the index range scan in this way would be terrible (refer to Chapter 11 for more details on this). A full table scan is called for to process many of your queries, but you end up having to scan millions of records, most of which won’t apply to your query.
Using an intelligent partitioning scheme, you can segregate the data by quarter such that when you query the data for any given quarter, you will full scan only that quarter’s data. This is the best of all possible solutions.
In addition, in a data warehouse/decision support system environment, parallel query is used frequently. Here, operations such as parallel index range scans or parallel fast full index scans are not only meaningful but also beneficial to us.
We want to maximize our use of all available resources, and parallel query is a way to do it. So, in this environment, partitioning stands a very good chance of speeding up processing.
Reduced Contention in an OLTP System
The last general benefit area for partitioning is potentially increasing concurrency by decreasing contention in an OLTP system. Partitions can be used to spread the modifications of a single table out over many physical partitions.
The idea is if you have a segment experiencing high contention, turning it into many segments could have the side effect of reducing that contention proportionally.
For example, instead of having a single table segment with a single index segment, you might have 20 table partitions and 20 index partitions. It could be like having 20 tables instead of 1 (and 20 indexes instead of 1); hence, contention would be decreased for this shared resource during modifications.