Techniques to reduce IO - partitioning and compression

Last time I wrote a few general notes about the storage of fact and reference data. I now will look at partitioning and compression in slightly more detail

Partitioning is a way to divide a large table into separately managed chunks; the contents of each chuck being determined by the partitioning key. Although it is possible to create a table with a single partition (and this does have uses!) most partitioned tables would have multiple partitions. With Oracle there are three possible type of partition key: Hash, Range, and List. Other database vendors also offer partitioning, most recently it was Microsoft with range partitioning in SQL Server 2005, however the partitioning from most vendors is offen restricted to one type (and usually hash)

Range and list partitions are perhaps simplest to understand; each table has a key column (or columns in the case of a compound key) rows being allocated to partitions based on the value of the key. In the case of range we store values less than the partition's key maximum value, and for list we store rows where the key matches a value in the list for the partition. With hash partitioning the column value is first hashed to determine which partition to use. The hashing algorithm works best if there is an exact power of two partitions in a table. Composite partitioning is also possible; here the partitions are further sub-divided into sub-partitions. In Oracle 8i, sub-partitions where limited to hash, but 10g we can also use list (but not range) sub-partitions.

Partitioning schemes first and foremost allow us to improve database manageability by dividing large tables into smaller, more manageable chunks; however there is also the possibility that we can also boost performance by partitioning. Consider a query against a partitioned table; if we include the partition key in the predicate the database will know in which partition to find the required data, the other partitions will not be accessed. Similarly, if all the data to be removed from a data warehouse is held in a single partition (probably not a hash partition!) then we can simply truncate or drop a partition which is far, far quicker than deleting from a table based on a predicate.

Although hash partitioning can work well in OLTP systems it is my opinion that it is less useful in a data warehouse. As we are not normally aware of which partition contains which key values we are unlikely to be able to exploit any of the partition manipulation operations such as partition exchange or partition truncation as part of our data load process. We are also unlikely to achieve much partition elimination for queries that involve more than one partition key value and in the real world people often query on related groups of items such as the cities in a region and the hashing algorithm would most likely spread the data across many partitions. A further limitation of hash partitioning is that we are unlikely to be able to set the tablespace for a partition to be read only as it is unlikely that we could ensure that newly arrived data would belong in that partition

Table Compression is another good way to reduce IO. The rational behind this is that we are reading lots of rows from a table and therefore the more rows we can read in one hit the better as the overhead to uncompress the rows is less than overhead in reading the uncompressed rows from disk. Table compression works with both tables and partitions (you can even mix compressed and uncompressed partitions within a single table). Unlike index compression, table compression only works on bulk insert; you can still use non-bulk techniques to insert into a compressed table but the data inserted will not be compressed, and updating rows also cause those rows to be uncompressed. Compression is organised by database blocks and relies on the detection of repeating data patterns within the block. The repeating patterns are tokenised and stored in a look-up area within the block and the data itself is replaced with the tokens. Enhanced compression can be obtained by ordering the data being inserted to maximise the number of tokens being replaced.