Partitioning

• It is hard to determine when a database become too big as it belongs to subjective views.
• Some tasks will become more tedious when the table grows:
  • Query that involves full-table scans.
  • Index creation/rebuild.
  • Data archival/deletion.
  • Generation of table/index statistics.
  • Table relocation (e.g., move to a different tablespace) and database backups.
• The best way to prevent administrative issues from occurring in the future is to break large tables into pieces, or partitions, when the table is first created.

Partitioning Concepts

Once a table has been partitioned, the table itself becomes a virtual concept. The partitions hold the data along with the indexes.

While every partition must have the same schema definition (columns, column types, etc.), there are several administrative features that can differ for each partition:

• Partitions may be stored on different tablespaces, which can be on different physical storage tiers.

• Partitions can be compressed using different compression schemes.

• Local indexes (more on this shortly) can be dropped for some partitions.

• Table statistics can be frozen on some partitions, while being periodically refreshed on others.

• Individual partitions can be pinned into memory or stored in the database’s flash storage tier.

Thus, table partitioning allows for flexibility with data storage and administration, while still presenting the simplicity of a single table to your user community.

Table Partitioning

The common practice is horizontal partioning, available in most relational databases. There is vertical partioning, but it must be done manually.

When partitioning a table horizontally, you must choose a partition key, the column whose values are used to determine which row goes where (consists of a single column or more.)

Index Partitioning

If your partitioned table has indexes, you will get to choose whether:

• A particular index should stay intact, known as a global index. It spans all partitions and useful when the query does not concern with the partition key.
• Or be broken into pieces such that each partition has its own index, which is called a local index.

<aside> 💡 The server will search every partition when the query does not include a filter condition on the partition key. If a global index exists, the server can utilize it more efficiently.

</aside>

Partitioning Methods