In our earlier article “ALTER TABLE or INSERT INTO…SELECT?” we carved out the differences between two methods of adding or dropping columns:
- ALTER TABLE
- INSERT INTO…SELECT (into a copy of the changed table)
In this article, we want to shed light on the question how adding partitions to a row partitioned table impacts performance.
As usual, I will underline my claims with some tests. The test setup uses a table containing about 100 000 rows.
We will start with a situation where no rows are available in the “NO RANGE” partition because column “TheDate” (see the DDL below) has only dates between ‘1900-01-01' and ‘2015-12-31':
At first, we use the ALTER TABLE approach to adding daily partitions for the year 2016:
Adding new partitions to a table is fast and inexpensive if no rows are moved from the “NO RANGE” partition. It only requires 110 logical IOs and no spool space.
Next, we are repeating the same task with the INSERT INTO…SELECT approach:
The INSERT INTO…SELECT approach consumes 225% more IOs, namely 248, and needs to spool the table PPI_Test.
These are no new insights for our readers who were reading our previous post on this topic. But now let's add more complexity to our example:
What happens if the “NO RANGE” partition is populated and rows have to be moved from “NO RANGE” to the newly added partition(s)?
Here it becomes interesting. Let's assume the “NO RANGE” partitions has already a lot of rows from the year 2016.
By adding the daily partitions to the table PPI_Test with an ALTER TABLE statement, the “NO RANGE” partition has to be scanned.
Rows from the year 2016 are moved to their matching daily partitions (similar, for a DROP RANGE, rows would be moved from the “DROPPED” to the “NO RANGE” partition).
In our test setup, logical IOs increased to 952, which is 865% more IOs compared with the test setup with an empty “NO RANGE” partition! Furthermore, this process consumes some spool space (but usually not as much as the INSERT INTO…SELECT approach).
Here again the summarized results of our 3 test setups:
|Method of adding partitions to PPI table||Logical IOs|
|ALTER TABLE without NO RANGE rows||110|
|ALTER TABLE with NO RANGE rows||952|
|INSERT INTO … SELECT into table copy||248|
As in the case of adding or dropping columns to/from a table, ALTER table in most cases will be the cheaper method. Costs (IOs and spool space) shoot up drastically if many rows from the “NO RANGE” partition have to be moved to their target partitions.
That's what you have to keep in mind before executing ALTER TABLE statements (don't forget: as soon as ALTER TABLE is running it can't be aborted and holds an exclusive table lock until it is finished).