如何在DDL语句中添加优化器提示，仅为单条ALTER语句设置lock_wait_timeout？

一、DDL查询中添加优化器提示 #

不同数据库的优化器提示语法略有差异，常见实现方式如下：

• MySQL：使用/*+ 提示内容 */格式将提示嵌入DDL语句中，例如：

  ALTER TABLE /*+ ALGORITHM=INPLACE, LOCK=NONE */ users ADD COLUMN age INT;
  

  这里的ALGORITHM和LOCK是针对ALTER操作的特定优化提示，也可使用通用优化器提示如MAX_EXECUTION_TIME(1000)。
• PostgreSQL：12及以上版本支持使用/*+ 提示内容 */格式，例如：

  ALTER TABLE /*+ PARALLEL(4) */ users ADD COLUMN age INT;
  

  若要临时调整会话参数配合优化，可在事务内用SET LOCAL（见下文lock_wait_timeout部分）。
• Oracle：使用/*+ 提示内容 */格式，针对DDL的提示相对较少，更多用于DML，但部分场景可指定，例如：

  ALTER TABLE /*+ NOLOGGING */ users ADD COLUMN age INT;
  

二、单条ALTER语句中设置lock_wait_timeout（仅当前语句生效） #

要实现仅对单条ALTER语句生效的lock_wait_timeout设置，不同数据库的实现方式不同：

• MySQL 8.0.14及以上版本：支持SET STATEMENT语法，直接在ALTER语句前指定参数，仅对后续的单条语句生效：

  SET STATEMENT lock_wait_timeout=1000 FOR ALTER TABLE users ADD COLUMN age INT;
  

  这里1000代表等待时间（单位：毫秒），执行后会话的lock_wait_timeout不会被修改。
• PostgreSQL：由于lock_wait_timeout是会话级参数，需借助事务包裹，通过SET LOCAL仅在当前事务内生效，而PostgreSQL的DDL默认支持事务（部分DDL除外），示例：

  BEGIN;
  SET LOCAL lock_wait_timeout = '1s';
  ALTER TABLE users ADD COLUMN age INT;
  COMMIT;
  

  整个事务是原子性的，且SET LOCAL的参数仅在该事务内有效，不会影响会话其他语句。
• Oracle：可通过PL/SQL块动态执行，先临时修改会话参数，执行ALTER后恢复原参数，示例：

  DECLARE
    original_timeout NUMBER;
  BEGIN
    SELECT lock_wait_timeout INTO original_timeout FROM v$parameter WHERE name='lock_wait_timeout';
    EXECUTE IMMEDIATE 'ALTER SESSION SET lock_wait_timeout=10';
    EXECUTE IMMEDIATE 'ALTER TABLE users ADD COLUMN age INT';
    EXECUTE IMMEDIATE 'ALTER SESSION SET lock_wait_timeout=' || original_timeout;
  END;
  /
  

  这种方式确保仅目标ALTER语句使用指定的lock_wait_timeout，执行后恢复原会话设置。

内容的提问来源于stack exchange，提问作者INDIAN DECODER