Use horizontal splitting if:
A table is large, and reducing its size reduces the number of index pages read in a query.
B-tree indexes, however, are generally very flat, and you can add large numbers of rows to a table with small index keys before the B-tree requires more levels.
An excessive number of index levels may be an issue with tables that have very large keys.
The table split corresponds to a natural separation of the rows, such as different geographical sites or historical versus current data.
You might choose horizontal splitting if you have a table that stores huge amounts of rarely used historical data, and your applications have high performance needs for current data in the same table.
Table splitting distributes data over the physical media, however, there are other ways to accomplish this goal.
Generally, horizontal splitting requires different table names in queries, depending on values in the tables. In most database applications this complexity usually far outweighs the advantages of table splitting .
As long as the index keys are short and indexes are used for queries on the table, doubling or tripling the number of rows in the table may increase the number of disk reads required for a query by only one index level. If many queries perform table scans, horizontal splitting may improve performance enough to be worth the extra maintenance effort.
Figure 6-9 shows how you might split the authors table to separate active and inactive authors:
Figure 6-9: Horizontal partitioning of active and inactive data
