《互联网oracle》PPT课件.ppt

1、ORACLE基础,一、基本查询语句 SELECT FROM WHERE GROUP BY HAVING ORDER BY,ORACLE基础,二、函数 1、处理null值 NVL(M,N) 2、字符串连接 | 3、查找子串位置 INSTR(原串,子串,从第n个字符开始) 4、取子串 SUBSTR(原串,起始位置,取字符位数),ORACLE基础,二、函数 5、日期转换为字符串:yyyy-mm-dd hh:mi:ss TO_CHAR(表达式,YYYY-MM-DD HH:MI:SS) 6、日期转换为字符串:yyyy-mm-dd“ TO_CHAR(表达式,YYYY-MM-DD)“ 7、日期转换为字符串:

2、hh:mi:ss“ TO_CHAR(表达式,HH:MI:SS),ORACLE基础,二、函数 8、字符串转换为日期 TO_DATE(表达式,YYYY-MM-DD HH:MI:SS) TO_DATE(表达式,YYYY-MM-DD) 9、数值转字符串TO_CHAR(表达式) 10、字符串转数值TO_NUMBER(表达式),ORACLE基础,二、函数 11、表达式判断取值 case when 条件表达式1 then 结果式1 when 条件表达式2 then 结果式2. else 结果式n end decode(字段，第一个判断值，第一个结果，第二个判断值，第二个结果，否则的结果),二,ORACLE基

3、础,三、连接 1、内联(自然连接) ： select * from a , b where a.id=b.id select * from a inner join b on a.id=b.id,ORACLE基础,三、连接 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 join a on a.id = b.id

4、,ORACLE基础,三、连接 3、全连： select * from a full join b on a.id=b.id 四、笛卡尔积 select * from a , b,ORACLE基础,三、连接 4、自连： select a.* from AAA a, AAA b on a.id=b.id,ORACLE基础,四、合并数据 UNION 合并去重复 UNION ALL 合并不去重复,ORACLE基础,五、触发器 1、概述： 触发器是特定事件出现的时候，自动执行的代码块。类似于存储过程，但是用户不能直接调用他们。 ORACLE触发器语法 触发器有两种after和before，区别在于每次提

5、交事务触发一次和每一行数据的更新都触发一次,ORACLE基础,五、触发器 2、语法： ORACLE产生数据库触发器的语法为： create or replace trigger 触发器名 触发时间 触发事件 on 表名 for each row pl/sql 语句,ORACLE基础,五、触发器 2、语法： create or replace trigger aaaa before insert on a for each row declare - local variables herebegin insert into aa(a,b)values(999,999);end aaaa;,OR

6、ACLE基础,五、触发器 2、语法： 1）、触发器名：触发器对象的名称。 2）、触发时间：指明触发器何时执行，该值可取， before-表示在数据库动作之前触发器执行； after-表示在数据库动作之后出发器执行。,ORACLE基础,五、触发器 2、语法： 3、触发事件：指明哪些数据库动作会触发此触发器： insert：数据库插入会触发此触发器； update：数据库修改会触发此触发器； delete：数据库删除会触发此触发器。 4、表 名：数据库触发器所在的表。 5、 for each row：对表的每一行触发器执行一次。如果没有这一 选项，则只对整个表执行一次。,ORACLE基础,五、触发

7、器 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基础,例子2： CREATE OR REPLACE TRIGGER ADDB2B_GHDWUSER AFTER INSERT ON B2B_GHDWUSER FOR EACH ROW DECLARE V_MENUID B2B_GHJBMENU.MENUID%TYPE; _MENUID VARCHA

