模式管理

通過(guò)顯著加快和簡(jiǎn)化許多常見(jiàn)操作的新功能，來(lái)更高效地管理數(shù)據(jù)庫(kù)對(duì)象。

下載 Oracle 數(shù)據(jù)庫(kù) 11g

Oracle 數(shù)據(jù)庫(kù) 11g 包括大量特性，這些特性不僅能夠簡(jiǎn)化作業(yè)，在某些情況下，還可以將某些常見(jiàn)的耗時(shí)操作縮減為實(shí)際上的一行代碼。在本文中，您將了解其中一些特性。

DDL Wait 選項(xiàng)

Jill（Acme Retailers 的 DBA）嘗試更改名為 SALES 的表，為其添加一列 TAX_CODE。這是很常見(jiàn)的任務(wù)；她執(zhí)行了以下 SQL 語(yǔ)句：

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

但是，她收到了以下消息，而非“Table altered”之類(lèi)的內(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ì)定期打開(kāi)，但 DBA 可能無(wú)法恰好在那時(shí)執(zhí)行 alter 命令。

 

當(dāng)然，Jill 也可以反復(fù)鍵入相同的命令，直到獲得獨(dú)占鎖定或者失?。▋烧呷∑湎龋?/p>

在 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ǔ)句沒(méi)有獲得獨(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（就像電話通過(guò)程序重試繁忙號(hào)碼），而不必反復(fù)嘗試以獲得難以捉摸的獨(dú)占鎖定可用時(shí)機(jī)。

現(xiàn)在，Jill 十分喜歡這個(gè)特性，并與其他所有 DBA 一起分享這個(gè)特性。由于在系統(tǒng)繁忙期間更改表時(shí)，每個(gè)人都遇到過(guò)相同的問(wèn)題，他們都發(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è)問(wèn)題相關(guān)的另一個(gè)問(wèn)題。她希望添加 TAX_CODE 列，但該列不能為空。顯然，當(dāng)她向非空表添加非空列時(shí)，還必須指定默認(rèn)值“XX”。因此，她編寫(xiě)了以下 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)生極大的性能開(kāi)銷(xiāo)。因此，Jill 必須在“安靜時(shí)段”（即，停機(jī)期間）才能進(jìn)行此更改。Oracle 數(shù)據(jù)庫(kù) 11g 中有更好的方法嗎？

當(dāng)然有。上述語(yǔ)句將不會(huì)對(duì)表中的所有記錄執(zhí)行更新。盡管對(duì)于列值將自動(dòng)設(shè)為“XX”的新記錄來(lái)說(shuō)，這不是問(wèn)題，但當(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)致任何重做和還原開(kāi)銷(xiāo)。真棒！

虛擬列

Acme 的數(shù)據(jù)庫(kù)包含了一個(gè)名為 SALES 的表，如前所示。該表的結(jié)構(gòu)如下：

SALES_ID	NUMBER
CUST_ID	NUMBER
SALES_AMT	NUMBER

某些用戶希望添加一個(gè)名為 SALE_CATEGORY 的列，以便根據(jù)銷(xiāo)售量和當(dāng)前客戶來(lái)標(biāo)識(shí)銷(xiāo)售的類(lèi)型：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	無(wú)限	ULTRA

盡管該列是重要的業(yè)務(wù)需求，但開(kāi)發(fā)團(tuán)隊(duì)不希望更改代碼以創(chuàng)建必要的邏輯。當(dāng)然，您可以在表中添加一個(gè)名為 sale_category 的新列，然后編寫(xiě)一個(gè)觸發(fā)器以使用上述邏輯填充該列 — 一個(gè)相當(dāng)簡(jiǎn)單的操作。但是，由于與觸發(fā)器代碼的上下文切換，可能會(huì)導(dǎo)致性能問(wèn)題。

在 Oracle 數(shù)據(jù)庫(kù) 11g 中，您不需要編寫(xiě)任何觸發(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

不可見(jiàn)的索引

您常常感到疑惑，索引是否真的有利于用戶的查詢(xún)？它可能有利于一個(gè)查詢(xún)，但會(huì)影響 10 個(gè)其他查詢(xún)。索引肯定會(huì)對(duì) INSERT 語(yǔ)句造成負(fù)面影響，也會(huì)執(zhí)行潛在的刪除和更新操作，這取決于 WHERE 條件是否在索引中包括該列。

一個(gè)相關(guān)的問(wèn)題是，使用索引時(shí)，如果該索引被刪除，會(huì)對(duì)查詢(xún)性能造成什么影響？當(dāng)然，您可以刪除索引并查看對(duì)查詢(xún)的影響，但說(shuō)起來(lái)容易做起來(lái)難。索引實(shí)際上如何有助于查詢(xún)？您必須重新定義索引，為此，需要進(jìn)行重新創(chuàng)建。完全重新創(chuàng)建之后，就沒(méi)有人能使用它了。重新創(chuàng)建索引也是一個(gè)昂貴的過(guò)程；它會(huì)占用許多有用的數(shù)據(jù)庫(kù)資源。

您有辦法創(chuàng)建一種對(duì)特定查詢(xún)不可用同時(shí)又不會(huì)影響其他查詢(xún)的索引嗎？在 Oracle 數(shù)據(jù)庫(kù) 11g 之前，不推薦使用 ALTER INDEX ...UNUSABLE，因?yàn)樗鼤?huì)使表上的所有 DML 失敗。但現(xiàn)在，您可以通過(guò)不可見(jiàn)的索引 精確使用該選項(xiàng)。簡(jiǎn)言之，您可以使索引對(duì)優(yōu)化器“不可見(jiàn)”，這樣就沒(méi)有查詢(xún)會(huì)使用它了。如果查詢(xún)希望使用索引，則必須將其顯式指定為提示。

