DSS clients are more tolerant of long-running queries. However, appropriate fragmentation can shorten the response time in the following ways:
Parallel table scans are an important benefit of fragmentation. For non-fragmented tables, a single thread reads the data to satisfy a query, regardless of the amount of data.
Fragment large tables to get the benefit of parallel scans. A separate scan thread scans each fragment that needs to be read. To maximize throughput by reading the data in parallel, the scan threads can run in different CPU VPs, and in turn the CPU VPs can run on many CPUs on several coservers.
A second important benefit of fragmentation is the ability of the database server to identify and eliminate fragments that do not contain data that is needed to satisfy a query. Refer Table 2 for information about fragmentation schemes that permit fragment elimination.
To determine which fragments have been eliminated from the query plan, examine the SET EXPLAIN output.
Data warehouses and data marts are usually updated by loading data from an external source, such as cleaned and transformed OLTP data. The program that loads data from external to internal database tables is fast and efficient. If the external data files and the internal table are fragmented across multiple coservers, the database server can execute all steps of the load process in parallel.
If your fragmentation scheme allows you to upload new data to a small table and attach it as a fragment to a large table, updating tables is even faster. Fragments that contain old data can be detached from the large current table and dropped or attached to an archive version that is used to store extended history.