Fast INSERT into...

- Why inserting into Partitioned Table is faster....

Background

Using partitioned database tables provides many benefits.

Fast remove of old data just by dropping oldest partition is the best-known advantage.

This article describes the least-known advantage: Faster INSERT's.

Summary

In case that indexes need to be active during inserting, it is for performance reasons crucial to have all index blocks related to new inserted records cached in the database cache.

In case of large tables, the indexes usually don't fit fully into the database cache.

As all or at least most inserted records are usually from one day, using partitioned indexes on (e.g. day-) partitioned tables allow full caching of that day's index partitions.

Based on an example this article derives and explains the time difference of 10 ms (partitioned) to 910 ms (not partitioned) for inserting 100 records into a large table.

Read the full article: Why inserts into partitioned tables are faster...