Partitioned View

Databases usually hold a lot of records that may span into millions of tables and figures stored over many years. Users of such records might only want to use a small amount of data from the database. The database is split into smaller tables with similar structures to speed up query performance. A partitioned view is a view constructed on an array of tables, which reside on remote or local servers. The tables used in partitioned views have similar or close structures comprising of names of columns, types of data, scale and precision. Consequently, they have to be similar to obtain a view referred to as "a read-only" or be identical if one has to enable updates through the view (Seidman, 2000). Most multinational firms with huge databases employ horizontally portioned views to improve query performance during data retrieval (Seidman, 2000). Distributed portioned views are used to scale out database servers as a primary means.

Horizontally partitioned views allow for division of large tables into much smaller tables, while, at the same time, providing a single view as a way of accessing all the tables. When developing or using it, only one view needs to be queried in order to retrieve the records from any of smaller tables. A horizontally partitioned view provides a convenient interface and also offers performance boost to the database.

Apart from improving query performance, this portioned view also enables easy archival of old information into removable devices. In addition to that, it plays an important role of ensuring availability of data with costly overhead. To perform updates, the partitioning column should also be a part of the base table's primary key. To make the "read only" partitioned views updatable, "instead off" triggers can be used. When planning a portioned view, the design team should consider various issues, such as the partition column being a fragment of the primary key. Apart from that, the team should have in mind that the partition key should to be changed, the table would be recreated, data - reloaded, and indexes - rebuilt. It is also important to note that both the partition key and column must have similar data length, type and precision.