In a partitioned table, a logically large table is divided into smaller physical pieces. This document discusses the aspects of table partitioning that are compatible with Oracle databases and supported by PolarDB-O.
Partitioning can provide the following benefits:
- Query performance can be significantly improved in specific situations, particularly when the most frequently accessed rows of the table are in a single partition or small number of partitions. Partitioning allows you to omit the partition column from the front of an index, reducing index size and making it more likely that the frequently used parts of the index fits in memory.
- You can experience improved performance when accessing (query or update) a large percentage of a single partition. This is because the server will perform a sequential scan of the partition instead of using an index and random access reads scattered across the whole table.
- A bulk load (or unload) can be implemented by adding or removing partitions, if you plan this requirement into the partitioning design. ALTER TABLE is much faster than a bulk operation. It also helps to avoid the VACUUM overhead caused by a bulk DELETE.
- You can migrate seldom-used data to less-expensive (or slower) storage media.
We recommend table partitioning only when a table is very large. The exact point at which a table will benefit from partitioning depends on the application. We recommend that the size of the table exceeds the physical memory of the database server.