Oracle數(shù)據(jù)庫10g：新的SQL和PLSQL功能

1、Oracle 數(shù)據(jù)庫數(shù)據(jù)庫 10g中新的中新的 SQL 和和 PL/SQL 功能功能議程議程 新的 SQL 特性 新的 PL/SQL 特性 PL/SQL 的速度是原來的兩倍 總結 問答Oracle8Oracle SQL 引擎的發(fā)展引擎的發(fā)展Oracle8iOracle9iOracle9i R2SQL /XML 集成集成（XMLType、URIType 等）等） OLAPDatetime 類型類型ANSI 聯(lián)接聯(lián)接CASE 表達式表達式XML 數(shù)據(jù)庫數(shù)據(jù)庫SQL/XMLW3C 標準標準數(shù)據(jù)挖掘數(shù)據(jù)挖掘網(wǎng)絡網(wǎng)絡Web 服務服務正則表達式正則表達式本地數(shù)值結構本地數(shù)值結構BLAST對象擴展對象擴展數(shù)

2、據(jù)插件數(shù)據(jù)插件 SQL/Java 集成集成interMedia 類型類型數(shù)據(jù)和處理復雜性數(shù)據(jù)和處理復雜性新的新的 SQL 特性特性 正則表達式 本地數(shù)值結構 LOB 增強 集合增強正則表達式簡介正則表達式簡介 起源于數(shù)學 首先在 UNIX 中計算機化從 ed、grep、perl、cgi、web 到任何地方 多種應用程序HTML FORMS 中的驗證 生物信息學服務器配置數(shù)據(jù)挖掘Oracle 正則表達式正則表達式 對數(shù)據(jù)庫的本地支持SQL 和 PL/SQL 中的接口基于 POSIX 標準與 GNU、PERL、Java、Xquery 等同步 描述數(shù)據(jù)的模式變?yōu)閿?shù)據(jù)的一個屬性 數(shù)據(jù)庫內(nèi)部強大的字符串

3、處理主要特性主要特性 POSIX 擴展的正則表達式 接口REGEXP_LIKE模式是否匹配？REGEXP_SUBSTR它與什么匹配？REGEXP_INSTR它在哪里匹配？REGEXP_REPLACE替換匹配的記錄。 匹配選項 語言環(huán)境支持 LOB 支持REGEXP_LIKE 確定模式是否存在。 查找名稱 Jon Stevens 的變體John 或 JonSteven 或 Stevens 或 Stephen 或 StephensSELECT c1 FROM t1 WHERE REGEXP_LIKE(c1, Joh?n Ste(ph|v)ens?); Jon Stevens John Stephe

4、ns John Stevens和和 DDL 一起使用一起使用 允許使用過濾器的數(shù)據(jù)，并帶檢查約束郵政編碼 (ZIP code) 列是 VARCHAR2(5)，但可以包含任意的 5 個字符。 使用視圖的查詢子集和格式化將電話號碼格式化為 (xxx) xxx-xxxx 創(chuàng)建基于函數(shù)的索引與與 PL/SQL 一起使用一起使用 非常強大的字符串處理功能src := REGEXP_REPLACE (src, );src := REGEXP_REPLACE (src, );src := REGEXP_REPLACE (src, ); 可以增強現(xiàn)有的功能支持 PERL 縮寫提取第 n 個子表達式 替換大量代

5、碼行可以簡化字符串處理函數(shù)性能考慮事項性能考慮事項 模式匹配可能很復雜需要編譯成狀態(tài)機詞法分析檢查所有可能的分支，直到發(fā)現(xiàn)匹配 每條語句編譯一次對于復雜情景可能比 LIKE 更快 郵政編碼檢查快 5 倍通常比等價的 PL/SQL 更快 編寫快速執(zhí)行的表達式本地浮點數(shù)據(jù)類型本地浮點數(shù)據(jù)類型 兩種新的數(shù)值數(shù)據(jù)類型 BINARY_FLOAT， BINARY_DOUBLE二進制浮點運算的 IEEE 754 標準是許多其他標準（例如，Java、XML 模式）和硬件平臺的一部分普遍存在于商務智能、生命科學、工程/科學計算本地浮點數(shù)據(jù)類型與數(shù)值數(shù)據(jù)類型本地浮點數(shù)據(jù)類型與數(shù)值數(shù)據(jù)類型 比 NUMBER 類型效

6、率更高硬件運算/數(shù)學運算快 5 10 倍占用更少的內(nèi)存/磁盤空間（5/9 字節(jié)與 1 22 字節(jié)）BINARY_DOUBLE 值范圍更大（e308 與 e125）無需類型轉換（使用與字節(jié)順序無關的存儲格式）本地浮點函數(shù)本地浮點函數(shù) 新的類型轉換函數(shù)TO_BINARY_FLOAT、TO_BINARY_DOUBLETO_NUMBER SQL 函數(shù)支持數(shù)值函數(shù)（sin、cos 等）聚合函數(shù)（sum、avg、stddev 等）分析函數(shù)（sum、avg、stddev 等） 對 SQL、PL/SQL、Java、XML 模式注冊、ODP.NET 和 OCI/OCCI 的無縫支持本地浮點約束本地浮點約束cre

7、ate table floating_point_table1 (fltNnull binary_float constraint flt_null not null,dblNnull binary_double constraint dbl_null not null,fltUnq binary_float constraint flt_unq unique,dblUnq binary_double constraint dbl_unq unique,fltChk binary_float constraint flt_chk check ( fltChk is not NaN ) ,dbl

8、Chk binary_double constraint dbl_chk check ( dblChk is not infinite) ,fltPrm binary_float constraint flt_prm primary key);* NaN (不是數(shù)字) e.g. 0/0, infinity/infinity本地浮點約束本地浮點約束create table floating_point_table2 (dblPrm binary_double constraint dbl_prm primary key,fltFrn binary_float constraint flt_frn

9、 references floating_point_table1(fltPrm) on delete cascade);PL/SQL 示例示例 使用歐拉級數(shù)計算 = sqrt ( 6 * ( 1 + 1/2*2 + 1/3*2 + . ) ) 使用牛頓方法近似真值的平方根 賦值、加、乘、除、比較、絕對值 procedure Euler_Pi_Series is . begin pi := one; prev_pi := zero; prod_over_six := zero; step := zero; while pi - prev_pi toler loop prev_pi := pi;

10、 step := step + one; prod_over_six := prod_over_six + one/(step*step); prod := six*prod_over_six; prev_root := prod; root := prod/two; while Abs(root - prev_root) root_toler loop prev_root := root; root := (root + prod/root)/two; end loop; pi := root; end loop; end Euler_Pi_Series; subtype My_Number

11、 is number; zero constant My_Number := 0.0; one constant My_Number := 1.0; two constant My_Number := 2.0; six constant My_Number := 6.0; toler constant My_Number := 0.00000000001; root_toler constant My_Number := toler/1000.0; root My_Number; prev_root My_Number; prod_over_six My_Number; prod My_Num

12、ber; pi My_Number; prev_pi My_Number; step My_Number; subtype My_Number is binary_double; zero constant My_Number := 0.0d; one constant My_Number := 1.0d; two constant My_Number := 2.0d; six constant My_Number := 6.0d; toler constant My_Number := 0.00000000001d; root_toler constant My_Number := tole

13、r/1000.0d; root My_Number; prev_root My_Number; prod_over_six My_Number; prod My_Number; pi My_Number; prev_pi My_Number; step My_Number;PL/SQL 示例示例 使用歐拉級數(shù)計算 近似 300,000 次迭代 NUMBER 花費約 27.7 秒 BINARY_DOUBLE 花費約 3.7 秒 提高倍數(shù)：約 7 倍LOB 增強增強 LOB 普遍用于存儲非結構化的數(shù)據(jù)（文本、AVI、基因/蛋白序列等）LOB 性能改善性能改善 訪問內(nèi)聯(lián) ( 4KB) LOB 時獲得

14、 5 倍的性能增益 （在 9iR2 中）臨時 LOB 使用引用計算提供了 幾個數(shù)量級的性能增益在讀取時引用 (Reference on Read)在寫入時復制 (Copy on Write)超大型超大型 LOB TB 大小的 Lob (8 128 TB) DB_BLOCK_SIZE (2 32 KB) x (4GB 1)新的 DBMS_LOB.GET_STORAGE_LIMIT 函數(shù)OCI、JDBC 和 DBMS_LOB 現(xiàn)在支持大于 4 GB 的 LOB OCILobRead2()、OCIWriteAppend2() 和 OCILobWrite2() 函數(shù) 為 JDBC 和 DBMS_LOB

15、 提供相同的 API集合類型增強集合類型增強 用戶定義的類型、集合類型和引用類型簡化了結構復雜的數(shù)據(jù)建模 集合類型用于映射實際世界應用程序中的包含關系例如，一個購物車包含了許多商品VARRAY 增強增強 VARRAY 的類型發(fā)展 CREATE TYPE email_list_typ AS OBJECT ( section_no NUMBER, emails email_list_arr);/CREATE TYPE email_varray_typ AS VARRAY(5) OF email_list_typ;/ALTER TYPE email_varray_typ MODIFY LIMIT 1

16、00 CASCADE; 在臨時表中支持 VARRAY 列提供應用程序開發(fā)靈活性嵌套表增強嵌套表增強 將一個不同的表空間用于嵌套表的存儲表帶嵌套表的列的表可以在表空間之間分配 I/O 負載CREATE TABLE purchase_orders ( order_items_column order_items_typ )NESTED TABLE order_items_columnSTORE AS order_items_column_nt (TABLESPACE users);為嵌套表提供的為嵌套表提供的 ANSI SQL 多集多集合操作合操作 嵌套表存儲無序的元素集合集合 只包含唯一的元素多

17、集合多集合 可以包含重復的元素 嵌套表上的各種多集合操作為嵌套表提供的多集合操作為嵌套表提供的多集合操作 Cardinality、Collect、Multiset Except、Multiset Intersection、Multiset Union、Powermultiset、Powermultiset_by_Cardinality 和 Set 操作查找熱銷商品集合的強大工具（市場指數(shù)總覽分析）多集合操作示例多集合操作示例create type categories as table of int;/ create table carts (c1 int, c2 categories) ne

