ORACLE优化.ppt

1、补充知识,ORACLE,本章内容,系统函数介绍 SQL优化,函数-1,日期函数 ： add_months(d,n) ： 返回指定日期加上指定月数后的日期值。 d是日期，n是月数。 months_between(d1,d2) ：返回两个日期之间的月数。 d1,d2都是两个日期。 last_day(d)： 返回指定日期当月的最后一天的日期值。 d是日期。 round(d,fmt) ： 返回日期值，此日期四舍五入为格式模型指定的单位。 d是日期，fmt指定格式模型。(fmt是一个选项，默认舍入为最靠近的那一天。如果格式为year则舍入到年的开始,即1月1日；如果格式为month则舍入到月的第一天；如

2、果格式为day则舍入到最靠近的星期天。) next_day(d,day) ：返回指定的下一个星期几的日期。 d是日期，day指定周内任何一天 如星期一。 trunc(d,fmt)：返回日期值，指定日期截断为格式模型指定的单位的日期。 d是日期，fmt指定格式模型。(与round函数类似,不同的是它只舍不入。) extract(fmt from d) ：提取日期时间类型中的特定部分。 fmt指定格式模型,d是日期。 (fmt取值可以是:year,month,day,hour,minute,second;注意此处的格式不能使用单引号。),函数-2,字符函数 ： initcap(char) ：首字母

3、大写。lower(char) ： 转换为小写。upper(char)：转换为大写。 ltrim(char,set)：左边截空。rtrim(char,set) ：右边截空。 translate(char,from,to)： 按字符翻译。 replace(char,search_str,replace_str)：字符串替换。 instr(char,substr,pos)： 查找子串位置。 substr(char,pos,len)： 取子字符串。 concat(char1,char2) ： 连接字符串。 chr(int) ：根据ASCII码返回对应的字符。 int是ASCII码。 lpad(str,

4、int,char)和rpad(str,int,char)：用指定的字符(左|右)填充源字符串到指定长度。 str是源字符串，int指定总长度，char是填充的字符。 length(str) ：返回字符串的长度。 str是字符串。 decode(expr,search1,trsult1,search2,trult2.,default)：进行逐个值的替换。expr是字符变量或数据表字段,search是expr的预期值,trsult是返回值,default是无匹配是返回的值。 trim(leading|trailing trim_char from trim_sourse)：trim_char指定裁

5、剪的字符,trim_sourse是源字符串。,函数-3,数学函数 ： abs(n) 取绝对值。 ceil(n) 向上取整 sin(n) 正弦 cos(n) 余弦 sign(n) 取符号 floor(n) 向下取整 power(n) m的n次幂 mod(m,n) 取余数 round(m,n) 四舍五入 trunc(m,n) 截断 sqrt(n) 平放根,函数-4,转换函数： to_char(d|n,fmt)：将指定的日期或数字转换成字符串(varchar2)。 d是日期，n是数字,fmt是指定日期或数字的格式。 to_date(char,fmt)：将char或varchar数据类型转换为日期类型

6、。 char是日期格式的字符串,fmt是日期的格式。 to_number(char)：将字符串转换为数字。 其他函数： nvl(ex1,ex2)：将空值替换为指定的值。ex1是变量或数据表字段，ex2是一个值。如果ex1为NULL，则nvl返回ex2。如果ex1不为NULL，则nvl返回ex1。 nvl2(ex1,ex2,ex3)：与nvl类似。如果ex1不为NULL，则返回ex2。如果ex1为NULL，则nvl返回ex3。 nullif(expr1,expr2)：比较两个表达式，如果相等，则返回空值(null)，否则返回expr1。,函数-5,分组函数： avg(column)：返回参数中指

7、定列的平均值。 min(column)： 返回参数中指定列的最小值。 max(column)： 返回参数中指定列的最大值。 sum(column)： 返回记录集中值的总和。 count(distinct*|column,column.) ： 返回记录集中的行数。 分析函数： row_number() over (partition by COL1 order by COL2) ：表示根据COL1分组，在分组内部根据 COL2排序，而这个值就表示每组内部排序后的顺序编号（组内连续的唯一的） 。 rank () over (partition by COL1 order by COL2) :与ro

8、w_number类似。允许重复排序，跳跃排序 。 dense_rank() over (partition by COL1 order by COL2)：允许重复排序，并是连续排序 。 lag（arg1,arg2,arg3)：arg1是从其他行返回的表达式，arg2是希望检索的当前行分区的偏移量，arg3是在arg2表示的数目超出了分组的范围时返回的值。,函数-5,正则表达式的函数 ： regexp_like() regexp_instr() regexp_substr() regexp_replace() 聚合函数： avg() count() first() last() max() mi

9、n() sum() grouping(),SQL优化,复制表 create table T_EMPas ( select * from scott.emp ); 复制表结构 create table T_EMPas ( select * from scott.emp where rownum=0 ); 删除重复的记录 Delete from emp e wheree.rowid(select min(x,rowid) from emp x where x.emp_no=e.emp_no),SQL优化,减少对表的查询 -低效 SELECT * FROM scott.emp emp where e

10、mp.job = (select emp.job from scott.emp emp where emp.empno = 7788) and emp.sal = (select emp.sal from scott.emp emp where emp.empno = 7788) -高效 SELECT * FROM scott.emp where (job, sal) in (select emp.job, emp.sal from scott.emp emp where emp.empno = 7788),SQL优化,使用表的别名(Alias) 当在 SQL 语句中连接多个表时， 请使用表的

11、别名并把别名前缀于每个 Column 上。这样一来，就可以减少解析的时间并减少那些由Column 歧义引起的语法错误。 (Column 歧义指的是由于 SQL 中不同的表具有相同的 Column 名，当 SQL 语句中出现这个 Column 时，SQL 解析器无法判断这个Column 的归属),SQL优化,用 EXISTS 替代 IN -低效 SELECT * FROM scott.emp WHERE EMPNO 0 AND DEPTNO IN (SELECT DEPTNO FROM scott.dept WHERE LOC = NEW YORK) -高效： SELECT * FROM scott.emp WHERE EMPNO 0 AND EXISTS (SELECT deptno FROM scott.dept WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = NEW YORK),SQL优化,-低效 SELECT * FROM SCOTT.EMP WHERE DEPTNO NOT IN (SELECT