***************************************/

2.2 注釋位置要求

1) 注釋行的長度以在最大化窗口內(nèi)可以看到全部內(nèi)容為宜，如果一行不夠顯示需要換行，下一行注釋語句與上一行注釋語句應對齊。

2) 變量申明的注釋放在變量申明語句的后面，并以– 為注釋語句

3) 注釋行一律放在被注釋語句的上一行。

4) 注釋行中，注釋命令與注釋內(nèi)容空一個空格，如：

V_BpMode VARCHAR2(10) ; — 用戶服務類型

BEGIN

— 選取用戶服務類型

BEGIN

SELECT BpMode INTO V_BpMode FROM ACC_BP WHERE User_ID = I_User_ID ;

EXCEPTION

WHEN NO_DATA_FOUND THEN

WHEN OTHERS THEN

END ;

2.3 代碼片斷注釋要求

1) 當處理流程比較復雜，不容易讓其它人看懂時，應該加以注釋。

2) 注釋行放在 被注釋的代碼片斷上一行，并與代碼片斷第一行對齊。

3 編碼命名規(guī)范

3.1 命名語言

命名應該使用英文單詞，避免使用拼音，特別不應該使用拼音簡寫。命名不允許使用中文或者特殊字符。

英文單詞使用對象本身意義相對或相近的單詞。選擇最簡單或最通用的單詞。不能使用毫不相干的單詞來命名。

當一個單詞不能表達對象含義時，用詞組組合，如果組合太長時，采用簡寫或縮寫，縮寫要基本能表達原單詞的意義。

當出現(xiàn)對象名重名時，是不同類型對象時，在對象名前后加類型前綴或后綴以示區(qū)別。

3.2 大小寫

名稱一律大寫，以方便不同數(shù)據(jù)庫移植，以及避免程序調(diào)用問題。

3.3 單詞分隔

命名的各單詞之間可以使用下劃線“_”進行分隔。

3.4 保留字

命名不允許使用SQL保留字。

3.5 命名長度

表名、字段名、視圖名長度應限制在20個字符內(nèi)(含前綴)。

3.6 字段名稱

同一個字段名在一個數(shù)據(jù)庫中只能代表一個意思。比如telephone在一個表中代表“電話號碼”的意思，在另外一個表中就不能代表“手機號碼”的意思。

不同的表用于相同內(nèi)容的字段應該采用同樣的名稱，字段類型定義。

4 編碼數(shù)據(jù)類型規(guī)范

4.1 字符型

固定長度的字串類型采用char，長度不固定的字串類型采用varchar。避免在長度不固定的情況下采用char類型。如果在數(shù)據(jù)遷移等出現(xiàn)以上情況，則必須使用trim()函數(shù)截去字串后的空格。

4.2 數(shù)字型

數(shù)字型字段盡量采用number類型，要注意精度。

4.3 日期和時間

4.3.1 系統(tǒng)時間

由數(shù)據(jù)庫產(chǎn)生的系統(tǒng)時間首選數(shù)據(jù)庫的日期型，如DATE類型。

4.3.2 外部時間

由數(shù)據(jù)導入或外部應用程序產(chǎn)生的日期時間類型采用varchar類型，數(shù)據(jù)格式采用：YYYYMMDDHH24MISS。

4.4 大字段

如無特別需要，避免使用大字段(blob，clob，long，text，image等)。

4.5 唯一鍵

對于數(shù)字型唯一鍵值，盡可能用系列sequence產(chǎn)生。

5 SQL編碼技巧

性能通常是指軟件的“時間—空間”效率，而不僅是指軟件的運行速度。人們總希望軟件的運行速度快些，并且占用資源少些。

5.1 SELECT子句中避免使用Select * 語句

如果不是必要取出所有數(shù)據(jù)，不要用*來代替，應給出字段列表，使用動態(tài)SQL列引用 '*' 是一個方便的方法。但是，這是一個非常低效的方法。ORACLE在解析的過程中，會將'*' 通過查詢數(shù)據(jù)庫系統(tǒng)的數(shù)據(jù)字典依次轉(zhuǎn)換成所有對應的列名，這意味著將耗費更多的時間和系統(tǒng)資源。

注意別名的使用：

SELECT A.ORDER_ID,A.ORDER_CONTENT FROM CRM_ORDER A

/*別名使用會加速查詢速度*/

并行查詢 ，效率可以提高

第一種形式

SELECT /* FULL(T) PARALLEL(T,2) */ * FROM CRM_ORDER T

/*多cpu 使用如上語句可以加速查詢速度適合unix 服務器這樣的配置*/

第二種形式

SELECT /* PARALLEL(T,2) */ * FROM CRM_ORDER T

5.2 避免嵌套的Select子句

這個實際上是In子句的特例。

5.3 使用SELECT COUNT(主鍵)計算表的記錄數(shù)

和一般的觀點相反，count(*) 比count(1)稍快，當然如果可以通過索引檢索，對索引列的計數(shù)仍舊是最快的。例如 COUNT(EMPNO)。如果沒有主鍵SELECT COUNT(1) FROM stuinfo; 要快一些。

例如：

select count(*) from testtab

/*得到表testtab的記錄數(shù)*/

select count(id) from testtab

/*得到表testtab id字段非空記錄數(shù)*/

select count(distinct id) from testtab

