Partitioning - partitioning of stored database objects (such as tables , indexes , materialized views ) into separate parts with separate physical storage parameters. Used to improve manageability, performance, and availability for large databases.
Possible criteria for data separation used in partitioning are according to predefined ranges of values, according to lists of values, using values of hash functions ; in some cases, other options are used. Composite (composite) separation criteria is understood to mean sequentially applied criteria of different types.
Unlike segmentation ( English sharding ), where each segment is controlled by a separate DBMS instance, and means of coordination between them are used (which allows you to distribute the database on several computing nodes), when partitioning, access to all sections is carried out from a single DBMS instance (or symmetrically from any instance of a clustered DBMS, such as Oracle RAC ).
Implementations
Partitioning is implemented in many relational DBMSs , among which are IBM DB2 , Informix , Oracle Database , Teradata Database , Microsoft SQL Server , PostgreSQL , MySQL , Adaptive Server Enterprise .
In various DBMSs, the implementation possibilities are somewhat different. In particular, the following methods are supported in Oracle Database:
- range partitioning
- hash partitioning
- partitioning according to a list of values ( list partitioning ),
- reference partitioning (starting with version 11 g ),
- interval partitioning (11 g ),
- composite partitioning:
- range partitioning with hash subsection ( range-hash composite partitioning ),
- range-list composite partitioning .
There are also three types of partitioned indexes in Oracle Database:
- a locally partitioned index ( local index ), that is, index sections correspond to table sections;
- globally partitioned index ( global partitioned index ), such an index is divided into sections according to other rules than the table itself;
- globally non-partitioned index ( global non-partitioned index ), such an index is not partitioned although the table is broken;
- all kinds of combinations of these three.
In Informix, partitioning is called fragmentation, and it also allows you to partition tables and indexes into separate fragments according to different schemes:
- circular fragmentation
- in terms of
- according to the list of values
- at intervals.
Starting with version 5.1, MySQL supports the following types of partitioning [1] :
- at intervals ( range partitioning ),
- according to the list of values ( list partitioning ),
- by hash ( hash partitioning ),
- by keys ( key partitioning ).