8、R2(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基础,BEGIN _MENUID:=1111111; OPEN INSERTUMENU; FETCH INSERTUMENU INTO V_MENUID,_MENUID; WHILE IN

9、SERTUMENU%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 INTO B2B_USERXX (USERBM,LXBM) VALUES(:NEW.USERBM,V_LXBM); FETCH

10、INSERTLXBM INTO V_LXBM; END LOOP; CLOSE INSERTLXBM;,ORACLE基础,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_APPLICATION_ERROR(-20005,不能删除!); END IF; DELETE RPC_CONTRACENTRY WHERE TID = L_ID; D

11、ELETE RPC_CONTRAC WHERE TID = L_ID; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END; END IF; END ADDB2B_GHDWUSER ;,ORACLE基础,例子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 SELE

12、CT 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基础,循环1 i :=1; loop insert into tb_zhaozhenlong(rpt_date ,dept_id,item,qty) values(to_date(2007-01-01,yyyy-MM-dd),D|i,I|i,round(i

13、*100/3,3); exit when i =10; i :=i+1; end loop;,ORACLE基础,循环2 i :=1; while i=5 loop i :=i+1; end loop;,ORACLE基础,循环3 -如果指定了reverse选项，则循环控制变量会自动减1，否则自动加1 for j in reverse 1.10 loop end loop;,ORACLE基础,循环3 for x in 1.10 loop end loop;,ORACLE基础,例子2： CREATE OR REPLACE TRIGGER ADDB2B_GHDWUSER AFTER INSERT ON

14、 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.JB AND B.DWBM= :NEW.GHDWBM; V_LXBM XTFBLX.LXBM%TYPE; CURSOR INSERTLXBM IS SELECT DISTINCT LXBM FROM XTFBLX;,ORACLE基础,六、存储过程

15、1、语法： 存储过程： 例子1： CREATE OR REPLACE PROCEDURE P_BJHHTED -输入、输出参数 ( AS_USER VARCHAR2, AD_RQ1 DATE, AD_RQ2 DATE) AS,ORACLE基础,六、存储过程 1、语法： -定义变量 LS_GW 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

16、,4);,ORACLE基础,六、存储过程 1、语法： -定义变量 LD_JE3 NUMBER(14,4); LD_SL4 NUMBER(14,4); LD_JE4 NUMBER(14,4); LD_SL5 NUMBER(14,4); LD_JE5 NUMBER(14,4); LD_SL6 NUMBER(14,4); LD_JE6 NUMBER(14,4);,ORACLE基础,六、存储过程 1、语法： -定义游标 CURSOR C1 IS SELECT HTGW,KSJC,SPR FROM BJHHTED WHERE ZDR=AS_USER FOR UPDATE;,ORACLE基础,六、存储过程

17、 1、语法： -开始执行存储过程 BEGIN -删除数据 DELETE FROM BJHHTED WHERE ZDR=AS_USER;,ORACLE基础,六、存储过程 1、语法： -插入数据 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基础,六、存储过程 1、语法： -打开游标 OPEN C1; -取出第

18、一条数据 FETCH C1 INTO LS_GW,LS_KS,LS_SPR; -循环数据 WHILE C1%FOUND LOOP,ORACLE基础,六、存储过程 1、语法： -查询出数据赋给变量 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 SPRM=LS_SPR AND SPJE=50000;,ORACLE基础,六、

19、存储过程 1、语法： -更新表数据 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=LS_SPR;,ORACLE基础,六、存储过程 1、语法： -取出下一条数据 FETCH C1 INTO LS_GW,LS_KS,LS_SP

20、R; -结束循环 END LOOP; -关闭游标 CLOSE C1; -关闭存储过程 END P_BJHHTED;,ORACLE基础,六、存储过程 create or replace procedure aa1 is v_aa number; v_bb varchar2(20); message varchar2(20);begin v_bb :=3s; begin v_aa :=to_number(v_bb); exception when others then message:=ddddddddddddd; end; end aa1;,ORACLE基础,六、存储过程 2、JAVA调用存储

21、过程1：,ORACLE基础,六、存储过程异常处理 3、 有两种类型的异常，一种为内部异常，一种为用户自定义异常，内部异常是执行期间返回到PL/SQL块的ORACLE错误或由PL/SQL代码的某操作引起的错误，如除数为零或内存溢出的情况。用户自定义异常由开发者显示定义，在PL/SQL块中传递信息以控制对于应用的错误处理,ORACLE基础,六、存储过程异常处理 3、对于预定义异常 ： no_data_found：select into 语句没有符合条件的记录返回too_many_rows：select into 语句符合条件的记录有多条返回dup_val_on_index：对于数据库表中的某一列，

22、该列已经被限制为唯一索引，程序试图存储两个重复的值value_error：在转换字符类型，截取或长度受限时，会发生该异常，如一个字符分配给一个变量，而该变量声明的长度比该字符短，就会引发该异常storage_error：内存溢出,ORACLE基础,六、存储过程异常处理 3、 zero_divide：除数为零case_not_found：对于选择case语句，没有与之相匹配的条件，同时，也没有else语句捕获其他的条件cursor_already_open：程序试图打开一个已经打开的游标timeout_on_resource：系统在等待某一资源，时间超时,ORACLE基础,六、存储过程异常处理

23、3、 系统异常： 如果要处理未命名的内部异常，必须使用OTHERS异常处理器或PRAGMA EXCEPTION_INIT,ORACLE基础,六、存储过程异常处理 3、预定义异常例子 create or replace procedure sdf isv_a varchar(10);begin Begin select aa.a into v_a from aa; Exception when no_data_found then insert into a(a) values(ssssssssssssssss); End;end sdf;,ORACLE基础,六、存储过程异常处理 3、系统异常例

24、子： create or replace procedure sdf isbegin Begin insert into aa(a) values(dddddddddddddddddd); Exception when OTHERS then insert into a(a) values(dddddddddddddddddd); End;end sdf;,ORACLE基础,七、函数 函数： 与过程相似，遵循了相同的规则。 参数传递：只能带有in参数，不能使用out, in out 参数 函数的主要特性是必须返回一个值。,ORACLE基础,七、函数 语法： CREATE OR REPLACE

25、FUNCTION function_name (参数) RETURN datatype IS | AS PRAGMA AUTONOMOUS_TRANACTION; -声明自主事务处理。 本地变量声明 BEGIN 执行语句部分 EXCEPTION 错误处理部分 ENDname; /,ORACLE基础,七、函数 函数例子： 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 fr

26、om cghtmx where htbm= a_htbm; exception when no_data_found then totl_spje := 0.0; return totl_spje; end;,ORACLE基础,八、程序包 是对PL/SQL类型，过程，函数，游标，异常，变量，常量的封装。 包括两部分：规范和主体 规范：是程序包的公共接口， 主体：规范的实现，以及私有例程、数据和变量。,ORACLE基础,八、程序包 语法： CREATE OR REPLACE PACKAGE package_name IS | AS 公用类型或变量常量的声明； 公用过程或函数的声明； END pa

27、ckage_name; / CREATE OR REPLACE PACKAGE BODY package_name IS | AS 私有类型或变量常量的声明； 公用过程或函数的实现； END package_name,ORACLE基础,八、程序包 规范： 规范是程序包的接口，规范中定义的所有内容都可以由调用者使用（当然需要具有EXECUTE特权），比如规范中定义的过程函数可以被执行，类型可以被访问，变量可以被引用。 例子：使用两个过程PRINT_ENAME() 和PRINT_SAL()，定义称为EMPLOYEE_PKG的程序包。,ORACLE基础,八、程序包 CREATE OR REPLACE

28、 PACKAGE employee_pkg as Procedure print_ename(p_empno number); Procedure print_sal(p_empno number); End; /,ORACLE基础,八、程序包 CREATE OR REPLACE PACKAGE employee_pkg as Procedure print_ename(p_empno number); Procedure print_sal(p_empno number); End; / 并没有为过程提供代码，只是定义了名称和参数。 这个时候如果试图使用这个包，会报错 exec employ

29、ee_pkg.print_ename(1234);,ORACLE基础,八、程序包 主体： 程序包是过程，函数的具体实现部分，实现规范中定义的接口。 CREATE OR REPLACE PACKAGE BODY employee_pkg as Procedure print_ename(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_dat

30、a_found then Dbms_output.put_line(Invalid employee number); End print_ename;,ORACLE基础,八、程序包 主体： 程序包是过程，函数的具体实现部分，实现规范中定义的接口。 Procedure print_sal(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

31、then Dbms_output.put_line(Invalid employee number); End print_sal; End employee_pkg; /,ORACLE基础,八、程序包 执行： set serveroutput on exec employee_pkg.print_ename(1234); exec employee_pkg.print_ename(7782); exec employee_pkg.print_sal(7782);,ORACLE基础,九、JOB begin sys.dbms_job.submit(job = :job, what = inser

32、t 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;end;/,ORACLE基础,十、分区 - 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

33、(14,4), JHQ DATE, YXQ DATE, BJSM VARCHAR2(60), BHSDJ NUMBER(14,4), BHSJE NUMBER(14,4), ZZSL NUMBER(14,4), PHSB VARCHAR2(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

34、 255 storage ( initial 2320K minextents 1 maxextents unlimited ), partition P_2004 values less than (2005) tablespace 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

35、1 maxtrans 255 storage ( initial 2320K minextents 1 maxextents unlimited ), partition P_2006 values less than (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

36、 initrans 1 maxtrans 255 storage ( initial 2320K minextents 1 maxextents unlimited ), partition P_2008 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

37、pctfree 10 initrans 1 maxtrans 255 storage ( initial 2320K minextents 1 maxextents unlimited );,ORACLE优化,一、优化策略 为了保证Oracle数据库运行在最佳的性能状态下，在信息系统开发之前就应该考虑数据库的优化策略。优化策略一般包括服务器操作系统参数调整、数据库参数调整、网络性能调整、应用程序SQL语句分析及设计等几个方面，其中应用程序的分析与设计是在信息系统开发,数据库性能优化包括如下五个部分： 实施工程师： 1. 调整操作系统参数 例如：运行在Unix操作系统上的 Oracle数据库，可

38、以调整 Unix数据缓冲区的大小、每个进程所能使用的内存大小等参数。,ORACLE优化,实施工程师： 2. 调整硬盘I/O 这一步是在信息系统开发之前完成的。数据库管理员可以将组成同一个表空间的数据文件放在不同的硬盘上，做到硬盘之间I/O 负载均衡。,ORACLE优化,实施工程师、开发工程师： 3. 调整服务器内存分配 内存分配是在信息系统运行过程中优化配置的。数据库管理员根据数据库的运行状况不仅可以调整数据库系统全局区（SGA区）的数据缓冲区、日志缓冲区和共享池的大小，而且还可以调整程序全局区（PGA区）的大小。,ORACLE优化,开发工程师： 4. 调整数据结构的设计 这一部分在开发信息系

39、统之前完成，程序员需要考虑是否使用Oracle数据库的分区功能，对于经常访问的数据库表是否需要建立索引等。,ORACLE优化,开发工程师： 5. 调整数据库SQL语句 应用程序的执行最终将归结为数据库中的SQL语句执行，因此SQL语句的执行效率最终决定了Oracle数据库的性能。 Oracle公司推荐使用Oracle语句优化器（Oracle Optimizer）和行锁管理器（Row-Level Manager）来调整优化SQL语句。,ORACLE优化,ORACLE优化,实施工程师、开发工程师： 3. 调整服务器内存分配,图1,3.1、SGA内存区结构 SGA就是系统全局区，是指内存中允许多个进

40、程相互通信的区域。在Oracle中，SGA对所有进程来说都是全局的可用的。图1为SGA结构图。,ORACLE优化,3.1.1、共享池 专有模式-是SGA中的保存着关于待执行的SQL语句的信息。他由两部分组成：数据字典高速缓存，存放从数据字典中读取的信息以用于处理SQL请求；库高速缓存，存放需要执行的SQL语句信息，包括每个SQL语句的语法分析树和执行计划。如果多个用户要执行同样的SQL语句，那么语法分析树和执行计划就可以重复利用，省去了语法分析步骤的昂贵花费。 共享模式-数据字典高速缓存。,ORACLE优化,3.1.2、缓冲区高速缓存 共享模式、专有模式-是SGA中为所有用户和系统进程保存数据

41、的区域，任何数据在传递给一个调用的应域是共享的，所以多个进程可以从这片高速缓存读取同样的数据块，而不必每次都从物理磁盘中读取。,ORACLE优化,3.1.3、大型池： 共享模式-。这部分主要用来保 存并行查询时候的一些信息，还有就是RMAN 在备份的时候可能会使用到。如果设置了 MTS，则由于UGA部分要移入这里，则需要具体根据server process数量和相关会话内存参 数的设置来综合考虑这部分大小的设置。,ORACLE优化,3.1.4、java池: 那是为满足在ORACLE中内嵌JAVA存储过程或其他JAVA程序（例如CORBA中间件）运行时而需要的内存，如果不用JAVA等程序，就无须

42、设置，使其值为0就可以了,ORACLE优化,3.2、PGA: 包含单个服务器进程或单个后台进程的数据和控制信息，与几个进程共享的SGA 正相反,PGA 是只被一个进程使用的区域，PGA 在创建进程时分配,在终止进程时回收.（客户专有的数据结果）,ORACLE优化,3.3、调整SGA结构 一般来讲，在系统硬件支持的情况下，系统全局区越大越有利于数据库高效的运行。大的缓冲区高速缓存可以缓存更多的数据块，这样可以提高缓存命中率，节省物理磁盘读取的高昂代价；大的共享池意味着大的库高速缓存。,ORACLE优化,库缓存越大，可以保存的SQL语法分析信息越多；此外，数据库中的一些对象，如表、索引、过程、触发

43、器、软件包等也在首次执行后进驻库高速缓存。大的库缓存可以保证对这些对象的高命中率，从而节省解析和载入代价。,ORACLE优化,开发工程师： 5. 调整数据库SQL语句 应用程序的执行最终将归结为数据库中的 语句执行，SQL语句消耗了-的数据库资源。因此 语句的执行效率最终决定了ORACLE数据库的性能。许多程序员认为查询优化是DBMS（数据库管理系统）的任务，与程序员所编写的语句关系不大，这是错误的。,ORACLE优化,一个好的查询计划往往可以使程序性能提高数十倍。另外，SQL语句独立于程序设计逻辑，相对于对程序源代码的优化，对语句的优化在时间成本和风险上的代价都很低。优化的主要途径是：,OR

44、ACLE优化,5.1、有效索引的建立。(列值重复率低) 5.1.1、在经常进行连接，但是没有指定为外键的列上建立索引； XTWZBM CGHTMXMX 测试时间： 均取第一次执行 和第二次以后三次平均时间,ORACLE优化,B-TREE,ORACLE优化,BITMAP,ORACLE优化,例： 在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优化,5.1.2、

45、在频繁进行排序的列上建立索引； 例： 在XTWZBM表WZMC未建索引：11.1s-11.1s 在XTWZBM表WZMC建索引：0.53s-0.14s SELECT A.*FROM XTWZBM AORDER BY A.WZMC,ORACLE优化,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优化,5.1.4、 IS NULL 不能用null作索引，任何包含null值的列都

46、将不会被包含在索引中。即使索引有多列的情况下，只要这些列中有一列含有null，该列就会从索引中排除。也就是说如果某列存在空值，即使对该列建索引也不会提高性能。 任何在where子句中使用is null的语句优化器是不允许使用索引的。,ORACLE优化,例： 时间：2.438s-2.453s SELECT T.* FROM XTWZBM T WHERE T.WZMC IS NULL,ORACLE优化,5.1.4、索引列不要加函数处理： 例：时间：0.078s-0.047s SELECT COUNT(*) FROM XTWZBM T WHERE T.GGXH = 4-72-11-6D AND T.

47、CZ = 河北华瑞玻璃钢有限公司;,ORACLE优化,时间：2.359s-2.453s SELECT COUNT(*) FROM XTWZBM T WHERE T.GGXH | T.CZ LIKE 4-72-11-6D | 河北华瑞玻璃钢有限公司;,ORACLE优化,5.2、SELECT优化 5.2.1、SELECT子句中避免使用 * 当你想在SELECT子句中列出所有的COLUMN时，使用动态SQL列引用 * 是一个方便的方法。不幸的是，这是一个非常低效的方法。 实际上，ORACLE在解析的过程中， 会将* 依次转换成所有的列名， 这个工作是通过查询数据字典完成的， 这意味着将耗费更多的时间

48、。,ORACLE优化,当全表查询时：时间基本相当 时间：0.141-0.125 SELECT * 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优化,列数减少时 时间：0.078s-0.078s SELECT WZBM, WZMC, GGXH, CZ FR

49、OM XTWZBM; 建议尽可能列出列名,ORACLE优化,5.2.2、SELECT子句中避免使用子查询 时间：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优化,5.2.3、COUNT(*) 、COUNT(1)对比速度差不多 时间：1.516s-1.422s SELECT COUNT(

50、1) FROM XTWZBM; 时间：1.5s-1.438s SELECT COUNT(*) FROM XTWZBM;,ORACLE优化,5.3、FROM优化 5.3.1、选择最有效率的表名顺序（只在基于规则的优化器中有效） ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，因此FROM子句中写在最后的表（基础表 driving table）将被最先处理。 在FROM子句中包含多个表的情况下，你必须选择记录条数最少的表作为基础表。,ORACLE优化,当ORACLE处理多个表时， 会运用排序及合并的方式连接它们。首先，扫描第一个表（FROM子句中最后的那个表）并对记录进行排序，然后

51、扫描第二个表（FROM子句中最后第二个表），最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并。,ORACLE优化,表数据量小的排在后面：测试数据时间相差不多 时间： 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优化,5.4、WHERE优化 5.4.1、ORACLE采用自下而

52、上的顺序解析WHERE子句，根据这个原理，表之间的连接必须写在其他WHERE条件之前， 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾。,ORACLE优化,SELECT COUNT(*) FROM XTWZBM A WHERE A.WZBM LIKE %2; 数据量：267400 SELECT COUNT(*) FROM XTWZBM A WHERE A.WZBM LIKE 01%; 数据量：801,ORACLE优化,时间：0.672s-0.656s SELECT COUNT(*) FROM XTWZBM A WHERE A.WZBM LIKE 01% OR A.WZBM LIK

53、E %2;,ORACLE优化,时间：0.594s-0.562s SELECT COUNT(*) FROM XTWZBM A WHERE A.WZBM LIKE %2 OR A.WZBM LIKE 01%;,ORACLE优化,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%

54、OR B.WZBM LIKE 4% OR B.WZBM LIKE 5%);,ORACLE优化,时间：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 B.WZBM LIKE 5%) 速度差不多,ORACLE优化,5.4.3、用NOT EXI

55、STS替代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 LIKE 5%);,ORACLE优化,时间： 0.422s-0.375s SELECT COUNT(*) FROM CGHTMXMX A WHERE NOT EXIST

56、S (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优化,5.4.4、用表连接替换EXISTS ： 时间：无法查出数据 SELECT COUNT(*) FROM CGHTMXMX A WHERE EXISTS (SELECT B.WZBM FROM XTWZBM B WHERE A.WZBM =

57、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优化,时间：0.891s-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; 根据以上测试结果建议： 1、不用exists用表 2、不用not in用not exists,ORACLE优化,5.4.5、避免在索引列上使用计算。 未索引时间：2.062s-1.719