/*得到表testtab id字段值非相同記錄數(shù)*/

5.4 WHERE子句

1) ORACLE采用自下而上的順序解析WHERE子句，根據(jù)這個原理，表之間的連接必須寫在其他WHERE條件之前，那些可以過濾掉最大數(shù)量記錄的條件必須寫在WHERE子句的末尾。

2) 除非你不考慮執(zhí)行效率問題，否則請不要在WHERE 子句中使用函數(shù)作為條件。例如 WHERE TO_NUMBER(TEL_NBR)=7654321 這種寫法是效率非常低下的。

3) 避免使用HAVING子句，HAVING 只會在檢索出所有記錄之后才對結(jié)果集進行過濾。 這個處理需要排序，總計等操作。如果能通過WHERE子句限制記錄的數(shù)目，那就能減少這方面的開銷。

4) 某些SELECT 語句中的WHERE子句不使用索引。

例如：

(1)‘!=' 將不使用索引。索引只能告訴你什么存在于表中, 而不能告訴你什么不存在于表中。

!= 運算符的使用.

不使用索引

SELECT * FROM reginfo WHERE regid != 8

使用索引

SELECT * FROM reginfo WHERE regid > 8 OR regid < 8

(2) ‘||'是字符連接函數(shù). 就象其他函數(shù)那樣, 停用了索引。

(3) ‘ '是數(shù)學函數(shù)。就象其他數(shù)學函數(shù)那樣, 停用了索引。

(4)相同的索引列不能互相比較,這將會啟用全表掃描。

5.5 用TRUNCATE替代DELETE

當刪除表中的記錄時，在通常情況下， 回滾段(rollback segments ) 用來存放可以被恢復的信息。 如果你沒有COMMIT事務，ORACLE會將數(shù)據(jù)恢復到刪除之前的狀態(tài)(準確地說是恢復到執(zhí)行刪除命令之前的狀況)。

而當運用TRUNCATE時， 回滾段不再存放任何可被恢復的信息。當命令運行后，數(shù)據(jù)不能被恢復。因此很少的資源被調(diào)用，執(zhí)行時間也會很短。

如果是刪除全表，請使用TRUNCATE TABLE TabName 來替代 DELETE FROM TabName，能有效提高速度，并釋放該表所占的存儲空間，減少磁盤碎片。由于TRUNCATE TABLE是DDL語言，在存儲過程中不能直接使用，應加上EXECUTE IMMEDIATE，使用方法如下：EXECUTE IMMEDIATE ‘TRUNCATE TABLE TabName’;

使用前請注意是否有權(quán)限問題。

5.6 盡量多使用COMMIT

只要有可能，在程序中盡量多使用COMMIT，這樣程序的性能得到提高，需求也會因為COMMIT所釋放的資源而減少 。

COMMIT所釋放的資源：

1) 回滾段上用于恢復數(shù)據(jù)的信息。

2) 被程序語句獲得的鎖。

3) redo log buffer 中的空間。

4) ORACLE為管理上述3種資源中的內(nèi)部花費。

5.7 關(guān)閉自動提交功能，提高系統(tǒng)性能

　　在第一次建立與數(shù)據(jù)庫的連接時，在缺省情況下，連接是在自動提交模式下的。為了獲得更好的性能，可以通過調(diào)用帶布爾值false參數(shù)的Connection類的setAutoCommit()方法關(guān)閉自動提交功能，如下所示：

　　conn.setAutoCommit(false);

值得注意的是，一旦關(guān)閉了自動提交功能，我們就需要通過調(diào)用Connection類的commit()和rollback()方法來人工的方式對事務進行管理。

5.8 避免在索引列上使用IS NULL和IS NOT NULL

任何SQL 語句，只要在where 子句中使用了is null 或is not null，那么Oracle 優(yōu)化器就不允許使用索引了。

避免在索引中使用任何可以為空的列，ORACLE將無法使用該索引。對于單列索引，如果列包含空值，索引中將不存在此記錄。對于復合索引，如果每個列都為空，索引中同樣不存在此記錄。如果至少有一個列不為空，則記錄存在于索引中。

例如: 如果唯一性索引建立在表的A列和B列上, 并且表中存在一條記錄的A,B值為(123,null) , ORACLE將不接受下一條具有相同A,B值（123,null）的記錄(插入)。然而如果所有的索引列都為空，ORACLE將認為整個鍵值為空而空不等于空. 因此你可以插入1000 條具有相同鍵值的記錄,當然它們都是空! 因為空值不存在于索引列中,所以WHERE子句中對索引列進行空值比較將使ORACLE停用該索引.
低效: (索引失效) SELECT … FROM DEPARTMENT WHERE DEPT_CODE IS NOT NULL; 高效: (索引有效) SELECT … FROM DEPARTMENT WHERE DEPT_CODE >=0;

5.9 避免In子句

使用In 或 not In子句時，特別是當子句中有多個值時，且查詢數(shù)據(jù)表數(shù)據(jù)較多時，速度會明顯下降。可以采用連接查詢或外連接查詢來提高性能。

Char 比 varchar 查詢時高效。在進行查詢及建立索引時，char比varchar的效率要高，當然varchar在存儲上比char要好。

/*in 語句走得全表掃描*/

SELECT * FROM reginfo a WHERE a.regid

