Partition (database)

Partition (database)

A partition is a division of a logical database or its constituting elements into distinct independent parts. Database partitioning is normally done for manageability, performance or availability reasons.

A popular and favourable application of partitioning is in a distributed database management system. Each partition may be spread over multiple nodes, and users at the node can perform local transactions on the partition. This increases performance for sites that have regular transactions involving certain views of data, whilst maintaining availability and security.

The partitioning can be done by either building separate smaller databases (each with its own tables, indices, and transaction logs), or by splitting selected elements, for example just one table.

Horizontal partitioning (also see "shard") involves putting different rows into different tables. Perhaps customers with ZIP codes less than 50000 are stored in CustomersEast, while customers with ZIP codes greater than or equal to 50000 are stored in CustomersWest. The two partition tables are then CustomersEast and CustomersWest, while a view with a union might be created over both of them to provide a complete view of all customers.

Vertical partitioning involves creating tables with fewer columns and using additional tables to store the remaining columns. Normalization is a process that inherently involves vertical partitioning. Different physical storage might be used to realize vertical partitioning as well; storing infrequently used or very wide columns on a different device, for example, is a method of vertical partitioning. Done explicitly or implicitly, this type of partitioning is called "row splitting". A common form of vertical partitioning is to split (slow to find) dynamic data from (fast to find) static data in a table where the dynamic data is not used as often as the static. Creating a view across the two newly created tables restores the original table with a performance penalty, however performance will increase when accessing the static data e.g. for statistical analysis.

Partitioning criteria

Current high end relational database management systems provide for different criteria to split the database. They take a "partitioning key" and assign a partition based on certain criteria. Common criteria are:

; Range partitioning : Selects a partition by determining if the partitioning key is inside a certain range. An example could be a partition for all rows where the column zipcode has a value between 70000 and 79999.; List partitioning : A partition is assigned a list of values. If the partitioning key has one of these values, the partition is chosen. For example all rows where the column Country is either Iceland, Norway, Sweden, Finland or Denmark could build a partition for the Nordic countries.; Hash partitioning : The value of a hash function determines membership in a partition. Assuming there are four partitions, the hash function could return a value from 0 to 3.

Composite partitioning allows for certain combinations of the above partitioning schemes, by for example first applying a range partitioning and then a hash partitioning. Consistent hashing could be considered a composite of hash and list partitioning where the hash reduces the key space to a size that can be listed.

External links

* [ IBM DB2 partitioning]
* [ MySQL partitioning]
* [ Oracle partitioning]
* [ SQL Server partitions]
* [ PostgreSQL partitioning]
* [ Sybase ASE 15.0 partitioning]

Wikimedia Foundation. 2010.

Игры ⚽ Поможем решить контрольную работу

Look at other dictionaries:

  • Partition — Generally, a partition is a splitting of something into parts. The term is used in a variety of senses: Law *Partition (law), to divide up a piece of land into separate portions representing the proportionate interests of the tenants. It may also …   Wikipedia

  • Database virtualization — is the decoupling of the database layer, which lies between the storage and application layers within the application stack. Virtualization at the database layer allows hardware resources to be extended to allow for better sharing resources… …   Wikipedia

  • Partition film — Partition (film) Pour les articles homonymes, voir Partition. Partition Acteurs principaux Jimi Mistry Kristin Kreuk Neve Campbell Irfan Khan John Light Musique Brian Tyler Montage …   Wikipédia en Français

  • Partition inachevée pour piano mécanique — (Неоконченная пьеса для механического пианино) est un film russe réalisé par Nikita Mikhalkov, produit en 1977 et sorti en France en 1979. Sommaire 1 Synopsis 2 Fiche technique 3 Distribution …   Wikipédia en Français

  • Database design — is the process of producing a detailed data model of a database. This logical data model contains all the needed logical and physical design choices and physical storage parameters needed to generate a design in a Data Definition Language, which… …   Wikipedia

  • Database administration and automation — Database administration is the function of managing and maintaining database management systems (DBMS) software. Mainstream DBMS software such as Oracle, IBM DB2 and Microsoft SQL Server need ongoing management. As such, corporations that use… …   Wikipedia

  • Database storage structures — Database tables/indexes are typically stored on hard disk in one of many forms, ordered/unordered Flat files, ISAM, Heaps, Hash buckets or B+ Trees. These have various advantages and disadvantages discussed in this topic. The most commonly used… …   Wikipedia

  • Partition coefficient — In chemistry and the pharmaceutical sciences, a partition (P) or distribution coefficient (D) is the ratio of concentrations of a compound in the two phases of a mixture of two immiscible solvents at equilibrium.[1] The terms gas/liquid partition …   Wikipedia

  • Database management system — A database management system (DBMS) is a software package with computer programs that control the creation, maintenance, and the use of a database. It allows organizations to conveniently develop databases for various applications by database… …   Wikipedia

  • Partition of India — The Partition of British India Colonial India …   Wikipedia

Share the article and excerpts

Direct link
Do a right-click on the link above
and select “Copy Link”