15

I would like to know what is different between database clustering and database partitioning? As i know there are two types called attributes or record clustering sometimes called partitioning sometimes called fragmentation (i know partitioning =fragmentation but what is clustering). Is any different between clustering and partitioning in database?. Many thanks to all Ayham

Dheya Majid
  • 383
  • 3
  • 6
  • 13

2 Answers2

11

A clustered table1 stores its rows in the physical order that matches the logical order. To do that, the clustered table has no heap at all, and instead stores its rows in the B-Tree leaves. This can benefit certain kinds of range scans tremendously. For more info, take a look at Use The Index, Luke!

A partitioned table is split to multiple physical disks, so accessing rows from different partitions can be done in parallel.

A table can be clustered or partitioned or both (depending on DBMS).

1Also known as "index-organized table" under Oracle. BTW, Oracle cluster is different thing from Oracle index-organized table.

Branko Dimitrijevic
  • 50,809
  • 10
  • 93
  • 167
  • 1
    Partitioned tables don't have to be stored on multiple disks. Some databases benefit from partitioned tables even on single-disk systems that don't support parallel processing. – Mike Sherrill 'Cat Recall' Jun 10 '13 at 16:36
  • Can we apply database clustering with multiple technologies(My SQL with SQL)? Is it possible? – Mathankumar Feb 13 '22 at 15:14
  • Different DBMSes have different capabilities. MySQL with InnoDB supports only clustered tables, PostgreSQL only heap-based, Oracle and SQL Server support both. – Branko Dimitrijevic Feb 14 '22 at 04:57
0

In simple words, If you have too much data then you have to partition data on the different machines so searching can become fast and Clustering is the process that sorts data in the partition.

vicky prajapati
  • 9
  • 3