IN (SELECT b.regid FROM reginfo_temp b)

/*正確的寫法一:*/

SELECT * FROM reginfo a

WHERE EXISTS (SELECT 1 FROM reginfo_temp b WHERE a.regid=b.regid)

/*正確寫法二：*/

SELECT a.regid,a.regnick FROM reginfo a

INNER JOIN reginfo_temp b ON a.regid=b.regid

/*正確寫法三早期sql 寫法:*/

SELECT a.regid,a.regnick FROM reginfo a, reginfo_temp b

WHERE a.regid=b.regid

5.10 用EXISTS替代IN

在許多基于基礎表的查詢中，為了滿足一個條件，往往需要對另一個表進行聯(lián)接。在這種情況下，使用EXISTS(或NOT EXISTS)通常將提高查詢的效率。

5.11 用NOT EXISTS替代NOT IN

在子查詢中，NOT IN子句將執(zhí)行一個內(nèi)部的排序和合并。無論在哪種情況下，NOT IN都是最低效的 (因為它對子查詢中的表執(zhí)行了一個全表遍歷)。為了避免使用NOT IN ，我們可以把它改寫成外連接(Outer Joins)或NOT EXISTS。

我們在查詢時經(jīng)常在where 子句使用一些邏輯表達式，如大于、小于、等于以及不等于等等，也可以使用and（與）、or（或）以及not（非）。NOT 可用來對任何邏輯運算符號取反。

下面是一個NOT 子句的例子：

… where not (status ='VALID')

如果要使用NOT，則應在取反的短語前面加上括號，并在短語前面加上NOT 運算符。NOT運算符包含在另外一個邏輯運算符中，這就是不等于（<>）運算符。換句話說，即使不在查詢where 子句中顯式地加入NOT 詞，NOT 仍在運算符中，見下例：

… where status <>'INVALID';

再看下面這個例子：

select * from employee where salary<>3000;

對這個查詢，可以改寫為不使用NOT：

select * from employee where salary<3000 or salary>3000;

雖然這兩種查詢的結(jié)果一樣，但是第二種查詢方案會比第一種查詢方案更快些。第二種查詢允許Oracle 對salary 列使用索引，而第一種查詢則不能使用索引。

有時候會將一列和一系列值相比較。最簡單的辦法就是在where 子句中使用子查詢。在where 子句中可以使用兩種格式的子查詢。

第一種格式是使用IN 操作符：

… where column in(select * from … where …);

第二種格式是使用EXIST 操作符：

… where exists (select 'X' from …where …);

我相信絕大多數(shù)人會使用第一種格式，因為它比較容易編寫，而實際上第二種格式要遠比第一種格式的效率高。在Oracle 中可以幾乎將所有的IN 操作符子查詢改寫為使用EXISTS 的子查詢。

第二種格式中，子查詢以‘select 'X'開始。運用EXISTS 子句不管子查詢從表中抽取什么數(shù)據(jù)它只查看where 子句。這樣優(yōu)化器就不必遍歷整個表而僅根據(jù)索引就可完成工作（這里假定在where 語句中使用的列存在索引）。相對于IN 子句來說，EXISTS 使用相連子查詢，構(gòu)造起來要比IN 子查詢困難一些。

通過使用EXIST，Oracle 系統(tǒng)會首先檢查主查詢，然后運行子查詢直到它找到第一個匹配項，這就節(jié)省了時間。Oracle 系統(tǒng)在執(zhí)行IN 子查詢時，首先執(zhí)行子查詢，并將獲得的結(jié)果列表存放在在一個加了索引的臨時表中。在執(zhí)行子查詢之前，系統(tǒng)先將主查詢掛起，待子查詢執(zhí)行完畢，存放在臨時表中以后再執(zhí)行主查詢。這也就是使用EXISTS 比使用IN 通常查詢速度快的原因。

同時應盡可能使用NOT EXISTS 來代替NOT IN，盡管二者都使用了NOT（不能使用索引而降低速度），NOT EXISTS 要比NOT IN 查詢效率更高。

5.12 用UNION替換OR (適用于索引列)

通常情況下, 用UNION替換WHERE子句中的OR將會起到較好的效果。對索引列使用OR將造成全表掃描。注意, 以上規(guī)則只針對多個索引列有效。如果有column沒有被索引, 查詢效率可能會因為你沒有選擇OR而降低。

在下面的例子中, LOC_ID 和REGION上都建有索引。

高效: SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE LOC_ID = 10 UNION SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE REGION = “MELBOURNE”

低效: SELECT LOC_ID , LOC_DESC , REGION FROM LOCATION WHERE LOC_ID = 10 OR REGION = “MELBOURNE”

如果你堅持要用OR, 那就需要返回記錄最少的索引列寫在最前面。

5.13 用UNION-ALL 替換UNION ( 如果有可能的話)

當SQL語句需要UNION兩個查詢結(jié)果集合時,這兩個結(jié)果集合會以UNION-ALL的方式被合并, 然后在輸出最終結(jié)果前進行排序. 如果用UNION ALL替代UNION, 這樣排序就不是必要了. 效率就會因此得到提高. 需要注意的是，UNION ALL 將重復輸出兩個結(jié)果集合中相同記錄. 因此各位還是要從業(yè)務需求分析使用UNION ALL的可行性. UNION 將對結(jié)果集合排序,這個操作會使用到SORT_AREA_SIZE這塊內(nèi)存. 對于這塊內(nèi)存的優(yōu)化也是相當重要的。

