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

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

1、ORACLE基礎(chǔ),一、基本查詢語(yǔ)句 SELECT FROM WHERE GROUP BY HAVING ORDER BY,ORACLE基礎(chǔ),二、函數(shù) 1、處理null值 NVL(M,N) 2、字符串連接 || 3、查找子串位置 INSTR(原串,子串,從第n個(gè)字符開始) 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(字段，第一個(gè)判斷值，第一個(gè)結(jié)果，第二個(gè)判斷值，第二個(gè)結(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)的時(shí)候，自動(dòng)執(zhí)行的代碼塊。類似于存儲(chǔ)過程，但是用戶不能直接調(diào)用他們。 ORACLE觸發(fā)器語(yǔ)法 觸發(fā)器有

5、兩種after和before，區(qū)別在于每次提交事務(wù)觸發(fā)一次和每一行數(shù)據(jù)的更新都觸發(fā)一次,ORACLE基礎(chǔ),五、觸發(fā)器 2、語(yǔ)法： ORACLE產(chǎn)生數(shù)據(jù)庫(kù)觸發(fā)器的語(yǔ)法為： create or replace trigger 觸發(fā)器名 觸發(fā)時(shí)間 觸發(fā)事件 on 表名 for each row pl/sql 語(yǔ)句,ORACLE基礎(chǔ),五、觸發(fā)器 2、語(yǔ)法： 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、語(yǔ)法： 1）、觸發(fā)器名：觸發(fā)器對(duì)象的名稱。 2）、觸發(fā)時(shí)間：指明觸發(fā)器何時(shí)執(zhí)行，該值可取， before---表示在數(shù)據(jù)庫(kù)動(dòng)作之前觸發(fā)器執(zhí)行； after---表示在數(shù)據(jù)庫(kù)動(dòng)作之后出發(fā)器執(zhí)行。,ORACLE基礎(chǔ),五、觸發(fā)器 2、語(yǔ)法： 3、觸發(fā)事件：指明哪些數(shù)據(jù)庫(kù)動(dòng)作會(huì)觸發(fā)此觸發(fā)器： insert：數(shù)據(jù)庫(kù)插入會(huì)觸發(fā)此觸發(fā)器； update：數(shù)據(jù)庫(kù)修改會(huì)觸發(fā)此觸發(fā)器； delete：數(shù)據(jù)庫(kù)刪除會(huì)觸發(fā)此觸發(fā)器。 4、表 名：數(shù)據(jù)庫(kù)觸發(fā)器所在的表。 5、 for each row：對(duì)表的每一行觸發(fā)器執(zhí)行一次。如果沒有這一

7、 選項(xiàng)，則只對(duì)整個(gè)表執(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)控制變量會(huì)自動(dòng)減1，否則自動(dòng)加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ǔ),六、存儲(chǔ)過程 1、語(yǔ)法： 存儲(chǔ)過程： 例子1： CREATE OR REPLACE PROCEDURE P_BJHHTED -------輸入、輸出參數(shù) ( AS_USER VARCHAR2, AD_RQ1 DATE, AD_RQ2 DATE) AS,ORACLE基礎(chǔ),六、存儲(chǔ)過程 1、語(yǔ)法： --------------------定義變量 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ǔ),六、存儲(chǔ)過程 1、語(yǔ)法： --------------------定義變量 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ǔ),六、存儲(chǔ)過程 1、語(yǔ)法： ---------------------定義游標(biāo) CURSOR C1 IS SELECT HTGW,KSJC,SPR FROM BJHHTED WHERE ZDR=AS_USER FOR UPDATE;,ORACLE基礎(chǔ),六、存儲(chǔ)過程 1、語(yǔ)法： ---------------------開始執(zhí)行存儲(chǔ)過程 BEGIN -------------------刪除數(shù)據(jù) DELETE FROM BJHHTED WHERE ZDR=AS_USER;

