简单整理下Oracle的语句，熟悉下，开始着手项目开发，一直使用MySQL，去年呢，使用Oracle，MySQL，SQL server，GSDB，写过简单的脚本，存储过程，没有真正的结合mybatis使用，对优化，性能，复杂的sql没实践过，今天整理下，希望大家指点指点，谢谢~~~~
目录
MySQL和Oracle的区别
一、总体区别
1、delete删除，oracle可用可不用表别名，而MySQL使用表别名则delete和from之间需要加入表别名。
2、MySQL的单引号和双引号都可以用，而oracle只能用单引号。
3、MySQL引号里的字母可以不用区分大小写，而oracle引号里的字母必须区分大小写。
二、具体来看
1.表
（1）创建表（同）
create table tableName(
columnName1 int,
columnName2 int
)（2）删除表（异）
MySQL:
drop table if exists tableName
Oracle:
drop table tableName注：Oracle没有if exists关键字，也没用类似if exists的SQL语法。
2.列
（1） 添加列（异）
MySQL:
A. alter table tableName add column columnName1 int;
B. alter table tableName add column columnName1 int, add column columnName2 int;
注：其中关键字column可有可无。
Oracle:
A. alter table tableName add columnName1 int;
B. alter table tableName add (columnName1 int);
C. alter table tableName add (columnName1 int, columnName2 int);
注：对于A，只有添加单列的时候才可使用，对于添加多列时需要使用C，不能像MySQL那样重复使用add column关键字。（2）删除列（异）
MySQL:
A. alter table tableName drop column columnName1
B. alter table tableName drop column columnName1, drop column columnName2
注：其中关键字column可有可无。
Oracle:
A. alter table tableName drop column columnName2
B. alter table tableName drop (columnName1)
C. alter table tableName drop (columnName1,columnName2)
注：对于A，只有删除单列的时候才可使用，对于删除多列时需要使用C，不能像MySQL那样重复使用drop column关键字。（3） 修改列名（异）
MySQL:
alter table tableName change column columnNameOld columnNameNew columnType;
Oracle:
alter table tableName rename column columnNameOld to columnNameNew;（4） 修改列类型（说明）
Oracle中，在列有数据的时候，无法修改列类型；没有数据时可以。
MySQL中，无论列是否有数据都可以修改列类型。
但是当有数据是，直接修改列类型都可能对数据造成丢失等，所以一般需要结合具体的业务来对列数据做处理后，再修改列类型类型。所以修改列的类型并非使用SQL语句进行一步到位的修改，而是通过以下流程：
A. 添加临时列
B. 将需要更改的列的值经过类型转换的验证后，赋值给临时列
C. 删除原有列
D. 将临时列的列名修改为原有列列名
3.索引
在整个数据库内，MySQL的索引可以同名，也就是说MySQL的索引是表级别的；但是Oracle索引不可以同名，也就是说Oracle的索引是数据库级别的。
（1） 创建索引（同）
create index indexName on tableName (columnName);
（2） 删除索引（异）
MySQL:
alter table tableName drop index indexName
Oracle:
drop index indexName
（3） 查询表的索引（异）
MySQL:
show index from tableName
Oracle:
select index_name, table_name, column_name from user_ind_columns where table_name=' tableName '
4.空字符串问题
Oracle中空字符串’‘就是null（也就是说，只有null，没有空字符），而MySQL是区分null和" "的。
对于使用语句：select * from table1 where user_name <> ''来查询列user_name不为空（不为null且不为空字符）时，Oracle会查不出任何结果，而MySQL可以正常运行。这里MySQL之所以可以得到正确结果，还因为比较符号<>会先将列为null的内容进行过滤，然后再比较内容是否为空字符串。
这就要求一方面，以后在编写代码的时候，尽量保证不会往数据库插入空字符串’‘这样的值，要么保持有数据，要么保持为null。另外，对于MySQL中已经同时存在Null和’‘时，所有判断是否为null或者’'的地方改为判断列的长度是否为0。
实例表还是使用Oracle中的员工表emp，哈哈哈
常用的查询语句
--1.查看emp表的所有信息
select * from emp;
--2.查看有哪些部门，部门有哪些人，每个人的工作是什么
select dname,ename,job from emp,dept where emp.deptno=dept.deptno;
select dname,ename,job from emp,dept where emp.deptno=dept.deptno order by dname;
--3.给每个员工多发600元，看每个员工要发多少钱 ★
select sal+nvl(comm,0)+600 "月薪" from emp;
--4.给老板打印工资表(仅sal基本工资，老板不认识英文，除了名字)
select ename "姓名",job "职称",sal "工资" from emp;
--5.字符串连接
select ename||' annual salary is '||sal "Employee' Salary" from emp;
--6.emp表中有几个部门
select count(distinct deptno) 部门数 from emp;
--7.查询每个部门中的岗位有哪些 distinct dname,job作用于后面所有的列
select distinct dname,job from emp,dept where emp.deptno=dept.deptno;
--8.查找smith的薪水，工作，所在部门
select sal+nvl(comm,0),job,dname from emp,dept where emp.deptno=dept.deptno and ename='SMITH';
--9.查找在82年1月23号入职的员工姓名、雇佣日期 --原题为1号 但1号无记录改成23号
--方法1
select ename,hiredate from emp where hiredate = '23-1月-82';
--方法2:
--session当前会话有效 scott有资格
alter session set NLS_DATE_FORMAT='yyyy-mm-dd';
select ename,hiredate from emp where hiredate = '1982-01-23';
--方法3::
select ename,hiredate from emp where hiredate=to_date('1982-01-01','yyyy-mm-dd');
--10.查找薪水在2000-3000的雇员姓名和薪水
select ename,sal from emp where sal between 2000 and 3000;
select ename,sal from emp where sal>=2000 and sal<=3000;
--11.显示首字母为S的员工的姓名、雇用日期 %
/*
%:可以匹配任意长度的内容
"_":可以匹配一个长度的内容
*/
select ename,hiredate from emp where ename like 'S%';
--12.显示第3个字符为O的所有员工姓名和工资
select ename,sal from emp where ename like '__O%';
--13.查找没有绩效的员工姓名
/*
广义绩效工资又称绩效加薪、奖励工资 也即绩效就是奖金comm
*/
select ename from emp where comm is null;
--14.查找员工姓名、工资、绩效、年收入
select ename,sal,comm,sal*12+nvl(comm,0) 年收入 from emp;
--15.查找员工岗位是SALESMAN,CLERK,MANAGER的员工信息
select * from emp where job in('SALESMAN','CLERK','MANAGER');
--16.查找员工岗位不是SALESMAN,CLERK,MANAGER的员工信息
select * from emp where job not in('SALESMAN','CLERK','MANAGER');
--看到此 2019年6月5日01:39:33
--17.按sal升序排列，如果有工资相同的，按姓名字符升序排列
select * from emp order by sal,ename;
--18.选择在部门30中的员工的所有信息
select * from emp where deptno=30;
--19.列出职位为(MANAGER)的员工编号、姓名
select empno,ename from emp where job='MANAGER';
--20.找出奖金高于工资的员工
select * from emp where comm>sal;
--21.找出每个员工奖金和工资的总和
select ename,sal+nvl(comm,0) from emp;
--22.找出部门10中的经理(MANAGER)和部门20中的普通员工(CLERK)
select * from emp where (deptno=10 and job='MANAGER') or (deptno=20 and job='CLERK');
--或者
select * from emp where deptno=20 and job='CLERK' union select * from emp where deptno=10 and job='MANAGER';
--23.找出部门10中既不是经理，也不是普通员工，而且工资大于等于2000的员工
select * from emp where job not in('MANAGER','CLERK') and sal>=2000 and deptno=10;
--24.找出没有奖金的不同工作
select distinct(job) from emp where comm is null;
--25.找出没有奖金或者奖金低于500的员工
select * from emp where comm is null or comm<500;
--更方便
select * from emp where nvl(comm,0)<500;单行函数
2.1字符型函数
1)upper 查询结果字符串全转大写
select coll from test;
select upper(coll) from test;
2)lower 查询结果字符串全转小写
select ename from emp;
select lower(ename) from emp;
3)initcap 每个单词的首字母大写
select coll from test;
select initcap(coll) from test;
4)concat 字符串拼接
select concat('hello',' world') from dual;
--输出:hello--语句输出存字符的方法
--隐藏列标题
set heading off
select '************当前用户下的表************' from dual;
set heading on
5)substr 字符串截取
--substr(s,i,[j])从字符串s第i位开始截取j个子字符串，省略j则一直截取到s末尾 (下标1开始 写0等价于写1)
select substr('helloworld',6) from dual;
--上行输出world
select substr('helloworld',6,3) from dual;
--上行输出wor
6)length 字符串长度
select length('hello') from dual;
--输出5
7)replace(s1,s2,[s3]),用字符串s3替换在s1中出现的所有字符串s2.默认s3为空字符串，即起删除作用
select replace('helloworld','low') from dual;
--输出helorld
select replace('helloworld','low','XXX') from dual;
--输出helXXXorld
8)instr instr(s1,s2[,i][,j]) s1,s2字符串 i,j整数
--返回字符串s2在字符串s1中第j次出现的位置，s1中搜索起点i,
--i为负数则搜索从右向左进行，但返回的位置还是按从左到右计算
select instr('XXXhelXXXhelXXXhel','hel',4,2) from dual;
--输出10
select instr('XXXhelXXXhelXXXhel','hel',5,2) from dual;
--输出16
select instr('XXXhelXXXhelXXXhel','hel',-5,2) from dual;
--从右往左搜索 输出4
select instr('XXXhelXXXhelXXXhel','hel',-5,3) from dual;
--无 返回0
数字型函数
1).round(num,n) 四舍五入数字num保留n位小数 (n默认值0 n为负数 小数点前n位的数字是精确的即可)
select round(3.14159) from dual;
--输出:3
select round(3.14159,4) from dual;
--输出:3.1416
select round(333.14159,-1) from dual;
--输出:330
select round(335.14159,-1) from dual;
--输出:3402).trunc(num,n) 作用同round 不过不四舍五入，只是去尾
select trunc(3.14159) from dual;
--输出:3
select trunc(3.14159,4) from dual;
--输出:3.1415
select trunc(333.14159,-1) from dual;
--输出:330
select trunc(339.14159,-1) from dual;
--输出:3303).mod(m,n) 返回m%n 取余
select mod(8,4) from dual;
--输出:0
select mod(8,3) from dual;
--输出:2
日期型函数
1).months_between(d1,d2) 返回d1,d2之间的月数 oracle里天转月时，除以基数为31
--日期类型默认 '08-9月 -81'
select months_between(to_date('2019-4-29','yyyy-mm-dd'),to_date('2019-4-30','yyyy-mm-dd')) from dual;
-- -.03225806 解释:d1<d2 返回负 d1与d2相差不到一个月，就一天。。1/31月 1/31=03225806
select months_between(to_date('2019-5-29','yyyy-mm-dd'),to_date('2019-4-29','yyyy-mm-dd')) from dual;
--输出:1 正好相差一个月 d1>d2 输出正数2).add_months(d,i) 返回日期d加上i个月之后的结果
select add_months(to_date('2019-5-29','yyyy-mm-dd'),5) from dual;
--输出:29-10月-19
select add_months(sysdate,5) from dual;
--输出:29-9月 -19
补:sysdate 系统时间
select sysdate from dual;
--输出:29-4月 -193).next_day(date,char) 返回日期date开始的下一个星期char的日期
-- date参数为日期型，
-- char:为1~7或Monday/Mon~Sunday/ 1星期天 2星期1 ... 7星期6
--比如今天星期1
select next_day(sysdate,2) from dual;
--输出:06-5月 -19 而下个星期1也确实是5月6号
--日期格式恶心死了，还是初始化时改下吧
select next_day('2019-04-29','MONDAY') from dual;
--周中的日无效 数据库语言中文导致 改成下面写法即可
select next_day('2019-04-29','星期一') from dual;
-- 输出:2019-05-064).last_day(d) 返回日期d所在月的最后一天
--不写04也行 ^_^
select last_day('2019-4-5') from dual;
--输出:2019-04-305).systime
-- select systime from dual;
-- oracle 似乎并没有systime 获取时间可以这样
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
--年
select to_char(sysdate, 'yyyy' ) from dual;
--月
select to_char(sysdate, 'MM' ) from dual;
--日
select to_char(sysdate, 'dd' ) from dual;
--季
select to_char(sysdate, 'Q') from dual;
--周--按日历上的那种，每年有52或者53周
select to_char(sysdate, 'iw') from dual;
6) systimestamp 得到当前详细日期时间信息
select systimestamp from dual;
--输出:29-4月 -19 10.45.11.100000 上午 +08:00
select to_char(systimestamp,'YYYY-MM-DD HH24:MI:SS.FF9') from dual;
--输出:2019-04-29 10:46:48.8080000007). current_timestamp
select current_timestamp from dual;
--输出:29-4月 -19 10.48.51.733000 上午 +08:00
--设置完时区再执行
alter session set time_zone='UTC';
select to_char(current_timestamp,'YYYY-MM-DD HH24:MI:SS') from dual;
--输出:2019-04-29 02:51:32
select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual;
--输出:2019-04-29 10:52:14 说明修改时区不会影响 sysdate的执行
转换函数
1).to_char(date,'format') 日期转换为指定的格式
select to_char(sysdate,'yyyy-mm-dd') from dual;
--输出:2019-04-292).to_char(数字,'format') 数字转换为字符串
select to_char(25436.78,'$999,999.99') from dual;
--输出:$25,436.783).to_date(字符串,['fmt']) 字符串转日期
select to_date('05-06---2019','mm-dd---yyyy') from dual;
--输出:2019-05-06
--前面第9题
select ename,hiredate from emp where hiredate=to_date('1982-01-23','yyyy-mm-dd');
分组函数
count 计数
--1.公司中有多少员工
select count(empno) from emp;
--2.公司中有多少员工由经理管理
select count(e1.empno) from emp e1,emp e2 where e1.mgr=e2.empno and e2.job='MANAGER';
-3.查看部门号为10的员工数
select count(empno) from emp where deptno=10;
--4.查看有多少个岗位
select count(distinct job) from emp;avg 求平均值函数
--1.查看公司的平均工资
select avg(sal) from emp;
--2.查看公司每个部门的平均工资
select deptno,avg(sal) from emp group by deptno;
sum 求和
--查看这个月工人的工资支出
select sum(sal) from emp;min
--查看公司的最少工资
select min(sal) from emp;
max
--1.查看公司最高的工资
select max(sal) from emp;
--2.查找雇佣第一员工和最迟雇佣员工
select ename,hiredate from emp where hiredate in(select max(hiredate) from emp union select min(hiredate) from emp);
group by
一般与分组函数一起使用
--查看公司每个部门的平均工资
select deptno,avg(sal) from emp group by deptno;
分组函数嵌套
--1.查找工作不是president的员工中按工作分类不同工作的最低平均工资和最高平均工资
select max(avg(sal)) 平均工资 from emp where job != 'PRESIDENT' group by job;
select min(avg(sal)) 平均工资 from emp where job != 'PRESIDENT' group by job;
select job 工作,avg(sal) 平均工资 from emp where job != 'PRESIDENT' group by job having avg(sal) in(
select max(avg(sal)) 平均工资 from emp where job != 'PRESIDENT' group by job
union
select min(avg(sal)) 平均工资 from emp where job != 'PRESIDENT' group by job
);
--方法二，不输出工作名称
select max(avg(sal)) 最高平均工资,min(avg(sal)) 最低平均工资 from emp where job != 'PRESIDENT' group by job;
--2. 查出高于本部门员工平均工资的员工信息
--各部门平均工资
select deptno,avg(sal) from emp group by deptno;
--大于本部门平均工资
select * from emp e1 where sal > (select avg(sal) from emp e2 where e2.deptno=e1.deptno) order by e1.deptno;
多表查询
相等连接
--1.查询每个员工所属部门和所在的具体地点
select ename 员工,dname 部门,loc 部门地址 from emp,dept where emp.deptno=dept.deptno;
--2.工资为1600元或者以上的员工所属的部门和地点
select ename,sal,dname,loc from emp,dept where emp.deptno=dept.deptno and sal>=1600;自连接
--1. 每个分析员及上司的姓名 (注意分析员只是众多职业其中之一 ANALYST)
select e1.ename 分析员,e2.ename 上级 from emp e1,emp e2 where e1.mgr=e2.empno and e1.job='ANALYST';不等连接
--1.查询显示工资级别在3-5级之间的所有员工
--最低
select losal from salgrade where grade=3;
--最高
select hisal from salgrade where grade=5;
--查询
select * from emp where sal between (select losal from salgrade where grade=3) and (select hisal from salgrade where grade=5);
--2.查询员工姓名、工资、工资级别
select ename,sal,grade from emp,salgrade where sal>=losal and sal<=hisal;外连接
内连接:★
格式1:显式的内连接
select a.*,b.* from a [inner] join b on ab的连接条件
格式2:隐式的内连接
select a.*,b.* from a,b where ab的连接条件
外连接:★
左外连接:★
select a.*,b.* from a left [outer] join b on 连接条件;
意思:
先展示join左边的(a)表的所有数据,根据条件关联查询 join右边的表(b),符合条件则展示出来,不符合以空白展示.
右外连接:
select a.*,b.* from b right [outer] join a on 连接条件;
意思:
先展示join右边的表(a)表的所有数据,根据条件关联查询join左边的表(b),符合条件则展示出来,不符合以空白展示.
--on子句
--1.查询员工编号、员工姓名、工资、部门号、部门地址 e.deptno为。。
select e.empno,e.ename,e.sal,e.deptno,d.loc from emp e join dept d on (e.deptno=d.deptno) order by loc;
--14行无空白
--左外连接
-- 2.同上 左外连接dept为主
select emp.empno,emp.ename,emp.sal,dept.deptno,loc from dept left outer join emp on (emp.deptno=dept.deptno);
--15行有空白
--先展示dept表的所有信息 然后根据条件展示右边emp表的信息，没有的null, 反正join左边表的每条记录至少打印一行(都要展示)
-- 而40号部门正好没人。所以有一空行
--右外连接
--3.同上 同上 右外连接dept为主
select emp.empno,emp.ename,dept.deptno,dept.loc from emp right outer join dept on (emp.deptno=dept.deptno);
--15行有空白
--先展示join右边表dept的所有记录 同样40号部门没人也要打印，对应的emp表内容为空白
子查询
一个查询依赖另一个查询.
单行子查询
--1. 查询与smith相同职位的人
select * from emp where job =(select job from emp where ename='SMITH') and ename != 'SMITH';
--2.查询职位与'SMITH'相同，而工资不超过ADAMS的员工
select * from emp where job =(select job from emp where ename='SMITH') and ename != 'SMITH'
and sal<=(select sal from emp where ename='ADAMS');
--3.查询最高工资的员工姓名、岗位、工资
select ename,job,sal from emp where sal=(select max(sal) from emp);
--4.查询高于本部门员工平均工资的员工姓名、部门号、工资、部门平均工资 ★★
--强大的临时表 select deptno,avg(sal) avgsal from emp group by deptno 查询结果作为临时表来与emp进行连接查询
col avgsal format 9999.00;
select ename,e1.deptno,sal,avgsal
from emp e1,(select deptno,avg(sal) avgsal from emp group by deptno) e2
where e1.deptno=e2.deptno and sal>avgsal order by deptno;多行子查询
1).使用in操作符的多行子查询:
--eg:查询每个部门工资最高的员工姓名、工作、工资(部门最高工资不重复时逻辑才对，否则就是工资等于某个部门最高工资的员工信息)
select ename,job,sal from emp where sal in (select max(sal) from emp group by job);
--查询与部门号10相同的工作
--本意
select distinct job from emp where job in (select job from emp where deptno=10);
--其实
select distinct job from emp where deptno=10;
2).使用all操作符的多行子查询
--查询比所有10号部门员工工资低的员工的姓名、工作、工资(两种方法) 修改原题
select ename,job,sal from emp where sal <(select min(sal) from emp where deptno=10);
select ename,job,sal from emp where sal < all(select sal from emp where deptno=10);
3).使用any操作符的多行子查询
--查询比任意部门平均工资高的员工姓名、工作、薪水（两种方法） 修改原题
select ename,job,sal from emp where sal >(select min(avg(sal)) from emp group by deptno);
select ename,job,sal from emp where sal > any(select avg(sal) from emp group by deptno);
4).多列子查询
--1.查询与smith工作、部门相同的员工
select * from emp where ename!='SMITH' and job=(select job from emp where ename='SMITH') and deptno=(select deptno from emp where ename='SMITH');
--2.如何显示高于自己部门平均工资的员工信息
select * from emp e1 where sal>(select avg(sal) from emp e2 where e2.deptno=e1.deptno);
其他语句中使用子查询
1).建表语句中使用子查询
--复制查询结果到新表
create table mytable(id,name,sal) as (select empno,ename,sal from emp);
--table 不可省略
drop table mytable;
-- 完全复制 20号部门的信息全部复制过去
create table mytable as select * from emp where deptno=20;
--可以用此来备份表 或者新建一个备份表
--只是复制表结构 where后条件为假即可
create table myemp as select * from emp where 1=2;
2).insert语句中使用子查询
--建立新表 --最后一行的,不能写 必须省略
create table employee(
name varchar2(10),
sal number(7,2),
job varchar2(10)
);
--插入数据 将查询结果批量插入 没有 as 也不能有as DDL语句才有as
insert into employee select ename,sal,job from emp where deptno=10;
3).删除语句中使用子查询
--删除工资大于工作为'CLERK'(该工资唯一)的员工信息
delete from employee where sal > (select sal from employee where job='CLERK');
4).update语句中使用子查询
update employee set sal=(select sal from employee where job ='CLERK');
dml(数据操作语言)
DML:数据操作语言
操作对象:记录
1）insert
--1)完全插入 指明要插入的具体字段值
insert into dept(deptno,dname,loc) values(50,'DEVELOPMENT','DETROIT');
--不指明具体字段，则按序每个字段都要插入一个值 用to_date函数指明日期格式，这样无论系统默认日期格式如何，都不会报错
insert into emp values(2296,'AROMANO','SALESMAN',7782,to_date('03-2月 1997'，'DD-MON,YYYY'),1300,null,10);
-- 2) 省略字段值
-- dept表共三个字段 DEPTNO DNAME LOC 仅主键DEPTNO不允许为空
--省略方法：列举列名，忽略有空值的列
insert into dept(deptno,dname) values(70,'FINANCE');
--省略列名 用null填充
insert into dept values(60,'MIS',null);
2)update
--改现有行
--更改7698号雇员的工作和部门，与第7499号雇员改为相同
--先备份
--7698 BLAKE MANAGER 7839 1981-05-01 2850 30
--多个列值 中间必须，不能是and
--update emp set job='MANAGER',deptno=30 where empno=7698;
--update set语句可以列对应赋值
update emp set (job,deptno)=(select job,deptno from emp where empno=7499) where empno=7698;
3)delete
--删除所有职位为salsman的员工信息，并练习rollback,commit
--先备份
create table salsEmp as select * from emp where job='SALESMAN';
--后期还原
-- insert into emp select * from salsEmp;
--开始删除
delete emp where job='SALESMAN';
--查询就剩10行了 SALESMAN job的全没了
-- 此时 rollback
rollback
--再次查询job为SALESMAN的又恢复了
delete emp where job='SALESMAN';
commit;
--查询 记录删除了
rollback;--无论怎么rollback记录都没有了。。。 还好提前有备份
--还原
insert into emp select * from salsEmp;
Oracle数据库优化的方法
1、减少访问数据库的次数。
2、不要让数据库做得太多。
（1）SELECT子句中避免使用' * '：ORACLE在解析的过程中，会将' * '依次转换成所有的列名， 这个工作是通过查询数据字典完成的，这意味着将耗费更多的时间。
（2）sql语句用大写的：因为oracle总是先解析sql语句，把小写的字母转换成大写的再执行。另外，在java代码中尽量少用连接符“＋”连接字符串！
（3）使用表的别名(Alias)：当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上。这样一来，就可以减少解析的时间并减少那些由Column歧义引起的语法错误。
（4） 用>=替代>：
高效: SELECT * FROM EMP WHERE DEPTNO >=4
低效: SELECT * FROM EMP WHERE DEPTNO >3
两者的区别在于， 前者DBMS将直接跳到第一个DEPT等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPT大于3的记录。
（5） 用EXISTS替代IN、用NOT EXISTS替代NOT IN：在许多基于基础表的查询中，为了满足一个条件，往往需要对另一个表进行联接.在这种情况下， 使用EXISTS(或NOT EXISTS)通常将提高查询的效率。 在子查询中，NOT IN子句将执行一个内部的排序和合并。无论在哪种情况下，NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历)。 为了避免使用NOT IN ，我们可以把它改写成外连接(Outer Joins)或NOT EXISTS。
例子：
（高效）SELECT * FROM EMP (基础表) WHERE EMPNO > 0 AND EXISTS (SELECT ‘X' FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB')
(低效)SELECT * FROM EMP (基础表) WHERE EMPNO > 0 AND DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC = ‘MELB')
（6）用EXISTS替换DISTINCT：例如:
低效: SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D,EMP E WHERE D.DEPT_NO = E.DEPT_NO
高效: SELECT DEPT_NO,DEPT_NAME FROM DEPT D WHERE EXISTS ( SELECT ‘X’ FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO);
（7）尽量用UNION-ALL 替换UNION ( if possible)
当SQL语句需要UNION两个查询结果集合时,这两个结果集合会以UNION-ALL的方式被合并， 然后在输出最终结果前进行排序。
低效：SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = ’31-DEC-95’
UNION SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = ’31-DEC-95’
高效: SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = ’31-DEC-95’
UNION ALL SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = ’31-DEC-95’
（7） 避免在索引列上使用NOT：通常，避免在索引列上使用NOT， NOT会产生在和在索引列上使用函数相同的影响。 当Oracle”遇到”NOT，就会停止使用索引转而执行全表扫描。
（8）用索引提高效率：索引是表的一个概念部分，用来提高检索数据的效率，ORACLE使用了一个复杂的自平衡B-tree结构。 通常，通过索引查询数据比全表扫描要快。当Oracle找出执行查询和Update语句的最佳路径时，Oracle优化器将使用索引。同样在联结多个表时使用索引也可以提高效率. 另一个使用索引的好处是，它提供了主键(primary key)的唯一性验证。通常, 在大型表中使用索引特别有效。 虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价。 索引需要空间来存储，也需要定期维护， 每当有记录在表中增减或索引列被修改时，索引本身也会被修改。这意味着每条记录的INSERT , DELETE , UPDATE将为此多付出4 , 5 次的磁盘I/O 。因为索引需要额外的存储空间和处理，那些不必要的索引反而会使查询反应时间变慢。定期的重构索引是有必要的。
Oracle建立索引的建议
1、表的主键、外键必须有索引；Oracle中外键不添加索引会引起死锁。当删除父表指定记录时，子表会添加表级锁，另一个进程删除父表记录（即使是不同记录）时，会造成子表死锁。当对子表的外键列添加索引后，死锁被消除，因为这时删除父表记录不需要对子表加表级锁。
2、经常与其他表进行连接的表，在连接字段上应该建立索引；
3、索引应该建在选择性高的字段上。例如：表示性别的数据列，由于只有男女两种值，就属于选择性低；
4、索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；
5、复合索引的建立需要进行仔细分析，尽量考虑用单字段索引代替：
A、正确选择复合索引中的主列字段（第一个字段），一般是选择性较好的字段；
B、复合索引的几个字段是否经常同时以AND方式出现在WHERE子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引；
C、如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引；
6、频繁进行数据操作的表，不要建立太多的索引；
7、删除无用的索引，避免对执行计划造成负面影响；
总之，索引的建立必须慎重，对每个索引的必要性都应该经过仔细分析，要有建立的依据。因为太多的索引与不充分、不正确的索引对性能都毫无益处：在表上建立的每个索引都会增加存储开销，索引对于插入、删除、更新操作也会增加处理上的开销。另外，过多的复合索引，在有单字段索引的情况下，一般都是没有存在价值的；相反，还会降低数据增加删除时的性能，特别是对频繁更新的表来说，负面影响更大。
在大多数情况下,复合索引比单字段索引好.复合索引比单字段索引的效率高，但是，复合索引比单字段索引的内容原理复杂，复合索引有两个重要原则需要把握：前缀性和可选性.如果糊里糊涂的滥用复合索引，效果适得其反。
以例子来说明，例子如下:
假设在员工表(EMP)的(ENAME,JOB,MGR)3个字段上建了一个索引，例如索引名叫IDX_1.3个字段分别为员工姓名，工作和所属经理号。
2399
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