5.14 用>=替代>

高效: SELECT * FROM EMP WHERE DEPTNO >=4

低效: SELECT * FROM EMP WHERE DEPTNO >3

兩者的區(qū)別在于, 前者DBMS將直接跳到第一個DEPT等于4的記錄而后者將首先定位到DEPTNO=3的記錄并且向前掃描到第一個DEPT大于3的記錄。

5.15 Order by 語句

ORDER BY 語句決定了Oracle 如何將返回的查詢結(jié)果排序。Order by 語句對要排序的列沒有什么特別的限制，也可以將函數(shù)加入列中（聯(lián)接或者附加等）。任何在Order by 語句的非索引項或者有計算表達式都將降低查詢速度。

仔細檢查order by 語句以找出非索引項或者表達式，它們會降低性能。解決這個問題的辦法就是重寫order by 語句以使用索引，也可以為所使用的列建立另外一個索引，同時應絕對避免在order by 子句中使用表達式。

避免不必要的排序，不必要的數(shù)據(jù)排序大大的降低系統(tǒng)性能。

5.16 避免帶通配符（%）的like 語句

同樣以上面的例子來看這種情況。目前的需求是這樣的，要求在職工表中查詢名字中包含cliton 的人。可以采用如下的查詢SQL 語句：

select * from employee where last_name like '%cliton%';

這里由于通配符（%）在搜尋詞首出現(xiàn)，所以Oracle 系統(tǒng)不使用last_name 的索引。在很多情況下可能無法避免這種情況，但是一定要心中有底，通配符如此使用會降低查詢速度。然而當通配符出現(xiàn)在字符串其他位置時，優(yōu)化器就能利用索引。在下面的查詢中索引得到了使用：

select * from employee where last_name like 'c%';

例如：

不使用索引情況:

SELECT * FROM temp_stuinfo WHERE stu_add LIKE '%甘肅%';

使用索引情況

SELECT * FROM temp_stuinfo WHERE stu_add LIKE '甘肅%';

可以使用oracle 函數(shù)instr 代替like 加快查詢速度:

SELECT * FROM temp_stuinfo WHERE instr(stu_add ,'肅')>0;

instr和like的性能比較:

其實從效率角度來看，誰能用到索引，誰的查詢速度就會快。

like有時可以用到索引，例如：name like ‘李%’，而當下面的情況時索引會失效：name like ‘%李’或者name like ‘%李%’。所以一般我們查找中文類似于‘%字符%’時，索引都會失效。與其他數(shù)據(jù)庫不同的是，oracle支持函數(shù)索引。例如在name字段上建個instr索引，查詢速度就比較快了，這也是為什么instr會比like效率高的原因。

注:instr(title,’手冊’)>0 相當于like‘%手冊%’

instr(title,’手冊’)=0 相當于not like‘%手冊%’

5.17 使用oracle全文檢索對海量的文本數(shù)據(jù)進行搜索

有很多時候，使用instr和like是很理想的, 特別是搜索僅跨越很小的表的時候。然而通過這些文本定位的方法將導致全表掃描，對資源來說消耗比較昂貴，而且實現(xiàn)的搜索功能也非常有限，因此對海量的文本數(shù)據(jù)進行搜索時，建議使用oralce提供的全文檢索功能。

設置全文檢索：

步驟一：檢查和設置數(shù)據(jù)庫角色

首先檢查數(shù)據(jù)庫中是否有CTXSYS用戶和CTXAPP腳色。如果沒有這個用戶和角色，意味著你的數(shù)據(jù)庫創(chuàng)建時未安裝intermedia功能（10G默認安裝都有此用戶和角色）。你必須修改數(shù)據(jù)庫以安裝這項功能。默認安裝情況下，ctxsys用戶是被鎖定的，因此要先啟用ctxsys的用戶。

步驟二：賦權(quán)

在ctxsys用戶下，授予測試用戶oratext以下權(quán)限：

1 GRANT resource, CONNECT, ctxapp TO oratext;

2 GRANT EXECUTE ON ctxsys.ctx_cls TO oratext;

3 GRANT EXECUTE ON ctxsys.ctx_ddl TO oratext;

4 GRANT EXECUTE ON ctxsys.ctx_doc TO oratext;

5 GRANT EXECUTE ON ctxsys.ctx_output TO oratext;

6 GRANT EXECUTE ON ctxsys.ctx_query TO oratext;

7 GRANT EXECUTE ON ctxsys.ctx_report TO oratext;

8 GRANT EXECUTE ON ctxsys.ctx_thes TO oratext;

9 GRANT EXECUTE ON ctxsys.ctx_ulexer TO oratext;

步驟三：設置詞法分析器(lexer)

Oracle實現(xiàn)全文檢索，其機制其實很簡單。即通過Oracle專利的詞法分析器(lexer),將文章中所有的表意單元(Oracle 稱為 term)找出來，記錄在一組以dr$開頭的表中，同時記下該term出現(xiàn)的位置、次數(shù)、hash值等信息。檢索時，Oracle從這組表中查找相應的term，并計算其出現(xiàn)頻率，根據(jù)某個算法來計算每個文檔的得分(score),即所謂的‘匹配率’。而lexer則是該機制的核心，它決定了全文檢索的效率。Oracle針對不同的語言提供了不同的lexer,而我們通常能用到其中的三個：