18、,ORACLE基礎(chǔ),六、存儲(chǔ)過程 1、語(yǔ)法： -------------------插入數(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ǔ),六、存儲(chǔ)過程 1、語(yǔ)法： --------------------打開游標(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ǔ),六、存儲(chǔ)過程 1、語(yǔ)法： ------------------查詢出數(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ǔ),六、存儲(chǔ)過程 1、語(yǔ)法： ------------------更新表數(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ǔ),六、存儲(chǔ)過程 1、語(yǔ)法： ------------------取出下一條數(shù)據(jù) FETCH C1 INTO LS_GW,LS_KS,LS_SPR; --------------------結(jié)束循環(huán) END LOOP; --------------------關(guān)閉游標(biāo) CLOSE C1; ----------------------關(guān)閉存儲(chǔ)過程 END P_BJHHTED;,ORACLE基礎(chǔ),六、存儲(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ǔ),六、存儲(chǔ)過程 2、JAVA調(diào)用存儲(chǔ)過程1：,ORACLE基礎(chǔ),六、存儲(chǔ)過程異常處理 3、 有兩種類型的異常，一種為內(nèi)部異常，一種為用戶自定義異常，內(nèi)部異常是執(zhí)行期間返回到PL/SQL塊的ORACLE錯(cuò)誤或由PL/SQL代碼的某操作引起的錯(cuò)誤，如除數(shù)為零或內(nèi)存溢出的情況。用戶自定義異常由開發(fā)者顯示定義，在PL/SQL塊中傳遞信息以控制對(duì)于

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

24、為零case_not_found：對(duì)于選擇case語(yǔ)句，沒有與之相匹配的條件，同時(shí)，也沒有else語(yǔ)句捕獲其他的條件cursor_already_open：程序試圖打開一個(gè)已經(jīng)打開的游標(biāo)timeout_on_resource：系統(tǒng)在等待某一資源，時(shí)間超時(shí),ORACLE基礎(chǔ),六、存儲(chǔ)過程異常處理 3、 系統(tǒng)異常： 如果要處理未命名的內(nèi)部異常，必須使用OTHERS異常處理器或PRAGMA EXCEPTION_INIT,ORACLE基礎(chǔ),六、存儲(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ǔ),六、存儲(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ù)的主要特性是必須返回一個(gè)值。,ORACLE基礎(chǔ),七、函數(shù) 語(yǔ)法： CREATE OR REPLACE FUNCTION function_name (參數(shù)) RETURN datatype IS | AS PRAGMA AUTONOMOUS_TRANACTION; --聲明自主事務(wù)處理。 本地變量聲明 BEGIN 執(zhí)行語(yǔ)句部分 EXCEPTION 錯(cuò)誤處理部分 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ǔ),八、程序包 是對(duì)PL/SQL類型，過程，函數(shù)，游標(biāo)，異常，

28、變量，常量的封裝。 包括兩部分：規(guī)范和主體 規(guī)范：是程序包的公共接口， 主體：規(guī)范的實(shí)現(xiàn)，以及私有例程、數(shù)據(jù)和變量。,ORACLE基礎(chǔ),八、程序包 語(yǔ)法： 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í)行，類型可以被訪問，變量可以被引用。 例子：使用兩個(gè)過程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ù)。 這個(gè)時(shí)候如果試圖使用這個(gè)包，會(huì)報(bào)錯(cuò) 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ù)庫(kù)運(yùn)行在最佳的性能狀態(tài)下，在信息系統(tǒng)開發(fā)之前就應(yīng)該考慮數(shù)據(jù)庫(kù)的優(yōu)化策略。優(yōu)化策略一般包括服務(wù)器操作系統(tǒng)參數(shù)調(diào)整、數(shù)據(jù)庫(kù)參數(shù)調(diào)整、網(wǎng)絡(luò)性能調(diào)整、應(yīng)用程序SQL語(yǔ)句分析及設(shè)計(jì)等幾個(gè)方面，其中應(yīng)用程序的分析與設(shè)計(jì)是在信息系統(tǒng)開發(fā),數(shù)據(jù)庫(kù)性能優(yōu)化包括如下五個(gè)部分： 實(shí)施工程師： 1. 調(diào)整操作系統(tǒng)參數(shù) 例如：運(yùn)行在Unix操作系統(tǒng)上的 Oracle數(shù)據(jù)庫(kù)，可以調(diào)整 Unix數(shù)據(jù)緩沖區(qū)的大小、每個(gè)進(jìn)程所能使用的內(nèi)存大小等參數(shù)。,ORACLE優(yōu)化,實(shí)施工程師： 2. 調(diào)整硬盤I/O 這一步是在信息系統(tǒng)開發(fā)之前完成的。數(shù)據(jù)庫(kù)管理員可以將組成同

