<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="innodb-online-ddl-operations">
      </a>
      17.12.1 Online DDL Operations
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045160541392">
  </a>
  <p>
   Online support details, syntax examples, and usage notes for DDL
      operations are provided under the following topics in this
      section.
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="xref" href="innodb-online-ddl-operations.html#online-ddl-index-operations" title="Index Operations">
       Index Operations
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="innodb-online-ddl-operations.html#online-ddl-primary-key-operations" title="Primary Key Operations">
       Primary Key Operations
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="innodb-online-ddl-operations.html#online-ddl-column-operations" title="Column Operations">
       Column Operations
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="innodb-online-ddl-operations.html#online-ddl-generated-column-operations" title="Generated Column Operations">
       Generated Column Operations
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="innodb-online-ddl-operations.html#online-ddl-foreign-key-operations" title="Foreign Key Operations">
       Foreign Key Operations
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="innodb-online-ddl-operations.html#online-ddl-table-operations" title="Table Operations">
       Table Operations
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="innodb-online-ddl-operations.html#online-ddl-tablespace-operations" title="Tablespace Operations">
       Tablespace Operations
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="innodb-online-ddl-operations.html#online-ddl-partitioning" title="Partitioning Operations">
       Partitioning Operations
      </a>
     </p>
    </li>
   </ul>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="online-ddl-index-operations">
       </a>
       Index Operations
      </h4>
     </div>
    </div>
   </div>
   <p>
    The following table provides an overview of online DDL support
        for index operations. An asterisk indicates additional
        information, an exception, or a dependency. For details, see
    <a class="xref" href="innodb-online-ddl-operations.html#online-ddl-index-syntax-notes" title="Syntax and Usage Notes">
     Syntax and Usage Notes
    </a>
    .
   </p>
   <div class="table">
    <a name="online-ddl-index-operations-table">
    </a>
    <p class="title">
     <b>
      Table 17.15 Online DDL Support for Index Operations
     </b>
    </p>
    <div class="table-contents">
     <table summary="Online DDL support for index operations indicating whether the operation is performed in place, rebuilds the table, permits concurrent DML, or only modifies metadata.">
      <colgroup>
       <col align="left" style="width: 16%"/>
       <col align="center" style="width: 16%"/>
       <col align="center" style="width: 16%"/>
       <col align="center" style="width: 16%"/>
       <col align="center" style="width: 16%"/>
       <col align="center" style="width: 16%"/>
      </colgroup>
      <thead>
       <tr>
        <th scope="col">
         Operation
        </th>
        <th scope="col">
         Instant
        </th>
        <th scope="col">
         In Place
        </th>
        <th scope="col">
         Rebuilds Table
        </th>
        <th scope="col">
         Permits Concurrent DML
        </th>
        <th scope="col">
         Only Modifies Metadata
        </th>
       </tr>
      </thead>
      <tbody>
       <tr>
        <th scope="row">
         Creating or adding a secondary index
        </th>
        <td>
         No
        </td>
        <td>
         Yes
        </td>
        <td>
         No
        </td>
        <td>
         Yes
        </td>
        <td>
         No
        </td>
       </tr>
       <tr>
        <th scope="row">
         Dropping an index
        </th>
        <td>
         No
        </td>
        <td>
         Yes
        </td>
        <td>
         No
        </td>
        <td>
         Yes
        </td>
        <td>
         Yes
        </td>
       </tr>
       <tr>
        <th scope="row">
         Renaming an index
        </th>
        <td>
         No
        </td>
        <td>
         Yes
        </td>
        <td>
         No
        </td>
        <td>
         Yes
        </td>
        <td>
         Yes
        </td>
       </tr>
       <tr>
        <th scope="row">
         Adding a
         <code class="literal">
          FULLTEXT
         </code>
         index
        </th>
        <td>
         No
        </td>
        <td>
         Yes*
        </td>
        <td>
         No*
        </td>
        <td>
         No
        </td>
        <td>
         No
        </td>
       </tr>
       <tr>
        <th scope="row">
         Adding a
         <code class="literal">
          SPATIAL
         </code>
         index
        </th>
        <td>
         No
        </td>
        <td>
         Yes
        </td>
        <td>
         No
        </td>
        <td>
         No
        </td>
        <td>
         No
        </td>
       </tr>
       <tr>
        <th scope="row">
         Changing the index type
        </th>
        <td>
         Yes
        </td>
        <td>
         Yes
        </td>
        <td>
         No
        </td>
        <td>
         Yes
        </td>
        <td>
         Yes
        </td>
       </tr>
      </tbody>
     </table>
    </div>
   </div>
   <br class="table-break"/>
   <h5>
    <a name="online-ddl-index-syntax-notes">
    </a>
    Syntax and Usage Notes
   </h5>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       Creating or adding a secondary index
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa60186569"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">INDEX</span> <span class="token keyword"><em class="replaceable">name</em></span> <span class="token keyword">ON</span> <span class="token keyword"><em class="replaceable">table</em></span> <span class="token punctuation">(</span><em class="replaceable">col_list</em><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
      </div>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa34216669"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> <em class="replaceable">tbl_name</em> <span class="token keyword">ADD</span> <span class="token keyword">INDEX</span> <span class="token keyword"><em class="replaceable">name</em></span> <span class="token punctuation">(</span><em class="replaceable">col_list</em><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       The table remains available for read and write operations
            while the index is being created. The
       <a class="link" href="create-index.html" title="15.1.15 CREATE INDEX Statement">
        <code class="literal">
         CREATE INDEX
        </code>
       </a>
       statement only
            finishes after all transactions that are accessing the table
            are completed, so that the initial state of the index
            reflects the most recent contents of the table.
      </p>
      <p>
       Online DDL support for adding secondary indexes means that
            you can generally speed the overall process of creating and
            loading a table and associated indexes by creating the table
            without secondary indexes, then adding secondary indexes
            after the data is loaded.
      </p>
      <p>
       A newly created secondary index contains only the committed
            data in the table at the time the
       <a class="link" href="create-index.html" title="15.1.15 CREATE INDEX Statement">
        <code class="literal">
         CREATE INDEX
        </code>
       </a>
       or
       <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
        <code class="literal">
         ALTER TABLE
        </code>
       </a>
       statement
            finishes executing. It does not contain any uncommitted
            values, old versions of values, or values marked for
            deletion but not yet removed from the old index.
      </p>
      <p>
       Some factors affect the performance, space usage, and
            semantics of this operation. For details, see
       <a class="xref" href="innodb-online-ddl-limitations.html" title="17.12.8 Online DDL Limitations">
        Section 17.12.8, “Online DDL Limitations”
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       Dropping an index
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa56285744"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">DROP</span> <span class="token keyword">INDEX</span> <span class="token keyword"><em class="replaceable">name</em></span> <span class="token keyword">ON</span> <span class="token keyword"><em class="replaceable">table</em></span><span class="token punctuation">;</span></code></pre>
      </div>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa53369002"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> <em class="replaceable">tbl_name</em> <span class="token keyword">DROP</span> <span class="token keyword">INDEX</span> <span class="token keyword"><em class="replaceable">name</em></span><span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       The table remains available for read and write operations
            while the index is being dropped. The
       <a class="link" href="drop-index.html" title="15.1.27 DROP INDEX Statement">
        <code class="literal">
         DROP INDEX
        </code>
       </a>
       statement only
            finishes after all transactions that are accessing the table
            are completed, so that the initial state of the index
            reflects the most recent contents of the table.
      </p>
     </li>
     <li class="listitem">
      <p>
       Renaming an index
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa30493681"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> <em class="replaceable">tbl_name</em> <span class="token keyword">RENAME</span> <span class="token keyword">INDEX</span> <em class="replaceable">old_index_name</em> <span class="token keyword">TO</span> <em class="replaceable">new_index_name</em><span class="token punctuation">,</span> <span class="token keyword">ALGORITHM</span><span class="token operator">=</span><span class="token keyword">INPLACE</span><span class="token punctuation">,</span> <span class="token keyword">LOCK</span><span class="token operator">=</span><span class="token keyword">NONE</span><span class="token punctuation">;</span></code></pre>
      </div>
     </li>
     <li class="listitem">
      <p>
       Adding a
       <code class="literal">
        FULLTEXT
       </code>
       index
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa28577871"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">FULLTEXT</span> <span class="token keyword">INDEX</span> <span class="token keyword"><em class="replaceable">name</em></span> <span class="token keyword">ON</span> <span class="token keyword">table</span><span class="token punctuation">(</span><span class="token keyword"><em class="replaceable">column</em></span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       Adding the first
       <code class="literal">
        FULLTEXT
       </code>
       index rebuilds
            the table if there is no user-defined
       <code class="literal">
        FTS_DOC_ID
       </code>
       column. Additional
       <code class="literal">
        FULLTEXT
       </code>
       indexes may be added without
            rebuilding the table.
      </p>
     </li>
     <li class="listitem">
      <p>
       Adding a
       <code class="literal">
        SPATIAL
       </code>
       index
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa42422109"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> geom <span class="token punctuation">(</span>g <span class="token keyword">GEOMETRY</span> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> geom <span class="token keyword">ADD</span> <span class="token keyword">SPATIAL</span> <span class="token keyword">INDEX</span><span class="token punctuation">(</span>g<span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token keyword">ALGORITHM</span><span class="token operator">=</span><span class="token keyword">INPLACE</span><span class="token punctuation">,</span> <span class="token keyword">LOCK</span><span class="token operator">=</span><span class="token keyword">SHARED</span><span class="token punctuation">;</span></code></pre>
      </div>
     </li>
     <li class="listitem">
      <p>
       Changing the index type (
       <code class="literal">
        USING {BTREE |
            HASH}
       </code>
       )
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa56391785"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> <em class="replaceable">tbl_name</em> <span class="token keyword">DROP</span> <span class="token keyword">INDEX</span> i1<span class="token punctuation">,</span> <span class="token keyword">ADD</span> <span class="token keyword">INDEX</span> i1<span class="token punctuation">(</span><em class="replaceable">key_part<span class="token punctuation">,</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span></em><span class="token punctuation"></span><span class="token punctuation">)</span> <span class="token keyword">USING</span> <span class="token keyword">BTREE</span><span class="token punctuation">,</span> <span class="token keyword">ALGORITHM</span><span class="token operator">=</span>INSTANT<span class="token punctuation">;</span></code></pre>
      </div>
     </li>
    </ul>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="online-ddl-primary-key-operations">
       </a>
       Primary Key Operations
      </h4>
     </div>
    </div>
   </div>
   <p>
    The following table provides an overview of online DDL support
        for primary key operations. An asterisk indicates additional
        information, an exception, or a dependency. See
    <a class="xref" href="innodb-online-ddl-operations.html#online-ddl-primary-key-syntax-notes" title="Syntax and Usage Notes">
     Syntax and Usage Notes
    </a>
    .
   </p>
   <div class="table">
    <a name="online-ddl-primary-key-operations-table">
    </a>
    <p class="title">
     <b>
      Table 17.16 Online DDL Support for Primary Key Operations
     </b>
    </p>
    <div class="table-contents">
     <table summary="Online DDL support for primary key operations indicating whether the operation is performed in place, rebuilds the table, permits concurrent DML, or only modifies metadata.">
      <colgroup>
       <col align="left" style="width: 16%"/>
       <col align="center" style="width: 16%"/>
       <col align="center" style="width: 16%"/>
       <col align="center" style="width: 16%"/>
       <col align="center" style="width: 16%"/>
       <col align="center" style="width: 16%"/>
      </colgroup>
      <thead>
       <tr>
        <th scope="col">
         Operation
        </th>
        <th scope="col">
         Instant
        </th>
        <th scope="col">
         In Place
        </th>
        <th scope="col">
         Rebuilds Table
        </th>
        <th scope="col">
         Permits Concurrent DML
        </th>
        <th scope="col">
         Only Modifies Metadata
        </th>
       </tr>
      </thead>
      <tbody>
       <tr>
        <th scope="row">
         Adding a primary key
        </th>
        <td>
         No
        </td>
        <td>
         Yes*
        </td>
        <td>
         Yes*
        </td>
        <td>
         Yes
        </td>
        <td>
         No
        </td>
       </tr>
       <tr>
        <th scope="row">
         Dropping a primary key
        </th>
        <td>
         No
        </td>
        <td>
         No
        </td>
        <td>
         Yes
        </td>
        <td>
         No
        </td>
        <td>
         No
        </td>
       </tr>
       <tr>
        <th scope="row">
         Dropping a primary key and adding another
        </th>
        <td>
         No
        </td>
        <td>
         Yes
        </td>
        <td>
         Yes
        </td>
        <td>
         Yes
        </td>
        <td>
         No
        </td>
       </tr>
      </tbody>
     </table>
    </div>
   </div>
   <br class="table-break"/>
   <h5>
    <a name="online-ddl-primary-key-syntax-notes">
    </a>
    Syntax and Usage Notes
   </h5>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       Adding a primary key
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa71233527"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> <em class="replaceable">tbl_name</em> <span class="token keyword">ADD</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span><span class="token keyword"><em class="replaceable">column</em></span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token keyword">ALGORITHM</span><span class="token operator">=</span><span class="token keyword">INPLACE</span><span class="token punctuation">,</span> <span class="token keyword">LOCK</span><span class="token operator">=</span><span class="token keyword">NONE</span><span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       Rebuilds the table in place. Data is reorganized
            substantially, making it an expensive operation.
       <code class="literal">
        ALGORITHM=INPLACE
       </code>
       is not permitted under
            certain conditions if columns have to be converted to
       <code class="literal">
        NOT NULL
       </code>
       .
      </p>
      <p>
       Restructuring the
       <a class="link" href="glossary.html#glos_clustered_index" title="clustered index">
        clustered index
       </a>
       always requires copying of table data. Thus, it is best to
            define the
       <a class="link" href="glossary.html#glos_primary_key" title="primary key">
        primary
            key
       </a>
       when you create a table, rather than issuing
       <code class="literal">
        ALTER TABLE ... ADD PRIMARY KEY
       </code>
       later.
      </p>
      <p>
       When you create a
       <code class="literal">
        UNIQUE
       </code>
       or
       <code class="literal">
        PRIMARY KEY
       </code>
       index, MySQL must do some
            extra work. For
       <code class="literal">
        UNIQUE
       </code>
       indexes, MySQL
            checks that the table contains no duplicate values for the
            key. For a
       <code class="literal">
        PRIMARY KEY
       </code>
       index, MySQL also
            checks that none of the
       <code class="literal">
        PRIMARY KEY
       </code>
       columns contains a
       <code class="literal">
        NULL
       </code>
       .
      </p>
      <p>
       When you add a primary key using the
       <code class="literal">
        ALGORITHM=COPY
       </code>
       clause, MySQL converts
       <code class="literal">
        NULL
       </code>
       values in the associated columns to
            default values: 0 for numbers, an empty string for
            character-based columns and BLOBs, and 0000-00-00 00:00:00
            for
       <code class="literal">
        DATETIME
       </code>
       . This is a non-standard
            behavior that Oracle recommends you not rely on. Adding a
            primary key using
       <code class="literal">
        ALGORITHM=INPLACE
       </code>
       is
            only permitted when the
       <a class="link" href="server-system-variables.html#sysvar_sql_mode">
        <code class="literal">
         SQL_MODE
        </code>
       </a>
       setting includes
            the
       <code class="literal">
        strict_trans_tables
       </code>
       or
       <code class="literal">
        strict_all_tables
       </code>
       flags; when the
       <code class="literal">
        SQL_MODE
       </code>
       setting is strict,
       <code class="literal">
        ALGORITHM=INPLACE
       </code>
       is permitted, but the
            statement can still fail if the requested primary key
            columns contain
       <code class="literal">
        NULL
       </code>
       values. The
       <code class="literal">
        ALGORITHM=INPLACE
       </code>
       behavior is more
            standard-compliant.
      </p>
      <p>
       If you create a table without a primary key,
       <code class="literal">
        InnoDB
       </code>
       chooses one for you, which can be
            the first
       <code class="literal">
        UNIQUE
       </code>
       key defined on
       <code class="literal">
        NOT NULL
       </code>
       columns, or a system-generated
            key. To avoid uncertainty and the potential space
            requirement for an extra hidden column, specify the
       <code class="literal">
        PRIMARY KEY
       </code>
       clause as part of the
       <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
        <code class="literal">
         CREATE TABLE
        </code>
       </a>
       statement.
      </p>
      <p>
       MySQL creates a new clustered index by copying the existing
            data from the original table to a temporary table that has
            the desired index structure. Once the data is completely
            copied to the temporary table, the original table is renamed
            with a different temporary table name. The temporary table
            comprising the new clustered index is renamed with the name
            of the original table, and the original table is dropped
            from the database.
      </p>
      <p>
       The online performance enhancements that apply to operations
            on secondary indexes do not apply to the primary key index.
            The rows of an InnoDB table are stored in a
       <a class="link" href="glossary.html#glos_clustered_index" title="clustered index">
        clustered index
       </a>
       organized based on the
       <a class="link" href="glossary.html#glos_primary_key" title="primary key">
        primary key
       </a>
       , forming
            what some database systems call an
       <span class="quote">
        “
        <span class="quote">
         index-organized
            table
        </span>
        ”
       </span>
       . Because the table structure is closely tied
            to the primary key, redefining the primary key still
            requires copying the data.
      </p>
      <p>
       When an operation on the primary key uses
       <code class="literal">
        ALGORITHM=INPLACE
       </code>
       , even though the data
            is still copied, it is more efficient than using
       <code class="literal">
        ALGORITHM=COPY
       </code>
       because:
      </p>
      <div class="itemizedlist">
       <ul class="itemizedlist" style="list-style-type: circle; ">
        <li class="listitem">
         <p>
          No undo logging or associated redo logging is required
                for
          <code class="literal">
           ALGORITHM=INPLACE
          </code>
          . These
                operations add overhead to DDL statements that use
          <code class="literal">
           ALGORITHM=COPY
          </code>
          .
         </p>
        </li>
        <li class="listitem">
         <p>
          The secondary index entries are pre-sorted, and so can
                be loaded in order.
         </p>
        </li>
        <li class="listitem">
         <p>
          The change buffer is not used, because there are no
                random-access inserts into the secondary indexes.
         </p>
        </li>
       </ul>
      </div>
     </li>
     <li class="listitem">
      <p>
       Dropping a primary key
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa47156673"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> <em class="replaceable">tbl_name</em> <span class="token keyword">DROP</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">,</span> <span class="token keyword">ALGORITHM</span><span class="token operator">=</span><span class="token keyword">COPY</span><span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       Only
       <code class="literal">
        ALGORITHM=COPY
       </code>
       supports dropping a
            primary key without adding a new one in the same
       <code class="literal">
        ALTER TABLE
       </code>
       statement.
      </p>
     </li>
     <li class="listitem">
      <p>
       Dropping a primary key and adding another
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa61068074"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> <em class="replaceable">tbl_name</em> <span class="token keyword">DROP</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span><span class="token punctuation">,</span> <span class="token keyword">ADD</span> <span class="token keyword">PRIMARY</span> <span class="token keyword">KEY</span> <span class="token punctuation">(</span><span class="token keyword"><em class="replaceable">column</em></span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token keyword">ALGORITHM</span><span class="token operator">=</span><span class="token keyword">INPLACE</span><span class="token punctuation">,</span> <span class="token keyword">LOCK</span><span class="token operator">=</span><span class="token keyword">NONE</span><span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       Data is reorganized substantially, making it an expensive
            operation.
      </p>
     </li>
    </ul>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="online-ddl-column-operations">
       </a>
       Column Operations
      </h4>
     </div>
    </div>
   </div>
   <p>
    The following table provides an overview of online DDL support
        for column operations. An asterisk indicates additional
        information, an exception, or a dependency. For details, see
    <a class="xref" href="innodb-online-ddl-operations.html#online-ddl-column-syntax-notes" title="Syntax and Usage Notes">
     Syntax and Usage Notes
    </a>
    .
   </p>
   <div class="table">
    <a name="online-ddl-column-operations-table">
    </a>
    <p class="title">
     <b>
      Table 17.17 Online DDL Support for Column Operations
     </b>
    </p>
    <div class="table-contents">
     <table summary="Online DDL support for column operations indicating whether the operation is performed in place, rebuilds the table, permits concurrent DML, or only modifies metadata.">
      <colgroup>
       <col align="left" style="width: 16%"/>
       <col align="center" style="width: 16%"/>
       <col align="center" style="width: 16%"/>
       <col align="center" style="width: 16%"/>
       <col align="center" style="width: 16%"/>
       <col align="center" style="width: 16%"/>
      </colgroup>
      <thead>
       <tr>
        <th scope="col">
         Operation
        </th>
        <th scope="col">
         Instant
        </th>
        <th scope="col">
         In Place
        </th>
        <th scope="col">
         Rebuilds Table
        </th>
        <th scope="col">
         Permits Concurrent DML
        </th>
        <th scope="col">
         Only Modifies Metadata
        </th>
       </tr>
      </thead>
      <tbody>
       <tr>
        <th scope="row">
         Adding a column
        </th>
        <td>
         Yes*
        </td>
        <td>
         Yes
        </td>
        <td>
         No*
        </td>
        <td>
         Yes*
        </td>
        <td>
         Yes
        </td>
       </tr>
       <tr>
        <th scope="row">
         Dropping a column
        </th>
        <td>
         Yes*
        </td>
        <td>
         Yes
        </td>
        <td>
         Yes
        </td>
        <td>
         Yes
        </td>
        <td>
         Yes
        </td>
       </tr>
       <tr>
        <th scope="row">
         Renaming a column
        </th>
        <td>
         Yes*
        </td>
        <td>
         Yes
        </td>
        <td>
         No
        </td>
        <td>
         Yes*
        </td>
        <td>
         Yes
        </td>
       </tr>
       <tr>
        <th scope="row">
         Reordering columns
        </th>
        <td>
         No
        </td>
        <td>
         Yes
        </td>
        <td>
         Yes
        </td>
        <td>
         Yes
        </td>
        <td>
         No
        </td>
       </tr>
       <tr>
        <th scope="row">
         Setting a column default value
        </th>
        <td>
         Yes
        </td>
        <td>
         Yes
        </td>
        <td>
         No
        </td>
        <td>
         Yes
        </td>
        <td>
         Yes
        </td>
       </tr>
       <tr>
        <th scope="row">
         Changing the column data type
        </th>
        <td>
         No
        </td>
        <td>
         No
        </td>
        <td>
         Yes
        </td>
        <td>
         No
        </td>
        <td>
         No
        </td>
       </tr>
       <tr>
        <th scope="row">
         Extending
         <code class="literal">
          VARCHAR
         </code>
         column size
        </th>
        <td>
         No
        </td>
        <td>
         Yes
        </td>
        <td>
         No
        </td>
        <td>
         Yes
        </td>
        <td>
         Yes
        </td>
       </tr>
       <tr>
        <th scope="row">
         Dropping the column default value
        </th>
        <td>
         Yes
        </td>
        <td>
         Yes
        </td>
        <td>
         No
        </td>
        <td>
         Yes
        </td>
        <td>
         Yes
        </td>
       </tr>
       <tr>
        <th scope="row">
         Changing the auto-increment value
        </th>
        <td>
         No
        </td>
        <td>
         Yes
        </td>
        <td>
         No
        </td>
        <td>
         Yes
        </td>
        <td>
         No*
        </td>
       </tr>
       <tr>
        <th scope="row">
         Making a column
         <code class="literal">
          NULL
         </code>
        </th>
        <td>
         No
        </td>
        <td>
         Yes
        </td>
        <td>
         Yes*
        </td>
        <td>
         Yes
        </td>
        <td>
         No
        </td>
       </tr>
       <tr>
        <th scope="row">
         Making a column
         <code class="literal">
          NOT NULL
         </code>
        </th>
        <td>
         No
        </td>
        <td>
         Yes*
        </td>
        <td>
         Yes*
        </td>
        <td>
         Yes
        </td>
        <td>
         No
        </td>
       </tr>
       <tr>
        <th scope="row">
         Modifying the definition of an
         <code class="literal">
          ENUM
         </code>
         or
         <code class="literal">
          SET
         </code>
         column
        </th>
        <td>
         Yes
        </td>
        <td>
         Yes
        </td>
        <td>
         No
        </td>
        <td>
         Yes
        </td>
        <td>
         Yes
        </td>
       </tr>
      </tbody>
     </table>
    </div>
    <div class="table-contents">
     <table cellpadding="0" cellspacing="0" style="position: fixed; top: 0px; display: none; left: 401px; width: 739px;">
      <thead>
       <tr>
        <th scope="col" style="width: 123px;">
         Operation
        </th>
        <th scope="col" style="width: 123px;">
         Instant
        </th>
        <th scope="col" style="width: 123px;">
         In Place
        </th>
        <th scope="col" style="width: 123px;">
         Rebuilds Table
        </th>
        <th scope="col" style="width: 123px;">
         Permits Concurrent DML
        </th>
        <th scope="col" style="width: 123px;">
         Only Modifies Metadata
        </th>
       </tr>
      </thead>
     </table>
    </div>
   </div>
   <br class="table-break"/>
   <h5>
    <a name="online-ddl-column-syntax-notes">
    </a>
    Syntax and Usage Notes
   </h5>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       Adding a column
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa10369623"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> <em class="replaceable">tbl_name</em> <span class="token keyword">ADD</span> <span class="token keyword">COLUMN</span> <span class="token keyword"><em class="replaceable">column_name</em></span> <em class="replaceable">column_definition</em><span class="token punctuation">,</span> <span class="token keyword">ALGORITHM</span><span class="token operator">=</span>INSTANT<span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       <code class="literal">
        INSTANT
       </code>
       is the default algorithm in MySQL
            8.4.
      </p>
      <p>
       The following limitations apply when the
       <code class="literal">
        INSTANT
       </code>
       algorithm adds a column:
      </p>
      <div class="itemizedlist">
       <ul class="itemizedlist" style="list-style-type: circle; ">
        <li class="listitem">
         <p>
          A statement cannot combine the addition of a column with
                other
          <code class="literal">
           ALTER TABLE
          </code>
          actions that do not
                support the
          <code class="literal">
           INSTANT
          </code>
          algorithm.
         </p>
        </li>
        <li class="listitem">
         <p>
          The
          <code class="literal">
           INSTANT
          </code>
          algorithm can add a
                column at any position in the table.
         </p>
        </li>
        <li class="listitem">
         <p>
          Columns cannot be added to tables that use
          <code class="literal">
           ROW_FORMAT=COMPRESSED
          </code>
          , tables with a
          <code class="literal">
           FULLTEXT
          </code>
          index, tables that reside in
                the data dictionary tablespace, or temporary tables.
                Temporary tables only support
          <code class="literal">
           ALGORITHM=COPY
          </code>
          .
         </p>
        </li>
        <li class="listitem">
         <p>
          MySQL checks the row size when the
          <code class="literal">
           INSTANT
          </code>
          algorithm adds a column, and
                throws the following error if the addition exceeds the
                limit.
         </p>
         <p>
          <span class="errortext">
           ERROR 4092 (HY000): Column can't be added
                with ALGORITHM=INSTANT as after this max possible row
                size crosses max permissible row size. Try
                ALGORITHM=INPLACE/COPY.
          </span>
         </p>
        </li>
        <li class="listitem">
         <p>
          The maximum number of columns in the internal
                representation of the table cannot exceed 1022 after
                column addition with the
          <code class="literal">
           INSTANT
          </code>
          algorithm. The error message is:
         </p>
         <p>
          <span class="errortext">
           ERROR 4158 (HY000): Column can't be added to
           <em class="replaceable">
            <code>
             tbl_name
            </code>
           </em>
           with
                ALGORITHM=INSTANT anymore. Please try
                ALGORITHM=INPLACE/COPY
          </span>
         </p>
        </li>
        <li class="listitem">
         <p>
          The
          <code class="literal">
           INSTANT
          </code>
          algorithm can not add or
                drop columns to system schema tables, such as the
                internal
          <code class="literal">
           mysql
          </code>
          table.
         </p>
        </li>
        <li class="listitem">
         <p>
          A column with a functional index cannot be dropped using
                the
          <code class="literal">
           INSTANT
          </code>
          algorithm.
         </p>
        </li>
       </ul>
      </div>
      <p>
       Multiple columns may be added in the same
       <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
        <code class="literal">
         ALTER TABLE
        </code>
       </a>
       statement. For
            example:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa91789871"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">ADD</span> <span class="token keyword">COLUMN</span> c2 <span class="token datatype">INT</span><span class="token punctuation">,</span> <span class="token keyword">ADD</span> <span class="token keyword">COLUMN</span> c3 <span class="token datatype">INT</span><span class="token punctuation">,</span> <span class="token keyword">ALGORITHM</span><span class="token operator">=</span>INSTANT<span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       A new row version is created after each
       <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
        <code class="literal">
         ALTER TABLE ...
            ALGORITHM=INSTANT
        </code>
       </a>
       operation that adds one or more
            columns, drops one or more columns, or adds and drops one or
            more columns in the same operation. The
       <code class="literal">
        INFORMATION_SCHEMA.INNODB_TABLES.TOTAL_ROW_VERSIONS
       </code>
       column tracks the number of row versions for a table. The
            value is incremented each time a column is instantly added
            or dropped. The initial value is 0.
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa54392439"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span>  <span class="token keyword">SELECT</span> <span class="token keyword">NAME</span><span class="token punctuation">,</span> TOTAL_ROW_VERSIONS <span class="token keyword">FROM</span> INFORMATION_SCHEMA<span class="token punctuation">.</span>INNODB_TABLES 
        <span class="token keyword">WHERE</span> <span class="token keyword">NAME</span> <span class="token operator">LIKE</span> <span class="token string">'test/t1'</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> NAME    <span class="token punctuation">|</span> TOTAL_ROW_VERSIONS <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> test/t1 <span class="token punctuation">|</span>                  0 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
      </div>
      <p>
       When a table with instantly added or dropped columns is
            rebuilt by table-rebuilding
       <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
        <code class="literal">
         ALTER TABLE
        </code>
       </a>
       or
       <a class="link" href="optimize-table.html" title="15.7.3.4 OPTIMIZE TABLE Statement">
        <code class="literal">
         OPTIMIZE TABLE
        </code>
       </a>
       operation, the
       <code class="literal">
        TOTAL_ROW_VERSIONS
       </code>
       value
            is reset to 0. The maximum number of row versions permitted
            is 64 (255 as of MySQL 9.1.0), as each row version requires
            additional space for table metadata. When the row version
            limit is reached,
       <code class="literal">
        ADD COLUMN
       </code>
       and
       <code class="literal">
        DROP COLUMN
       </code>
       operations using
       <code class="literal">
        ALGORITHM=INSTANT
       </code>
       are rejected with an
            error message that recommends rebuilding the table using the
       <code class="literal">
        COPY
       </code>
       or
       <code class="literal">
        INPLACE
       </code>
       algorithm.
      </p>
      <p>
       <span class="errortext">
        ERROR 4080 (HY000): Maximum row versions reached
            for table test/t1. No more columns can be added or dropped
            instantly. Please use COPY/INPLACE.
       </span>
      </p>
      <p>
       The following
       <a class="link" href="information-schema.html" title="Chapter 28 INFORMATION_SCHEMA Tables">
        <code class="literal">
         INFORMATION_SCHEMA
        </code>
       </a>
       columns
            provide additional metadata for instantly added columns.
            Refer to the descriptions of those columns for more
            information. See
       <a class="xref" href="information-schema-innodb-columns-table.html" title="28.4.9 The INFORMATION_SCHEMA INNODB_COLUMNS Table">
        Section 28.4.9, “The INFORMATION_SCHEMA INNODB_COLUMNS Table”
       </a>
       ,
            and
       <a class="xref" href="information-schema-innodb-tables-table.html" title="28.4.23 The INFORMATION_SCHEMA INNODB_TABLES Table">
        Section 28.4.23, “The INFORMATION_SCHEMA INNODB_TABLES Table”
       </a>
       .
      </p>
      <div class="itemizedlist">
       <ul class="itemizedlist" style="list-style-type: circle; ">
        <li class="listitem">
         <p>
          <code class="literal">
           INNODB_COLUMNS.DEFAULT_VALUE
          </code>
         </p>
        </li>
        <li class="listitem">
         <p>
          <code class="literal">
           INNODB_COLUMNS.HAS_DEFAULT
          </code>
         </p>
        </li>
        <li class="listitem">
         <p>
          <code class="literal">
           INNODB_TABLES.INSTANT_COLS
          </code>
         </p>
        </li>
       </ul>
      </div>
      <p>
       Concurrent DML is not permitted when adding an
       <a class="link" href="glossary.html#glos_auto_increment" title="auto-increment">
        auto-increment
       </a>
       column. Data is reorganized substantially, making it an
            expensive operation. At a minimum,
       <code class="literal">
        ALGORITHM=INPLACE, LOCK=SHARED
       </code>
       is
            required.
      </p>
      <p>
       The table is rebuilt if
       <code class="literal">
        ALGORITHM=INPLACE
       </code>
       is used to add a column.
      </p>
     </li>
     <li class="listitem">
      <p>
       Dropping a column
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa34816924"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> <em class="replaceable">tbl_name</em> <span class="token keyword">DROP</span> <span class="token keyword">COLUMN</span> <span class="token keyword"><em class="replaceable">column_name</em></span><span class="token punctuation">,</span> <span class="token keyword">ALGORITHM</span><span class="token operator">=</span>INSTANT<span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       <code class="literal">
        INSTANT
       </code>
       is the default algorithm in MySQL
            8.4.
      </p>
      <p>
       The following limitations apply when the
       <code class="literal">
        INSTANT
       </code>
       algorithm is used to drop a
            column:
      </p>
      <div class="itemizedlist">
       <ul class="itemizedlist" style="list-style-type: circle; ">
        <li class="listitem">
         <p>
          Dropping a column cannot be combined in the same
                statement with other
          <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
           <code class="literal">
            ALTER
                TABLE
           </code>
          </a>
          actions that do not support
          <code class="literal">
           ALGORITHM=INSTANT
          </code>
          .
         </p>
        </li>
        <li class="listitem">
         <p>
          Columns cannot be dropped from tables that use
          <code class="literal">
           ROW_FORMAT=COMPRESSED
          </code>
          , tables with a
          <code class="literal">
           FULLTEXT
          </code>
          index, tables that reside in
                the data dictionary tablespace, or temporary tables.
                Temporary tables only support
          <code class="literal">
           ALGORITHM=COPY
          </code>
          .
         </p>
        </li>
       </ul>
      </div>
      <p>
       Multiple columns may be dropped in the same
       <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
        <code class="literal">
         ALTER TABLE
        </code>
       </a>
       statement; for
            example:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa54432301"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">DROP</span> <span class="token keyword">COLUMN</span> c4<span class="token punctuation">,</span> <span class="token keyword">DROP</span> <span class="token keyword">COLUMN</span> c5<span class="token punctuation">,</span> <span class="token keyword">ALGORITHM</span><span class="token operator">=</span>INSTANT<span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       Each time a column is added or dropped using
       <code class="literal">
        ALGORITHM=INSTANT
       </code>
       , a new row version is
            created. The
       <code class="literal">
        INFORMATION_SCHEMA.INNODB_TABLES.TOTAL_ROW_VERSIONS
       </code>
       column tracks the number of row versions for a table. The
            value is incremented each time a column is instantly added
            or dropped. The initial value is 0.
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa30898531"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span>  <span class="token keyword">SELECT</span> <span class="token keyword">NAME</span><span class="token punctuation">,</span> TOTAL_ROW_VERSIONS <span class="token keyword">FROM</span> INFORMATION_SCHEMA<span class="token punctuation">.</span>INNODB_TABLES 
        <span class="token keyword">WHERE</span> <span class="token keyword">NAME</span> <span class="token operator">LIKE</span> <span class="token string">'test/t1'</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> NAME    <span class="token punctuation">|</span> TOTAL_ROW_VERSIONS <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> test/t1 <span class="token punctuation">|</span>                  0 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
      </div>
      <p>
       When a table with instantly added or dropped columns is
            rebuilt by table-rebuilding
       <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
        <code class="literal">
         ALTER TABLE
        </code>
       </a>
       or
       <a class="link" href="optimize-table.html" title="15.7.3.4 OPTIMIZE TABLE Statement">
        <code class="literal">
         OPTIMIZE TABLE
        </code>
       </a>
       operation, the
       <code class="literal">
        TOTAL_ROW_VERSIONS
       </code>
       value
            is reset to 0. The maximum number of row versions permitted
            is 64 (255 as of MySQL 9.1.0), as each row version requires
            additional space for table metadata. When the row version
            limit is reached,
       <code class="literal">
        ADD COLUMN
       </code>
       and
       <code class="literal">
        DROP COLUMN
       </code>
       operations using
       <code class="literal">
        ALGORITHM=INSTANT
       </code>
       are rejected with an
            error message that recommends rebuilding the table using the
       <code class="literal">
        COPY
       </code>
       or
       <code class="literal">
        INPLACE
       </code>
       algorithm.
      </p>
      <p>
       <span class="errortext">
        ERROR 4080 (HY000): Maximum row versions reached
            for table test/t1. No more columns can be added or dropped
            instantly. Please use COPY/INPLACE.
       </span>
      </p>
      <p>
       If an algorithm other than
       <code class="literal">
        ALGORITHM=INSTANT
       </code>
       is used, data is
            reorganized substantially, making it an expensive operation.
      </p>
     </li>
     <li class="listitem">
      <p>
       Renaming a column
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa5527649"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> <em class="replaceable">tbl</em> <span class="token keyword">CHANGE</span> <em class="replaceable">old_col_name</em> <em class="replaceable">new_col_name</em> <em class="replaceable">data_type</em><span class="token punctuation">,</span> <span class="token keyword">ALGORITHM</span><span class="token operator">=</span>INSTANT<span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       To permit concurrent DML, keep the same data type and only
            change the column name.
      </p>
      <p>
       When you keep the same data type and
       <code class="literal">
        [NOT]
            NULL
       </code>
       attribute, only changing the column name, the
            operation can always be performed online.
      </p>
      <p>
       Renaming a column referenced from another table is only
            permitted with
       <code class="literal">
        ALGORITHM=INPLACE
       </code>
       . If you
            use
       <code class="literal">
        ALGORITHM=INSTANT
       </code>
       ,
       <code class="literal">
        ALGORITHM=COPY
       </code>
       , or some other condition
            that causes the operation to use those algorithms, the
       <code class="literal">
        ALTER TABLE
       </code>
       statement fails.
      </p>
      <p>
       <code class="literal">
        ALGORITHM=INSTANT
       </code>
       supports renaming a
            virtual column;
       <code class="literal">
        ALGORITHM=INPLACE
       </code>
       does
            not.
      </p>
      <p>
       <code class="literal">
        ALGORITHM=INSTANT
       </code>
       and
       <code class="literal">
        ALGORITHM=INPLACE
       </code>
       do not support renaming
            a column when adding or dropping a virtual column in the
            same statement. In this case, only
       <code class="literal">
        ALGORITHM=COPY
       </code>
       is supported.
      </p>
     </li>
     <li class="listitem">
      <p>
       Reordering columns
      </p>
      <p>
       To reorder columns, use
       <code class="literal">
        FIRST
       </code>
       or
       <code class="literal">
        AFTER
       </code>
       in
       <code class="literal">
        CHANGE
       </code>
       or
       <code class="literal">
        MODIFY
       </code>
       operations.
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa89263936"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> <em class="replaceable">tbl_name</em> <span class="token keyword">MODIFY</span> <span class="token keyword">COLUMN</span> <em class="replaceable">col_name</em> <em class="replaceable">column_definition</em> <span class="token keyword">FIRST</span><span class="token punctuation">,</span> <span class="token keyword">ALGORITHM</span><span class="token operator">=</span><span class="token keyword">INPLACE</span><span class="token punctuation">,</span> <span class="token keyword">LOCK</span><span class="token operator">=</span><span class="token keyword">NONE</span><span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       Data is reorganized substantially, making it an expensive
            operation.
      </p>
     </li>
     <li class="listitem">
      <p>
       Changing the column data type
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa32526800"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> <em class="replaceable">tbl_name</em> <span class="token keyword">CHANGE</span> c1 c1 <span class="token datatype">BIGINT</span><span class="token punctuation">,</span> <span class="token keyword">ALGORITHM</span><span class="token operator">=</span><span class="token keyword">COPY</span><span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       Changing the column data type is only supported with
       <code class="literal">
        ALGORITHM=COPY
       </code>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       Extending
       <code class="literal">
        VARCHAR
       </code>
       column size
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa23667998"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> <em class="replaceable">tbl_name</em> <span class="token keyword">CHANGE</span> <span class="token keyword">COLUMN</span> c1 c1 <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">255</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token keyword">ALGORITHM</span><span class="token operator">=</span><span class="token keyword">INPLACE</span><span class="token punctuation">,</span> <span class="token keyword">LOCK</span><span class="token operator">=</span><span class="token keyword">NONE</span><span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       The number of length bytes required by a
       <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
        <code class="literal">
         VARCHAR
        </code>
       </a>
       column must remain
            the same. For
       <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
        <code class="literal">
         VARCHAR
        </code>
       </a>
       columns
            of 0 to 255 bytes in size, one length byte is required to
            encode the value. For
       <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
        <code class="literal">
         VARCHAR
        </code>
       </a>
       columns of 256 bytes in size or more, two length bytes are
            required. As a result, in-place
       <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
        <code class="literal">
         ALTER
            TABLE
        </code>
       </a>
       only supports increasing
       <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
        <code class="literal">
         VARCHAR
        </code>
       </a>
       column size from 0 to
            255 bytes, or from 256 bytes to a greater size. In-place
       <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
        <code class="literal">
         ALTER TABLE
        </code>
       </a>
       does not support
            increasing the size of a
       <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
        <code class="literal">
         VARCHAR
        </code>
       </a>
       column from less than
            256 bytes to a size equal to or greater than 256 bytes. In
            this case, the number of required length bytes changes from
            1 to 2, which is only supported by a table copy
            (
       <code class="literal">
        ALGORITHM=COPY
       </code>
       ). For example, attempting
            to change
       <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
        <code class="literal">
         VARCHAR
        </code>
       </a>
       column size
            for a single byte character set from VARCHAR(255) to
            VARCHAR(256) using in-place
       <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
        <code class="literal">
         ALTER
            TABLE
        </code>
       </a>
       returns this error:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa79198572"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> <em class="replaceable">tbl_name</em> <span class="token keyword">ALGORITHM</span><span class="token operator">=</span><span class="token keyword">INPLACE</span><span class="token punctuation">,</span> <span class="token keyword">CHANGE</span> <span class="token keyword">COLUMN</span> c1 c1 <span class="token datatype">VARCHAR</span><span class="token punctuation">(</span><span class="token number">256</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">ERROR 0A000<span class="token punctuation">:</span> ALGORITHM=INPLACE is not supported. Reason<span class="token punctuation">:</span> Cannot change
column type INPLACE. Try ALGORITHM=COPY.</span></code></pre>
      </div>
      <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
       <div class="admon-title">
        Note
       </div>
       <p>
        The byte length of a
        <code class="literal">
         VARCHAR
        </code>
        column is
              dependant on the byte length of the character set.
       </p>
      </div>
      <p>
       Decreasing
       <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
        <code class="literal">
         VARCHAR
        </code>
       </a>
       size using
            in-place
       <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
        <code class="literal">
         ALTER TABLE
        </code>
       </a>
       is not
            supported. Decreasing
       <a class="link" href="char.html" title="13.3.2 The CHAR and VARCHAR Types">
        <code class="literal">
         VARCHAR
        </code>
       </a>
       size requires a table copy
            (
       <code class="literal">
        ALGORITHM=COPY
       </code>
       ).
      </p>
     </li>
     <li class="listitem">
      <p>
       Setting a column default value
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa13401106"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> <em class="replaceable">tbl_name</em> <span class="token keyword">ALTER</span> <span class="token keyword">COLUMN</span> <em class="replaceable">col</em> <span class="token keyword">SET</span> <span class="token keyword">DEFAULT</span> <em class="replaceable">literal</em><span class="token punctuation">,</span> <span class="token keyword">ALGORITHM</span><span class="token operator">=</span>INSTANT<span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       Only modifies table metadata. Default column values are
            stored in the
       <a class="link" href="glossary.html#glos_data_dictionary" title="data dictionary">
        data
            dictionary
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       Dropping a column default value
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa67883281"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> <em class="replaceable">tbl</em> <span class="token keyword">ALTER</span> <span class="token keyword">COLUMN</span> <em class="replaceable">col</em> <span class="token keyword">DROP</span> <span class="token keyword">DEFAULT</span><span class="token punctuation">,</span> <span class="token keyword">ALGORITHM</span><span class="token operator">=</span>INSTANT<span class="token punctuation">;</span></code></pre>
      </div>
     </li>
     <li class="listitem">
      <p>
       Changing the auto-increment value
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa61027218"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> <span class="token keyword"><em class="replaceable">table</em></span> <span class="token keyword">AUTO_INCREMENT</span><span class="token operator">=</span><em class="replaceable">next_value</em><span class="token punctuation">,</span> <span class="token keyword">ALGORITHM</span><span class="token operator">=</span><span class="token keyword">INPLACE</span><span class="token punctuation">,</span> <span class="token keyword">LOCK</span><span class="token operator">=</span><span class="token keyword">NONE</span><span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       Modifies a value stored in memory, not the data file.
      </p>
      <p>
       In a distributed system using replication or sharding, you
            sometimes reset the auto-increment counter for a table to a
            specific value. The next row inserted into the table uses
            the specified value for its auto-increment column. You might
            also use this technique in a data warehousing environment
            where you periodically empty all the tables and reload them,
            and restart the auto-increment sequence from 1.
      </p>
     </li>
     <li class="listitem">
      <p>
       Making a column
       <code class="literal">
        NULL
       </code>
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa16475298"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> tbl_name <span class="token keyword">MODIFY</span> <span class="token keyword">COLUMN</span> <span class="token keyword"><em class="replaceable">column_name</em></span> <em class="replaceable">data_type</em> <span class="token boolean">NULL</span><span class="token punctuation">,</span> <span class="token keyword">ALGORITHM</span><span class="token operator">=</span><span class="token keyword">INPLACE</span><span class="token punctuation">,</span> <span class="token keyword">LOCK</span><span class="token operator">=</span><span class="token keyword">NONE</span><span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       Rebuilds the table in place. Data is reorganized
            substantially, making it an expensive operation.
      </p>
     </li>
     <li class="listitem">
      <p>
       Making a column
       <code class="literal">
        NOT NULL
       </code>
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa64091350"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> <em class="replaceable">tbl_name</em> <span class="token keyword">MODIFY</span> <span class="token keyword">COLUMN</span> <span class="token keyword"><em class="replaceable">column_name</em></span> <em class="replaceable">data_type</em> <span class="token operator">NOT</span> <span class="token boolean">NULL</span><span class="token punctuation">,</span> <span class="token keyword">ALGORITHM</span><span class="token operator">=</span><span class="token keyword">INPLACE</span><span class="token punctuation">,</span> <span class="token keyword">LOCK</span><span class="token operator">=</span><span class="token keyword">NONE</span><span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       Rebuilds the table in place.
       <code class="literal">
        STRICT_ALL_TABLES
       </code>
       or
       <code class="literal">
        STRICT_TRANS_TABLES
       </code>
       <a class="link" href="server-system-variables.html#sysvar_sql_mode">
        <code class="literal">
         SQL_MODE
        </code>
       </a>
       is required for
            the operation to succeed. The operation fails if the column
            contains NULL values. The server prohibits changes to
            foreign key columns that have the potential to cause loss of
            referential integrity. See
       <a class="xref" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
        Section 15.1.9, “ALTER TABLE Statement”
       </a>
       .
            Data is reorganized substantially, making it an expensive
            operation.
      </p>
     </li>
     <li class="listitem">
      <p>
       Modifying the definition of an
       <code class="literal">
        ENUM
       </code>
       or
       <code class="literal">
        SET
       </code>
       column
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa55734134"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> t1 <span class="token punctuation">(</span>c1 <span class="token datatype">ENUM</span><span class="token punctuation">(</span><span class="token string">'a'</span><span class="token punctuation">,</span> <span class="token string">'b'</span><span class="token punctuation">,</span> <span class="token string">'c'</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">MODIFY</span> <span class="token keyword">COLUMN</span> c1 <span class="token datatype">ENUM</span><span class="token punctuation">(</span><span class="token string">'a'</span><span class="token punctuation">,</span> <span class="token string">'b'</span><span class="token punctuation">,</span> <span class="token string">'c'</span><span class="token punctuation">,</span> <span class="token string">'d'</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token keyword">ALGORITHM</span><span class="token operator">=</span>INSTANT<span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       Modifying the definition of an
       <a class="link" href="enum.html" title="13.3.5 The ENUM Type">
        <code class="literal">
         ENUM
        </code>
       </a>
       or
       <a class="link" href="set.html" title="13.3.6 The SET Type">
        <code class="literal">
         SET
        </code>
       </a>
       column by adding new
            enumeration or set members to the
       <span class="emphasis">
        <em>
         end
        </em>
       </span>
       of the list of valid member values may be performed
            instantly or in place, as long as the storage size of the
            data type does not change. For example, adding a member to a
       <a class="link" href="set.html" title="13.3.6 The SET Type">
        <code class="literal">
         SET
        </code>
       </a>
       column that has 8 members
            changes the required storage per value from 1 byte to 2
            bytes; this requires a table copy. Adding members in the
            middle of the list causes renumbering of existing members,
            which requires a table copy.
      </p>
     </li>
    </ul>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="online-ddl-generated-column-operations">
       </a>
       Generated Column Operations
      </h4>
     </div>
    </div>
   </div>
   <p>
    The following table provides an overview of online DDL support
        for generated column operations. For details, see
    <a class="xref" href="innodb-online-ddl-operations.html#online-ddl-generated-column-syntax-notes" title="Syntax and Usage Notes">
     Syntax and Usage Notes
    </a>
    .
   </p>
   <div class="table">
    <a name="online-ddl-generated-column-operations-table">
    </a>
    <p class="title">
     <b>
      Table 17.18 Online DDL Support for Generated Column Operations
     </b>
    </p>
    <div class="table-contents">
     <table summary="Online DDL support for generated column operations indicating whether the operation is performed in place, rebuilds the table, permits concurrent DML, or only modifies metadata.">
      <colgroup>
       <col align="left" style="width: 16%"/>
       <col align="center" style="width: 16%"/>
       <col align="center" style="width: 16%"/>
       <col align="center" style="width: 16%"/>
       <col align="center" style="width: 16%"/>
       <col align="center" style="width: 16%"/>
      </colgroup>
      <thead>
       <tr>
        <th scope="col">
         Operation
        </th>
        <th scope="col">
         Instant
        </th>
        <th scope="col">
         In Place
        </th>
        <th scope="col">
         Rebuilds Table
        </th>
        <th scope="col">
         Permits Concurrent DML
        </th>
        <th scope="col">
         Only Modifies Metadata
        </th>
       </tr>
      </thead>
      <tbody>
       <tr>
        <th scope="row">
         Adding a
         <code class="literal">
          STORED
         </code>
         column
        </th>
        <td>
         No
        </td>
        <td>
         No
        </td>
        <td>
         Yes
        </td>
        <td>
         No
        </td>
        <td>
         No
        </td>
       </tr>
       <tr>
        <th scope="row">
         Modifying
         <code class="literal">
          STORED
         </code>
         column order
        </th>
        <td>
         No
        </td>
        <td>
         No
        </td>
        <td>
         Yes
        </td>
        <td>
         No
        </td>
        <td>
         No
        </td>
       </tr>
       <tr>
        <th scope="row">
         Dropping a
         <code class="literal">
          STORED
         </code>
         column
        </th>
        <td>
         No
        </td>
        <td>
         Yes
        </td>
        <td>
         Yes
        </td>
        <td>
         Yes
        </td>
        <td>
         No
        </td>
       </tr>
       <tr>
        <th scope="row">
         Adding a
         <code class="literal">
          VIRTUAL
         </code>
         column
        </th>
        <td>
         Yes
        </td>
        <td>
         Yes
        </td>
        <td>
         No
        </td>
        <td>
         Yes
        </td>
        <td>
         Yes
        </td>
       </tr>
       <tr>
        <th scope="row">
         Modifying
         <code class="literal">
          VIRTUAL
         </code>
         column order
        </th>
        <td>
         No
        </td>
        <td>
         No
        </td>
        <td>
         Yes
        </td>
        <td>
         No
        </td>
        <td>
         No
        </td>
       </tr>
       <tr>
        <th scope="row">
         Dropping a
         <code class="literal">
          VIRTUAL
         </code>
         column
        </th>
        <td>
         Yes
        </td>
        <td>
         Yes
        </td>
        <td>
         No
        </td>
        <td>
         Yes
        </td>
        <td>
         Yes
        </td>
       </tr>
      </tbody>
     </table>
    </div>
   </div>
   <br class="table-break"/>
   <h5>
    <a name="online-ddl-generated-column-syntax-notes">
    </a>
    Syntax and Usage Notes
   </h5>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       Adding a
       <code class="literal">
        STORED
       </code>
       column
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa43539850"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">ADD</span> <span class="token keyword">COLUMN</span> <span class="token punctuation">(</span>c2 <span class="token datatype">INT</span> <span class="token keyword">GENERATED</span> <span class="token keyword">ALWAYS</span> <span class="token keyword">AS</span> <span class="token punctuation">(</span>c1 <span class="token operator">+</span> <span class="token number">1</span><span class="token punctuation">)</span> <span class="token keyword">STORED</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token keyword">ALGORITHM</span><span class="token operator">=</span><span class="token keyword">COPY</span><span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       <code class="literal">
        ADD COLUMN
       </code>
       is not an in-place operation
            for stored columns (done without using a temporary table)
            because the expression must be evaluated by the server.
      </p>
     </li>
     <li class="listitem">
      <p>
       Modifying
       <code class="literal">
        STORED
       </code>
       column order
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa36157760"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">MODIFY</span> <span class="token keyword">COLUMN</span> c2 <span class="token datatype">INT</span> <span class="token keyword">GENERATED</span> <span class="token keyword">ALWAYS</span> <span class="token keyword">AS</span> <span class="token punctuation">(</span>c1 <span class="token operator">+</span> <span class="token number">1</span><span class="token punctuation">)</span> <span class="token keyword">STORED</span> <span class="token keyword">FIRST</span><span class="token punctuation">,</span> <span class="token keyword">ALGORITHM</span><span class="token operator">=</span><span class="token keyword">COPY</span><span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       Rebuilds the table in place.
      </p>
     </li>
     <li class="listitem">
      <p>
       Dropping a
       <code class="literal">
        STORED
       </code>
       column
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa37816791"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">DROP</span> <span class="token keyword">COLUMN</span> c2<span class="token punctuation">,</span> <span class="token keyword">ALGORITHM</span><span class="token operator">=</span><span class="token keyword">INPLACE</span><span class="token punctuation">,</span> <span class="token keyword">LOCK</span><span class="token operator">=</span><span class="token keyword">NONE</span><span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       Rebuilds the table in place.
      </p>
     </li>
     <li class="listitem">
      <p>
       Adding a
       <code class="literal">
        VIRTUAL
       </code>
       column
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa72759313"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">ADD</span> <span class="token keyword">COLUMN</span> <span class="token punctuation">(</span>c2 <span class="token datatype">INT</span> <span class="token keyword">GENERATED</span> <span class="token keyword">ALWAYS</span> <span class="token keyword">AS</span> <span class="token punctuation">(</span>c1 <span class="token operator">+</span> <span class="token number">1</span><span class="token punctuation">)</span> <span class="token keyword">VIRTUAL</span><span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token keyword">ALGORITHM</span><span class="token operator">=</span>INSTANT<span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       Adding a virtual column can be performed instantly or in
            place for non-partitioned tables.
      </p>
      <p>
       Adding a
       <code class="literal">
        VIRTUAL
       </code>
       is not an in-place
            operation for partitioned tables.
      </p>
     </li>
     <li class="listitem">
      <p>
       Modifying
       <code class="literal">
        VIRTUAL
       </code>
       column order
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa47570332"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">MODIFY</span> <span class="token keyword">COLUMN</span> c2 <span class="token datatype">INT</span> <span class="token keyword">GENERATED</span> <span class="token keyword">ALWAYS</span> <span class="token keyword">AS</span> <span class="token punctuation">(</span>c1 <span class="token operator">+</span> <span class="token number">1</span><span class="token punctuation">)</span> <span class="token keyword">VIRTUAL</span> <span class="token keyword">FIRST</span><span class="token punctuation">,</span> <span class="token keyword">ALGORITHM</span><span class="token operator">=</span><span class="token keyword">COPY</span><span class="token punctuation">;</span></code></pre>
      </div>
     </li>
     <li class="listitem">
      <p>
       Dropping a
       <code class="literal">
        VIRTUAL
       </code>
       column
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa45042535"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> t1 <span class="token keyword">DROP</span> <span class="token keyword">COLUMN</span> c2<span class="token punctuation">,</span> <span class="token keyword">ALGORITHM</span><span class="token operator">=</span>INSTANT<span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       Dropping a
       <code class="literal">
        VIRTUAL
       </code>
       column can be
            performed instantly or in place for non-partitioned tables.
      </p>
     </li>
    </ul>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="online-ddl-foreign-key-operations">
       </a>
       Foreign Key Operations
      </h4>
     </div>
    </div>
   </div>
   <p>
    The following table provides an overview of online DDL support
        for foreign key operations. An asterisk indicates additional
        information, an exception, or a dependency. For details, see
    <a class="xref" href="innodb-online-ddl-operations.html#online-ddl-foreign-key-syntax-notes" title="Syntax and Usage Notes">
     Syntax and Usage Notes
    </a>
    .
   </p>
   <div class="table">
    <a name="online-ddl-foreign-key-operations-table">
    </a>
    <p class="title">
     <b>
      Table 17.19 Online DDL Support for Foreign Key Operations
     </b>
    </p>
    <div class="table-contents">
     <table summary="Online DDL support for foreign key operations indicating whether the operation is performed in place, rebuilds the table, permits concurrent DML, or only modifies metadata.">
      <colgroup>
       <col align="left" style="width: 16%"/>
       <col align="center" style="width: 16%"/>
       <col align="center" style="width: 16%"/>
       <col align="center" style="width: 16%"/>
       <col align="center" style="width: 16%"/>
       <col align="center" style="width: 16%"/>
      </colgroup>
      <thead>
       <tr>
        <th scope="col">
         Operation
        </th>
        <th scope="col">
         Instant
        </th>
        <th scope="col">
         In Place
        </th>
        <th scope="col">
         Rebuilds Table
        </th>
        <th scope="col">
         Permits Concurrent DML
        </th>
        <th scope="col">
         Only Modifies Metadata
        </th>
       </tr>
      </thead>
      <tbody>
       <tr>
        <th scope="row">
         Adding a foreign key constraint
        </th>
        <td>
         No
        </td>
        <td>
         Yes*
        </td>
        <td>
         No
        </td>
        <td>
         Yes
        </td>
        <td>
         Yes
        </td>
       </tr>
       <tr>
        <th scope="row">
         Dropping a foreign key constraint
        </th>
        <td>
         No
        </td>
        <td>
         Yes
        </td>
        <td>
         No
        </td>
        <td>
         Yes
        </td>
        <td>
         Yes
        </td>
       </tr>
      </tbody>
     </table>
    </div>
   </div>
   <br class="table-break"/>
   <h5>
    <a name="online-ddl-foreign-key-syntax-notes">
    </a>
    Syntax and Usage Notes
   </h5>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       Adding a foreign key constraint
      </p>
      <p>
       The
       <code class="literal">
        INPLACE
       </code>
       algorithm is supported when
       <a class="link" href="server-system-variables.html#sysvar_foreign_key_checks">
        <code class="literal">
         foreign_key_checks
        </code>
       </a>
       is
            disabled. Otherwise, only the
       <code class="literal">
        COPY
       </code>
       algorithm is supported.
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa79255447"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> <em class="replaceable">tbl1</em> <span class="token keyword">ADD</span> <span class="token keyword">CONSTRAINT</span> <em class="replaceable">fk_name</em> <span class="token keyword">FOREIGN</span> <span class="token keyword">KEY</span> <span class="token keyword"><em class="replaceable">index</em></span> <span class="token punctuation">(</span><em class="replaceable">col1</em><span class="token punctuation">)</span>
  <span class="token keyword">REFERENCES</span> <em class="replaceable">tbl2</em><span class="token punctuation">(</span><em class="replaceable">col2</em><span class="token punctuation">)</span> <em class="replaceable">referential_actions</em><span class="token punctuation">;</span></code></pre>
      </div>
     </li>
     <li class="listitem">
      <p>
       Dropping a foreign key constraint
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa87959640"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> <em class="replaceable">tbl</em> <span class="token keyword">DROP</span> <span class="token keyword">FOREIGN</span> <span class="token keyword">KEY</span> <em class="replaceable">fk_name</em><span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       Dropping a foreign key can be performed online with the
       <a class="link" href="server-system-variables.html#sysvar_foreign_key_checks">
        <code class="literal">
         foreign_key_checks
        </code>
       </a>
       option
            enabled or disabled.
      </p>
      <p>
       If you do not know the names of the foreign key constraints
            on a particular table, issue the following statement and
            find the constraint name in the
       <code class="literal">
        CONSTRAINT
       </code>
       clause for each foreign key:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa49579196"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SHOW</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> <span class="token keyword"><em class="replaceable">table</em></span>\G</code></pre>
      </div>
      <p>
       Or, query the Information Schema
       <a class="link" href="information-schema-table-constraints-table.html" title="28.3.41 The INFORMATION_SCHEMA TABLE_CONSTRAINTS Table">
        <code class="literal">
         TABLE_CONSTRAINTS
        </code>
       </a>
       table and use
            the
       <code class="literal">
        CONSTRAINT_NAME
       </code>
       and
       <code class="literal">
        CONSTRAINT_TYPE
       </code>
       columns to identify the
            foreign key names.
      </p>
      <p>
       You can also drop a foreign key and its associated index in
            a single statement:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa7862575"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> <span class="token keyword"><em class="replaceable">table</em></span> <span class="token keyword">DROP</span> <span class="token keyword">FOREIGN</span> <span class="token keyword">KEY</span> <span class="token keyword"><em class="replaceable">constraint</em></span><span class="token punctuation">,</span> <span class="token keyword">DROP</span> <span class="token keyword">INDEX</span> <span class="token keyword"><em class="replaceable">index</em></span><span class="token punctuation">;</span></code></pre>
      </div>
     </li>
    </ul>
   </div>
   <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
    <div class="admon-title">
     Note
    </div>
    <p>
     If
     <a class="link" href="glossary.html#glos_foreign_key" title="foreign key">
      foreign keys
     </a>
     are
          already present in the table being altered (that is, it is a
     <a class="link" href="glossary.html#glos_child_table" title="child table">
      child table
     </a>
     containing
          a
     <code class="literal">
      FOREIGN KEY ... REFERENCE
     </code>
     clause),
          additional restrictions apply to online DDL operations, even
          those not directly involving the foreign key columns:
    </p>
    <div class="itemizedlist">
     <ul class="itemizedlist" style="list-style-type: disc; ">
      <li class="listitem">
       <p>
        An
        <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
         <code class="literal">
          ALTER TABLE
         </code>
        </a>
        on the child
              table could wait for another transaction to commit, if a
              change to the parent table causes associated changes in
              the child table through an
        <code class="literal">
         ON UPDATE
        </code>
        or
        <code class="literal">
         ON DELETE
        </code>
        clause using the
        <code class="literal">
         CASCADE
        </code>
        or
        <code class="literal">
         SET NULL
        </code>
        parameters.
       </p>
      </li>
      <li class="listitem">
       <p>
        In the same way, if a table is the
        <a class="link" href="glossary.html#glos_parent_table" title="parent table">
         parent table
        </a>
        in a
              foreign key relationship, even though it does not contain
              any
        <code class="literal">
         FOREIGN KEY
        </code>
        clauses, it could wait
              for the
        <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
         <code class="literal">
          ALTER TABLE
         </code>
        </a>
        to
              complete if an
        <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
         <code class="literal">
          INSERT
         </code>
        </a>
        ,
        <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
         <code class="literal">
          UPDATE
         </code>
        </a>
        , or
        <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
         <code class="literal">
          DELETE
         </code>
        </a>
        statement causes an
        <code class="literal">
         ON UPDATE
        </code>
        or
        <code class="literal">
         ON
              DELETE
        </code>
        action in the child table.
       </p>
      </li>
     </ul>
    </div>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="online-ddl-table-operations">
       </a>
       Table Operations
      </h4>
     </div>
    </div>
   </div>
   <p>
    The following table provides an overview of online DDL support
        for table operations. An asterisk indicates additional
        information, an exception, or a dependency. For details, see
    <a class="xref" href="innodb-online-ddl-operations.html#online-ddl-table-syntax-notes" title="Syntax and Usage Notes">
     Syntax and Usage Notes
    </a>
    .
   </p>
   <div class="table">
    <a name="online-ddl-table-operations-table">
    </a>
    <p class="title">
     <b>
      Table 17.20 Online DDL Support for Table Operations
     </b>
    </p>
    <div class="table-contents">
     <table summary="Online DDL support for table operations indicating whether the operation is performed in place, rebuilds the table, permits concurrent DML, or only modifies metadata.">
      <colgroup>
       <col align="left" style="width: 16%"/>
       <col align="center" style="width: 16%"/>
       <col align="center" style="width: 16%"/>
       <col align="center" style="width: 16%"/>
       <col align="center" style="width: 16%"/>
       <col align="center" style="width: 16%"/>
      </colgroup>
      <thead>
       <tr>
        <th scope="col">
         Operation
        </th>
        <th scope="col">
         Instant
        </th>
        <th scope="col">
         In Place
        </th>
        <th scope="col">
         Rebuilds Table
        </th>
        <th scope="col">
         Permits Concurrent DML
        </th>
        <th scope="col">
         Only Modifies Metadata
        </th>
       </tr>
      </thead>
      <tbody>
       <tr>
        <th scope="row">
         Changing the
         <code class="literal">
          ROW_FORMAT
         </code>
        </th>
        <td>
         No
        </td>
        <td>
         Yes
        </td>
        <td>
         Yes
        </td>
        <td>
         Yes
        </td>
        <td>
         No
        </td>
       </tr>
       <tr>
        <th scope="row">
         Changing the
         <code class="literal">
          KEY_BLOCK_SIZE
         </code>
        </th>
        <td>
         No
        </td>
        <td>
         Yes
        </td>
        <td>
         Yes
        </td>
        <td>
         Yes
        </td>
        <td>
         No
        </td>
       </tr>
       <tr>
        <th scope="row">
         Setting persistent table statistics
        </th>
        <td>
         No
        </td>
        <td>
         Yes
        </td>
        <td>
         No
        </td>
        <td>
         Yes
        </td>
        <td>
         Yes
        </td>
       </tr>
       <tr>
        <th scope="row">
         Specifying a character set
        </th>
        <td>
         No
        </td>
        <td>
         Yes
        </td>
        <td>
         Yes*
        </td>
        <td>
         Yes
        </td>
        <td>
         No
        </td>
       </tr>
       <tr>
        <th scope="row">
         Converting a character set
        </th>
        <td>
         No
        </td>
        <td>
         No
        </td>
        <td>
         Yes*
        </td>
        <td>
         No
        </td>
        <td>
         No
        </td>
       </tr>
       <tr>
        <th scope="row">
         Optimizing a table
        </th>
        <td>
         No
        </td>
        <td>
         Yes*
        </td>
        <td>
         Yes
        </td>
        <td>
         Yes
        </td>
        <td>
         No
        </td>
       </tr>
       <tr>
        <th scope="row">
         Rebuilding with the
         <code class="literal">
          FORCE
         </code>
         option
        </th>
        <td>
         No
        </td>
        <td>
         Yes*
        </td>
        <td>
         Yes
        </td>
        <td>
         Yes
        </td>
        <td>
         No
        </td>
       </tr>
       <tr>
        <th scope="row">
         Performing a null rebuild
        </th>
        <td>
         No
        </td>
        <td>
         Yes*
        </td>
        <td>
         Yes
        </td>
        <td>
         Yes
        </td>
        <td>
         No
        </td>
       </tr>
       <tr>
        <th scope="row">
         Renaming a table
        </th>
        <td>
         Yes
        </td>
        <td>
         Yes
        </td>
        <td>
         No
        </td>
        <td>
         Yes
        </td>
        <td>
         Yes
        </td>
       </tr>
      </tbody>
     </table>
    </div>
   </div>
   <br class="table-break"/>
   <h5>
    <a name="online-ddl-table-syntax-notes">
    </a>
    Syntax and Usage Notes
   </h5>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       Changing the
       <code class="literal">
        ROW_FORMAT
       </code>
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa57524705"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> <em class="replaceable">tbl_name</em> <span class="token keyword">ROW_FORMAT</span> <span class="token operator">=</span> <span class="token keyword"><em class="replaceable">row_format</em></span><span class="token punctuation">,</span> <span class="token keyword">ALGORITHM</span><span class="token operator">=</span><span class="token keyword">INPLACE</span><span class="token punctuation">,</span> <span class="token keyword">LOCK</span><span class="token operator">=</span><span class="token keyword">NONE</span><span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       Data is reorganized substantially, making it an expensive
            operation.
      </p>
      <p>
       For additional information about the
       <code class="literal">
        ROW_FORMAT
       </code>
       option, see
       <a class="xref" href="create-table.html#create-table-options" title="Table Options">
        Table Options
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       Changing the
       <code class="literal">
        KEY_BLOCK_SIZE
       </code>
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa81677453"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> <em class="replaceable">tbl_name</em> <span class="token keyword">KEY_BLOCK_SIZE</span> <span class="token operator">=</span> <span class="token keyword"><em class="replaceable">value</em></span><span class="token punctuation">,</span> <span class="token keyword">ALGORITHM</span><span class="token operator">=</span><span class="token keyword">INPLACE</span><span class="token punctuation">,</span> <span class="token keyword">LOCK</span><span class="token operator">=</span><span class="token keyword">NONE</span><span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       Data is reorganized substantially, making it an expensive
            operation.
      </p>
      <p>
       For additional information about the
       <code class="literal">
        KEY_BLOCK_SIZE
       </code>
       option, see
       <a class="xref" href="create-table.html#create-table-options" title="Table Options">
        Table Options
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       Setting persistent table statistics options
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa93841705"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> <em class="replaceable">tbl_name</em> <span class="token keyword">STATS_PERSISTENT</span><span class="token operator">=</span><span class="token number">0</span><span class="token punctuation">,</span> <span class="token keyword">STATS_SAMPLE_PAGES</span><span class="token operator">=</span><span class="token number">20</span><span class="token punctuation">,</span> <span class="token keyword">STATS_AUTO_RECALC</span><span class="token operator">=</span><span class="token number">1</span><span class="token punctuation">,</span> <span class="token keyword">ALGORITHM</span><span class="token operator">=</span><span class="token keyword">INPLACE</span><span class="token punctuation">,</span> <span class="token keyword">LOCK</span><span class="token operator">=</span><span class="token keyword">NONE</span><span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       Only modifies table metadata.
      </p>
      <p>
       Persistent statistics include
       <code class="literal">
        STATS_PERSISTENT
       </code>
       ,
       <code class="literal">
        STATS_AUTO_RECALC
       </code>
       , and
       <code class="literal">
        STATS_SAMPLE_PAGES
       </code>
       . For more information,
            see
       <a class="xref" href="innodb-persistent-stats.html" title="17.8.10.1 Configuring Persistent Optimizer Statistics Parameters">
        Section 17.8.10.1, “Configuring Persistent Optimizer Statistics Parameters”
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       Specifying a character set
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa74970790"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> <em class="replaceable">tbl_name</em> <span class="token keyword">CHARACTER</span> <span class="token keyword">SET</span> <span class="token operator">=</span> <em class="replaceable">charset_name</em><span class="token punctuation">,</span> <span class="token keyword">ALGORITHM</span><span class="token operator">=</span><span class="token keyword">INPLACE</span><span class="token punctuation">,</span> <span class="token keyword">LOCK</span><span class="token operator">=</span><span class="token keyword">NONE</span><span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       Rebuilds the table if the new character encoding is
            different.
      </p>
     </li>
     <li class="listitem">
      <p>
       Converting a character set
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa33225729"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> <em class="replaceable">tbl_name</em> CONVERT <span class="token keyword">TO</span> <span class="token keyword">CHARACTER</span> <span class="token keyword">SET</span> <em class="replaceable">charset_name</em><span class="token punctuation">,</span> <span class="token keyword">ALGORITHM</span><span class="token operator">=</span><span class="token keyword">COPY</span><span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       Rebuilds the table if the new character encoding is
            different.
      </p>
     </li>
     <li class="listitem">
      <p>
       Optimizing a table
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa67914549"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">OPTIMIZE</span> <span class="token keyword">TABLE</span> <em class="replaceable">tbl_name</em><span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       In-place operation is not supported for tables with
       <code class="literal">
        FULLTEXT
       </code>
       indexes. The operation uses the
       <code class="literal">
        INPLACE
       </code>
       algorithm, but
       <code class="literal">
        ALGORITHM
       </code>
       and
       <code class="literal">
        LOCK
       </code>
       syntax is not permitted.
      </p>
     </li>
     <li class="listitem">
      <p>
       Rebuilding a table with the
       <code class="literal">
        FORCE
       </code>
       option
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa70301573"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> <em class="replaceable">tbl_name</em> <span class="token keyword">FORCE</span><span class="token punctuation">,</span> <span class="token keyword">ALGORITHM</span><span class="token operator">=</span><span class="token keyword">INPLACE</span><span class="token punctuation">,</span> <span class="token keyword">LOCK</span><span class="token operator">=</span><span class="token keyword">NONE</span><span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       Uses
       <code class="literal">
        ALGORITHM=INPLACE
       </code>
       as of MySQL
            5.6.17
       <code class="literal">
       </code>
       .
       <code class="literal">
        ALGORITHM=INPLACE
       </code>
       is
            not supported for tables with
       <code class="literal">
        FULLTEXT
       </code>
       indexes.
      </p>
     </li>
     <li class="listitem">
      <p>
       Performing a "null" rebuild
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa44588404"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> <em class="replaceable">tbl_name</em> <span class="token keyword">ENGINE</span><span class="token operator">=</span>InnoDB<span class="token punctuation">,</span> <span class="token keyword">ALGORITHM</span><span class="token operator">=</span><span class="token keyword">INPLACE</span><span class="token punctuation">,</span> <span class="token keyword">LOCK</span><span class="token operator">=</span><span class="token keyword">NONE</span><span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       Uses
       <code class="literal">
        ALGORITHM=INPLACE
       </code>
       as of MySQL
            5.6.17.
       <code class="literal">
        ALGORITHM=INPLACE
       </code>
       is not
            supported for tables with
       <code class="literal">
        FULLTEXT
       </code>
       indexes.
      </p>
     </li>
     <li class="listitem">
      <p>
       Renaming a table
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa61403780"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> <em class="replaceable">old_tbl_name</em> <span class="token keyword">RENAME</span> <span class="token keyword">TO</span> <em class="replaceable">new_tbl_name</em><span class="token punctuation">,</span> <span class="token keyword">ALGORITHM</span><span class="token operator">=</span>INSTANT<span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       Renaming a table can be performed instantly or in place.
            MySQL renames files that correspond to the table
       <em class="replaceable">
        <code>
         tbl_name
        </code>
       </em>
       without making a copy.
            (You can also use the
       <a class="link" href="rename-table.html" title="15.1.36 RENAME TABLE Statement">
        <code class="literal">
         RENAME
            TABLE
        </code>
       </a>
       statement to rename tables. See
       <a class="xref" href="rename-table.html" title="15.1.36 RENAME TABLE Statement">
        Section 15.1.36, “RENAME TABLE Statement”
       </a>
       .) Privileges granted
            specifically for the renamed table are not migrated to the
            new name. They must be changed manually.
      </p>
     </li>
    </ul>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="online-ddl-tablespace-operations">
       </a>
       Tablespace Operations
      </h4>
     </div>
    </div>
   </div>
   <p>
    The following table provides an overview of online DDL support
        for tablespace operations. For details, see
    <a class="xref" href="innodb-online-ddl-operations.html#online-ddl-tablespace-syntax-notes" title="Syntax and Usage Notes">
     Syntax and Usage Notes
    </a>
    .
   </p>
   <div class="table">
    <a name="online-ddl-tablespace-operations-table">
    </a>
    <p class="title">
     <b>
      Table 17.21 Online DDL Support for Tablespace Operations
     </b>
    </p>
    <div class="table-contents">
     <table summary="Online DDL support for tablespace operations indicating whether the operation is performed in place, rebuilds tables within the tablespace, permits concurrent DML, or only modifies metadata.">
      <colgroup>
       <col align="left" style="width: 16%"/>
       <col align="center" style="width: 16%"/>
       <col align="center" style="width: 16%"/>
       <col align="center" style="width: 16%"/>
       <col align="center" style="width: 16%"/>
       <col align="center" style="width: 16%"/>
      </colgroup>
      <thead>
       <tr>
        <th scope="col">
         Operation
        </th>
        <th scope="col">
         Instant
        </th>
        <th scope="col">
         In Place
        </th>
        <th scope="col">
         Rebuilds Table
        </th>
        <th scope="col">
         Permits Concurrent DML
        </th>
        <th scope="col">
         Only Modifies Metadata
        </th>
       </tr>
      </thead>
      <tbody>
       <tr>
        <th scope="row">
         Renaming a general tablespace
        </th>
        <td>
         No
        </td>
        <td>
         Yes
        </td>
        <td>
         No
        </td>
        <td>
         Yes
        </td>
        <td>
         Yes
        </td>
       </tr>
       <tr>
        <th scope="row">
         Enabling or disabling general tablespace encryption
        </th>
        <td>
         No
        </td>
        <td>
         Yes
        </td>
        <td>
         No
        </td>
        <td>
         Yes
        </td>
        <td>
         No
        </td>
       </tr>
       <tr>
        <th scope="row">
         Enabling or disabling file-per-table tablespace encryption
        </th>
        <td>
         No
        </td>
        <td>
         No
        </td>
        <td>
         Yes
        </td>
        <td>
         No
        </td>
        <td>
         No
        </td>
       </tr>
      </tbody>
     </table>
    </div>
   </div>
   <br class="table-break"/>
   <h5>
    <a name="online-ddl-tablespace-syntax-notes">
    </a>
    Syntax and Usage Notes
   </h5>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       Renaming a general tablespace
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa14365910"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLESPACE</span> <em class="replaceable">tablespace_name</em> <span class="token keyword">RENAME</span> <span class="token keyword">TO</span> <em class="replaceable">new_tablespace_name</em><span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       <a class="link" href="alter-tablespace.html" title="15.1.10 ALTER TABLESPACE Statement">
        <code class="literal">
         ALTER
            TABLESPACE ... RENAME TO
        </code>
       </a>
       uses the
       <code class="literal">
        INPLACE
       </code>
       algorithm but does not support
            the
       <code class="literal">
        ALGORITHM
       </code>
       clause.
      </p>
     </li>
     <li class="listitem">
      <p>
       Enabling or disabling general tablespace encryption
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa60127475"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLESPACE</span> <em class="replaceable">tablespace_name</em> <span class="token keyword">ENCRYPTION</span><span class="token operator">=</span><span class="token string">'Y'</span><span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       <a class="link" href="alter-tablespace.html" title="15.1.10 ALTER TABLESPACE Statement">
        <code class="literal">
         ALTER
            TABLESPACE ... ENCRYPTION
        </code>
       </a>
       uses the
       <code class="literal">
        INPLACE
       </code>
       algorithm but does not support
            the
       <code class="literal">
        ALGORITHM
       </code>
       clause.
      </p>
      <p>
       For related information, see
       <a class="xref" href="innodb-data-encryption.html" title="17.13 InnoDB Data-at-Rest Encryption">
        Section 17.13, “InnoDB Data-at-Rest Encryption”
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       Enabling or disabling file-per-table tablespace encryption
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa18482847"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">ALTER</span> <span class="token keyword">TABLE</span> <em class="replaceable">tbl_name</em> <span class="token keyword">ENCRYPTION</span><span class="token operator">=</span><span class="token string">'Y'</span><span class="token punctuation">,</span> <span class="token keyword">ALGORITHM</span><span class="token operator">=</span><span class="token keyword">COPY</span><span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       For related information, see
       <a class="xref" href="innodb-data-encryption.html" title="17.13 InnoDB Data-at-Rest Encryption">
        Section 17.13, “InnoDB Data-at-Rest Encryption”
       </a>
       .
      </p>
     </li>
    </ul>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="online-ddl-partitioning">
       </a>
       Partitioning Operations
      </h4>
     </div>
    </div>
   </div>
   <p>
    With the exception of some
    <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
     <code class="literal">
      ALTER
        TABLE
     </code>
    </a>
    partitioning clauses, online DDL operations for
        partitioned
    <code class="literal">
     InnoDB
    </code>
    tables follow the same
        rules that apply to regular
    <code class="literal">
     InnoDB
    </code>
    tables.
   </p>
   <p>
    Some
    <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
     <code class="literal">
      ALTER TABLE
     </code>
    </a>
    partitioning
        clauses do not go through the same internal online DDL API as
        regular non-partitioned
    <code class="literal">
     InnoDB
    </code>
    tables. As a
        result, online support for
    <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
     <code class="literal">
      ALTER
        TABLE
     </code>
    </a>
    partitioning clauses varies.
   </p>
   <p>
    The following table shows the online status for each
    <code class="literal">
     ALTER TABLE
    </code>
    partitioning statement.
        Regardless of the online DDL API that is used, MySQL attempts to
        minimize data copying and locking where possible.
   </p>
   <p>
    <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
     <code class="literal">
      ALTER TABLE
     </code>
    </a>
    partitioning options
        that use
    <code class="literal">
     ALGORITHM=COPY
    </code>
    or that only permit
    <span class="quote">
     “
     <span class="quote">
      <code class="literal">
       ALGORITHM=DEFAULT,
        LOCK=DEFAULT
      </code>
     </span>
     ”
    </span>
    , repartition the table using the
    <code class="literal">
     COPY
    </code>
    algorithm. In other words, a new
        partitioned table is created with the new partitioning scheme.
        The newly created table includes any changes applied by the
    <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
     <code class="literal">
      ALTER TABLE
     </code>
    </a>
    statement, and table
        data is copied into the new table structure.
   </p>
   <div class="table">
    <a name="innodb-online-ddl-partitioning-clauses">
    </a>
    <p class="title">
     <b>
      Table 17.22 Online DDL Support for Partitioning Operations
     </b>
    </p>
    <div class="table-contents">
     <table summary="Online DDL support for partitioning operations indicating whether the operation is performed in place and permits concurrent DML.">
      <colgroup>
       <col align="left" style="width: 24%"/>
       <col align="center" style="width: 8%"/>
       <col align="center" style="width: 8%"/>
       <col align="center" style="width: 12%"/>
       <col align="left" style="width: 32%"/>
      </colgroup>
      <thead>
       <tr>
        <th scope="col">
         Partitioning Clause
        </th>
        <th scope="col">
         Instant
        </th>
        <th scope="col">
         In Place
        </th>
        <th scope="col">
         Permits DML
        </th>
        <th scope="col">
         Notes
        </th>
       </tr>
      </thead>
      <tbody>
       <tr>
        <th scope="row">
         <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
          <code class="literal">
           PARTITION BY
          </code>
         </a>
        </th>
        <td>
         No
        </td>
        <td>
         No
        </td>
        <td>
         No
        </td>
        <td>
         Permits
         <code class="literal">
          ALGORITHM=COPY
         </code>
         ,
         <code class="literal">
          LOCK={DEFAULT|SHARED|EXCLUSIVE}
         </code>
        </td>
       </tr>
       <tr>
        <th scope="row">
         <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
          <code class="literal">
           ADD PARTITION
          </code>
         </a>
        </th>
        <td>
         No
        </td>
        <td>
         Yes*
        </td>
        <td>
         Yes*
        </td>
        <td>
         <code class="literal">
          ALGORITHM=INPLACE,
              LOCK={DEFAULT|NONE|SHARED|EXCLUSISVE}
         </code>
         is
              supported for
         <code class="literal">
          RANGE
         </code>
         and
         <code class="literal">
          LIST
         </code>
         partitions,
         <code class="literal">
          ALGORITHM=INPLACE,
              LOCK={DEFAULT|SHARED|EXCLUSISVE}
         </code>
         for
         <code class="literal">
          HASH
         </code>
         and
         <code class="literal">
          KEY
         </code>
         partitions, and
         <code class="literal">
          ALGORITHM=COPY,
              LOCK={SHARED|EXCLUSIVE}
         </code>
         for all partition types.
              Does not copy existing data for tables partitioned by
         <code class="literal">
          RANGE
         </code>
         or
         <code class="literal">
          LIST
         </code>
         .
              Concurrent queries are permitted with
         <code class="literal">
          ALGORITHM=COPY
         </code>
         for tables partitioned
              by
         <code class="literal">
          HASH
         </code>
         or
         <code class="literal">
          LIST
         </code>
         , as
              MySQL copies the data while holding a shared lock.
        </td>
       </tr>
       <tr>
        <th scope="row">
         <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
          <code class="literal">
           DROP PARTITION
          </code>
         </a>
        </th>
        <td>
         No
        </td>
        <td>
         Yes*
        </td>
        <td>
         Yes*
        </td>
        <td>
         <p>
          <code class="literal">
           ALGORITHM=INPLACE,
                LOCK={DEFAULT|NONE|SHARED|EXCLUSIVE}
          </code>
          is
                supported. Does not copy data for tables partitioned by
          <code class="literal">
           RANGE
          </code>
          or
          <code class="literal">
           LIST
          </code>
          .
         </p>
         <p>
          <code class="literal">
           DROP PARTITION
          </code>
          with
          <code class="literal">
           ALGORITHM=INPLACE
          </code>
          deletes data stored
                in the partition and drops the partition. However,
          <code class="literal">
           DROP PARTITION
          </code>
          with
          <code class="literal">
           ALGORITHM=COPY
          </code>
          or
          <a class="link" href="server-system-variables.html#sysvar_old_alter_table">
           <code class="literal">
            old_alter_table=ON
           </code>
          </a>
          rebuilds the partitioned table and attempts to move data
                from the dropped partition to another partition with a
                compatible
          <code class="literal">
           PARTITION ... VALUES
          </code>
          definition. Data that cannot be moved to another
                partition is deleted.
         </p>
        </td>
       </tr>
       <tr>
        <th scope="row">
         <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
          <code class="literal">
           DISCARD PARTITION
          </code>
         </a>
        </th>
        <td>
         No
        </td>
        <td>
         No
        </td>
        <td>
         No
        </td>
        <td>
         Only permits
         <code class="literal">
          ALGORITHM=DEFAULT
         </code>
         ,
         <code class="literal">
          LOCK=DEFAULT
         </code>
        </td>
       </tr>
       <tr>
        <th scope="row">
         <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
          <code class="literal">
           IMPORT PARTITION
          </code>
         </a>
        </th>
        <td>
         No
        </td>
        <td>
         No
        </td>
        <td>
         No
        </td>
        <td>
         Only permits
         <code class="literal">
          ALGORITHM=DEFAULT
         </code>
         ,
         <code class="literal">
          LOCK=DEFAULT
         </code>
        </td>
       </tr>
       <tr>
        <th scope="row">
         <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
          <code class="literal">
           TRUNCATE
              PARTITION
          </code>
         </a>
        </th>
        <td>
         No
        </td>
        <td>
         Yes
        </td>
        <td>
         Yes
        </td>
        <td>
         Does not copy existing data. It merely deletes rows; it does not alter
              the definition of the table itself, or of any of its
              partitions.
        </td>
       </tr>
       <tr>
        <th scope="row">
         <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
          <code class="literal">
           COALESCE
              PARTITION
          </code>
         </a>
        </th>
        <td>
         No
        </td>
        <td>
         Yes*
        </td>
        <td>
         No
        </td>
        <td>
         <code class="literal">
          ALGORITHM=INPLACE, LOCK={DEFAULT|SHARED|EXCLUSIVE}
         </code>
         is
              supported.
        </td>
       </tr>
       <tr>
        <th scope="row">
         <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
          <code class="literal">
           REORGANIZE
              PARTITION
          </code>
         </a>
        </th>
        <td>
         No
        </td>
        <td>
         Yes*
        </td>
        <td>
         No
        </td>
        <td>
         <code class="literal">
          ALGORITHM=INPLACE, LOCK={DEFAULT|SHARED|EXCLUSIVE}
         </code>
         is
              supported.
        </td>
       </tr>
       <tr>
        <th scope="row">
         <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
          <code class="literal">
           EXCHANGE
              PARTITION
          </code>
         </a>
        </th>
        <td>
         No
        </td>
        <td>
         Yes
        </td>
        <td>
         Yes
        </td>
        <td>
        </td>
       </tr>
       <tr>
        <th scope="row">
         <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
          <code class="literal">
           ANALYZE PARTITION
          </code>
         </a>
        </th>
        <td>
         No
        </td>
        <td>
         Yes
        </td>
        <td>
         Yes
        </td>
        <td>
        </td>
       </tr>
       <tr>
        <th scope="row">
         <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
          <code class="literal">
           CHECK PARTITION
          </code>
         </a>
        </th>
        <td>
         No
        </td>
        <td>
         Yes
        </td>
        <td>
         Yes
        </td>
        <td>
        </td>
       </tr>
       <tr>
        <th scope="row">
         <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
          <code class="literal">
           OPTIMIZE
              PARTITION
          </code>
         </a>
        </th>
        <td>
         No
        </td>
        <td>
         No
        </td>
        <td>
         No
        </td>
        <td>
         <code class="literal">
          ALGORITHM
         </code>
         and
         <code class="literal">
          LOCK
         </code>
         clauses are
              ignored. Rebuilds the entire table. See
         <a class="xref" href="partitioning-maintenance.html" title="26.3.4 Maintenance of Partitions">
          Section 26.3.4, “Maintenance of Partitions”
         </a>
         .
        </td>
       </tr>
       <tr>
        <th scope="row">
         <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
          <code class="literal">
           REBUILD PARTITION
          </code>
         </a>
        </th>
        <td>
         No
        </td>
        <td>
         Yes*
        </td>
        <td>
         No
        </td>
        <td>
         <code class="literal">
          ALGORITHM=INPLACE, LOCK={DEFAULT|SHARED|EXCLUSIVE}
         </code>
         is
              supported.
        </td>
       </tr>
       <tr>
        <th scope="row">
         <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
          <code class="literal">
           REPAIR PARTITION
          </code>
         </a>
        </th>
        <td>
         No
        </td>
        <td>
         Yes
        </td>
        <td>
         Yes
        </td>
        <td>
        </td>
       </tr>
       <tr>
        <th scope="row">
         <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
          <code class="literal">
           REMOVE
              PARTITIONING
          </code>
         </a>
        </th>
        <td>
         No
        </td>
        <td>
         No
        </td>
        <td>
         No
        </td>
        <td>
         Permits
         <code class="literal">
          ALGORITHM=COPY
         </code>
         ,
         <code class="literal">
          LOCK={DEFAULT|SHARED|EXCLUSIVE}
         </code>
        </td>
       </tr>
      </tbody>
     </table>
    </div>
    <div class="table-contents">
     <table cellpadding="0" cellspacing="0" style="position: fixed; top: 0px; display: none; left: 401px; width: 739px;">
      <thead>
       <tr>
        <th scope="col" style="width: 202.031px;">
         Partitioning Clause
        </th>
        <th scope="col" style="width: 67.3281px;">
         Instant
        </th>
        <th scope="col" style="width: 67.3281px;">
         In Place
        </th>
        <th scope="col" style="width: 101px;">
         Permits DML
        </th>
        <th scope="col" style="width: 300.312px;">
         Notes
        </th>
       </tr>
      </thead>
     </table>
    </div>
   </div>
   <br class="table-break"/>
   <p>
    Non-partitioning online
    <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
     <code class="literal">
      ALTER
        TABLE
     </code>
    </a>
    operations on partitioned tables follow the same
        rules that apply to regular tables. However,
    <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
     <code class="literal">
      ALTER TABLE
     </code>
    </a>
    performs online
        operations on each table partition, which causes increased
        demand on system resources due to operations being performed on
        multiple partitions.
   </p>
   <p>
    For additional information about
    <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
     <code class="literal">
      ALTER
        TABLE
     </code>
    </a>
    partitioning clauses, see
    <a class="xref" href="alter-table.html#alter-table-partition-options" title="Partitioning Options">
     Partitioning Options
    </a>
    , and
    <a class="xref" href="alter-table-partition-operations.html" title="15.1.9.1 ALTER TABLE Partition Operations">
     Section 15.1.9.1, “ALTER TABLE Partition Operations”
    </a>
    . For
        information about partitioning in general, see
    <a class="xref" href="partitioning.html" title="Chapter 26 Partitioning">
     Chapter 26,
     <i>
      Partitioning
     </i>
    </a>
    .
   </p>
  </div>
 </div>
 <br/>
</div>