basic_lexer:針對英語。它能根據(jù)空格和標點來將英語單詞從句子中分離，還能自動將一些出現(xiàn)頻率過高已經(jīng)失去檢索意義的單詞作為‘垃圾’處理，如if,is等，具有較高的處理效率。但該lexer應用于漢語則有很多問題，由于它只認空格和標點，而漢語的一句話中通常不會有空格，因此，它會把整句話作為一個term,事實上失去檢索能力。以‘中國人民站起來了’這句話為例，basic_lexer分析的結(jié)果只有一個term,就是‘中國人民站起來了’。此時若檢索‘中國’，將檢索不到內(nèi)容。

chinese_vgram_lexer:專門的漢語分析器，支持所有漢字字符集(ZHS16CGB231280 ZHS16GBK ZHT32EUC ZHT16BIG5 ZHT32TRIS ZHT16MSWIN950 ZHT16HKSCS UTF8 )。該分析器按字為單元來分析漢語句子?！袊嗣裾酒饋砹恕@句話，會被它分析成如下幾個term:‘中’，‘中國’，‘國人’，‘人民’，‘民站’，‘站起’，起來’，‘來了’，‘了’?？梢钥闯?，這種分析方法，實現(xiàn)算法很簡單，并且能實現(xiàn)‘一網(wǎng)打盡’，但效率則是差強人意。

chinese_lexer:這是一個新的漢語分析器，只支持utf8字符集。上面已經(jīng)看到，chinese vgram lexer這個分析器由于不認識常用的漢語詞匯，因此分析的單元非常機械，像上面的‘民站’，‘站起’在漢語中根本不會單獨出現(xiàn)，因此這種term是沒有意義的，反而影響效率。chinese_lexer的最大改進就是該分析器能認識大部分常用漢語詞匯，因此能更有效率地分析句子，像以上兩個愚蠢的單元將不會再出現(xiàn)，極大提高了效率。但是它只支持utf8,如果你的數(shù)據(jù)庫是zhs16gbk字符集，則只能使用笨笨的那個Chinese vgram lexer。如果不做任何設置，Oracle缺省使用basic_lexer這個分析器。

要指定使用哪一個lexer,可以這樣操作：

第一．建立一個preference:

1EXEC ctx_ddl.create_preference ('my_lexer', 'chinese_vgram_lexer');

第二．在建立全文索引時，指明所用的lexer:

1CREATE INDEX myindex ON mytable(mycolumn) indextype IS ctxsys.context parameters('lexer my_lexer');

這樣建立的全文檢索索引，就會使用chinese_vgram_lexer作為分析器。

全文檢索舉例：

測試用戶為oratext，建立此用戶和對應表空間的內(nèi)容就不寫了：

步驟一：授權(quán)，ctxsys登陸并對oratext用戶授權(quán)：

GRANT resource, CONNECT, ctxapp TO oratext;

GRANT EXECUTE ON ctxsys.ctx_cls TO oratext;

GRANT EXECUTE ON ctxsys.ctx_ddl TO oratext;

GRANT EXECUTE ON ctxsys.ctx_doc TO oratext;

GRANT EXECUTE ON ctxsys.ctx_output TO oratext;

GRANT EXECUTE ON ctxsys.ctx_query TO oratext;

GRANT EXECUTE ON ctxsys.ctx_report TO oratext;

GRANT EXECUTE ON ctxsys.ctx_thes TO oratext;

GRANT EXECUTE ON ctxsys.ctx_ulexer TO oratext;

步驟二：設置詞法分析器，使用chinese_vgram_lexer作為分析器：

BEGIN

–設置詞法分析器

ctx_ddl.create_preference ('oratext_lexer', 'chinese_vgram_lexer');

END;

可以通過下面的語句查看系統(tǒng)默認及設置的oracle text參數(shù)：

SELECT pre_name, pre_object FROM ctx_preferences

可以看到我剛剛設置的語法分析器參數(shù)oratext_lexer，（默認的有一個MY_LEXER的語法分析器參數(shù)）。

步驟三：建立測試表，插入測試數(shù)據(jù)：

CREATE TABLE textdemo(

id NUMBER NOT NULL PRIMARY KEY,

book_author varchar2(20),–作者

publish_time DATE,–發(fā)布日期

title varchar2(400),–標題

book_abstract varchar2(2000),–摘要

path varchar2(200)–路徑

);

commit;

INSERT INTO textdemo VALUES(1,'宮琦峻',to_date('2008-10-07','yyyy-mm-dd'),' 移動城堡','故事發(fā)生在19世紀末的歐洲，善良可愛的蘇菲被惡毒的女巫施下魔咒，從18歲的女孩變成90歲的婆婆，孤單無助的她無意中走入鎮(zhèn)外的移動城堡，據(jù)說它的主人哈爾以吸取女孩的靈魂為樂，但是事情并沒有人們傳說的那么可怕，性情古怪的哈爾居然收留了蘇菲，兩個人在四腳的移動城堡中開始了奇妙的共同生活，一段交織了愛與痛、樂與悲的愛情故事在戰(zhàn)火中悄悄展開','E:textsearchmoveingcastle.doc');

