Partitioning in Database


To speed up the query processing, we need to efficiently manage data.
And partitioning the database is considered as one of the ways to enhance the processing speed.

Using Partitioning, we can divide the data based on some value/field, so that data belonging to different groups are identified easily(may be stored on different nodes), and therefore processing a query won’t require a Full Table Scan, hence making the processing faster.

Following keys can be used to achieve partitioning :

  • Partition Key: The Partition Key is responsible for data distribution across your nodes.
  • Clustering Key: The Clustering Key is responsible for data sorting within the partition.
  • Row Key: A Row Key is just the identifier of an entity within a partition.

Partition key

Determines the node on the cluster, where the partition will be stored.

Partitioning Strategies

  1. Single-Level Partitioning –

    a) Range Partition
    Range partitioning maps data to partitions based on ranges of partition key values that you establish for each partition. A range is defined using the operators( ‘>’ or ‘<’ (non-inclusive))

    Consider the following table :
    CUSTOMER –
    – CustomerId
    – Name
    – Location
    – DOB

    The Lower Bound and the Upper Bound are required for the partition.
    Ex: we can define range partition on DOB like:-
    Range Partition on DOB  ‘2016-01-01’ < DOB <=  ’2017-01-01’

    b) List Partition – List partitioning enables you to explicitly control how rows map to partitions. A list of discrete values for the partitioning key in the description for each partition is defined.

    A row is mapped to a partition by checking whether the value of the partitioning column for a row falls within the set of values that describes the partition.  If a table is partitioned by list, the partitioning key can only consist of a single column of the table.

    Ex: we can define list partition on Location like
    List Partition on Location( ‘Asia’, ‘Europe’, ‘Africa’ )

    c) Hash Partition – A column is defined, where hash values are generated for each value and based on the buckets, the partition is made.

    The hashing algorithm evenly distributes rows among partitions, giving partitions approximately the same size.
    This is typically used where ranges aren’t appropriate, i.e. employee number, productID, etc.

    It is a better choice than range partitioning when:
    You do not know beforehand how much data maps into a given range
    The sizes of range partitions would differ quite substantially or would be difficult to balance manually
    Range partitioning would cause the data to be undesirably clustered
    Performance features such as parallel DML, partition pruning, and partition-wise joins are important

    Ex: we can define hash partition on any column like:-
    Hash Partition( ‘column-name’ )

    partitioning

  2. Composite Partitioning

A combination of more than one partitioning technique.
         These can be any of the following type: Range-Range, Range-List, Range-Hash, List-Range, and so on.

partitioning_composite

Row Key

A Row Key in Table Storage can be defined as the “primary key” within a partition.

PartitionKey + RowKey form the composite unique identifier for an entity.

Within one PartitionKey, you can only have unique RowKeys. If you use multiple partitions, the same RowKey can be reused in every partition.

Row key can be viewed as the combination of partition key and the clustering key.

References :

  1. https://docs.oracle.com/cd/B28359_01/server.111/b32024/partition.htm#i46083

knoldus-advt-sticker


Advertisements
This entry was posted in database and tagged , , , . Bookmark the permalink.

One Response to Partitioning in Database

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s