Oracle 数据库 11g新特性：模式管理

Oracle 数据库 11g 包括大量特性，这些特性不仅能够简化作业，在某些情况下，还可以将某些常见的耗时操作缩减为实际上的一行代码。在本文中，您将了解其中一些特性。

DDL Wait 选项

Jill（Acme Retailers 的 DBA）尝试更改名为 SALES 的表，为其添加一列 TAX_CODE。这是很常见的任务；她执行了以下 SQL 语句：

SQL> alter table sales add (tax_code varchar2(10));

但是，她收到了以下消息，而非“Table altered”之类的内容：

alter table sales add (tax_code varchar2(10))
 *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

错误消息描述的是：该表目前可能正由一个事务使用，因此要获得该表的独占锁定不太可能。当然，表的行不会永远锁定。当会话执行提交动作后，会释放对这些行的锁定，但在此之前，由于解除锁定期间很长，其他会话可能会更新表的其他行 — 这样，获得表的独占锁定的时机又消失了。在典型的商务环境中，以独占方式锁定表的窗口会定期打开，但 DBA 可能无法恰好在那时执行 alter 命令。

当然，Jill 也可以反复键入相同的命令，直到获得独占锁定或者失败（两者取其先）。

在 Oracle 数据库 11g 中，Jill 有更好的选择：DDL Wait 选项。她可以执行以下命令：

SQL> alter session set ddl_lock_timeout = 10;
 
Session altered.

现在，如果会话中的 DDL 语句没有获得独占锁定，也不会显示错误消息。相反，它将等待 10 秒钟。在这 10 秒钟内，它将不断重试 DDL 操作，直到成功或超时（两者取其先）。如果执行以下命令：

SQL> alter table sales add (tax_code varchar2(10));

该语句将挂起，并且不会显示错误消息。这样，Jill 就将重复尝试操作外包给了 Oracle 数据库 11g（就像电话通过程序重试繁忙号码），而不必反复尝试以获得难以捉摸的独占锁定可用时机。

现在，Jill 十分喜欢这个特性，并与其他所有 DBA 一起分享这个特性。由于在系统繁忙期间更改表时，每个人都遇到过相同的问题，他们都发现这个新特性非常有帮助。因此，Jill 很想知道是否可以将该行为设为默认行为，这样就不需要每次都执行 ALTER SESSION 语句？

是的，可以。如果您执行 ALTER SYSTEM SET DDL_LOCK_TIMEOUT = 10，会话将在 DDL 操作期间自动等待该时间段。与任何其他 ALTER SYSTEM 语句一样，该语句可被 ALTER SESSION 语句覆盖。

添加具有默认值的列

尽管对该特性感到满意，Jill 还在考虑与第一个问题相关的另一个问题。她希望添加 TAX_CODE 列，但该列不能为空。显然，当她向非空表添加非空列时，还必须指定默认值“XX”。因此，她编写了以下 SQL：

alter table sales add tax_code varchar2(20) default 'XX' not null;

但她在这里停下了。SALES 表十分巨大，大约有 4 亿行。她知道，在执行该语句时，Oracle 会立即添加该列，但在将控制权返回给她之前将更新所有行中的值“XX”。更新 4 亿行不仅要花费很长时间，还要填充还原段、生成大量重做任务并产生极大的性能开销。因此，Jill 必须在“安静时段”（即，停机期间）才能进行此更改。Oracle 数据库 11g 中有更好的方法吗？

当然有。上述语句将不会对表中的所有记录执行更新。尽管对于列值将自动设为“XX”的新记录来说，这不是问题，但当用户选择现有记录的该列时，这将返回 NULL，是吗？

实际上并非如此。当用户选择现有记录的列时，Oracle 将从数据字典获取默认值并将其返回给用户。这样，您就实现了一箭双雕：可以将一个新列定义为非空并具有默认值，同时不会导致任何重做和还原开销。真棒！

虚拟列

Acme 的数据库包含了一个名为 SALES 的表，如前所示。该表的结构如下：

SALES_ID	NUMBER
CUST_ID	NUMBER
SALES_AMT	NUMBER

某些用户希望添加一个名为 SALE_CATEGORY 的列，以便根据销售量和当前客户来标识销售的类型：LOW、MEDIUM、HIGH 和 ULTRA。该列将帮助他们识别相应动作的记录，并将记录路由给相关人员以进行处理。以下是列值的逻辑：

如果 sale_amt 大于：	且 sale_amt 小于或等于：	则 sale_category 为
0	1000	LOW
10001	100000	MEDIUM
100001	1000000	HIGH
1000001	无限	ULTRA

