文章
数据库
Oracle Database 12c 中的 DDL 优化
简介
从 Oracle 11g 开始，实现了一项可以提高数据定义语言操作性能的强大的新优化技术。当您向现有表添加一个非空列、同时希望向这个新增列附加一个常量默认值时，新的 DDL 优化正好发挥作用，立即完成 DDL 操作。当更改的表有数百万条记录需将新添加的列更新为常量默认值时，这如何实现？这种新优化有哪些需要在使用前注意的副作用吗？本文将对此进行介绍。
概念
看看下面这个有约 3 百万行记录的表：
SQL> create table t1
as select
rownum n1
, trunc ((rownum-1)/3) n2
, trunc(dbms_random.value(rownum, rownum*10)) n3
, dbms_random.string('U', 10) c1
from dual
connect by level <= 3e6;
SQL> desc t1
Name Null? Type
----------------------- ---------
1 N1 NUMBER
2 N2 NUMBER
3 N3 NUMBER
4 C1 VARCHAR2(4000 CHAR) 我将向此表添加一个带默认值的非空列。
SQL> alter table t1 add C_DDL number default 42 not null;
其中两个关键字 default 和 not null 已经用粗体显示，因为它们代表驱动此新特性的关键。
为了解上面的 alter table 命令执行时间的差异，我将在两个不同的 Oracle 数据库版本（10.2.0.4.0 和 11.2.0.3.0）中分别执行此命令：
10.2.0.4.0 > alter table t1 add C_DDL number default 42 not null; Table altered. Elapsed: 00:00:48.53 11.2.0.3.0> alter table t1 add C_DDL number default 42 not null; Table altered. Elapsed: 00:00:00.04
注意它们在执行时间上的差异。在 11gR2 数据库中立即添加了 C_DDL 列，而在 10gR2 中用时近 49 秒。这是什么样的新机制，允许这么快速地向现有表添加具有默认值的非空列？
3 百万行记录是如何在 4 毫秒内更新的？
我们来直观地验证是否真的进行了更新（从现在起，未指定 Oracle 版本时默认为 11.0.2.3）。
SQL> select count(1) from t1; COUNT(1) ---------- 3000000 SQL> select count(1) from t1 where c_ddl = 42; COUNT(1) ---------- 3000000
虽然 Oracle 立即修改了 t1 表，但查询显示整个 C_DDL 列上下均已更新且默认值设置为 42。怎么会出现这种情况？执行计划是否对此有任何帮助？
SQL> select * from table(dbms_xplan.display_cursor);
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3016 (100) | |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
|*2 | TABLE ACCESS FULL | T1 | 2999K| 8788K| 3016 (5) | 00:00:10 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(NVL("C_DDL",42)=42) 这里再次提醒注意，上述执行计划的谓词 (predicate) 部分能揭示有助于您了解幕后操作的重要信息。尽管我在查询中未使用 NVL 函数，但它还是出现在谓词部分，这表明 Oracle 在内部仍将 C_DDL 列视为可能包含空值（这意味着它未更新），因此 Oracle 用默认值 42 替换它。
我们以早期版本作为比较基准，找寻二者之间的差异：
10.2.0.4.0 > select count(1) from t1 where c_ddl = 42;
COUNT(1)
----------
3000000
10.2.0.4.0> select * from table(dbms_xplan.display_cursor);
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4001 (100) | |
| 1 | SORT AGGREGATE | | 1| 3 | | |
|* 2 | TABLE ACCESS FULL| T1 | 3000K| 8789K | 4001 (8) | 00:00:09 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("C_DDL"=42) 谓词部分不含 NVL 函数连同 10gR2 中添加该列的用时 (00:00:48.53) 解释了 11gR1 中引入的工作概念，即通过向现有表添加非空带默认值列来优化。
简单地说，从 Oracle 11gR1 开始，当您添加一个带默认值的非空列时，Oracle 不会用此默认值更新所有现有行。相反，Oracle 将存储这个新列的元数据（非空约束和默认值 42），并允许几乎立即添加该列，无论被更改的表大小如何。当然，其代价是从表块检索添加的列时要添加一个 NVL 函数。
解释完 DDL 优化的这一精妙概念之后，下一节将稍稍探讨下 Oracle 如何管理此特性以确保 DDL 的快速执行，以及保证正确、高效完成数据检索。特别是，我们将看到从表块检索所添加列与通过索引叶块访问该列之间的区别。
更改的表上的工作机制
我们在上面看到了添加具默认值的非空列时的性能提升。但我们还看到之所以有此性能提升，是因为 Oracle 引入了一个 NVL 函数，并将其应用于添加的列，从而通过数据字典中存储的对应元数据模拟等于其默认值的空 C_DDL。这种对 NVL 函数的隐式使用会带来什么副作用吗？
首先我们在上面看到的是这对 CBO 进行的估算没有任何影响，因为这是精确估算生成的行数，如下所示：
SQL> select /*+ gather_plan_statistics */ count(1) from t1 where C_DDL = 42; COUNT(1) ---------- 3000000 SQL> select * from table(dbms_xplan.display_cursor(null,null,’ALLSTATS LAST’)); --------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1| 00:00:00.37 | | 1 | SORT AGGREGATE | | 1 | 1| 1| 00:00:00.37 | |* 2 | TABLE ACCESS FULL| T1 | 1 | 2999K| 3000K| 00:00:00.44 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter(NVL("C_DDL",42)=42)
但对表块进行完全扫描时还是多花了一点时间（44 毫秒），可能是因为与在早期版本中执行同一操作的时间（5 毫秒）相比，新的筛选器使用了 NVL 函数：
10.2.0.4.0> select /*+ gather_plan_statistics */ count(1) from t1 where C_DDL = 42
COUNT(1)
----------
3000000
10.2.0.4.0> select * from table(dbms_xplan.display_cursor(null,null,’ALLSTATS LAST’));
---------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |
---------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | 1 | 1 | 00:00:01.06 |
|* 2 | TABLE ACCESS FULL| T1 | 1 | 3000K| 3000K| 00:00:00.05 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("C_DDL"=42) 索引 C_DDL 列上的工作机制
对于计入谓词部分的列，当对其应用函数时，该函数将阻止使用此列上可能存在的任何索引。在这种特定情况下，应用于 C_DDL 列的 NVL 函数是否会抢占本应由 CBO 使用的索引（如果此列编有索引）？稍后将对此进行介绍。
请考虑以下索引：
SQL> create index i1_c_ddl on t1(c_ddl);
Index created.
Elapsed: 00:00:02.14
再重新查询：
SQL> select /*+ gather_plan_statistics */ count(1) from t1 where C_DDL = 42;
COUNT(1)
----------
3000000
SQL> select * from table(dbms_xplan.display_cursor(null,null,’ALLSTATS LAST’));
---------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 | 00:00:00.47 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 | 00:00:00.47 |
|* 2 | INDEX FAST FULL SCAN| I1_C_DDL | 1 | 2999K| 3000K| 00:00:00.75 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("C_DDL"=42) 在此强调一条好消息：从索引叶块检索 C_DDL 列的值时，并不对该列应用隐藏的 NVL 函数，这解释了 CBO 一直使用此索引的原因。
但您可能会争辩说这是正常行为：索引不能包含空列值。因此，我们创建一个含非空列的多列组合索引来保护 C_DDL 列的非空值。如下所示：
SQL> drop index i1_c_ddl;
Index dropped.
SQL> alter table t1 modify n1 not null;
Table altered.
SQL> create index i2_n1_c_ddl on t1(n1,c_ddl);
Index created.
SQL> select /*+ gather_plan_statistics */ count(1) from t1 where n1= 100 and C_DDL = 42;
COUNT(1)
----------
1
---------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 | 00:00:00.01 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 | 00:00:00.01 |
|* 2 | INDEX RANGE SCAN | I2_N1_C_DDL | 1 | 1 | 1 | 00:00:00.01 |
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("N1"=100 AND "C_DDL"=42) 即使在添加的 C_DDL 列因出现在组合索引内而防止出现空值时，也无迹象表明隐藏的 NVL 函数应用于 C_DDL 列。这清楚地表明，与 C_DDL 列无更新的表块相比，在同一列中创建的索引的叶块将立即由 C_DDL 列的默认值填充。
完成本节之前，再介绍一个有趣的问题。我们到目前为止所看到的是，每次 CBO 决定访问一个表块时，它会对 C_DDL 列应用 NVL 函数，以确保检索非空 C_DDL 值（只要此值未更新）。但是我们看到，对表进行全面扫描 (TABLE ACCESS FULL) 时，总是会应用此筛选器。当通过索引访问 t1 表 (TABLE ACCESS BY INDEX ROWID) 时，CBO 会应用此 NVL 函数吗？我们设计一个简单情形，观察这种特殊情况下 CBO 的反应：
SQL> drop index i2_n1_c_ddl;
SQL> create index i2_n1_c_ddl on t1(n1);
SQL> select /*+ gather_plan_statistics */ count(1) from t1 where n1= 100 and C_DDL = 42;
-------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |
|* 2 | TABLE ACCESS BY INDEX ROWID | T1 | 1 | 1 | 1 |
|* 3 | INDEX RANGE SCAN | I2_N1_C_DDL | 1 | 1 | 1 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(NVL("C_DDL",42)=42)
3 - access("N1"=100) 观察即便通过索引 rowid 访问表 t1 时，NVL 函数如何仍应用于 C_DDL 列。
现在我们可以有信心地说，CBO 每次访问表块时，无论是通过单块还是多块读取，都会将 NVL 函数应用于任何必须从已访问表块筛选的“DDL 优化”列。然而，如果“DDL 优化”列是从索引叶块获取的，CBO 不会对其应用 NVL 函数。
Oracle 12c 和针对 NULL 列的数据优化
随着 12c Oracle 版发布，现在可以问 Oracle 12c 中是否仍然提供 DDL 优化了。一图胜千言，我们在该版本中重复相同的实验：
12c > alter table t1 add C_DDL number default 42 not null;
Elapsed: 00:00:00.02 几乎立即完成。如以下查询的谓词部分使用 NVL 函数所显示和证明的，DDL 优化在此再次发挥了作用：
12c> select count(1) from t1 where c_ddl=42;
COUNT(1)
----------
3000000
12c> select * from table(dbms_xplan.display_cursor);
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3802 (100)| |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | TABLE ACCESS FULL| T1 | 3538K| 43M| 3802 (1)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(NVL("C_DDL",42)=42)
Note
-----
- dynamic statistics used: dynamic sampling (level=2) 尽管如此，但与 11gR1 版相比，12c 版中的 DDL 优化仍有所改进。在 12c 数据库中，DDL 优化已经扩展到包括拥有 default 值的 null 列。考虑分别在 11gR2 和 12c 中完成以下 alter table，清晰地体会二者间的区别。
11.2.0.3.0> alter table t1 add C_DDL_2 number default 84; Table altered. Elapsed: 00:00:58.25 12c> alter table t1 add C_DDL_2 number default 84; Elapsed: 00:00:00.02
虽然在 11gR2 中添加可为空 C_DDL_2 列用时 58 秒也不错，但在 12c 中是立即完成的。
这清楚地演示了 Oracle Database 12c 中的 DDL 优化已经扩展到包括具有默认值的空列。实际上，查询 t1 表以获取新增列 (C_DDL_2) 的不同值时，您将意识到整个表行的元数据（默认值 84）均已更新，如以下查询所示：
12c> select c_ddl_2, count(1) from t1 group by c_ddl_2;
C_DDL_2 COUNT(1)
------- ----------
84 3000000
SQL> select count(1) from t1 where c_ddl_2=84;
COUNT(1)
----------
3000000
SQL> select * from table(dbms_xplan.display_cursor);
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3803 (100) | |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2| TABLE ACCESS FULL | T1 | 3538K| 43M| 3803 (1) | 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00006$",0)),NULL,NVL("
C_DDL_2",84),'0',NVL("C_DDL_2",84),'1',"C_DDL_2")=84)
Note
-----
- dynamic statistics used: dynamic sampling (level=2) 但为了确保对具默认值的空列进行 DDL 优化，事情比前一版本中针对非空列的 DDL 优化更复杂。我们由简单的隐式使用 NVL 函数开始，到涉及无 Oracle 配套文档的 SYS_OP_VECBIT 函数和新内部列 SYS_NC00006$ 的复杂奇特的谓词部分，为的是沿用默认值，因为此值实际上未更新。
与您的直觉可能不同，SYS_NC00006$ 列并非虚拟列。它表示一个由系统生成的隐藏列，如下所示：
12c> SELECT
column_name
,virtual_column
,hidden_column
,user_generated
FROM
user_tab_cols
WHERE table_name = 'T1'
AND column_name = ‘SYS_NC00006$’;
COLUMN_NAME VIR HID USE
-------------------- --- --- ---
SYS_NC00006$ NO YES NO 尽管此列是隐藏的，但无妨我们选择它。
12c> select
a.c_ddl_2
,a.SYS_NC00006$
from t1 a
where c_ddl_2 =84
and rownum <=5;
C_DDL_2 SYS_NC00006$
------- ------------
84
84
84
84
84 在 C_DDL_2 列被赋予不等于默认值 84 的值之前，SYS_NC00006$ 列将保持为空。考虑以下插入语句：
12c> insert into t1 values (0,0,0,'xxxxx',110,130);
1 row created.
12c> insert into t1 values (1,1,1,'xxxxx',140,150);
1 row created.
12c> insert into t1 values (1,1,1,'xxxxx',200,null);
12c> select
a.c_ddl_2
,a.SYS_NC00006$
from t1 a
where a.c_ddl_2 in (130,150);
C_DDL_2 SYS_NC00006$
------- ------------
130 01
150 01
SQL> select
a.c_ddl_2
,a.SYS_NC00006$
from t1 a
where a.c_ddl_2 is null;
C_DDL_2 SYS_NC00006$
------- ------------
01 注意我们将非默认值插入 C_DDL_2 列（包括显式 NULL 值）时，SYS_NC00006$ 隐藏列值如何不再为 NULL。
将拼图的各块拼在一起，我们就能很容易地明白在以下复制的这种奇特但很简单的谓词部分正是这样做的：
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00006$",0)),NULL,NVL("
C_DDL_2",84),'0',NVL("C_DDL_2",84),'1',"C_DDL_2")=84) Oracle 只是通过其系统生成的列并通过 SYS_OP_VECBIT 函数检查是考虑 C_DDL_2 列的默认值，还是最终用户或通过显式插入语句引入的实际值。我们模拟 Oracle 对以上 SYS_NC00006$ 列值（即“01”和 NULL）的行为。
12c> SELECT
a.c_ddl_2
,TO_CHAR(sys_op_vecbit(a.sys_nc00006$,0)) cbo_ddl
FROM t1 a
WHERE a.c_ddl_2 IN (130,150)
UNION ALL
SELECT
a.c_ddl_2
,TO_CHAR(sys_op_vecbit(a.sys_nc00006$,0)) cbo_ddl
FROM t1 a
WHERE a.c_ddl_2 IS NULL
UNION ALL
SELECT
a.c_ddl_2
,TO_CHAR(sys_op_vecbit(a.sys_nc00006$,0)) cbo_ddl
FROM t1 a
WHERE c_ddl_2 =84
AND rownum <=1
order by c_ddl_2 nulls last
;
C_DDL_2 CBO_DDL
---------- ---------
84 {null}
130 1
150 1
{null} 1 C_DDL_2 列有 4 种不同值：默认值 (84) 和 3 个显式插入值 130、150 和 null。对 C_DDL_2 列使用谓词从表块检索行时，Oracle CBO 将对以上 CBO_DDL 值解码（基于 SYS_NC00006$），以检查其值与输入的绑定（或字面）变量是否相同。因此，它可以正确模拟 C_DDL_2 列的所有值，包括拥有默认值 (84) 的和实际未更新反映此默认值的。
总结
Oracle 11gR1 附带一个奇妙的特性，让我们将具有默认值的非空列联机添加到实际大型生产表时根本无需担心应用的连续性。这个被称作 DDL 优化的特性不仅允许立即执行此 alter table 操作，而且无需锁定表。Oracle 12c 更将此特性扩展为包括具默认值的空列。而且锦上添花的是，检索更改的列时对性能并无显著负面影响，虽然在 12c 中出现一个奇特但实际无害的谓词部分，确保从表块获取更改的列时可获得正确的值。
关于作者
Mohamed Houri 从艾克斯-马赛第二大学获航空工程学位，之后还获得流体力学（科学计算）博士学位。他作为独立 Oracle 顾问为不同欧洲客户从事 Oracle 数据库工作已逾 14 年，擅长调优和排除 Oracle 性能疑难问题。Mohamed 还与日本船舶设计师协会合作使用名为 Wavelet Transform 的强大信号分析方法分析海啸和碎浪。
Printer View