40、一個(gè)表空間的數(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ù)庫(kù)管理員根據(jù)數(shù)據(jù)庫(kù)的運(yùn)行狀況不僅可以調(diào)整數(shù)據(jù)庫(kù)系統(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ù)庫(kù)的分區(qū)功能，對(duì)于經(jīng)常訪問的數(shù)據(jù)庫(kù)表是否需要建立索引等。,ORACLE優(yōu)化,開發(fā)工程師： 5. 調(diào)整數(shù)據(jù)庫(kù)SQL語(yǔ)句 應(yīng)

41、用程序的執(zhí)行最終將歸結(jié)為數(shù)據(jù)庫(kù)中的SQL語(yǔ)句執(zhí)行，因此SQL語(yǔ)句的執(zhí)行效率最終決定了Oracle數(shù)據(jù)庫(kù)的性能。 Oracle公司推薦使用Oracle語(yǔ)句優(yōu)化器（Oracle Optimizer）和行鎖管理器（Row-Level Manager）來調(diào)整優(yōu)化SQL語(yǔ)句。,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)存中允許多個(gè)進(jìn)程相互通信的區(qū)域。在Oracle中，SGA對(duì)所有進(jìn)程來說都是全局的可用的。圖1為SGA結(jié)構(gòu)圖。,ORACLE優(yōu)化,3.1.1、共享池 專有模式--是SGA中的保存著關(guān)于

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

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

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

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

46、MX 測(cè)試時(shí)間： 均取第一次執(zhí)行 和第二次以后三次平均時(shí)間,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值的列都將不會(huì)被包含在索引中。即使索引有多列的情況下，只要這些列中有一列含有null，該列就會(huì)從索引中排除。也就是說如果某列存

48、在空值，即使對(duì)該列建索引也不會(huì)提高性能。 任何在where子句中使用is null的語(yǔ)句優(yōu)化器是不允許使用索引的。,ORACLE優(yōu)化,例： 時(shí)間：2.438s---2.453s SELECT T.* FROM XTWZBM T WHERE T.WZMC IS NULL,ORACLE優(yōu)化,5.1.4、索引列不要加函數(shù)處理： 例：時(shí)間：0.078s---0.047s SELECT COUNT(*) FROM XTWZBM T WHERE T.GGXH = 4-72-11-6D AND T.CZ = 河北華瑞玻璃鋼有限公司;,ORACLE優(yōu)化,時(shí)間：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時(shí)，使用動(dòng)態(tài)SQL列引用 * 是一個(gè)方便的方法。不幸的是，這是一個(gè)非常低效的方法。 實(shí)際上，ORACLE在解析的過程中， 會(huì)將* 依次轉(zhuǎn)換成所有的列名， 這個(gè)工作是通過查詢數(shù)據(jù)字典完成的， 這意味著將耗費(fèi)更多的時(shí)間。,ORACLE優(yōu)化,當(dāng)全表查詢時(shí)：時(shí)間基本相當(dāng) 時(shí)間：0.141---0.125 SELE

50、CT * FROM XTWZBM; 時(shí)間：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ù)減少時(shí) 時(shí)間：0.078s---0.078s SELECT WZBM, WZMC, GGXH, CZ FROM XTWZBM; 建議盡可能列出列名,ORACLE優(yōu)化,5.2.2、SELE

51、CT子句中避免使用子查詢 時(shí)間：0.14s---0.125s SELECT B.*,(SELECT A.WZMC FROM XTWZBM A WHERE A.WZBM=B.WZBM)FROM CGHTMXMX B; 時(shí)間：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)對(duì)比速度差不多 時(shí)間：1.516s---1.422s SELECT COUNT(1) FROM XTWZBM; 時(shí)間：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子句中包含多個(gè)表的情況下，你必須選擇記錄條數(shù)最少的表作為基礎(chǔ)表。,ORACLE優(yōu)化,當(dāng)ORACLE處理多個(gè)表時(shí)， 會(huì)運(yùn)用排序及合并的方式連接它們。首先，掃描第一個(gè)表（FROM子句中最后的那個(gè)表）并對(duì)記錄進(jìn)行排序，然后掃描第二個(gè)表（FROM子句中最后第二個(gè)表），最后將所有從第二