18、sted table c2 store as tb1_c2; insert into carts values(1, categories(1,2,3,4);insert into carts values(2, categories(2,4,6); select t1.c2 multiset intersect t2.c2 from carts t1, carts t2 where t1.c1=1 and t2.c1=2; categories(2,4) - frequent set嵌套表比較嵌套表比較 Equal 和 Not Equal、In、Submultiset、Member Of、E

19、mpty 和 Is Not A SetSELECT * FROM customer cWHERE item_typ(2) MEMBER OF c.freq_set; 在在 PL/SQL 中也提供中也提供declare type Tab_t is table of pls_integer; a Tab_t := Tab_t ( 1, 2, 3, 4, 5 );begin if 3 member of a then Show ( member ); end if;end;SQL 小結小結 Oracle 數(shù)據(jù)庫 10g SQL 引擎 聚合來自不同的分布式數(shù)據(jù)源的數(shù)據(jù)網(wǎng)格信息無縫地集成 Java、XM

20、L 和基本數(shù)據(jù)處理功能一個全面的高性能和基于標準（SQL-2003 W3C、J2SE、POSIX 等）的特性集簡化的應用程序開發(fā)、部署和管理RAS、可管理性和安全性 議程議程 新的 SQL 特性 新的新的 PL/SQL 特性特性 PL/SQL 的速度是原來的兩倍 總結 問答新的新的 PL/SQL 特性特性 剛才討論的所有 SQL 語言特性 FORALL 語句中的稀疏綁定數(shù)組 編譯器警告 新的 Utl_Mail 和 Utl_Compress 程序包FORALL 語句中的稀疏綁定數(shù)組語句中的稀疏綁定數(shù)組 您的 PL/SQL 表最初是密集的，例如，是由 BULK COLLECT 填充的 您刪除了一些

21、元素 您想將這些元素插入到某個表中forall j in INDICES OF sparse_emp_records BETWEEN AND - optional insert into new_employees values sparse_emp_records(j);FORALL 語句中的稀疏綁定數(shù)組語句中的稀疏綁定數(shù)組 您有一個 PL/SQL 表（密集或稀疏） 您想將這些記錄的一個指定的子集插入到某個表中forall j in VALUES OF pointer_tab insert into new_employees values emp_records(j);編譯器警告編譯器警告

22、create or replace procedure P is debugging boolean := false;begin if debugging then Dbms_Output.Put_Line ( debugging ); end if;end P;/SP2-0804: Procedure created with compilation warningsShow ErrorsPLW-06002: Unreachable codealter session set plsql_warnings = ENABLE:ALL;編譯器警告編譯器警告create or replace p

23、rocedure P ( i in out clob ) isbegin i := x;end P;/SP2-0804: Procedure created with compilation warningsShow ErrorsPLW-07203: parameter I may benefit from use of the NOCOPY compiler hintalter session set plsql_warnings = ENABLE:PERFORMANCE;新提供的程序包新提供的程序包 Utl_Mail它的 API 反映了，比如說，Netscape Communicator

24、的最終用戶的思維模式無需 SMTP 協(xié)議的知識（與使用 Utl_Smtp 相比）確保簡單化的一些限制 Utl_Compress提供了一個 API 來壓縮和解壓縮 RAW 或 BLOB （服務器端）議程議程 新的 SQL 特性 新的 PL/SQL 特性 PL/SQL 的速度是原來的兩倍的速度是原來的兩倍 總結 問答PL/SQL 編譯和執(zhí)行編譯和執(zhí)行 101PL/SQL 源代碼源代碼前端前端IR = Diana后端后端對象代碼對象代碼 = MCodePVM全新的后端全新的后端 10g 引進了一個全新的后端，又叫做優(yōu)化代碼生成器 它全面地查看源代碼所表達的內(nèi)容，然后刪除對最終的正確結果確實沒有影響的

25、所有計算 提供了實現(xiàn)巨大 性能的潛力全新的后端全新的后端 在 9iR2 中的 ORACLE 可執(zhí)行文件中，新的代碼生成器與舊的代碼生成器并存，一個允許在兩者之間進行選擇的開關 兩者都曾在回歸測試中使用 因此我們保證它是安全的！ 我們沒有將這個開關提供給客戶重要的重要的 pvm 升級升級 刪除了過時的指令 增加了新的指令例如 one-shot multi concatenate 簡化了用于使用 PVM 指令的系統(tǒng) 實施經(jīng)過調(diào)整的指令的 C 例程改進了改進了本地本地 編譯編譯 數(shù)據(jù)庫中的 DLL 對于數(shù)據(jù)庫管理員，配置更加簡單 改進了生成的 C 代碼讓我們修改其工作方式PL/SQL 源代碼源代碼前

26、端前端IR = Diana后端后端對象代碼對象代碼 = MCodePVMPL/SQL 編譯和執(zhí)行編譯和執(zhí)行 101對象代碼對象代碼 = MCode 或本地計算機代碼或本地計算機代碼硬件硬件性能實驗結果性能實驗結果 由 PL/SQL 小組進行的測試 Beta 程序測試 由 Oracle 的應用程序部進行的測試由由 PL/SQL 小組進行的測試小組進行的測試 17 個自包含的 PL/SQL 程序的基準測試套件 設計用來實踐大范圍的結構和編程習慣 一些測試選擇用來演示特定的優(yōu)化器效果例如，從一個循環(huán)中刪除一個常量賦值由由 PL/SQL 小組進行的測試小組進行的測試 下一張幻燈片顯示了每一個程序在 9

27、iR2 INTERPRETED 和 10g NATIVE 中的速度 9iR2 中速度相同 10g 中顯示了兩種測量方式的完成 CPU 時間之比由由 PL/SQL 小組進行的測試小組進行的測試 大多數(shù)測試程序速度提高超過 2 倍 一些測試程序速度提高遠超過 2 倍 速度提高 10 倍的程序使用 BINARY_INTEGER，并擁有特別易于優(yōu)化的編程習慣 procedure P is /* constrained binary_integer subtypes */ a positiven := 1; b positiven := 1; c positiven := 1; d positiven:

28、=1; begin for j in 1.i_nof_iterations loop b := j /* constraint check */; d := 42 /* constant assignment within a loop */; c := d + b /* can be combined. */; a := b + c /* .c not used except here */; end loop; end P;由由 PL/SQL 小組進行的測試小組進行的測試 下一張幻燈片顯示了更多詳細信息 基準是 Oracle Version 8.0.6 顯示了 8i9iR2 INTERPR

29、ETED9iR2 NATIVE10g INTERPRETED10g NATIVE 速度提高的倍數(shù)由由 PL/SQL 小組進行的測試小組進行的測試 TMI？ 下一張幻燈片為每一個測試的版本顯示了這 17 個程序的集合的統(tǒng)計信息：中值最大和最小值第 1 和第 3 四分點（一半的觀察都落在第 1 和第 3 四分點之間）由由 PL/SQL 小組進行的測試小組進行的測試 8i 比 8.0.6 快 9iR2 比 8i 快 NATIVE 始終比 INTERPRETED 快在 9iR2 中在 10g 中 10g 始終比 9iR2 快INTERPRETED 與 INTERPRETEDNATIVE 與 NATIVE由由 IFS 進行的進行的 Beta 程序測試程序測試由由 IFS 進行的進行的 Beta 程序測試程序測試 IFS 應用程序基于 web 和門戶技術提供用于制造、供應鏈管理、客戶關系管理、服務供應、財務管理、產(chǎn)品開發(fā)、維護和人力資源管理的 60 多種企