INSERT INTO textdemo VALUES(2,'莫貝克曼貝托夫',to_date('2008-10-07','yyyy-mm-dd'),' 子彈轉(zhuǎn)彎','這部由俄羅斯導演提莫貝克曼貝托夫執(zhí)導的影片自6 月末在北美上映以來，已經(jīng)在全球取得了超過3億美元的票房收入。在亞洲上映后也先后拿下日本、韓國等地的票房冠軍寶座。雖然不少網(wǎng)友在此之前也相繼通過各種渠道接觸到本片，但相信影片憑著在大銀幕上呈現(xiàn)出的超酷的視聽效果，依然能夠吸引大量影迷前往影院捧場。','E:textsearchcatch.pdf');

INSERT INTO textdemo VALUES(3,'袁泉',to_date('2008-10-07','yyyy-mm-dd'),'主演吳彥祖和袁泉現(xiàn)身','電影《如夢》在上海同樂坊拍攝，主演吳彥祖和袁泉現(xiàn)身。由于是深夜拍攝，所以周圍并沒有過多的fans注意到，給了劇組一個很清凈的拍攝環(huán)境，站在街頭的袁泉低著頭，在寒冷的夜里看上去還真有些像女鬼，令人毛骨悚然。','E:textsearchdream.txt');

commit;

步驟四：在book_abstract字段建立索引使用剛剛設置的ORATEXT_LEXER ：chinese_vgram_lexer作為分析器。

CREATE INDEX demo_abstract ON textdemo(book_abstract) indextype IS ctxsys.context parameters('lexer ORATEXT_LEXER');

commit;

之后如上所述多出很多dr$開頭的表和索引，系統(tǒng)會創(chuàng)建四個相關(guān)的表：

DR$DEMO_ABSTRACT$I(分詞后的TOKEN表)DR$DEMO_ABSTRACT$KDR$DEMO_ABSTRACT$N DR$DEMO_ABSTRACT$R

下面的語句可以查看索引創(chuàng)建過程中是否發(fā)生了錯誤：

SELECT * FROM ctx_USER_index_errors

附：對于建立索引的類型（例如ctxsys.context），包括四種：context,ctxcat,ctxrule,ctxxpath。

CONTEXT用于對含有大量連續(xù)文本數(shù)據(jù)進行檢索。支持word、html、xml、text等很多數(shù)據(jù)格式。支持范圍（range）分區(qū)，支持并行創(chuàng)建索引（Parallel indexing）的索引類型。

支持類型：VARCHAR2, CLOB, BLOB, CHAR, BFILE, XMLType, and URIType.DML。操作后，需要CTX_DDL.SYNC_INDEX手工同步索引如果有查詢包含多個詞語，直接用空格隔開(如 oracle itpub)。

查詢標識符CONTAINS

CTXCAT適用于混合查詢語句（如查詢條件包括產(chǎn)品id，價格，描述等）。適合于查詢較小的具有一定結(jié)構(gòu)的文本段。具有事務性。DML 操作后，索引會自動進行同步。

操作符：and,or,>,;<, =,between,in

查詢標識符CATSEARCH

CTXRULE查詢標識符MATCHES。

CTXXPATH（這兩個索引沒有去更多搜索相關(guān)內(nèi)容）

一般來說我們建立CONTEXT類型的索引（CONTAINS來查詢）。

步驟五：查詢測試

–查詢或

SELECT score(20),t.* FROM textdemo t WHERE contains(book_abstract,'移動城堡 or 俄羅斯',20)>0;

SELECT score(20),t.* FROM textdemo t WHERE contains(book_abstract,'移動城堡 or 歐洲',20)>0;

–基本查詢

SELECT score(20),t.* FROM textdemo t WHERE contains(book_abstract,'移動城堡',20)>0;

–查詢包含多個詞語and測試通過

SELECT score(20),t.* FROM textdemo t WHERE contains(book_abstract,'移動城堡 and 歐洲',20)>0;

測試通過。

5.18 使用CREATE TABLE AS 替代INSERT INTO…select

如果可能，請使用

CREATE TABLE TabName AS SELECT * FROM ……

來替代

INSERT INTO TabName SELECT * FROM …… ,

特別是在記錄數(shù)比較多的情況下，前者的速度上會有非常明顯的優(yōu)勢。

5.19 用索引提高效率

索引是表的一個概念部分,用來提高檢索數(shù)據(jù)的效率，ORACLE使用了一個復雜的自平衡B-tree結(jié)構(gòu)。通常,通過索引查詢數(shù)據(jù)比全表掃描要快。當ORACLE找出執(zhí)行查詢和Update語句的最佳路徑時, ORACLE優(yōu)化器將使用索引。同樣在聯(lián)接多個表時使用索引也可以提高效率。另一個使用索引的好處是,它提供了主鍵(primary key)的唯一性驗證。那些LONG或LONG RAW數(shù)據(jù)類型, 你可以索引幾乎所有的列。通常, 在大型表中使用索引特別有效。當然,你也會發(fā)現(xiàn),在掃描小表時,使用索引同樣能提高效率。雖然使用索引能得到查詢效率的提高,但是我們也必須注意到它的代價。索引需要空間來存儲,也需要定期維護, 每當有記錄在表中增減或索引列被修改時, 索引本身也會被修改。這意味著每條記錄的INSERT , DELETE , UPDATE將為此多付出4 , 5 次的磁盤I/O 。因為索引需要額外的存儲空間和處理,那些不必要的索引反而會使查詢反應時間變慢。

