Partitioning in SQL Databases —

Data partitioning in SQL databases is a design technique in which large tables are divided into smaller components called partitions. Each partition in the table acts as a separate data unit within the table only. It improves the performance of our queries.

Different partitioning methods —

1. Range partitioning — Here, the data is partitioned based on range like date range or numeric range. We specify PARTITION BY RANGE clause in the CREATE TABLE command.
2. List partitioning — Data is partitioned based on the values that are present in the specified column. We specify PARTITION BY LIST clause in the CREATE TABLE command.
3. Hash partitioning — Data is partitioned based on a hash function. It ensures distribution of data evenly across the partitions. We specify PARTITION BY HASH clause in the CREATE TABLE command.
4. Composite partitioning — It is a combination of more than one partitioning method.

How partitioning improves the efficiency of queries ?

1. Data Segmentation — It divides table into smaller segments or chunks, thus allowing database systems to work with more manageable portions of data.
2. Query performance — Whenever we execute a query, database can quickly identify which partition to choose based on the filter condition we have used in the query, thus only a partition will be scanned to find the results.
3. Data maintenance — It helps tasks like data archiving or data purging. For example we can purge data older than three months and we have partitioned our data on monthly basis.

How sql partitioning is different from NoSql data partitioning ?

Both Sql and NoSql use data partitioning to improve the performance and efficiency of a system, but they vary significantly.

1. NoSql databases typically have flexible or schema-less data model, and data is partitioned horizontally which means data is present on more than one node whereas in Sql databases, data is present on single node only.
2. Partitioning in NoSql databases is often based on the distribution of data using a partition key which is an attribute with in the document. Partitioning in SQL databases is usually based on table structure and specific ranges of data.

References —

1. https://docs.oracle.com/cd/E11882_01/server.112/e25523/part_admin001.htm