《互联网oracle》PPT课件.ppt

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、日期转换为字符串: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基础,三、连接 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,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，区别在于每次提交事务触发一次和每一行数据的更新都触发一次,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;,ORACLE基础,五、触发器 2、语法： 1）、触发器名：触发器对象的名称。 2）、触发时间：指明触发器何时执行，该值可取， before-表示在数据库动作之前触发器执行； after-表示在数据库动作之后出发器执行。,ORACLE基础,五、触发器 2、语法： 3、触发事件：指明哪些数据库动作会触发此触发器： insert：数据库插入会触发此触发器； update：数据库修改会触发此触发器； delete：数据库删除会触发此触发器。 4、表 名：数据库触发器所在的表。 5、 for each row：对表的每一行触发器执行一次。如果没有这一 选项，则只对整个表执行一次。,ORACLE基础,五、触发器 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 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基础,BEGIN _MENUID:=1111111; OPEN INSERTUMENU; FETCH 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 INTO B2B_USERXX (USERBM,LXBM) VALUES(:NEW.USERBM,V_LXBM); FETCH 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; DELETE 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 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基础,循环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*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 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基础,六、存储过程 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,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基础,六、存储过程 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; -取出第一条数据 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基础,六、存储过程 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_SPR; -结束循环 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调用存储过程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：对于数据库表中的某一列，该列已经被限制为唯一索引，程序试图存储两个重复的值value_error：在转换字符类型，截取或长度受限时，会发生该异常，如一个字符分配给一个变量，而该变量声明的长度比该字符短，就会引发该异常storage_error：内存溢出,ORACLE基础,六、存储过程异常处理 3、 zero_divide：除数为零case_not_found：对于选择case语句，没有与之相匹配的条件，同时，也没有else语句捕获其他的条件cursor_already_open：程序试图打开一个已经打开的游标timeout_on_resource：系统在等待某一资源，时间超时,ORACLE基础,六、存储过程异常处理 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、系统异常例子： 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 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 from 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 package_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 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 employee_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_data_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 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 = 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;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(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 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 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 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 pctfree 10 initrans 1 maxtrans 255 storage ( initial 2320K minextents 1 maxextents unlimited );,ORACLE优化,一、优化策略 为了保证Oracle数据库运行在最佳的性能状态下，在信息系统开发之前就应该考虑数据库的优化策略。优化策略一般包括服务器操作系统参数调整、数据库参数调整、网络性能调整、应用程序SQL语句分析及设计等几个方面，其中应用程序的分析与设计是在信息系统开发,数据库性能优化包括如下五个部分： 实施工程师： 1. 调整操作系统参数 例如：运行在Unix操作系统上的 Oracle数据库，可以调整 Unix数据缓冲区的大小、每个进程所能使用的内存大小等参数。,ORACLE优化,实施工程师： 2. 调整硬盘I/O 这一步是在信息系统开发之前完成的。数据库管理员可以将组成同一个表空间的数据文件放在不同的硬盘上，做到硬盘之间I/O 负载均衡。,ORACLE优化,实施工程师、开发工程师： 3. 调整服务器内存分配 内存分配是在信息系统运行过程中优化配置的。数据库管理员根据数据库的运行状况不仅可以调整数据库系统全局区（SGA区）的数据缓冲区、日志缓冲区和共享池的大小，而且还可以调整程序全局区（PGA区）的大小。,ORACLE优化,开发工程师： 4. 调整数据结构的设计 这一部分在开发信息系统之前完成，程序员需要考虑是否使用Oracle数据库的分区功能，对于经常访问的数据库表是否需要建立索引等。,ORACLE优化,开发工程师： 5. 调整数据库SQL语句 应用程序的执行最终将归结为数据库中的SQL语句执行，因此SQL语句的执行效率最终决定了Oracle数据库的性能。 Oracle公司推荐使用Oracle语句优化器（Oracle Optimizer）和行锁管理器（Row-Level Manager）来调整优化SQL语句。,ORACLE优化,ORACLE优化,实施工程师、开发工程师： 3. 调整服务器内存分配,图1,3.1、SGA内存区结构 SGA就是系统全局区，是指内存中允许多个进程相互通信的区域。在Oracle中，SGA对所有进程来说都是全局的可用的。图1为SGA结构图。,ORACLE优化,3.1.1、共享池 专有模式-是SGA中的保存着关于待执行的SQL语句的信息。他由两部分组成：数据字典高速缓存，存放从数据字典中读取的信息以用于处理SQL请求；库高速缓存，存放需要执行的SQL语句信息，包括每个SQL语句的语法分析树和执行计划。如果多个用户要执行同样的SQL语句，那么语法分析树和执行计划就可以重复利用，省去了语法分析步骤的昂贵花费。 共享模式-数据字典高速缓存。,ORACLE优化,3.1.2、缓冲区高速缓存 共享模式、专有模式-是SGA中为所有用户和系统进程保存数据的区域，任何数据在传递给一个调用的应域是共享的，所以多个进程可以从这片高速缓存读取同样的数据块，而不必每次都从物理磁盘中读取。,ORACLE优化,3.1.3、大型池： 共享模式-。这部分主要用来保 存并行查询时候的一些信息，还有就是RMAN 在备份的时候可能会使用到。如果设置了 MTS，则由于UGA部分要移入这里，则需要具体根据server process数量和相关会话内存参 数的设置来综合考虑这部分大小的设置。,ORACLE优化,3.1.4、java池: 那是为满足在ORACLE中内嵌JAVA存储过程或其他JAVA程序（例如CORBA中间件）运行时而需要的内存，如果不用JAVA等程序，就无须设置，使其值为0就可以了,ORACLE优化,3.2、PGA: 包含单个服务器进程或单个后台进程的数据和控制信息，与几个进程共享的SGA 正相反,PGA 是只被一个进程使用的区域，PGA 在创建进程时分配,在终止进程时回收.（客户专有的数据结果）,ORACLE优化,3.3、调整SGA结构 一般来讲，在系统硬件支持的情况下，系统全局区越大越有利于数据库高效的运行。大的缓冲区高速缓存可以缓存更多的数据块，这样可以提高缓存命中率，节省物理磁盘读取的高昂代价；大的共享池意味着大的库高速缓存。,ORACLE优化,库缓存越大，可以保存的SQL语法分析信息越多；此外，数据库中的一些对象，如表、索引、过程、触发器、软件包等也在首次执行后进驻库高速缓存。大的库缓存可以保证对这些对象的高命中率，从而节省解析和载入代价。,ORACLE优化,开发工程师： 5. 调整数据库SQL语句 应用程序的执行最终将归结为数据库中的 语句执行，SQL语句消耗了-的数据库资源。因此 语句的执行效率最终决定了ORACLE数据库的性能。许多程序员认为查询优化是DBMS（数据库管理系统）的任务，与程序员所编写的语句关系不大，这是错误的。,ORACLE优化,一个好的查询计划往往可以使程序性能提高数十倍。另外，SQL语句独立于程序设计逻辑，相对于对程序源代码的优化，对语句的优化在时间成本和风险上的代价都很低。优化的主要途径是：,ORACLE优化,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、在频繁进行排序的列上建立索引； 例： 在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值的列都将不会被包含在索引中。即使索引有多列的情况下，只要这些列中有一列含有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.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在解析的过程中， 会将* 依次转换成所有的列名， 这个工作是通过查询数据字典完成的， 这意味着将耗费更多的时间。,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 FROM 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(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子句中最后的那个表）并对记录进行排序，然后扫描第二个表（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采用自下而上的顺序解析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 LIKE %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% 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 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 LIKE 5%);,ORACLE优化,时间： 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优化,5.4.4、用表连接替换EXISTS ： 时间：无法查出数据 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优化,时间：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.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优化,索引时间：0.381s-0.265s SELECT COUNT(*) FROM XTWZBM T WHERE T.WZBM 200000*1000; 索引时间：0.5s-0.453s SELECT COUNT(*) FROM XTWZBM T WHERE T.WZBM/1000 200000; 建议：尽可能按照规则建索引,ORACLE优化,5.4.6、用=4替代 3 两者的区别在于， 前者DBMS将直接跳到第一个等于4的记录而后者将首先定位到等于3的记录并且向前扫描到第一个大于3的记录。 效率不明显！,ORACLE优化,5.4.7、用UNION替换OR （适用于索引列） 通常情况下， 用UNION替换WHERE子句中的OR将会起到较好的效果。 对索引列使用OR将造成全表扫描。注意， 以上规则只针对多个索引列有效。 如果有column没有被索引， 查询效率可能会因为你没有选择OR而降低。,ORACLE优化,时间：3.829s-4.414s SELECT COUNT(*) FROM XTWZBM A WHERE A.WZBM LIKE %2 OR A.WZMC LIKE %铁%;,ORACLE优化,时间：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优化,5.4.8、如果非用OR （适用于索引列） 那就需要返回记录最少的索引列写在最前面。 时间：3.312s-3.234s SELECT COUNT(*) FROM XTWZBM A WHERE A.WZMC LIKE %铁% OR A.WZBM LIKE %2;,ORACLE优化,5.5、其他 5.5.1、用UNION-ALL 替换UNION （ 如果有可能的话） 当SQL语句需要UNION两个查询结果集合时， 这两个结果集合会以UNION-ALL的方式被合并， 然后在输出最终结果前进行排序。 如果用UNION ALL替代UNION， 这样排序就不是必要了。 效率就会因此得到提高。 UNION ALL ：输出结果可能有相同记录。 UNION ：输出结果剔出相同记录。,ORACLE优化,时间：2.234s-2.453s SELECT COUNT(*) FROM(SELECT WZBM FROM XTWZBMUNION SELECT WZBM FROM XTWZBM_BAK);,ORACLE优化,时间：0.688s-0.719s SELECT COUNT(*) FROM(SELECT WZBM FROM XTWZBMUNION ALLSELECT WZBM FROM XTWZBM_BAK),ORACLE优化,二、Oracle优化器的优化方式和优化模式 : 待细化 1、优化器的优化方式 Oracle的优化器共有两种的优化方式,即基于规则的优化方式(Rule-Based Optimization,简称为RBO)和基于代价的优化方式(Cost-Based Optimization,简称为CBO)。,ORACLE优化,1.1、RBO方式：优化器在分析SQL语句时,所遵循的是Oracle内部预定的一些规则。比如我们常见的,当一个where子句中的一列有索引时去走索引。,ORACLE优化,1.2、CBO方式：依词义可知,它是看语句的代价(Cost)了,这里的代价主要指Cpu和内存。优化器在判断是否用这种方式时,主要参照的是表及索引的统计信息。统计信息给出表的大小 、有少行、每行的长度等信息。这些统计信息起初在库内是没有的,是你在做analyze后才出现的,很多的时侯过期统计信息会令优化器做出一个错误的执行计划,因些我们应及时更新这些信息。在Oracle8及以后的版本,Oracle列推荐用CBO的方式。,ORACLE优化,2、优化器的优化模式(Optermizer Mode) 优化模式包括Rule,Choose,First rows,All rows这四种方式,ORACLE优化,2.1、Rule: 即走基于规则的方式。,