Oracle performance of schema changes as compared to MySQL ALTER TABLE?

Question

When using MySQL MyISAM tables, and issuing an ALTER TABLE statement to add a column, MySQL creates a temporary table and copies all the data into the new table before overwriting the original table.

If that table has a lot of data, this process can be very slow (especially when rebuilding indexes), and requires you to have enough free space on the disk to store 2 copies of the table. This is very annoying.

How does Oracle work when adding columns? Is it fast on large tables?

I'm always interested in being able to do schema changes without having a lot of downtime. We are always adding new features to our software which require schema changes with every release. Any advice is appreciated...

Answer 1

Adding a column with no data to a large table in Oracle is generally very fast. There is no temporary copy of the data and no need to rebuild indexes. Slowness will generally arise when you want to add a column to a large table and backfill data into that new column for all the existing rows, since now you're talking about an UPDATE statement that affects a large number of rows.

Adding columns can lead to row migration over time. If you have a block that is 80% full with 4 rows and you add columns that will grow the size of each row 30% over time, you'll eventually reach a point where Oracle has to move one of the 4 rows to a different block. It does this by leaving a pointer to the new block in the old block, which causes reads on that migrated row to require more I/O. Eliminating migrated rows can be somewhat costly, and though it is generally possible to do without downtime assuming you're using the enterprise edition, it is generally easier if you have a bit of downtime. But row migration is something that you generally only have to worry about well down the road. If you know that certain tables are likely to have their row size increase substantially in the future, you can mitigate problems in advance by specifying a larger PCTFREE setting for the table.

Answer 2

With regard to downtime, altering a table (and a bunch of other DDL operations) take an exclusive lock. However Oracle can also perform online redefinition of oobjects using the DBMS_REDEFINITION package, which can really take a bite out of downtime.