/*定期的重構(gòu)索引是有必要的 */

ALTER INDEX <INDEXNAME> REBUILD <TABLESPACENAME>

必要的時候可以強制使用索引

SELECT /* INDEX(CRM_ORDER INDEX_A) */ * FROM CRM_ORDER A

/*強制使用索引可以加快查詢速度*/

索引對SQL性能的提高：

/*不使用索引的情況，如果在字段regtime 上創(chuàng)建了索引：*/

SELECT * FROM reginfo WHERE to_char(regtime,'yyyy-mm-dd') ='2013-01-01';

這時候上面語句就沒有走索引.

換成如下語句:

SELECT * FROM reginfo WHERE regtime = to_date('2013-01-01','yyyy-mm-dd');

效果就好些.

或者創(chuàng)建函數(shù)索引.

CREATE INDEX index_abc ON reginfo(to_char(regtime,'yyyy-mm-dd'));

組合索引（索引字段最好不超過三個）舉例:

如果我們在查詢中使用兩列作為條件，就可以創(chuàng)建組合索引

CREATE INDEX index_com ON reginfo(regnick,regtime)

例子:

SELECT * FROM reginfo WHERE regnick='張三' AND regtime=to_date('2013-1-1','yyyy-mm-dd');

/*使用了索引index_com*/

SELECT * FROM reginfo WHERE regnick='張三' 也使用了索引index_com

如果我們在regnick 和regtime 上面分別創(chuàng)建了單列索引，通過聯(lián)合查詢后，效率也沒有組合索引好些。

如果下查詢

SELECT * FROM reginfo WHERE regtime=to_date('2013-1-1','yyyy-mm-dd')

這時使用了組合索引的第二個字段，但是oracle9i 以后就使用了跳躍式索引，如果加上優(yōu)化就是用了跳躍式索引:

例子：

SELECT /* index(reginfo index_com)*/* FROM reginfo WHERE

regtime=to_date('2013-1-1','yyyy-mm-dd');

跳躍索引比全表掃描也快，但是要慢于單列索引，所以有必要創(chuàng)建單列索引在此列上。

nvl 相同索引列不能互相比較

/*不使用索引的情況:*/

SELECT * FROM reginfo WHERE regnick = nvl(regnick,'張三');

/*使用索引的情況*/

SELECT * FROM reginfo WHERE regnick LIKE nvl(regnick,'%');

/*不要在索引列上使用運算*/

SELECT * FROM temp_stuinfo WHERE substr(stu_name,1,1)='張';

5.20 選擇最有效率的表名順序(只在基于規(guī)則的優(yōu)化器中有效)

ORACLE的解析器按照從右到左的順序處理FROM子句中的表名，因此FROM子句中寫在最后的表(基礎表 driving table)將被最先處理。在FROM子句中包含多個表的情況下，你必須選擇記錄條數(shù)最少的表作為基礎表。當ORACLE處理多個表時， 會運用排序及合并的方式連接它們。首先，掃描第一個表(FROM子句中最后的那個表)并對記錄進行排序，然后掃描第二個表(FROM子句中最后第二個表)，最后將所有從第二個表中檢索出的記錄與第一個表中合適記錄進行合并。

如果有3個以上的表連接查詢， 那就需要選擇交叉表(intersection table)作為基礎表， 交叉表是指那個被其他表所引用的表。

5.21 共享SQL語句

為了不重復解析相同的SQL語句，在第一次解析之后，ORACLE將SQL語句存放在內(nèi)存中。這塊位于系統(tǒng)全局區(qū)域SGA(system global area)的共享池(shared buffer pool)中的內(nèi)存可以被所有的數(shù)據(jù)庫用戶共享。 因此，當你執(zhí)行一個SQL語句(有時被稱為一個游標)時，如果它和之前的執(zhí)行過的語句完全相同， ORACLE就能很快獲得已經(jīng)被解析的語句以及最好的執(zhí)行路徑。ORACLE的這個功能大大地提高了SQL的執(zhí)行性能并節(jié)省了內(nèi)存的使用。

可惜的是ORACLE只對簡單的表提供高速緩沖(cache buffering)，這個功能并不適用于多表連接查詢。

數(shù)據(jù)庫管理員必須在init.ora中為這個區(qū)域設置合適的參數(shù)，當這個內(nèi)存區(qū)域越大，就可以保留更多的語句，當然被共享的可能性也就越大了。

當你向ORACLE提交一個SQL語句，ORACLE會首先在這塊內(nèi)存中查找相同的語句。這里需要注明的是，ORACLE對兩者采取的是一種嚴格匹配，要達成共享，SQL語句必須完全相同(包括空格，換行等)。

共享的語句必須滿足三個條件：

a) 字符級的比較： 當前被執(zhí)行的語句和共享池中的語句必須完全相同。

b) 兩個語句所指的對象必須完全相同。

c) 兩個SQL語句中必須使用相同的名字的綁定變量(bind variables)。

6 健壯性

健壯性是指在異常情況下，軟件能夠正常運行的能力。正確性與健壯性的區(qū)別是：前者描述軟件在需求范圍之內(nèi)的行為，后者描述軟件在需求范圍之外的行為。想不到異常情況，把異常錯當正常而不作處理，這些都會降低健壯性。提高軟件的健壯性也是開發(fā)者的義務。

健壯性有兩層含義：一是容錯能力，二是恢復能力。

容錯是指發(fā)生異常情況時系統(tǒng)不出錯誤的能力。高風險系統(tǒng)如航空航天、武器、金融等領(lǐng)域的系統(tǒng)，容錯性設計非常重要。

容錯是非常健壯的意思。而恢復則是指軟件發(fā)生錯誤后（不論死活）重新運行時，能否恢復到?jīng)]有發(fā)生錯誤前的狀態(tài)的能力。

從語義上理解，恢復不及容錯那么健壯。

6.1 在創(chuàng)建存儲過程語句中提供必要的參數(shù)

創(chuàng)建存儲過程語句可以包含很多參數(shù)，雖然從語法角度講它們不是必須的，但是在創(chuàng)建存儲過程時提供這些參數(shù)可以提高執(zhí)行效率。

6.2 對輸入?yún)?shù)進行必要的的檢查和預處理

無論使用哪種編程語言，對輸入?yún)?shù)的判斷都是必須的。正確的參數(shù)驗證是保證程序良好運行的前提。

正確的驗證和預處理操作包括：

如果輸入?yún)?shù)錯誤，存儲過程應返回一個明確的值告訴客戶應用，然后客戶應用可以根據(jù)返回的值進行處理，或者向存儲過程提交新的參數(shù)，或者去調(diào)用其他的程序。

根據(jù)業(yè)務邏輯，對輸入?yún)?shù)作一定的預處理，如大小寫的轉(zhuǎn)換，NULL與空字符串或0的轉(zhuǎn)換等。

6.3 異常處理

在存儲過程執(zhí)行的過程中，經(jīng)常因為數(shù)據(jù)或者其他問題產(chǎn)生異常（condition）。根據(jù)業(yè)務邏輯，存儲過程應該對異常進行相應處理或直接返回給調(diào)用者。此處暫且將condition譯為異常以方便理解。實際上有些異常（condition）并非是由于錯誤引起的。

6.4 PL/SQL 異常處理規(guī)范

對于存儲過程、函數(shù)等程序塊都要有異常處理部分，以提高程序的自檢能力。

例如:

EXCEPTION

WHEN NO_DATA_FOUND THEN

DBMS_OUTPUT.PUT_LINE(SQLERRM);

6.5 在 PL/SQL 中使用 sqlcode,sqlerrm

當存儲過程執(zhí)行出錯拋出EXCEPTION時，可通過 sqlcode 和 sqlerrm取得當前的ORACLE錯誤代碼和錯誤信息，以下是使用范例：

DECLARE

V_SQLCODE number(6);

V_ERRMSG varchar2(512);

BEGIN

UPDATE dept SET username = (SELECT username FROM work_group)

WHERE rownum=1;

EXCEPTION

WHEN OTHERS THEN

V_SQLCODE := sqlcode;

V_ERRMSG := sqlerrm;

Dmbs_output.put_line (‘程序出錯，錯誤代碼:’|| V_SQLCODE||’錯誤信息:’|| V_ERRMSG);

END;

6.6 后臺驗證異常信息規(guī)范

使用數(shù)據(jù)庫過程、函數(shù)進行后臺數(shù)據(jù)驗證時，發(fā)現(xiàn)異常情況，需要記錄異常，并返回到用戶界面。

異常信息描述要求簡潔、準確、完整，揭示異常實質(zhì)，準確定位異常出現(xiàn)的位置。

異常分為警告和錯誤兩類。

由于每個實際項目，業(yè)務不同，異常信息也變化很大。每個項目開始時根據(jù)需求，統(tǒng)一進行定義。

6.7 Insert語句健壯性

使用Insert語句一定要給出要插入值的字段列表，這樣即使更改了表結(jié)構(gòu)加了字段也不會影響現(xiàn)有系統(tǒng)的運行。

6.8 外鍵值可用null的問題

外鍵列如沒有明確說明not null，可插入null記錄（而null是在外部表的記錄中沒有的），如無可插null記錄的想法，要對外鍵字段加not null約束。

6.9 序列 sequence 跳號的問題

sequence 因回滾，系統(tǒng)崩潰（使用cache 內(nèi)的值將認為已用），多表引用都將使其跳號，所以不能用于連續(xù)序號 。

7 安全性和完整性約束

無論在使用Select,還是使用破壞力極大的Update和Delete語句時，一定要檢查Where條件判斷的完整性，不要在運行時出現(xiàn)數(shù)據(jù)的重大丟失。

如果不確定，最好先用Select語句帶上相同條件來驗證一下結(jié)果集，來檢驗條件是否正確。

有依賴關(guān)系的表，例如主外鍵關(guān)系表，在刪除父表時必須級聯(lián)刪除其子表相應數(shù)據(jù)，或則按照某種業(yè)務規(guī)則轉(zhuǎn)移該數(shù)據(jù)。9I中表中字段縮小及變類型，字段為空或表空，varchar和char長度不變可任意改，字段名和表名字段可用 ALTER TABLE table SET UNUSED (column) 設定為不可用，注意無命令再設為可用。