尽管该列是重要的业务需求，但开发团队不希望更改代码以创建必要的逻辑。当然，您可以在表中添加一个名为 sale_category 的新列，然后编写一个触发器以使用上述逻辑填充该列 — 一个相当简单的操作。但是，由于与触发器代码的上下文切换，可能会导致性能问题。

在 Oracle 数据库 11g 中，您不需要编写任何触发器代码。您只需添加一个虚拟列。虚拟列为您提供了灵活性，可以添加传达商业意识的列，而不增加任何复杂性或性能影响。

以下是创建该表的方法：

SQL> create table sales
 2 (
 3 sales_id number,
 4 cust_id number,
 5 sales_amt number,
 6 sale_category varchar2(6)
 7 generated always as
 8 (
 9 case
 10 when sales_amt <= 10000 then 'LOW'
 11 when sales_amt > 10000 and sales_amt <= 100000 then 'MEDIUM'
 12 when sales_amt > 100000 and sales_amt <= 1000000 then 'HIGH'
 13 else 'ULTRA'
 14 end
 15 ) virtual
 16 );

注意 6-7 行；该列被指定为“generated always as”，这意味着，列值在运行时生成，而非作为表的一部分进行存储。该子句的后面是在详细的 CASE 语句中计算值的方法。最后，在第 15 行，指定了“virtual”以加强这是一个虚拟列的事实。现在，如果您插入一些记录：

SQL> insert into sales (sales_id, cust_id, sales_amt) values (1,1,100);
 
1 row created.
 
SQL> insert into sales (sales_id, cust_id, sales_amt) values (2,102,1500);
 
1 row created.
 
SQL>insert into sales (sales_id, cust_id, sales_amt) values (3,102,100000);
 
1 row created.
 
SQL> commit;
 
Commit complete.
 
SQL> select * from sales;
 
 SALES_ID CUST_ID SALES_AMT SALE_C
---------- ---------- ---------- ------
 1 1 100 LOW
 2 102 1500 LOW
 3 102 100000 MEDIUM
 
3 rows selected.

虚拟列值都将照常填充。即使该列未存储，您也可以将其视为表的任何其他列，甚至可以在其上创建索引。

SQL> create index in_sales_cat on sales (sale_category);
 
Index created.

其结果将是一个基于函数的索引。

SQL> select index_type
 2 from user_indexes
 3 where index_name = 'IN_SALES_CAT';
 
INDEX_TYPE
---------------------------
FUNCTION-BASED NORMAL

SQL> select column_expression
 2 from user_ind_expressions
 3 where index_name = 'IN_SALES_CAT';
 
COLUMN_EXPRESSION
--------------------------------------------------------------------------------
CASE WHEN "SALES_AMT"<=10000 THEN 'LOW' WHEN ("SALES_AMT">10000 AND "SALES_AMT"
<=100000) THEN CASE WHEN "CUST_ID"<101 THEN 'LOW' WHEN ("CUST_ID">=101 AND "CUS
T_ID"<=200) THEN 'MEDIUM' ELSE 'MEDIUM' END WHEN ("SALES_AMT">100000 AND "SALES
_AMT"<=1000000) THEN CASE WHEN "CUST_ID"<101 THEN 'MEDIUM' WHEN ("CUST_ID">=101
 AND "CUST_ID"<=200) THEN 'HIGH' ELSE 'ULTRA' END ELSE 'ULTRA' END

您甚至可以在该列上分区，如本系列的中所述。但是，您不能为该列输入值。如果您尝试输入值，很快就会收到错误消息：

insert into sales values (5,100,300,'HIGH','XX')
 *
ERROR at line 1:
ORA-54013: INSERT operation disallowed on virtual columns

不可见的索引

您常常感到疑惑，索引是否真的有利于用户的查询？它可能有利于一个查询，但会影响 10 个其他查询。索引肯定会对 INSERT 语句造成负面影响，也会执行潜在的删除和更新操作，这取决于 WHERE 条件是否在索引中包括该列。

一个相关的问题是，使用索引时，如果该索引被删除，会对查询性能造成什么影响？当然，您可以删除索引并查看对查询的影响，但说起来容易做起来难。索引实际上如何有助于查询？您必须重新定义索引，为此，需要进行重新创建。完全重新创建之后，就没有人能使用它了。重新创建索引也是一个昂贵的过程；它会占用许多有用的数据库资源。

您有办法创建一种对特定查询不可用同时又不会影响其他查询的索引吗？在 Oracle 数据库 11g 之前，不推荐使用 ALTER INDEX ...UNUSABLE，因为它会使表上的所有 DML 失败。但现在，您可以通过不可见的索引 精确使用该选项。简言之，您可以使索引对优化器“不可见”，这样就没有查询会使用它了。如果查询希望使用索引，则必须将其显式指定为提示。

