Modifying an existing table is expensive and can consume a lot of resources (especially IOs). In this article, we will discuss the advantages and disadvantages of the two available primary strategies: ALTER TABLE and INSERT INTO…SELECT into a copy of the table.
Both strategies have their individual applications and usefulness.
I was creating a test scenario were the table’s data block size was 127 KB. This table contained short rows (each of them consuming only a few KB). Into each data block several hundreds of rows were packed.
The ALTER TABLE approach required 133 logical IOs, the INSERT INTO…SELECT approach logical 322 IOs. The ALTER TABLE was the clear winner with 59% less logical IOs.
Physical IOs showed a similar picture: While the ALTER TABLE only caused 14 physical IOs, the INSERT INTO…SELECT needed 30 IOs.
Interestingly, CPU usage was almost identical.
In order to get a more detailed picture, I was creating a second test scenario. While keeping the data block size at 127KB, I replaced the small rows with huge rows. With each row being about 64KB in size, only 1-2 rows were fitting into each data block.
The result was impressive:
The ALTER TABLE approach required 539 logical IOs, the INSERT INTO…SELECT method 270 853 logical IOs. The ALTER TABLE needed 99.8% less logical IOs than the INSERT INTO…SELECT!
The difference between physical IOs was as well impressive: ALTER TABLE needed 15 physical IOs, the INSERT INTO…SELECT 12 992!
I tried several additional setups, leading to the same conclusion: ALTER TABLE is much cheaper. What I learned as well: Increasing the data block size in case of very large rows can drastically decrease IOs.
If the table has indexes defined (USI, NUSI, Join Index, Hash Index), the difference between the two strategies becomes even bigger, as ALTER TABLE don’t have to create indexes for the copy of the table.
Here are the summarized results:
|Logical IO||Physical IO|
|INSERT BIG ROW||270 853||12 992|
|ALTER BIG ROW||539||15|
|INSERT SMALL ROW||322||30|
|ALTER SMALL ROW||113||14|
Another argument in favor of the ALTER TABLE approach is that no spool is required. INSERT INTO…SELECT needs to spool the table.
And you are no doubt wondering why you should not always use the ALTER TABLE approach after seeing these results. The answer is: ALTER TABLE comes with some limitation: