《互聯(lián)網(wǎng)oracle》PPT課件.ppt

《《互聯(lián)網(wǎng)oracle》PPT課件.ppt》由會員分享，可在線閱讀，更多相關(guān)《《互聯(lián)網(wǎng)oracle》PPT課件.ppt（126頁珍藏版）》請?jiān)谘b配圖網(wǎng)上搜索。

1、ORACLE基礎(chǔ),一、基本查詢語句 SELECT FROM WHERE GROUP BY HAVING ORDER BY,ORACLE基礎(chǔ),二、函數(shù) 1、處理null值 NVL(M,N) 2、字符串連接 || 3、查找子串位置 INSTR(原串,子串,從第n個字符開始) 4、取子串 SUBSTR(原串,起始位置,取字符位數(shù)),ORACLE基礎(chǔ),二、函數(shù) 5、日期轉(zhuǎn)換為字符串:yyyy-mm-dd hh:mi:ss TO_CHAR(表達(dá)式,YYYY-MM-DD HH:MI:SS) 6、日期轉(zhuǎn)換為字符串:yyyy-mm-dd“ TO_CHAR(表達(dá)式,YYYY-MM-DD)“ 7、日期轉(zhuǎn)

2、換為字符串:hh:mi:ss“ TO_CHAR(表達(dá)式,HH:MI:SS),ORACLE基礎(chǔ),二、函數(shù) 8、字符串轉(zhuǎn)換為日期 TO_DATE(表達(dá)式,YYYY-MM-DD HH:MI:SS) TO_DATE(表達(dá)式,YYYY-MM-DD) 9、數(shù)值轉(zhuǎn)字符串TO_CHAR(表達(dá)式) 10、字符串轉(zhuǎn)數(shù)值TO_NUMBER(表達(dá)式),ORACLE基礎(chǔ),二、函數(shù) 11、表達(dá)式判斷取值 case when 條件表達(dá)式1 then 結(jié)果式1 when 條件表達(dá)式2 then 結(jié)果式2... else 結(jié)果式n end decode(字段，第一個判斷值，第一個結(jié)果，第二個判斷值，第二個結(jié)果，否則的結(jié)果),

3、二,ORACLE基礎(chǔ),三、連接 1、內(nèi)聯(lián)(自然連接) ： select * from a , b where a.id=b.id select * from a inner join b on a.id=b.id,ORACLE基礎(chǔ),三、連接 2、外接： 左接(左外連) select * from a , b where a.id =b.id (+) select * from a left join b on a.id=b.id 右接(右外連) select * from a , b where b.id(+) = a.id select * from b right joi

4、n a on a.id = b.id,ORACLE基礎(chǔ),三、連接 3、全連： select * from a full join b on a.id=b.id 四、笛卡爾積 select * from a , b,ORACLE基礎(chǔ),三、連接 4、自連： select a.* from AAA a, AAA b on a.id=b.id,ORACLE基礎(chǔ),四、合并數(shù)據(jù) UNION 合并去重復(fù) UNION ALL 合并不去重復(fù),ORACLE基礎(chǔ),五、觸發(fā)器 1、概述： 觸發(fā)器是特定事件出現(xiàn)的時候，自動執(zhí)行的代碼塊。類似于存儲過程，但是用戶不能直接調(diào)用他們。 ORACLE觸發(fā)器語法 觸發(fā)器有

5、兩種after和before，區(qū)別在于每次提交事務(wù)觸發(fā)一次和每一行數(shù)據(jù)的更新都觸發(fā)一次,ORACLE基礎(chǔ),五、觸發(fā)器 2、語法： ORACLE產(chǎn)生數(shù)據(jù)庫觸發(fā)器的語法為： create or replace trigger 觸發(fā)器名 觸發(fā)時間 觸發(fā)事件 on 表名 for each row pl/sql 語句,ORACLE基礎(chǔ),五、觸發(fā)器 2、語法： create or replace trigger aaaa before insert on a for each row declare -- local variables herebegin insert into aa(a,b)valu

6、es(999,999);end aaaa;,ORACLE基礎(chǔ),五、觸發(fā)器 2、語法： 1）、觸發(fā)器名：觸發(fā)器對象的名稱。 2）、觸發(fā)時間：指明觸發(fā)器何時執(zhí)行，該值可取， before---表示在數(shù)據(jù)庫動作之前觸發(fā)器執(zhí)行； after---表示在數(shù)據(jù)庫動作之后出發(fā)器執(zhí)行。,ORACLE基礎(chǔ),五、觸發(fā)器 2、語法： 3、觸發(fā)事件：指明哪些數(shù)據(jù)庫動作會觸發(fā)此觸發(fā)器： insert：數(shù)據(jù)庫插入會觸發(fā)此觸發(fā)器； update：數(shù)據(jù)庫修改會觸發(fā)此觸發(fā)器； delete：數(shù)據(jù)庫刪除會觸發(fā)此觸發(fā)器。 4、表 名：數(shù)據(jù)庫觸發(fā)器所在的表。 5、 for each row：對表的每一行觸發(fā)器執(zhí)行一次。如果沒有這一

7、 選項(xiàng)，則只對整個表執(zhí)行一次。,ORACLE基礎(chǔ),五、觸發(fā)器 3、例子： 例子1：插入主鍵 CREATE OR REPLACE TRIGGER A_ BEFORE INSERT ON A FOR EACH ROW DECLARE BEGIN SELECT AA_SEQ.NEXTVAL INTO :NEW.A FROM DUAL; END A_;,ORACLE基礎(chǔ),例子2： CREATE OR REPLACE TRIGGER ADDB2B_GHDWUSER AFTER INSERT ON B2B_GHDWUSER FOR EACH ROW DECLARE V_MENUID B2B_GHJBM

8、ENU.MENUID%TYPE; _MENUID VARCHAR2(20); CURSOR INSERTUMENU IS SELECT A.MENUID FROM B2B_GHJBMENU A,XTGHDW B WHERE A.JNAM = B.JB AND B.DWBM= :NEW.GHDWBM; V_LXBM XTFBLX.LXBM%TYPE; CURSOR INSERTLXBM IS SELECT DISTINCT LXBM FROM XTFBLX;,ORACLE基礎(chǔ),BEGIN _MENUID:=1111111; OPEN INSERTUMENU; FE

9、TCH INSERTUMENU INTO V_MENUID,_MENUID; WHILE INSERTUMENU%FOUND LOOP INSERT INTO B2B_USERMENU (USERBM,MENUID) VALUES(:NEW.USERBM,V_MENUID); FETCH INSERTUMENU INTO V_MENUID; END LOOP; CLOSE INSERTUMENU; OPEN INSERTLXBM; FETCH INSERTLXBM INTO V_LXBM; WHILE INSERTLXBM%FOUND LOOP INSERT INT

10、O B2B_USERXX (USERBM,LXBM) VALUES(:NEW.USERBM,V_LXBM); FETCH INSERTLXBM INTO V_LXBM; END LOOP; CLOSE INSERTLXBM;,ORACLE基礎(chǔ),IF :OLD.SWBZ=2 AND :NEW.SWBZ = 0 THEN BEGIN SELECT TID,FUSED INTO L_ID,L_FUSED FROM RPC_CONTRAC WHERE HTBM = :NEW.HTBM AND HTLB 9; IF L_FUSED=1 THEN RAISE_APPLICATIO

11、N_ERROR(-20005,不能刪除!); END IF; DELETE RPC_CONTRACENTRY WHERE TID = L_ID; DELETE RPC_CONTRAC WHERE TID = L_ID; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END; END IF; END ADDB2B_GHDWUSER ;,ORACLE基礎(chǔ),例子2： CREATE OR REPLACE TRIGGER ADDB2B_GHDWUSER AFTER INSERT ON B2B_GHDWUSER FOR EACH ROW DECLAR

12、E V_MENUID B2B_GHJBMENU.MENUID%TYPE; _MENUID VARCHAR2(20); CURSOR INSERTUMENU IS SELECT A.MENUID FROM B2B_GHJBMENU A,XTGHDW B WHERE A.JNAM = B.JB AND B.DWBM= :NEW.GHDWBM; V_LXBM XTFBLX.LXBM%TYPE; CURSOR INSERTLXBM IS SELECT DISTINCT LXBM FROM XTFBLX;,ORACLE基礎(chǔ),循環(huán)1 i :=1; loop insert i

13、nto tb_zhaozhenlong(rpt_date ,dept_id,item,qty) values(to_date(2007-01-01,yyyy-MM-dd),D||i,I||i,round(i*100/3,3)); exit when i =10; i :=i+1; end loop;,ORACLE基礎(chǔ),循環(huán)2 i :=1; while i<=5 loop i :=i+1; end loop;,ORACLE基礎(chǔ),循環(huán)3 --如果指定了reverse選項(xiàng)，則循環(huán)控制變量會自動減1，否則自動加1 for j in reverse 1..10 loop end loo

14、p;,ORACLE基礎(chǔ),循環(huán)3 for x in 1..10 loop end loop;,ORACLE基礎(chǔ),例子2： CREATE OR REPLACE TRIGGER ADDB2B_GHDWUSER AFTER INSERT ON B2B_GHDWUSER FOR EACH ROW DECLARE V_MENUID B2B_GHJBMENU.MENUID%TYPE; _MENUID VARCHAR2(20); CURSOR INSERTUMENU IS SELECT A.MENUID FROM B2B_GHJBMENU A,XTGHDW B WHERE A.JNAM = B.

15、JB AND B.DWBM= :NEW.GHDWBM; V_LXBM XTFBLX.LXBM%TYPE; CURSOR INSERTLXBM IS SELECT DISTINCT LXBM FROM XTFBLX;,ORACLE基礎(chǔ),六、存儲過程 1、語法： 存儲過程： 例子1： CREATE OR REPLACE PROCEDURE P_BJHHTED -------輸入、輸出參數(shù) ( AS_USER VARCHAR2, AD_RQ1 DATE, AD_RQ2 DATE) AS,ORACLE基礎(chǔ),六、存儲過程 1、語法： --------------------定義變量 LS_G

16、W VARCHAR2(8); LS_SPR VARCHAR2(10); LS_KS VARCHAR2(4); LD_SL1 NUMBER(14,4); LD_JE1 NUMBER(14,4); LD_SL2 NUMBER(14,4); LD_JE2 NUMBER(14,4); LD_SL3 NUMBER(14,4);,ORACLE基礎(chǔ),六、存儲過程 1、語法： --------------------定義變量 LD_JE3 NUMBER(14,4); LD_SL4 NUMBER(14,4); LD_JE4 NUMBER(14,4); LD_SL5 NUMBER(14,4); LD_JE5 NU

17、MBER(14,4); LD_SL6 NUMBER(14,4); LD_JE6 NUMBER(14,4);,ORACLE基礎(chǔ),六、存儲過程 1、語法： ---------------------定義游標(biāo) CURSOR C1 IS SELECT HTGW,KSJC,SPR FROM BJHHTED WHERE ZDR=AS_USER FOR UPDATE;,ORACLE基礎(chǔ),六、存儲過程 1、語法： ---------------------開始執(zhí)行存儲過程 BEGIN -------------------刪除數(shù)據(jù) DELETE FROM BJHHTED WHERE ZDR=AS_USER;

18、,ORACLE基礎(chǔ),六、存儲過程 1、語法： -------------------插入數(shù)據(jù) INSERT INTO BJHHTED(ZDR,HTGW,KSJC,SPR) SELECT DISTINCT AS_USER,HTGW,NVL(SUBSTR(HTBH,6,2),%),SPRM FROM CGHT WHERE QDRQ=AD_RQ1 AND QDRQ<=AD_RQ2 AND LB=5 AND SWBZ =2;,ORACLE基礎(chǔ),六、存儲過程 1、語法： --------------------打開游標(biāo) OPEN C1; --------------------取出第一條數(shù)據(jù) FETCH

19、 C1 INTO LS_GW,LS_KS,LS_SPR; --------------------循環(huán)數(shù)據(jù) WHILE C1%FOUND LOOP,ORACLE基礎(chǔ),六、存儲過程 1、語法： ------------------查詢出數(shù)據(jù)賦給變量 SELECT COUNT(HTBM), SUM(SPJE) INTO LD_SL1,LD_JE1 FROM CGHT WHERE QDRQ=AD_RQ1 AND QDRQ<=AD_RQ2 AND LB=5 AND SWBZ=2 AND HTGW=LS_GW AND NVL(HTBH,%) LIKE %||LS_KS||% AND S

20、PRM=LS_SPR AND SPJE<=50000;,ORACLE基礎(chǔ),六、存儲過程 1、語法： ------------------更新表數(shù)據(jù) UPDATE BJHHTED SET FS1=LD_SL1,JE1=LD_JE1, FS2=LD_SL2,JE2=LD_JE2, FS3=LD_SL3,JE3=LD_JE3, FS4=LD_SL4,JE4=LD_JE4, FS5=LD_SL5,JE5=LD_JE5, FS6=LD_SL6,JE6=LD_JE6 WHERE ZDR=AS_USER AND HTGW=LS_GW AND KSJC=LS_KS AND SPR=

21、LS_SPR;,ORACLE基礎(chǔ),六、存儲過程 1、語法： ------------------取出下一條數(shù)據(jù) FETCH C1 INTO LS_GW,LS_KS,LS_SPR; --------------------結(jié)束循環(huán) END LOOP; --------------------關(guān)閉游標(biāo) CLOSE C1; ----------------------關(guān)閉存儲過程 END P_BJHHTED;,ORACLE基礎(chǔ),六、存儲過程 create or replace procedure aa1 is v_aa number; v_bb varchar2(20); message varc

22、har2(20);begin v_bb :=3s; begin v_aa :=to_number(v_bb); exception when others then message:=ddddddddddddd; end; end aa1;,ORACLE基礎(chǔ),六、存儲過程 2、JAVA調(diào)用存儲過程1：,ORACLE基礎(chǔ),六、存儲過程異常處理 3、 有兩種類型的異常，一種為內(nèi)部異常，一種為用戶自定義異常，內(nèi)部異常是執(zhí)行期間返回到PL/SQL塊的ORACLE錯誤或由PL/SQL代碼的某操作引起的錯誤，如除數(shù)為零或內(nèi)存溢出的情況。用戶自定義異常由開發(fā)者顯示定義，在PL/SQL塊中傳遞信息以控制對于

23、應(yīng)用的錯誤處理,ORACLE基礎(chǔ),六、存儲過程異常處理 3、對于預(yù)定義異常 ： no_data_found：select into 語句沒有符合條件的記錄返回too_many_rows：select into 語句符合條件的記錄有多條返回dup_val_on_index：對于數(shù)據(jù)庫表中的某一列，該列已經(jīng)被限制為唯一索引，程序試圖存儲兩個重復(fù)的值value_error：在轉(zhuǎn)換字符類型，截取或長度受限時，會發(fā)生該異常，如一個字符分配給一個變量，而該變量聲明的長度比該字符短，就會引發(fā)該異常storage_error：內(nèi)存溢出,ORACLE基礎(chǔ),六、存儲過程異常處理 3、 zero_divide：除數(shù)

24、為零case_not_found：對于選擇case語句，沒有與之相匹配的條件，同時，也沒有else語句捕獲其他的條件cursor_already_open：程序試圖打開一個已經(jīng)打開的游標(biāo)timeout_on_resource：系統(tǒng)在等待某一資源，時間超時,ORACLE基礎(chǔ),六、存儲過程異常處理 3、 系統(tǒng)異常： 如果要處理未命名的內(nèi)部異常，必須使用OTHERS異常處理器或PRAGMA EXCEPTION_INIT,ORACLE基礎(chǔ),六、存儲過程異常處理 3、預(yù)定義異常例子 create or replace procedure sdf isv_a varchar(10);begin Begin

25、 select aa.a into v_a from aa; Exception when no_data_found then insert into a(a) values(ssssssssssssssss); End;end sdf;,ORACLE基礎(chǔ),六、存儲過程異常處理 3、系統(tǒng)異常例子： create or replace procedure sdf isbegin Begin insert into aa(a) values(dddddddddddddddddd); Exception when OTHERS then insert into a(a) values

26、(dddddddddddddddddd); End;end sdf;,ORACLE基礎(chǔ),七、函數(shù) 函數(shù)： 與過程相似，遵循了相同的規(guī)則。 參數(shù)傳遞：只能帶有in參數(shù)，不能使用out, in out 參數(shù) 函數(shù)的主要特性是必須返回一個值。,ORACLE基礎(chǔ),七、函數(shù) 語法： CREATE OR REPLACE FUNCTION function_name (參數(shù)) RETURN datatype IS | AS PRAGMA AUTONOMOUS_TRANACTION; --聲明自主事務(wù)處理。 本地變量聲明 BEGIN 執(zhí)行語句部分 EXCEPTION 錯誤處理部分 ENDname; /,OR

27、ACLE基礎(chǔ),七、函數(shù) 函數(shù)例子： create or replace function f_cghtmx_jehz(a_htbm varchar2) return number is totl_spje number(14,4); begin select sum(spje) into totl_spje from cghtmx where htbm= a_htbm; exception when no_data_found then totl_spje := 0.0; return totl_spje; end;,ORACLE基礎(chǔ),八、程序包 是對PL/SQL類型，過程，函數(shù)，游標(biāo)，異常，

28、變量，常量的封裝。 包括兩部分：規(guī)范和主體 規(guī)范：是程序包的公共接口， 主體：規(guī)范的實(shí)現(xiàn)，以及私有例程、數(shù)據(jù)和變量。,ORACLE基礎(chǔ),八、程序包 語法： CREATE OR REPLACE PACKAGE package_name IS | AS 公用類型或變量常量的聲明； 公用過程或函數(shù)的聲明； END package_name; / CREATE OR REPLACE PACKAGE BODY package_name IS | AS 私有類型或變量常量的聲明； 公用過程或函數(shù)的實(shí)現(xiàn)； END package_name,ORACLE基礎(chǔ),八、程序包 規(guī)范： 規(guī)范是程序包的接口，規(guī)范中定義

29、的所有內(nèi)容都可以由調(diào)用者使用（當(dāng)然需要具有EXECUTE特權(quán)），比如規(guī)范中定義的過程函數(shù)可以被執(zhí)行，類型可以被訪問，變量可以被引用。 例子：使用兩個過程PRINT_ENAME() 和PRINT_SAL()，定義稱為EMPLOYEE_PKG的程序包。,ORACLE基礎(chǔ),八、程序包 CREATE OR REPLACE PACKAGE employee_pkg as Procedure print_ename(p_empno number); Procedure print_sal(p_empno number); End; /,ORACLE基礎(chǔ),八、程序包 CREATE OR REPLACE PA

30、CKAGE employee_pkg as Procedure print_ename(p_empno number); Procedure print_sal(p_empno number); End; / 并沒有為過程提供代碼，只是定義了名稱和參數(shù)。 這個時候如果試圖使用這個包，會報(bào)錯 exec employee_pkg.print_ename(1234);,ORACLE基礎(chǔ),八、程序包 主體： 程序包是過程，函數(shù)的具體實(shí)現(xiàn)部分，實(shí)現(xiàn)規(guī)范中定義的接口。 CREATE OR REPLACE PACKAGE BODY employee_pkg as Procedure print_ename(

31、p_empno number) is L_ename emp.ename%type; Begin Select ename into l_ename from emp where empno=p_empno; Dbms_output.put_line(l_ename); Exception When no_data_found then Dbms_output.put_line(Invalid employee number); End print_ename;,ORACLE基礎(chǔ),八、程序包 主體： 程序包是過程，函數(shù)的具體實(shí)現(xiàn)部分，實(shí)現(xiàn)規(guī)范中定義的接口。 Procedure print_sa

32、l(p_empno number) is L_sal emp.sal%type; Begin Select sal into l_sal from emp where empno=p_empno; Dbms_output.put_line(l_sal); Exception When NO_DATA_FOUND then Dbms_output.put_line(Invalid employee number); End print_sal; End employee_pkg; /,ORACLE基礎(chǔ),八、程序包 執(zhí)行： set serveroutput on exec employee_pkg

33、.print_ename(1234); exec employee_pkg.print_ename(7782); exec employee_pkg.print_sal(7782);,ORACLE基礎(chǔ),九、JOB begin sys.dbms_job.submit(job = :job, what = insert into a(a1)values(1111);, next_date = to_date(29-09-2009 15:09:06, dd-mm-yyyy hh24:mi:ss), interval = sysdate+1/5440); commit

34、;end;/,ORACLE基礎(chǔ),十、分區(qū) -- Create tablecreate table CGBJMX2( BJBM VARCHAR2(10) not null, WZBM VARCHAR2(20) not null, SL NUMBER(14,4), DJ NUMBER(14,4), YZF NUMBER(14,4), ZE NUMBER(14,4), JHQ DATE, YXQ DATE, BJSM VARCHAR2(60), BHSDJ NUMBER(14,4), BHSJE NUMBER(14,4), ZZSL NUMBER(14,4), PHSB VARCHAR2

35、(60), ZBDJ NUMBER(14,4), ZBYZF NUMBER(14,4), ZBBHSDJ NUMBER(14,4))partition by range (BJBM)( partition P_2003 values less than (2004) tablespace PSRM03 pctfree 10 initrans 1 maxtrans 255 storage ( initial 2320K minextents 1 maxextents unlimited ), partition P_2004 values less than (2005) tablespa

36、ce PSRM03 pctfree 10 initrans 1 maxtrans 255 storage ( initial 2320K minextents 1 maxextents unlimited ), partition P_2005 values less than (2006) tablespace PSRM03 pctfree 10 initrans 1 maxtrans 255 storage ( initial 2320K minextents 1 maxextents unlimited ), partition P_2006 values less than

37、 (2007) tablespace PSRM03 pctfree 10 initrans 1 maxtrans 255 storage ( initial 2320K minextents 1 maxextents unlimited ), partition P_2007 values less than (2008) tablespace PSRM03 pctfree 10 initrans 1 maxtrans 255 storage ( initial 2320K minextents 1 maxextents unlimited ), partition P_2008

38、values less than (2009) tablespace PSRM03 pctfree 10 initrans 1 maxtrans 255 storage ( initial 2320K minextents 1 maxextents unlimited ), partition P_2009 values less than (2010) tablespace PSRM03 pctfree 10 initrans 1 maxtrans 255 storage ( initial 2320K minextents 1 maxextents unlimited ));,

39、ORACLE優(yōu)化,一、優(yōu)化策略 為了保證Oracle數(shù)據(jù)庫運(yùn)行在最佳的性能狀態(tài)下，在信息系統(tǒng)開發(fā)之前就應(yīng)該考慮數(shù)據(jù)庫的優(yōu)化策略。優(yōu)化策略一般包括服務(wù)器操作系統(tǒng)參數(shù)調(diào)整、數(shù)據(jù)庫參數(shù)調(diào)整、網(wǎng)絡(luò)性能調(diào)整、應(yīng)用程序SQL語句分析及設(shè)計(jì)等幾個方面，其中應(yīng)用程序的分析與設(shè)計(jì)是在信息系統(tǒng)開發(fā),數(shù)據(jù)庫性能優(yōu)化包括如下五個部分： 實(shí)施工程師： 1. 調(diào)整操作系統(tǒng)參數(shù) 例如：運(yùn)行在Unix操作系統(tǒng)上的 Oracle數(shù)據(jù)庫，可以調(diào)整 Unix數(shù)據(jù)緩沖區(qū)的大小、每個進(jìn)程所能使用的內(nèi)存大小等參數(shù)。,ORACLE優(yōu)化,實(shí)施工程師： 2. 調(diào)整硬盤I/O 這一步是在信息系統(tǒng)開發(fā)之前完成的。數(shù)據(jù)庫管理員可以將組成同

40、一個表空間的數(shù)據(jù)文件放在不同的硬盤上，做到硬盤之間I/O 負(fù)載均衡。,ORACLE優(yōu)化,實(shí)施工程師、開發(fā)工程師： 3. 調(diào)整服務(wù)器內(nèi)存分配 內(nèi)存分配是在信息系統(tǒng)運(yùn)行過程中優(yōu)化配置的。數(shù)據(jù)庫管理員根據(jù)數(shù)據(jù)庫的運(yùn)行狀況不僅可以調(diào)整數(shù)據(jù)庫系統(tǒng)全局區(qū)（SGA區(qū)）的數(shù)據(jù)緩沖區(qū)、日志緩沖區(qū)和共享池的大小，而且還可以調(diào)整程序全局區(qū)（PGA區(qū)）的大小。,ORACLE優(yōu)化,開發(fā)工程師： 4. 調(diào)整數(shù)據(jù)結(jié)構(gòu)的設(shè)計(jì) 這一部分在開發(fā)信息系統(tǒng)之前完成，程序員需要考慮是否使用Oracle數(shù)據(jù)庫的分區(qū)功能，對于經(jīng)常訪問的數(shù)據(jù)庫表是否需要建立索引等。,ORACLE優(yōu)化,開發(fā)工程師： 5. 調(diào)整數(shù)據(jù)庫SQL語句 應(yīng)

41、用程序的執(zhí)行最終將歸結(jié)為數(shù)據(jù)庫中的SQL語句執(zhí)行，因此SQL語句的執(zhí)行效率最終決定了Oracle數(shù)據(jù)庫的性能。 Oracle公司推薦使用Oracle語句優(yōu)化器（Oracle Optimizer）和行鎖管理器（Row-Level Manager）來調(diào)整優(yōu)化SQL語句。,ORACLE優(yōu)化,ORACLE優(yōu)化,實(shí)施工程師、開發(fā)工程師： 3. 調(diào)整服務(wù)器內(nèi)存分配,圖1,3.1、SGA內(nèi)存區(qū)結(jié)構(gòu) SGA就是系統(tǒng)全局區(qū)，是指內(nèi)存中允許多個進(jìn)程相互通信的區(qū)域。在Oracle中，SGA對所有進(jìn)程來說都是全局的可用的。圖1為SGA結(jié)構(gòu)圖。,ORACLE優(yōu)化,3.1.1、共享池 專有模式--是SGA中的保存著關(guān)于

42、待執(zhí)行的SQL語句的信息。他由兩部分組成：數(shù)據(jù)字典高速緩存，存放從數(shù)據(jù)字典中讀取的信息以用于處理SQL請求；庫高速緩存，存放需要執(zhí)行的SQL語句信息，包括每個SQL語句的語法分析樹和執(zhí)行計(jì)劃。如果多個用戶要執(zhí)行同樣的SQL語句，那么語法分析樹和執(zhí)行計(jì)劃就可以重復(fù)利用，省去了語法分析步驟的昂貴花費(fèi)。 共享模式--數(shù)據(jù)字典高速緩存。,ORACLE優(yōu)化,3.1.2、緩沖區(qū)高速緩存 共享模式、專有模式--是SGA中為所有用戶和系統(tǒng)進(jìn)程保存數(shù)據(jù)的區(qū)域，任何數(shù)據(jù)在傳遞給一個調(diào)用的應(yīng)域是共享的，所以多個進(jìn)程可以從這片高速緩存讀取同樣的數(shù)據(jù)塊，而不必每次都從物理磁盤中讀取。,ORACLE優(yōu)化,3.1.3、

43、大型池： 共享模式--。這部分主要用來保 存并行查詢時候的一些信息，還有就是RMAN 在備份的時候可能會使用到。如果設(shè)置了 MTS，則由于UGA部分要移入這里，則需要具體根據(jù)server process數(shù)量和相關(guān)會話內(nèi)存參 數(shù)的設(shè)置來綜合考慮這部分大小的設(shè)置。,ORACLE優(yōu)化,3.1.4、java池: 那是為滿足在ORACLE中內(nèi)嵌JAVA存儲過程或其他JAVA程序（例如CORBA中間件）運(yùn)行時而需要的內(nèi)存，如果不用JAVA等程序，就無須設(shè)置，使其值為0就可以了,ORACLE優(yōu)化,3.2、PGA: 包含單個服務(wù)器進(jìn)程或單個后臺進(jìn)程的數(shù)據(jù)和控制信息，與幾個進(jìn)程共享的SGA 正相反,PGA

44、 是只被一個進(jìn)程使用的區(qū)域，PGA 在創(chuàng)建進(jìn)程時分配,在終止進(jìn)程時回收.（客戶專有的數(shù)據(jù)結(jié)果）,ORACLE優(yōu)化,3.3、調(diào)整SGA結(jié)構(gòu) 一般來講，在系統(tǒng)硬件支持的情況下，系統(tǒng)全局區(qū)越大越有利于數(shù)據(jù)庫高效的運(yùn)行。大的緩沖區(qū)高速緩存可以緩存更多的數(shù)據(jù)塊，這樣可以提高緩存命中率，節(jié)省物理磁盤讀取的高昂代價；大的共享池意味著大的庫高速緩存。,ORACLE優(yōu)化,庫緩存越大，可以保存的SQL語法分析信息越多；此外，數(shù)據(jù)庫中的一些對象，如表、索引、過程、觸發(fā)器、軟件包等也在首次執(zhí)行后進(jìn)駐庫高速緩存。大的庫緩存可以保證對這些對象的高命中率，從而節(jié)省解析和載入代價。,ORACLE優(yōu)化,開發(fā)工程師： 5. 調(diào)整

45、數(shù)據(jù)庫SQL語句 應(yīng)用程序的執(zhí)行最終將歸結(jié)為數(shù)據(jù)庫中的 語句執(zhí)行，SQL語句消耗了-的數(shù)據(jù)庫資源。因此 語句的執(zhí)行效率最終決定了ORACLE數(shù)據(jù)庫的性能。許多程序員認(rèn)為查詢優(yōu)化是DBMS（數(shù)據(jù)庫管理系統(tǒng)）的任務(wù)，與程序員所編寫的語句關(guān)系不大，這是錯誤的。,ORACLE優(yōu)化,一個好的查詢計(jì)劃往往可以使程序性能提高數(shù)十倍。另外，SQL語句獨(dú)立于程序設(shè)計(jì)邏輯，相對于對程序源代碼的優(yōu)化，對語句的優(yōu)化在時間成本和風(fēng)險上的代價都很低。優(yōu)化的主要途徑是：,ORACLE優(yōu)化,5.1、有效索引的建立。(列值重復(fù)率低) 5.1.1、在經(jīng)常進(jìn)行連接，但是沒有指定為外鍵的列上建立索引； XTWZBM CGHTMX

46、MX 測試時間： 均取第一次執(zhí)行 和第二次以后三次平均時間,ORACLE優(yōu)化,B-TREE,ORACLE優(yōu)化,BITMAP,ORACLE優(yōu)化,例： 在XTWZBM表WZMC未建索引：1.07s---0.81s 在XTWZBM表WZMC建索引：0.26s---0.09s SELECT B.* FROM XTWZBM A, CGHTMXMX B WHERE A.WZBM = B.WZBM AND A.WZMC = 浮筒式液位變送器,ORACLE優(yōu)化,5.1.2、在頻繁進(jìn)行排序的列上建立索引； 例： 在XTWZBM表WZMC未建索引：11.1s---11.1s 在XTWZBM表WZMC建索引：0.5

47、3s---0.14s SELECT A.*FROM XTWZBM AORDER BY A.WZMC,ORACLE優(yōu)化,5.1.3、建議在分組的列上建立索引； 例： 在XTWZBM表WZMC未建索引：2.9s---2.5s 在XTWZBM表WZMC建索引：0.13s---0.06s SELECT A.WZMC,COUNT(*)FROM XTWZBM AGROUP BY A.WZMC,ORACLE優(yōu)化,5.1.4、 IS NULL 不能用null作索引，任何包含null值的列都將不會被包含在索引中。即使索引有多列的情況下，只要這些列中有一列含有null，該列就會從索引中排除。也就是說如果某列存

48、在空值，即使對該列建索引也不會提高性能。 任何在where子句中使用is null的語句優(yōu)化器是不允許使用索引的。,ORACLE優(yōu)化,例： 時間：2.438s---2.453s SELECT T.* FROM XTWZBM T WHERE T.WZMC IS NULL,ORACLE優(yōu)化,5.1.4、索引列不要加函數(shù)處理： 例：時間：0.078s---0.047s SELECT COUNT(*) FROM XTWZBM T WHERE T.GGXH = 4-72-11-6D AND T.CZ = 河北華瑞玻璃鋼有限公司;,ORACLE優(yōu)化,時間：2.359s---2.453s SELECT

49、COUNT(*) FROM XTWZBM T WHERE T.GGXH || T.CZ LIKE 4-72-11-6D || 河北華瑞玻璃鋼有限公司;,ORACLE優(yōu)化,5.2、SELECT優(yōu)化 5.2.1、SELECT子句中避免使用 * 當(dāng)你想在SELECT子句中列出所有的COLUMN時，使用動態(tài)SQL列引用 * 是一個方便的方法。不幸的是，這是一個非常低效的方法。 實(shí)際上，ORACLE在解析的過程中， 會將* 依次轉(zhuǎn)換成所有的列名， 這個工作是通過查詢數(shù)據(jù)字典完成的， 這意味著將耗費(fèi)更多的時間。,ORACLE優(yōu)化,當(dāng)全表查詢時：時間基本相當(dāng) 時間：0.141---0.125 SELE

50、CT * FROM XTWZBM; 時間：0.141---0.125 SELECT WZBM, WZMC, GGXH, CZ, JLXZ, JLDW, JLXZ2, JLDW2, XS, BC, TC, ZJBZ, SBBZ, SBBM, YHBM, RQ, ZGCB, ZDCB, CBRQ, XBZ, NOUSE, XWZBM, YHMC, BZH, YWBM FROM XTWZBM;,ORACLE優(yōu)化,列數(shù)減少時 時間：0.078s---0.078s SELECT WZBM, WZMC, GGXH, CZ FROM XTWZBM; 建議盡可能列出列名,ORACLE優(yōu)化,5.2.2、SELE

51、CT子句中避免使用子查詢 時間：0.14s---0.125s SELECT B.*,(SELECT A.WZMC FROM XTWZBM A WHERE A.WZBM=B.WZBM)FROM CGHTMXMX B; 時間：0.125s---0.109s SELECT B.*,A.WZMC FROM XTWZBM A,CGHTMXMX B WHERE A.WZBM(+)=B.WZBM,ORACLE優(yōu)化,5.2.3、COUNT(*) 、COUNT(1)對比速度差不多 時間：1.516s---1.422s SELECT COUNT(1) FROM XTWZBM; 時間：1.5s---1.438s S

52、ELECT COUNT(*) FROM XTWZBM;,ORACLE優(yōu)化,5.3、FROM優(yōu)化 5.3.1、選擇最有效率的表名順序（只在基于規(guī)則的優(yōu)化器中有效） ORACLE的解析器按照從右到左的順序處理FROM子句中的表名，因此FROM子句中寫在最后的表（基礎(chǔ)表 driving table）將被最先處理。 在FROM子句中包含多個表的情況下，你必須選擇記錄條數(shù)最少的表作為基礎(chǔ)表。,ORACLE優(yōu)化,當(dāng)ORACLE處理多個表時， 會運(yùn)用排序及合并的方式連接它們。首先，掃描第一個表（FROM子句中最后的那個表）并對記錄進(jìn)行排序，然后掃描第二個表（FROM子句中最后第二個表），最后將所有從第二

53、個表中檢索出的記錄與第一個表中合適記錄進(jìn)行合并。,ORACLE優(yōu)化,表數(shù)據(jù)量小的排在后面：測試數(shù)據(jù)時間相差不多 時間： 0.125 SELECT * FROM AA T,XTWZBM B WHERE T.WZBM=B.WZBM AND T.WZBM=010902000430002002; 時間：0.125 SELECT * FROM XTWZBM B,AA T WHERE T.WZBM=B.WZBM AND T.WZBM=010902000430002002,ORACLE優(yōu)化,5.4、WHERE優(yōu)化 5.4.1、ORACLE采用自下而上的順序解析WHERE子句，根據(jù)這個原理，表之間的連接必須寫

54、在其他WHERE條件之前， 那些可以過濾掉最大數(shù)量記錄的條件必須寫在WHERE子句的末尾。,ORACLE優(yōu)化,SELECT COUNT(*) FROM XTWZBM A WHERE A.WZBM LIKE %2; 數(shù)據(jù)量：267400 SELECT COUNT(*) FROM XTWZBM A WHERE A.WZBM LIKE 01%; 數(shù)據(jù)量：801,ORACLE優(yōu)化,時間：0.672s---0.656s SELECT COUNT(*) FROM XTWZBM A WHERE A.WZBM LIKE 01% OR A.WZBM LIKE %2;,ORACLE優(yōu)化,時間：0.594s---0

55、.562s SELECT COUNT(*) FROM XTWZBM A WHERE A.WZBM LIKE %2 OR A.WZBM LIKE 01%;,ORACLE優(yōu)化,5.4.2、用EXISTS替代IN 時間： 0.828s---0.781s SELECT COUNT(*) FROM CGHTMXMX A WHERE A.WZBM IN (SELECT B.WZBM FROM XTWZBM B WHERE B.WZBM LIKE 0% OR B.WZBM LIKE 1% OR B.WZBM LIKE 2% OR B.WZBM LIKE 3%

56、 OR B.WZBM LIKE 4% OR B.WZBM LIKE 5%);,ORACLE優(yōu)化,時間：0.844s---0.781s SELECT COUNT(*) FROM CGHTMXMX A WHERE EXISTS (SELECT B.WZBM FROM XTWZBM B WHERE A.WZBM = B.WZBM AND (B.WZBM LIKE 0% OR B.WZBM LIKE 1% OR B.WZBM LIKE 2% OR B.WZBM LIKE 3% OR B.WZBM LIKE 4% OR

57、 B.WZBM LIKE 5%)) 速度差不多,ORACLE優(yōu)化,5.4.3、用NOT EXISTS替代NOT IN ： 時間： 0.859s---0.718s SELECT COUNT(*) FROM CGHTMXMX A WHERE A.WZBM NOT IN (SELECT B.WZBM FROM XTWZBM B WHERE B.WZBM LIKE 0% OR B.WZBM LIKE 1% OR B.WZBM LIKE 2% OR B.WZBM LIKE 3% OR B.WZBM LIKE 4% OR B.WZBM LI

58、KE 5%);,ORACLE優(yōu)化,時間： 0.422s---0.375s SELECT COUNT(*) FROM CGHTMXMX A WHERE NOT EXISTS (SELECT B.WZBM FROM XTWZBM B WHERE A.WZBM = B.WZBM AND (B.WZBM LIKE 0% OR B.WZBM LIKE 1% OR B.WZBM LIKE 2% OR B.WZBM LIKE 3% OR B.WZBM LIKE 4% OR B.WZBM LIKE 5%)) 速度相差較大,ORACLE優(yōu)化,

59、5.4.4、用表連接替換EXISTS ： 時間：無法查出數(shù)據(jù) SELECT COUNT(*) FROM CGHTMXMX A WHERE EXISTS (SELECT B.WZBM FROM XTWZBM B WHERE A.WZBM = B.WZBM AND (B.WZBM LIKE 0% OR B.WZBM LIKE 1% OR B.WZBM LIKE 2% OR B.WZBM LIKE 3% OR B.WZBM LIKE 4% OR B.WZBM LIKE 5% OR B.WZBM LIKE 6%)),ORACLE優(yōu)化,時間：0.891s--

