模式管理
通過顯著加快和簡(jiǎn)化許多常見操作的新功能，來更高效地管理數(shù)據(jù)庫(kù)對(duì)象。
下載 Oracle 數(shù)據(jù)庫(kù) 11gOracle 數(shù)據(jù)庫(kù) 11g 包括大量特性，這些特性不僅能夠簡(jiǎn)化作業(yè)，在某些情況下，還可以將某些常見的耗時(shí)操作縮減為實(shí)際上的一行代碼。在本文中，您將了解其中一些特性。
DDL Wait 選項(xiàng)
Jill（Acme Retailers 的 DBA）嘗試更改名為 SALES 的表，為其添加一列 TAX_CODE。這是很常見的任務(wù)；她執(zhí)行了以下 SQL 語(yǔ)句：
SQL> alter table sales add (tax_code varchar2(10));
但是，她收到了以下消息，而非“Table altered”之類的內(nèi)容：
alter table sales add (tax_code varchar2(10))
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
錯(cuò)誤消息描述的是：該表目前可能正由一個(gè)事務(wù)使用，因此要獲得該表的獨(dú)占鎖定不太可能。當(dāng)然，表的行不會(huì)永遠(yuǎn)鎖定。當(dāng)會(huì)話執(zhí)行提交動(dòng)作后，會(huì)釋放對(duì)這些行的鎖定，但在此之前，由于解除鎖定期間很長(zhǎng)，其他會(huì)話可能會(huì)更新表的其他行 — 這樣，獲得表的獨(dú)占鎖定的時(shí)機(jī)又消失了。在典型的商務(wù)環(huán)境中，以獨(dú)占方式鎖定表的窗口會(huì)定期打開，但 DBA 可能無法恰好在那時(shí)執(zhí)行 alter 命令。
當(dāng)然，Jill 也可以反復(fù)鍵入相同的命令，直到獲得獨(dú)占鎖定或者失敗（兩者取其先）。
在 Oracle 數(shù)據(jù)庫(kù) 11g 中，Jill 有更好的選擇：DDL Wait 選項(xiàng)。她可以執(zhí)行以下命令：
SQL> alter session set ddl_lock_timeout = 10;
Session altered.
現(xiàn)在，如果會(huì)話中的 DDL 語(yǔ)句沒有獲得獨(dú)占鎖定，也不會(huì)顯示錯(cuò)誤消息。相反，它將等待 10 秒鐘。在這 10 秒鐘內(nèi)，它將不斷重試 DDL 操作，直到成功或超時(shí)（兩者取其先）。如果執(zhí)行以下命令：SQL> alter table sales add (tax_code varchar2(10));
該語(yǔ)句將掛起，并且不會(huì)顯示錯(cuò)誤消息。這樣，Jill 就將重復(fù)嘗試操作外包給了 Oracle 數(shù)據(jù)庫(kù) 11g（就像電話通過程序重試繁忙號(hào)碼），而不必反復(fù)嘗試以獲得難以捉摸的獨(dú)占鎖定可用時(shí)機(jī)。
現(xiàn)在，Jill 十分喜歡這個(gè)特性，并與其他所有 DBA 一起分享這個(gè)特性。由于在系統(tǒng)繁忙期間更改表時(shí)，每個(gè)人都遇到過相同的問題，他們都發(fā)現(xiàn)這個(gè)新特性非常有幫助。因此，Jill 很想知道是否可以將該行為設(shè)為默認(rèn)行為，這樣就不需要每次都執(zhí)行 ALTER SESSION 語(yǔ)句？
是的，可以。如果您執(zhí)行 ALTER SYSTEM SET DDL_LOCK_TIMEOUT = 10，會(huì)話將在 DDL 操作期間自動(dòng)等待該時(shí)間段。與任何其他 ALTER SYSTEM 語(yǔ)句一樣，該語(yǔ)句可被 ALTER SESSION 語(yǔ)句覆蓋。
添加具有默認(rèn)值的列
盡管對(duì)該特性感到滿意，Jill 還在考慮與第一個(gè)問題相關(guān)的另一個(gè)問題。她希望添加 TAX_CODE 列，但該列不能為空。顯然，當(dāng)她向非空表添加非空列時(shí)，還必須指定默認(rèn)值“XX”。因此，她編寫了以下 SQL：alter table sales add tax_code varchar2(20) default 'XX' not null;
但她在這里停下了。SALES 表十分巨大，大約有 4 億行。她知道，在執(zhí)行該語(yǔ)句時(shí)，Oracle 會(huì)立即添加該列，但在將控制權(quán)返回給她之前將更新所有行中的值“XX”。更新 4 億行不僅要花費(fèi)很長(zhǎng)時(shí)間，還要填充還原段、生成大量重做任務(wù)并產(chǎn)生極大的性能開銷。因此，Jill 必須在“安靜時(shí)段”（即，停機(jī)期間）才能進(jìn)行此更改。Oracle 數(shù)據(jù)庫(kù) 11g 中有更好的方法嗎？
當(dāng)然有。上述語(yǔ)句將不會(huì)對(duì)表中的所有記錄執(zhí)行更新。盡管對(duì)于列值將自動(dòng)設(shè)為“XX”的新記錄來說，這不是問題，但當(dāng)用戶選擇現(xiàn)有記錄的該列時(shí)，這將返回 NULL，是嗎？
實(shí)際上并非如此。當(dāng)用戶選擇現(xiàn)有記錄的列時(shí)，Oracle 將從數(shù)據(jù)字典獲取默認(rèn)值并將其返回給用戶。這樣，您就實(shí)現(xiàn)了一箭雙雕：可以將一個(gè)新列定義為非空并具有默認(rèn)值，同時(shí)不會(huì)導(dǎo)致任何重做和還原開銷。真棒！
虛擬列
Acme 的數(shù)據(jù)庫(kù)包含了一個(gè)名為 SALES 的表，如前所示。該表的結(jié)構(gòu)如下：
SALES_ID NUMBER
CUST_ID NUMBER
SALES_AMT NUMBER
某些用戶希望添加一個(gè)名為 SALE_CATEGORY 的列，以便根據(jù)銷售量和當(dāng)前客戶來標(biāo)識(shí)銷售的類型：LOW、MEDIUM、HIGH 和 ULTRA。該列將幫助他們識(shí)別相應(yīng)動(dòng)作的記錄，并將記錄路由給相關(guān)人員以進(jìn)行處理。以下是列值的邏輯：
如果 sale_amt 大于： 且 sale_amt 小于或等于： 則 sale_category 為
0 1000 LOW
10001 100000 MEDIUM
100001 1000000 HIGH
1000001 無限 ULTRA
盡管該列是重要的業(yè)務(wù)需求，但開發(fā)團(tuán)隊(duì)不希望更改代碼以創(chuàng)建必要的邏輯。當(dāng)然，您可以在表中添加一個(gè)名為 sale_category 的新列，然后編寫一個(gè)觸發(fā)器以使用上述邏輯填充該列 — 一個(gè)相當(dāng)簡(jiǎn)單的操作。但是，由于與觸發(fā)器代碼的上下文切換，可能會(huì)導(dǎo)致性能問題。
在 Oracle 數(shù)據(jù)庫(kù) 11g 中，您不需要編寫任何觸發(fā)器代碼。您只需添加一個(gè)虛擬列。虛擬列為您提供了靈活性，可以添加傳達(dá)商業(yè)意識(shí)的列，而不增加任何復(fù)雜性或性能影響。
以下是創(chuàng)建該表的方法：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”，這意味著，列值在運(yùn)行時(shí)生成，而非作為表的一部分進(jìn)行存儲(chǔ)。該子句的后面是在詳細(xì)的 CASE 語(yǔ)句中計(jì)算值的方法。最后，在第 15 行，指定了“virtual”以加強(qiáng)這是一個(gè)虛擬列的事實(shí)?，F(xiàn)在，如果您插入一些記錄：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.
虛擬列值都將照常填充。即使該列未存儲(chǔ)，您也可以將其視為表的任何其他列，甚至可以在其上創(chuàng)建索引。
SQL> create index in_sales_cat on sales (sale_category);
Index created.
其結(jié)果將是一個(gè)基于函數(shù)的索引。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
您甚至可以在該列上分區(qū)，如本系列的
分區(qū)一文中所述。但是，您不能為該列輸入值。如果您嘗試輸入值，很快就會(huì)收到錯(cuò)誤消息：insert into sales values (5,100,300,'HIGH','XX')
*
ERROR at line 1:
ORA-54013: INSERT operation disallowed on virtual columns
不可見的索引
您常常感到疑惑，索引是否真的有利于用戶的查詢？它可能有利于一個(gè)查詢，但會(huì)影響 10 個(gè)其他查詢。索引肯定會(huì)對(duì) INSERT 語(yǔ)句造成負(fù)面影響，也會(huì)執(zhí)行潛在的刪除和更新操作，這取決于 WHERE 條件是否在索引中包括該列。
一個(gè)相關(guān)的問題是，使用索引時(shí)，如果該索引被刪除，會(huì)對(duì)查詢性能造成什么影響？當(dāng)然，您可以刪除索引并查看對(duì)查詢的影響，但說起來容易做起來難。索引實(shí)際上如何有助于查詢？您必須重新定義索引，為此，需要進(jìn)行重新創(chuàng)建。完全重新創(chuàng)建之后，就沒有人能使用它了。重新創(chuàng)建索引也是一個(gè)昂貴的過程；它會(huì)占用許多有用的數(shù)據(jù)庫(kù)資源。
您有辦法創(chuàng)建一種對(duì)特定查詢不可用同時(shí)又不會(huì)影響其他查詢的索引嗎？在 Oracle 數(shù)據(jù)庫(kù) 11g 之前，不推薦使用 ALTER INDEX ...UNUSABLE，因?yàn)樗鼤?huì)使表上的所有 DML 失敗。但現(xiàn)在，您可以通過不可見的索引 精確使用該選項(xiàng)。簡(jiǎn)言之，您可以使索引對(duì)優(yōu)化器“不可見”，這樣就沒有查詢會(huì)使用它了。如果查詢希望使用索引，則必須將其顯式指定為提示。
下面是一個(gè)例子。假設(shè)有一個(gè)名為 RES 的表，并且您創(chuàng)建了如下所示的索引：
SQL> create index in_res_guest on res (guest_id);
分析完該表和索引后，如果您執(zhí)行SQL> select * from res where guest_id = 101;
將發(fā)現(xiàn)該索引正在使用：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)
現(xiàn)在，使索引不可見：SQL> alter index in_res_guest invisible;
Index altered.
現(xiàn)在，將顯示以下內(nèi)容：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)
未使用索引。要使優(yōu)化器再次使用索引，您必須在提示中顯式命名索引：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 |
--------------------------------------------------------------------------------------------
真快！優(yōu)化器再次使用了索引。
或者，您可以設(shè)置會(huì)話級(jí)參數(shù)以使用不可見的索引：
SQL> alter session set optimizer_use_invisible_indexes = true;
如果您無法修改代碼（如第三方應(yīng)用程序中的代碼），該特性將十分有用。您創(chuàng)建索引時(shí)，可以在末尾追加子句 INVISIBLE，將索引構(gòu)建為對(duì)優(yōu)化器不可見。您還可以使用字典視圖 USER_INDEXES 查看索引的當(dāng)前設(shè)置。SQL> select visibility
2 from user_indexes
3 where index_name = 'IN_RES_GUEST';
VISIBILITY
---------
INVISIBLE
注意，如果您重新構(gòu)建該索引，則該索引將變?yōu)榭梢?。您必須再次將其顯式設(shè)為不可見。
那么，該索引到底對(duì)“什么”不可見？當(dāng)然，它不會(huì)對(duì)用戶不可見。它只是對(duì)優(yōu)化器不可見。常規(guī)數(shù)據(jù)庫(kù)操作（如插入、更新和刪除）將繼續(xù)更新索引。創(chuàng)建不可見索引時(shí)應(yīng)注意這一點(diǎn)；由于該索引，您將不會(huì)再次查看性能，但同時(shí)您在 DML 操作期間可能會(huì)付出一些代價(jià)。
只讀表
Robin 是 Acme 數(shù)據(jù)倉(cāng)庫(kù)系統(tǒng)的開發(fā)人員，他正在考慮一個(gè)典型問題。作為 ETL 流程的一部分，幾個(gè)表的更新周期有所不同。在更新時(shí)，表會(huì)按業(yè)務(wù)規(guī)則對(duì)用戶開放，即使用戶不應(yīng)修改它們。但是，取消用戶對(duì)這些表的 DML 權(quán)限不是一個(gè)可選方法。
Robin 需要一個(gè)能夠充當(dāng)開關(guān)角色的功能，可以允許或不允許更新表。要實(shí)現(xiàn)這個(gè)聽起來簡(jiǎn)單的操作，實(shí)際上相當(dāng)困難。Robin 有哪些選擇呢？
一個(gè)選擇是，在表上創(chuàng)建一個(gè)觸發(fā)器，以針對(duì) INSERT、DELETE 和 UPDATE 引發(fā)異常。執(zhí)行觸發(fā)器會(huì)涉及上下文切換，這會(huì)影響性能。另一個(gè)選擇是，創(chuàng)建一個(gè)虛擬專用數(shù)據(jù)庫(kù) (VPD) 策略，始終返回 false 字符串（如“1=2”）。如果表上的 VPD 策略使用該函數(shù)，它就會(huì)返回 FALSE，并且 DML 會(huì)失敗。這可能比使用觸發(fā)器具有更好的性能，但用戶肯定不愿意使用，因?yàn)闀?huì)看到“policy function returned error”之類的錯(cuò)誤消息。
然而，在 Oracle 數(shù)據(jù)庫(kù) 11g 中，您可以通過一個(gè)更好的方法來實(shí)現(xiàn)這個(gè)目標(biāo)。您只需將表設(shè)為只讀，如下所示：
SQL> alter table TRANS read only;
Table altered.
現(xiàn)在，當(dāng)用戶嘗試執(zhí)行如下所示的 DML 時(shí)：SQL> delete trans;
Oracle 數(shù)據(jù)庫(kù) 11g 就會(huì)立即拋出錯(cuò)誤：delete trans
*
ERROR at line 1:
ORA-12081: update operation not allowed on table "SCOTT"."TRANS"
錯(cuò)誤消息不會(huì)將操作反映到代碼中，但會(huì)有目的地傳遞消息，而無需觸發(fā)器或 VPD 策略的開銷。
如果您希望表可更新，則需要將其設(shè)為讀/寫，如下所示：
SQL> alter table trans read write;
Table altered.
現(xiàn)在，DML 就沒有問題了：SQL> update trans set amt = 1 where trans_id = 1;
1 row updated.
當(dāng)表僅處于只讀模式時(shí)，則不允許執(zhí)行 DML；但您可以執(zhí)行所有 DDL 操作（創(chuàng)建索引、維護(hù)分區(qū)等）。因此，這個(gè)特性的一個(gè)非常有用的應(yīng)用就是表維護(hù)。您可以將表設(shè)為只讀，執(zhí)行必要的 DDL，然后再將其設(shè)為讀/寫。
要查看表的狀態(tài)，請(qǐng)?jiān)跀?shù)據(jù)字典視圖 dba_tables 中查找 read_only 列。
SQL> select read_only from user_tables where table_name = 'TRANS';
REA
---
NO
結(jié)論
如您所見，這些特性不僅大大簡(jiǎn)化了以前費(fèi)力的命令，在某些情況下，還可以根據(jù)日常操作的執(zhí)行開發(fā)全新的方式。
在我的職業(yè)生涯中，經(jīng)歷了 Oracle 數(shù)據(jù)庫(kù)功能的許多更改，其中有一些標(biāo)志性特性重新定義了業(yè)務(wù)的完成過程。這里描述的特性就屬于這一類特性。
返回“Oracle 數(shù)據(jù)庫(kù) 11g：面向 DBA 和開發(fā)人員的重要特性”主頁(yè)Arup Nanda (
arup@proligence.com) 是 Starwood Hotels and Resorts 的數(shù)據(jù)庫(kù)系統(tǒng)經(jīng)理，從事 Oracle 的 DBA 職業(yè)十多年，并且在 2003 年由《Oracle Magazine》 評(píng)選為“年度 DBA”。Arup 經(jīng)常在 Oracle 相關(guān)活動(dòng)中發(fā)表演講，并在 Oracle 相關(guān)雜志上撰寫文章，他是紐約 Oracle 用戶群執(zhí)行委員會(huì)的成員，并且是一位
Oracle ACE。他與其他人合作編寫了
《Oracle Privacy Security Auditing》(Rampant TechPress) 一書。
