Skip to content
  • Bin Su's avatar
    eb30bcd9
    WL#11250 - Support Instant Add Column · eb30bcd9
    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: default avatarJimmy Yang <jimmy.yang@oracle.com>
    Reviewed-by: default avatarDarshan M N <darshan.m.n@oracle.com>
    eb30bcd9
    WL#11250 - Support Instant Add Column
    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: default avatarJimmy Yang <jimmy.yang@oracle.com>
    Reviewed-by: default avatarDarshan M N <darshan.m.n@oracle.com>
Loading