60、-0.938s SELECT COUNT(*) FROM CGHTMXMX A, (SELECT B.WZBM FROM XTWZBM B WHERE B.WZBM LIKE 0% OR B.WZBM LIKE 1% OR B.WZBM LIKE 2% OR B.WZBM LIKE 3% OR B.WZBM LIKE 4% OR B.WZBM LIKE 5% OR B.WZBM LIKE 6%) S WHERE A.WZBM = S.WZBM; 根據(jù)以上測試結(jié)果建議： 1、不用exists用表 2、不用not in用not

61、 exists,ORACLE優(yōu)化,5.4.5、避免在索引列上使用計(jì)算。 未索引時間：2.062s---1.719s SELECT COUNT(*) FROM XTWZBM T WHERE T.WZBM 200000*1000; 未索引時間：2.156s---2.047s SELECT COUNT(*) FROM XTWZBM T WHERE T.WZBM/1000 200000;,ORACLE優(yōu)化,索引時間：0.381s---0.265s SELECT COUNT(*) FROM XTWZBM T WHERE T.WZBM 200000*1000; 索引時間：0.5s---0.453s SEL

62、ECT COUNT(*) FROM XTWZBM T WHERE T.WZBM/1000 200000; 建議：盡可能按照規(guī)則建索引,ORACLE優(yōu)化,5.4.6、用=4替代 3 兩者的區(qū)別在于， 前者DBMS將直接跳到第一個等于4的記錄而后者將首先定位到等于3的記錄并且向前掃描到第一個大于3的記錄。 效率不明顯！?。?ORACLE優(yōu)化,5.4.7、用UNION替換OR （適用于索引列） 通常情況下， 用UNION替換WHERE子句中的OR將會起到較好的效果。 對索引列使用OR將造成全表掃描。注意， 以上規(guī)則只針對多個索引列有效。 如果有column沒有被索引， 查詢效率可能會因?yàn)槟銢]有選

63、擇OR而降低。,ORACLE優(yōu)化,時間：3.829s---4.414s SELECT COUNT(*) FROM XTWZBM A WHERE A.WZBM LIKE %2 OR A.WZMC LIKE %鐵%;,ORACLE優(yōu)化,時間：3.062s---3.108s SELECT COUNT(*) FROM( SELECT WZBM FROM XTWZBM A WHERE A.WZBM LIKE %2 UNION SELECT WZBM FROM XTWZBM A WHERE A.WZMC LIKE %鐵% ),ORACLE優(yōu)化,5.4.8、如果非用OR （適用于索引列） 那就需要返回記錄最

64、少的索引列寫在最前面。 時間：3.312s---3.234s SELECT COUNT(*) FROM XTWZBM A WHERE A.WZMC LIKE %鐵% OR A.WZBM LIKE %2;,ORACLE優(yōu)化,5.5、其他 5.5.1、用UNION-ALL 替換UNION （ 如果有可能的話） 當(dāng)SQL語句需要UNION兩個查詢結(jié)果集合時， 這兩個結(jié)果集合會以UNION-ALL的方式被合并， 然后在輸出最終結(jié)果前進(jìn)行排序。 如果用UNION ALL替代UNION， 這樣排序就不是必要了。 效率就會因此得到提高。 UNION ALL ：輸出結(jié)果可能有相同記錄。 UNION ：輸出結(jié)果

65、剔出相同記錄。,ORACLE優(yōu)化,時間：2.234s---2.453s SELECT COUNT(*) FROM(SELECT WZBM FROM XTWZBMUNION SELECT WZBM FROM XTWZBM_BAK);,ORACLE優(yōu)化,時間：0.688s---0.719s SELECT COUNT(*) FROM(SELECT WZBM FROM XTWZBMUNION ALLSELECT WZBM FROM XTWZBM_BAK),ORACLE優(yōu)化,二、Oracle優(yōu)化器的優(yōu)化方式和優(yōu)化模式 : 待細(xì)化 1、優(yōu)化器的優(yōu)化方式 Oracle的優(yōu)化器共有兩種的優(yōu)化方式,即基于

66、規(guī)則的優(yōu)化方式(Rule-Based Optimization,簡稱為RBO)和基于代價的優(yōu)化方式(Cost-Based Optimization,簡稱為CBO)。,ORACLE優(yōu)化,1.1、RBO方式：優(yōu)化器在分析SQL語句時,所遵循的是Oracle內(nèi)部預(yù)定的一些規(guī)則。比如我們常見的,當(dāng)一個where子句中的一列有索引時去走索引。,ORACLE優(yōu)化,1.2、CBO方式：依詞義可知,它是看語句的代價(Cost)了,這里的代價主要指Cpu和內(nèi)存。優(yōu)化器在判斷是否用這種方式時,主要參照的是表及索引的統(tǒng)計(jì)信息。統(tǒng)計(jì)信息給出表的大小 、有少行、每行的長度等信息。這些統(tǒng)計(jì)信息起初在庫內(nèi)是沒有的,是你在做analyze后才出現(xiàn)的,很多的時侯過期統(tǒng)計(jì)信息會令優(yōu)化器做出一個錯誤的執(zhí)行計(jì)劃,因些我們應(yīng)及時更新這些信息。在Oracle8及以后的版本,Oracle列推薦用CBO的方式。,ORACLE優(yōu)化,2、優(yōu)化器的優(yōu)化模式(Optermizer Mode) 優(yōu)化模式包括Rule,Choose,First rows,All rows這四種方式,ORACLE優(yōu)化,2.1、Rule: 即走基于規(guī)則的方式。,