下面是一个例子。假设有一个名为 RES 的表，并且您创建了如下所示的索引：

SQL> create index in_res_guest on res (guest_id);

分析完该表和索引后，如果您执行

SQL> select * from res where guest_id = 101;

将发现该索引正在使用：

Execution Plan
----------------------------------------------------------
Plan hash value: 1519600902
 
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| RES | 1 | 28 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IN_RES_GUEST | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
 2 - access("GUEST_ID"=101)

现在，使索引不可见：

SQL> alter index in_res_guest invisible;
 
Index altered.

现在，将显示以下内容：

SQL> select * from res where guest_id = 101
 2 /
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3824022422
 
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 140 (2)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| RES | 1 | 28 | 140 (2)| 00:00:02 |
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
 1 - filter("GUEST_ID"=101)

未使用索引。要使优化器再次使用索引，您必须在提示中显式命名索引：

SQL> select /*+ INDEX (res IN_RES_GUEST) */ res_id from res where guest_id = 101;

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 28 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| RES | 1 | 28 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IN_RES_GUEST | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

真快！优化器再次使用了索引。

或者，您可以设置会话级参数以使用不可见的索引：

SQL> alter session set optimizer_use_invisible_indexes = true;

如果您无法修改代码（如第三方应用程序中的代码），该特性将十分有用。您创建索引时，可以在末尾追加子句 INVISIBLE，将索引构建为对优化器不可见。您还可以使用字典视图 USER_INDEXES 查看索引的当前设置。

SQL> select visibility
 2 from user_indexes
 3 where index_name = 'IN_RES_GUEST';

VISIBILITY
---------
INVISIBLE

注意，如果您重新构建该索引，则该索引将变为可见。您必须再次将其显式设为不可见。

那么，该索引到底对“什么”不可见？当然，它不会对用户不可见。它只是对优化器不可见。常规数据库操作（如插入、更新和删除）将继续更新索引。创建不可见索引时应注意这一点；由于该索引，您将不会再次查看性能，但同时您在 DML 操作期间可能会付出一些代价。

只读表

Robin 是 Acme 数据仓库系统的开发人员，他正在考虑一个典型问题。作为 ETL 流程的一部分，几个表的更新周期有所不同。在更新时，表会按业务规则对用户开放，即使用户不应修改它们。但是，取消用户对这些表的 DML 权限不是一个可选方法。

Robin 需要一个能够充当开关角色的功能，可以允许或不允许更新表。要实现这个听起来简单的操作，实际上相当困难。Robin 有哪些选择呢？

一个选择是，在表上创建一个触发器，以针对 INSERT、DELETE 和 UPDATE 引发异常。执行触发器会涉及上下文切换，这会影响性能。另一个选择是，创建一个虚拟专用数据库 (VPD) 策略，始终返回 false 字符串（如“1=2”）。如果表上的 VPD 策略使用该函数，它就会返回 FALSE，并且 DML 会失败。这可能比使用触发器具有更好的性能，但用户肯定不愿意使用，因为会看到“policy function returned error”之类的错误消息。

然而，在 Oracle 数据库 11g 中，您可以通过一个更好的方法来实现这个目标。您只需将表设为只读，如下所示：

SQL> alter table TRANS read only;
 
Table altered.

现在，当用户尝试执行如下所示的 DML 时：

SQL> delete trans;

Oracle 数据库 11g 就会立即抛出错误：

delete trans
 *
ERROR at line 1:
ORA-12081: update operation not allowed on table "SCOTT"."TRANS"

错误消息不会将操作反映到代码中，但会有目的地传递消息，而无需触发器或 VPD 策略的开销。

如果您希望表可更新，则需要将其设为读/写，如下所示：

SQL> alter table trans read write;
 
Table altered.

现在，DML 就没有问题了：

SQL> update trans set amt = 1 where trans_id = 1;
 
1 row updated.

当表仅处于只读模式时，则不允许执行 DML；但您可以执行所有 DDL 操作（创建索引、维护分区等）。因此，这个特性的一个非常有用的应用就是表维护。您可以将表设为只读，执行必要的 DDL，然后再将其设为读/写。

要查看表的状态，请在数据字典视图 dba_tables 中查找 read_only 列。

SQL> select read_only from user_tables where table_name = 'TRANS';
 
REA
---
NO

结论

如您所见，这些特性不仅大大简化了以前费力的命令，在某些情况下，还可以根据日常操作的执行开发全新的方式。

在我的职业生涯中，经历了 Oracle 数据库功能的许多更改，其中有一些标志性特性重新定义了业务的完成过程。这里描述的特性就属于这一类特性。