下面是一個(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)在，使索引不可見(jià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ù)以使用不可見(jiàn)的索引：

SQL> alter session set optimizer_use_invisible_indexes = true;

如果您無(wú)法修改代碼（如第三方應(yīng)用程序中的代碼），該特性將十分有用。您創(chuàng)建索引時(shí)，可以在末尾追加子句 INVISIBLE，將索引構(gòu)建為對(duì)優(yōu)化器不可見(jiàn)。您還可以使用字典視圖 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)榭梢?jiàn)。您必須再次將其顯式設(shè)為不可見(jiàn)。

那么，該索引到底對(duì)“什么”不可見(jiàn)？當(dāng)然，它不會(huì)對(duì)用戶不可見(jiàn)。它只是對(duì)優(yōu)化器不可見(jiàn)。常規(guī)數(shù)據(jù)庫(kù)操作（如插入、更新和刪除）將繼續(xù)更新索引。創(chuàng)建不可見(jiàn)索引時(shí)應(yīng)注意這一點(diǎn)；由于該索引，您將不會(huì)再次查看性能，但同時(shí)您在 DML 操作期間可能會(huì)付出一些代價(jià)。

只讀表

Robin 是 Acme 數(shù)據(jù)倉(cāng)庫(kù)系統(tǒng)的開(kāi)發(fā)人員，他正在考慮一個(gè)典型問(wèn)題。作為 ETL 流程的一部分，幾個(gè)表的更新周期有所不同。在更新時(shí)，表會(huì)按業(yè)務(wù)規(guī)則對(duì)用戶開(kāi)放，即使用戶不應(yīng)修改它們。但是，取消用戶對(duì)這些表的 DML 權(quán)限不是一個(gè)可選方法。

Robin 需要一個(gè)能夠充當(dāng)開(kāi)關(guān)角色的功能，可以允許或不允許更新表。要實(shí)現(xiàn)這個(gè)聽(tīng)起來(lái)簡(jiǎn)單的操作，實(shí)際上相當(dāng)困難。Robin 有哪些選擇呢？

一個(gè)選擇是，在表上創(chuàng)建一個(gè)觸發(fā)器，以針對(duì) INSERT、DELETE 和 UPDATE 引發(fā)異常。執(zhí)行觸發(fā)器會(huì)涉及上下文切換，這會(huì)影響性能。另一個(gè)選擇是，創(chuàng)建一個(gè)虛擬專(zhuān)用數(shù)據(jù)庫(kù) (VPD) 策略，始終返回 false 字符串（如“1=2”）。如果表上的 VPD 策略使用該函數(shù)，它就會(huì)返回 FALSE，并且 DML 會(huì)失敗。這可能比使用觸發(fā)器具有更好的性能，但用戶肯定不愿意使用，因?yàn)闀?huì)看到“policy function returned error”之類(lèi)的錯(cuò)誤消息。

然而，在 Oracle 數(shù)據(jù)庫(kù) 11g 中，您可以通過(guò)一個(gè)更好的方法來(lái)實(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ì)有目的地傳遞消息，而無(wú)需觸發(fā)器或 VPD 策略的開(kāi)銷(xiāo)。

如果您希望表可更新，則需要將其設(shè)為讀/寫(xiě)，如下所示：

SQL> alter table trans read write;
 
Table altered.

現(xiàn)在，DML 就沒(méi)有問(wèn)題了：

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è)為讀/寫(xiě)。

 

要查看表的狀態(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)，這些特性不僅大大簡(jiǎn)化了以前費(fèi)力的命令，在某些情況下，還可以根據(jù)日常操作的執(zhí)行開(kāi)發(fā)全新的方式。

在我的職業(yè)生涯中，經(jīng)歷了 Oracle 數(shù)據(jù)庫(kù)功能的許多更改，其中有一些標(biāo)志性特性重新定義了業(yè)務(wù)的完成過(guò)程。這里描述的特性就屬于這一類(lèi)特性。

返回“Oracle 數(shù)據(jù)庫(kù) 11g：面向 DBA 和開(kāi)發(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)雜志上撰寫(xiě)文章，他是紐約 Oracle 用戶群執(zhí)行委員會(huì)的成員，并且是一位 Oracle ACE。他與其他人合作編寫(xiě)了《Oracle Privacy Security Auditing》(Rampant TechPress) 一書(shū)。