Oracle 11g新特性之模式管理(1)
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 |
