-
Bin Su authored
With this worklog, some ALTER TABLE operations can now be done instantly, including adding stored columns at last(with some limitations), adding virtual columns, dropping virtual columns and some other operations which won't touch the table data. And this 'instantly' means that during ALTER TABLE, no data files need to be copied as before. The limitations of add stored columns instantly are: 1. The new columns must be added at last(opposite to our existing key word FIRST) 2. If a table has fulltext indexes, then new columns can't always be added instantly 3. If a table resides in data dictionary tablespace, or is in row format COMPRESSED, or is a temporary table, new columns can't always be added instantly Adding stored columns instantly won't change the record format in InnoDB data files. However, after an instant ADD COLUMN, updates on the same table will result in some record format changes. So this kind of data files can't be used directly for servers of lower versions. But data files from lower versions can be read and updated by servers with this feature. A new ALGORITHM called INSTANT is introduced by this worklog. This algorithm would be now the first default algorithm to check. The INSTANT algorithm can only work for those statements which can really done instantly, like the foregoing operations. For example: ALTER TABLE table ADD COLUMN a INT, ALGORITHM = INSTANT; ... Also please note that INSTANT algorithm can't be work together with LOCK which is not NONE. There are ways in which user can know which table or which column is affected by adding stored columns instantly: 1. I_S.innodb_tables.instant_cols, which means the number of columns before first adding stored columns instantly 2. I_S.innodb_columns.has_default/default_value, once adding stored columns instantly is done, the default value of the columns added instantly would be stored here. RB: 18633 Reviewed-by:
Jimmy Yang <jimmy.yang@oracle.com> Reviewed-by:
Darshan M N <darshan.m.n@oracle.com>
Bin Su authoredWith this worklog, some ALTER TABLE operations can now be done instantly, including adding stored columns at last(with some limitations), adding virtual columns, dropping virtual columns and some other operations which won't touch the table data. And this 'instantly' means that during ALTER TABLE, no data files need to be copied as before. The limitations of add stored columns instantly are: 1. The new columns must be added at last(opposite to our existing key word FIRST) 2. If a table has fulltext indexes, then new columns can't always be added instantly 3. If a table resides in data dictionary tablespace, or is in row format COMPRESSED, or is a temporary table, new columns can't always be added instantly Adding stored columns instantly won't change the record format in InnoDB data files. However, after an instant ADD COLUMN, updates on the same table will result in some record format changes. So this kind of data files can't be used directly for servers of lower versions. But data files from lower versions can be read and updated by servers with this feature. A new ALGORITHM called INSTANT is introduced by this worklog. This algorithm would be now the first default algorithm to check. The INSTANT algorithm can only work for those statements which can really done instantly, like the foregoing operations. For example: ALTER TABLE table ADD COLUMN a INT, ALGORITHM = INSTANT; ... Also please note that INSTANT algorithm can't be work together with LOCK which is not NONE. There are ways in which user can know which table or which column is affected by adding stored columns instantly: 1. I_S.innodb_tables.instant_cols, which means the number of columns before first adding stored columns instantly 2. I_S.innodb_columns.has_default/default_value, once adding stored columns instantly is done, the default value of the columns added instantly would be stored here. RB: 18633 Reviewed-by:
Jimmy Yang <jimmy.yang@oracle.com> Reviewed-by:
Darshan M N <darshan.m.n@oracle.com>
Loading