53、個(gè)表中檢索出的記錄與第一個(gè)表中合適記錄進(jìn)行合并。,ORACLE優(yōu)化,表數(shù)據(jù)量小的排在后面：測(cè)試數(shù)據(jù)時(shí)間相差不多 時(shí)間： 0.125 SELECT * FROM AA T,XTWZBM B WHERE T.WZBM=B.WZBM AND T.WZBM=010902000430002002; 時(shí)間：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ù)這個(gè)原理，表之間的連接必須寫

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)化,時(shí)間：0.672s---0.656s SELECT COUNT(*) FROM XTWZBM A WHERE A.WZBM LIKE 01% OR A.WZBM LIKE %2;,ORACLE優(yōu)化,時(shí)間：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 時(shí)間： 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)化,時(shí)間：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 ： 時(shí)間： 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)化,時(shí)間： 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í)間：無(wú)法查出數(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)化,時(shí)間：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ù)以上測(cè)試結(jié)果建議： 1、不用exists用表 2、不用not in用not

61、 exists,ORACLE優(yōu)化,5.4.5、避免在索引列上使用計(jì)算。 未索引時(shí)間：2.062s---1.719s SELECT COUNT(*) FROM XTWZBM T WHERE T.WZBM 200000*1000; 未索引時(shí)間：2.156s---2.047s SELECT COUNT(*) FROM XTWZBM T WHERE T.WZBM/1000 200000;,ORACLE優(yōu)化,索引時(shí)間：0.381s---0.265s SELECT COUNT(*) FROM XTWZBM T WHERE T.WZBM 200000*1000; 索引時(shí)間：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將直接跳到第一個(gè)等于4的記錄而后者將首先定位到等于3的記錄并且向前掃描到第一個(gè)大于3的記錄。 效率不明顯！??！,ORACLE優(yōu)化,5.4.7、用UNION替換OR （適用于索引列） 通常情況下， 用UNION替換WHERE子句中的OR將會(huì)起到較好的效果。 對(duì)索引列使用OR將造成全表掃描。注意， 以上規(guī)則只針對(duì)多個(gè)索引列有效。 如果有column沒有被索引， 查詢效率可能會(huì)因?yàn)槟銢]有選

63、擇OR而降低。,ORACLE優(yōu)化,時(shí)間：3.829s---4.414s SELECT COUNT(*) FROM XTWZBM A WHERE A.WZBM LIKE %2 OR A.WZMC LIKE %鐵%;,ORACLE優(yōu)化,時(shí)間：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、少的索引列寫在最前面。 時(shí)間：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語(yǔ)句需要UNION兩個(gè)查詢結(jié)果集合時(shí)， 這兩個(gè)結(jié)果集合會(huì)以UNION-ALL的方式被合并， 然后在輸出最終結(jié)果前進(jìn)行排序。 如果用UNION ALL替代UNION， 這樣排序就不是必要了。 效率就會(huì)因此得到提高。 UNION ALL ：輸出結(jié)果可能有相同記錄。 UNION ：輸出結(jié)果

65、剔出相同記錄。,ORACLE優(yōu)化,時(shí)間：2.234s---2.453s SELECT COUNT(*) FROM(SELECT WZBM FROM XTWZBMUNION SELECT WZBM FROM XTWZBM_BAK);,ORACLE優(yōu)化,時(shí)間：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,簡(jiǎn)稱為RBO)和基于代價(jià)的優(yōu)化方式(Cost-Based Optimization,簡(jiǎn)稱為CBO)。,ORACLE優(yōu)化,1.1、RBO方式：優(yōu)化器在分析SQL語(yǔ)句時(shí),所遵循的是Oracle內(nèi)部預(yù)定的一些規(guī)則。比如我們常見的,當(dāng)一個(gè)where子句中的一列有索引時(shí)去走索引。,ORACLE優(yōu)化,1.2、CBO方式：依詞義可知,它是看語(yǔ)句的代價(jià)(Cost)了,這里的代價(jià)主要指Cpu和內(nèi)存。優(yōu)化器在判斷是否用這種方式時(shí),主要參照的是表及索引的統(tǒng)計(jì)信息。統(tǒng)計(jì)信息給出表的大小 、有少行、每行的長(zhǎng)度等信息。這些統(tǒng)計(jì)信息起初在庫(kù)內(nèi)是沒有的,是你在做analyze后才出現(xiàn)的,很多的時(shí)侯過期統(tǒng)計(jì)信息會(huì)令優(yōu)化器做出一個(gè)錯(cuò)誤的執(zhí)行計(jì)劃,因些我們應(yīng)及時(shí)更新這些信息。在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ī)則的方式。,