部分常见ORACLE面试题以及SQL注意事项
一、表的创建：
一个通过单列外键联系起父表和子表的简单例子如下：
CREATETABLEparent(idINTNOTNULL,
PRIMARYKEY(id)
)
CREATETABLEchild(idINT,parent_idINT,
INDEXpar_ind(parent_id),
FOREIGNKEY(parent_id)REFERENCESparent(id)
ONDELETECASCADE
)
建表时注意不要用关键字当表名或字段名，如insert,use等。
CREATETABLEparent(idINTNOTNULL,
PRIMARYKEY(id)
)TYPE=INNODB;
InnoDBTables概述
InnoDB给MySQL提供了具有事务(commit)、回滚(rollback)和崩溃修复能力(crashrecoverycapabilities)的事务安全(transaction-safe(ACIDcompliant))型表。
InnoDB提供了行锁(lockingonrowlevel)，提供与Oracle类型一致的不加锁读取(non-lockingreadinSELECTs)。这些特性均提高了多用户并发操作的性能表现。在InnoDB表中不需要扩大锁定(lockescalation)，
因为InnoDB的列锁定(rowlevellocks)适宜非常小的空间。
InnoDB是MySQL上第一个提供外键约束(FOREIGNKEYconstraints)的表引擎。
InnoDB的设计目标是处理大容量数据库系统，它的CPU利用率是其它基于磁盘的关系数据库引擎所不能比的。
从一个表中查询出数据插入到另一个表中的方法：
select*intodestTblfromsrcTbl;
insertintodestTbl(fld1,fld2)selectfld1,5fromsrcTbl;
以上两句都是将srcTbl的数据插入到destTbl，但两句又有区别的。
第一句（selectintofrom）要求目标表（destTbl）不存在，因为在插入时会自动创建。
第二句（insertintoselectfrom）要求目标表（destTbl）存在，由于目标表已经存在，所以我们除了插入源表（srcTbl）的字段外，还可以插入常量，如例中的：5。
如果只想要结构而不要数据。
createtables_emp_42asselect*froms_empwhere1=2;//永假式
SQL查询练习题
1.
表1：book表，字段有id(主键)，name(书名)；
表2：bookEnrol表（图书借出归还登记），字段有id，bookId（外键），dependDate（变更时间），state（1.借出2.归还）。
idname
1English
2Math
3JAVA
idbookIddependDatestate
112009-01-021
212009-01-122
322009-01-141
412009-01-171
522009-02-142
622009-02-151
732009-02-181
832009-02-192
要求查询结果应为：(被借出的书和被借出的日期)
IdNamedependDate
1English2009-01-17
2Math2009-02-15
Selecte.bookId,b.name,e.dependDatefrombookb,bookEnrolewhere
第二个表是用来登记的，不管你是借还是还，都要添加一条记录。
请写一个SQL语句，获取到现在状态为已借出的所有图书的相关信息。
参考语句：
selectbook.id,book.name,max(dependDate)
frombookinnerjoinbookEnrolonbook.id=bookEnrol.bookidANDbooker.state=1
groupbybook.id;
2
第(1)题练习使用groupby/having子句。类似的笔试题还有：
表一：各种产品年销售量统计表sale
年产品销量
2005a700
2005b550
2005c600
2006a340
2006b500
2007a220
2007b350
要求得到的结果应为：
年产品销量
2005a700
2006b500
2007b350
即：每年销量最多的产品的相关信息。
参考答案：
Select*fromsaleawherenotexists(select*fromsalewhere年=a.年and销量>a.销量);
--or:
select*fromsaleainnerjoin(select年,max(销量)as销量fromsalegroupby年)b
ona.年=b.年anda.销量=b.销量
3.查询语句排名问题：
名次月积分(char)总积分（char）
1WhatIsJava199
2水王76981
3新浪网6596
4牛人229
5中国队6489
6北林信息6666
7加太阳5366
8中成药1133
9西洋参2526
10大拿3323
如果用总积分做降序排序..因为总积分是字符型，所以排出来是这样子(9,8,7,6,5...)，要求按照总积分的数字大小排序。
select*fromtablenameorderbycast(总积分asint)desc
表tb
uidmark
17
16
23
22
25
34
33
48
41
43
想查出uid=4的名次：
uidmc
43
selectuid,sum(mark)astotalfromtab_namegroupbyuidorderbytotaldesc;
4
表A字段如下
monthnameincome
月份人员收入
1a1000
2a2000
3a3000
要求用一个SQL语句（注意是一个）的处所有人（不区分人员）每个月及上月和下月的总收入
要求列表输出为
月份当月收入上月收入下月收入
2200010003000
Select(SelectMonthFromTableWhereMonth=To_Char(Sysdate,'mm'))月份,
(SelectSum(Income)FromTableWhereMonth=To_Char(Sysdate,'mm'))当月收入,
(SelectSum(Income)FromTableWhereTo_Number(Month)=To_Number(Extract(MonthFromSysdate))-1)上月收入,
(SelectSum(Income)FromTableWhereTo_Number(Month)=To_Number(Extract(MonthFromSysdate))+1)下月收入
FromDual
5.删除重复记录
方法原理：
1、Oracle中，每一条记录都有一个rowid，rowid在整个数据库中是唯一的，
rowid确定了每条记录是在ORACLE中的哪一个数据文件、块、行上。
2、在重复的记录中，可能所有列的内容都相同，但rowid不会相同，所以只要确定出重复记录中
那些具有最大rowid的就可以了，其余全部删除。
实现方法：
SQL>createtablea(
2bmchar(4),--编码
3mcvarchar2(20)--名称
4)
5/
SQL>selectrowid,bm,mcfroma;
ROWIDBMMC
-----------------------------
000000D5.0000.000211111111
000000D5.0001.000211121111
000000D5.0002.000211131111
000000D5.0003.000211141111
000000D5.0004.000211111111
000000D5.0005.000211121111
000000D5.0006.000211131111
000000D5.0007.000211141111
查询到8记录.
查出重复记录
SQL>selectrowid,bm,mcfromawherea.rowid!=(selectmax(rowid)fromabwherea.bm=b.bmanda.mc=b.mc);
ROWIDBMMC
------------------------------------------
000000D5.0000.000211111111
000000D5.0001.000211121111
000000D5.0002.000211131111
000000D5.0003.000211141111
删除重复记录
SQL>deletefromaawherea.rowid!=(selectmax(rowid)fromabwherea.bm=b.bmanda.mc=b.mc);
删除4个记录.
SQL>selectrowid,bm,mcfroma;
ROWIDBMMC
------------------------------------------
000000D5.0004.000211111111
000000D5.0005.000211121111
000000D5.0006.000211131111
000000D5.0007.000211141111
其他组合函数
Groupby子句
Distinct关键字
伪列ROWNUM，用于为子查询返回的每个行分配序列值
注意：组函数可以处理一组数据，返回一个值。组函数会忽略空值。where后只能跟单行函数，不能有组函数。
使用TOP-N分析法
TOP-N分析法基于条件显示表中最上面N条记录或最下面N条记录
TOP-N查询包含以下内容：
1，一个用于排序数据的内联视图
2，使用ORDERBY子句或DESC参数的子查询
3，一个外层查询。由它决定最终记录中行的数目。这包括ROWNUM伪列和用于比较运算符的WHERE子句
//语法：
SELECTROWNUM,column_list
FROM(SELECTcolumn_listFROMtable_nameORDERBYTop-n-column_name)
WHEREROWNUM<=N
例1：查询Employee表的顶部10条记录
//方法1：单表时可以用
selectcEmployeeCode,vFirstName,vLastNamefromemployeewhererownum<=10
//方法2：较复杂的查询，建议使用这种
select*from(selectrownumasnum,cEmployeeCode,vFirstName,vLastNamefromemployee)
wherenum<=10
例2:查询Employee表的第1到第10条记录，可以用于分页显示
//注意:因为这里子查询的rownum需要被外层查询所使用，因此要使用别名，否则将被认为是两个不同的rownum
select*from(selectrownumasnum,Employee.*fromEmployee)wherenumbetween10and20
select*from(selectrownumasnum,Employee.*fromEmployee)wherenumbetween1and10
SQL注入1=1永远成立,相当于查询所有记录
select*fromperson_zdkwhere1=1ornamelike'%a%'andage=13;
DECODE函数
是ORACLEPL/SQL是功能强大的函数之一，目前还只有ORACLE公司的SQL提供了此函数，其他数据库厂商的SQL实现还没有此功能。
decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)
该函数的含义如下:
IF条件=值1THEN
RETURN(翻译值1)
ELSIF条件=值2THEN
RETURN(翻译值2)
......
ELSIF条件=值nTHEN
RETURN(翻译值n)
ELSE
RETURN(缺省值)
ENDIF
假设我们想给智星职员加工资，其标准是：工资在8000元以下的将加20％；工资在8000元以上的加15％，用DECODE函数，那么我们就可以把这些流控制语句省略，通过SQL语句就可以直接完成。如下：selectdecode(sign(salary-8000),1,salary*1.15,-1,salary*1.2,salaryfromemployee.
SQL中的单记录函数
1.CONCAT
连接两个字符串;
SQL>selectconcat('010-','88888888')||'转23'高乾竞电话fromdual;
高乾竞电话
----------------
010-88888888转23
2.LTRIM和RTRIM
LTRIM删除左边出现的字符串
RTRIM删除右边出现的字符串
SQL>selectltrim(rtrim('gaoqianjing',''),'')fromdual;
LTRIM(RTRIM('
-------------
gaoqianjing
3..SUBSTR(string,start,count)
取子字符串,从start开始,取count个
SQL>selectsubstr('13088888888',3,8)fromdual;
SUBSTR('
--------
08888888
4日期函数
如:LAST_DAY返回本月日期的最后一天
具体参见oracle笔记.
其他主要函数：.TRUNC按照指定的精度截取一个数；SQRT返回数字n的根；POWER（n1,n2）返回n1的n2次方根；MOD(n1,n2)返回一个n1除以n2的余数；FLOOR对给定的数字取整数；REPLACE('string','s1','s2')string希望被替换的字符或变量s1被替换的字符串s2要替换的字符串；LOWER返回字符串,并将所有的字符小写；UPPER返回字符串,并将所有的字符大写；LENGTH
返回字符串的长度。
ORALCE常识及SQL基本语法
1，ORACLE安装完成后的初始口令？
internal/oracle
sys/change_on_install
system/manager
scott/tigerscott是Oracle的核心开发人员之一，tiger是他家的一只猫的名字
sysman/oem_temp
例：connscott/tiger@jspdev;
connsystem/manager@jspdevassysdba;
2，IBM的Codd（EdgarFrankCodd）博士提出《大型共享数据库数据的关系模型》
3,ORACLE9i中的i（internet）是因特网的意思
4,ORACLE的数据库的物理结构：数据文件、日志文件、控制文件
5,ORACLE的数据库的逻辑结构：表空间——表——段——区间——块
表空间类似于SQLSERVER中数据库的概念
6,SYSDATE返回当前系统日期（说明：当函数没有参数时可以省略括号）
7,在SQLPLUS中执行缓冲区中的SQL命令的方式：
SQL>run
SQL>r
SQL>/
8,在SQLPLUS中修改当前会话的日期显示格式
SQL>altersessionsetnls_date_format='YYYY-MM-DD'
9,使用临时变量，提高输入效率
SQL>insertintoemp(empno,ename,sal)values(&;employeeno,'&;employeename',&;employeesal);
10,从其他表中复制数据并写入表
SQL>insertintomanagers(id,name,salary,hiredate)
SQL>selectempno,ename,sal,hiredate
SQL>fromemp
SQL>wherejob='MANAGER';
11,修改表中的记录
SQL>updatetablesetcolumn=value[,column=value,……][wherecondition];
12,删除表中的记录
SQL>delete[from]table[wherecondition];
13,数据库事务，事务是数据库一组逻辑操作的集合
一个事务可能是：
多个DML语句
单个DDL语句
单个DCL语句
14,事务控制使用savepoint,rollback,commit关键字
SQL>savepointaaa;
SQL>rollbacktoaaa;
SQL>commit;
15,查询表中的数据
select*fromtable_name;
selectcolumn_listfromtable_name;
16,NumberandDate可以用于算术运算
因为Date类型其实存储为Number类型
17,用运算表达式产生新列
SQL>selectename,sal,sal+3000fromemp;
SQL>selectename,sal,12*sal+100fromemp;
18,算术表达式中NULL值错误的处理
因为任何数与NULL运算无意义，所以为避免错误，需要用其他值替换NULL值
例如：
SQL>selectename"",12*sal+comm"年薪"fromempwhereename='KING';
薪水
--------------------
KING
因为comm（提成工资）列为NULL值，结果也出现了NULL值，所以需要用0来替换NULL
注意函数nvl的使用NVL(原值,新值)
SQL>selectename"",12*sal+NVL(comm,0)"年薪"fromempwhereename='KING';
员工员工薪水
--------------------
KING60000
——————————————
19,使用友好的列名,有下面三种形式
SQL>selectenameas,sal月薪,sal*12"年薪"fromemp
20,过滤重复行，使用关键字distinct
SQL>selectdistinct*fromemp;
21,SQLPLUS访问ORACLE数据库的原理
SQL*Plus—>Buffer—>Server—>QueryResult
22,where子句中字符型是区分大小写的，最好都转成大写
因为在ORACLE库中，字符会转换成大写来保存
23,比较运算符：等于"=",不等于有两种"<>"或者"!="
24,复杂的比较运算符：
between……and……
in(……valuelist……)
like（%代表匹配至多个任意字符，_代表单个任意字符）
null（与NULL进行比较时，需要使用isnull或者isnotnull）
25,逻辑运算符，按优先级从高到低排列
Not,And,Or
26,Orderby子句中（asc表示升序，desc表示降序）
27,ORACLE函数，分为
单行函数：每条记录返回一个结果值
多行函数：多条记录返回一个结果值
28,字符函数——转换函数
LOWER：转为小写
UPPER：转为大写
INITCAP：将每个单词的首字母大写，其他字母小写
29,字符函数——操纵函数（注意：ORACLE以UNICODE存储字符）
CONCAT:连接两个字符串，与并置运算符“||”类似
SUBSTR:substr(string,position,length)从string中的position开始取length个字符
LENGTH:返回字符串的长度
INSTR:instr(string,value)返回value在string的起始位置
LPAD:lpad(string,number,value)若string不够number位，从左起用vlaue字符串填充(不支持中文)
30,四舍五入函数round(数值,小数位)
SQL>SELECTROUND(45.923,2),ROUND(45.923,0),ROUND(45.923,-1)FROMDUAL;
ROUND(45.923,2)ROUND(45.923,0)ROUND(45.923,-1)
----------------------------------------------
45.924650
31,数值截取函数trunct
SQL>SELECTTRUNC(45.923,2),TRUNC(45.923,0),TRUNC(45.923,-1)FROMDUAL;
TRUNC(45.923,2)TRUNC(45.923,0)TRUNC(45.923,-1)
----------------------------------------------
45.924540
32,求模函数MOD(a,b)返回a被b整除后的余数
33,Oracle内部默认的日期格式:DD-MON-YY（24-9月-06）
34,DUAL：哑元系统表，是名义表，只能范围唯一值
35,Date类型的算术运算，以天为单位
例如：部门编号为10的员工分别工作了多少年
SQL>selectename,(sysdate-hiredate)/365asyearsfromempwheredeptno=10;
ENAMEYEARS
--------------------
CLARK25.3108341
KING24.8697382
MILLER24.6861766
36,日期函数
MONTHS_BETWEEN返回两个日期之间相差多少个月
ADD_MONTHS在日期上加上月份数
NEXT_DAY下一个日子selectnext_day(sysdate,'星期一')fromdual;
LAST_DAY该月的最后一天
ROUND四舍五入日期round(sysdate,'year')或者round(sysdate,'month')
TRUNC截取日期trunc(sysdate,'year')或者trunc(sysdate,'month')
37,数据类型转换——Oracle可隐式转换的情况有：
FromTo
varchar2orchar——number（当字符串是数字字符时）
varchar2orchar——date
number——varchar2
date——varchar2
38,数据类型转换——Oracle数据类型转换函数
to_char
to_number
to_date
39,日期格式模型字符
YYYY代表完整的年份
YEAR年份
MM两位数的月份
MONTH月份的完整名称
DY每星期中天的三个字符缩写
DAY表示星期日——星期六
另外还有D,DD,DDD等。。。
40,NVL(value,substitute)
value:是可能有null的列，substitute是缺省值
这个函数的作用就是当出现null值的时候，后缺省值替换null
41,Coalesce(exp_name1,exp_name2……exp_n)
42,Decode函数：Decode(exp,testvalue1,resultvalue1,testvalue2,resultvalue2)
例如，根据国家名称显示相应的国家代码:
1>创建国家表
createtablecountrys
(
vCountryNamevarchar2(50)
);
2>写入几行，分别为中国、日本、韩国
insertintocountrysvalues('&;name');
3>用DECODE函数，进行匹配和显示
selectvCountryNameas"国家名称",
DECODE(vCountryName,'中国','086','日本','116')as"国家编号"fromcountrys;
国家名称国家编号
-----------------------------------------------------
中国086
日本116
韩国
结果，在DECODE中存在且成功匹配的值将会被显示，否则显示为NULL
SQL语句书可以提高执行效率的方法
1、操作符号：NOTIN操作符
此操作是强列推荐不使用的，因为它不能应用表的索引。推荐方案：用NOTEXISTS或（外连接+判断为空）方案代替"ISNULL","<>","!=","!>","!<","NOT","NOTEXISTS","NOTIN","NOTLIKE","LIKE'%500'"，因为他们不走索引全是表扫描。NOTIN会多次扫描表，使用EXISTS、NOTEXISTS、IN、LEFTOUTERJOIN来替代，特别是左连接,而Exists比IN更快，最慢的是NOT操作。
2、注意union和unionall的区别。union比unionall多做了一步distinct操作。能用unionall的情况下尽量不用union。
如:两个表A和B都有一个序号字段ID,要求两个表中的ID字段最大的值：
selectmax(id)asmax_id
from(
selectidfrom表A
unionall
selectidfrom表B)t
3、查询时尽量不要返回不需要的行、列。另外在多表连接查询时，尽量改成连接查询，少用子查询。
4、尽量少用视图，它的效率低。对视图操作比直接对表操作慢,可以用存储过程来代替它。特别的是不要用视图嵌套,嵌套视图增加了寻找原始资料的难度。
我们看视图的本质：它是存放在服务器上的被优化好了的已经产生了查询规划的SQL。对单个表检索数据时，不要使用指向多个表的视图，
直接从表检索或者仅仅包含这个表的视图上读，否则增加了不必要的开销,查询受到干扰.为了加快视图的查询，MsSQL增加了视图索引的功能。
5、创建合理的索引，对于插入或者修改比较频繁的表，尽量慎用索引。因为如果表中存在索引，插入和修改时也会引起全表扫描。
索引一般使用于where后经常用作条件的字段上。
6、在表中定义字段或者存储过程、函数中定义参数时，将参数的大小设置为合适即可，勿设置太大。这样开销很大。
7、Between在某些时候比IN速度更快,Between能够更快地根据索引找到范围。用查询优化器可见到差别。
select*fromchineseresumewheretitlein('男','女')
Select*fromchineseresumewherebetween'男'and'女'是一样的。由于in会在比较多次，所以有时会慢些。
8、在必要是对全局或者局部临时表创建索引，有时能够提高速度，但不是一定会这样，因为索引也耗费大量的资源。他的创建同是实际表一样。
9、WHERE后面的条件顺序影响
WHERE子句后面的条件顺序对大数据量表的查询会产生直接的影响，如
Select*fromzl_yhjbqkwheredy_dj='1KV以下'andxh_bz=1
Select*fromzl_yhjbqkwherexh_bz=1anddy_dj='1KV以下'
以上两个SQL中dy_dj（电压等级）及xh_bz（销户标志）两个字段都没进行索引，所以执行的时候都是全表扫描，如果dy_dj='1KV以下'条件在记录集内比率为99%，而xh_bz=1的比率只为0.5%，在进行第一条SQL的时候99%条记录都进行dy_dj及xh_bz的比较，而在进行第二条SQL的时候0.5%条记录都进行dy_dj及xh_bz的比较，以此可以得出第二条SQL的CPU占用率明显比第一条低。所以尽量将范围小的条件放在前面。。
10、用OR的字句可以分解成多个查询，并且通过UNION连接多个查询。他们的速度只同是否使用索引有关,如果查询需要用到联合索引，用UNIONall执行的效率更高.多个OR的字句没有用到索引，改写成UNION的形式再试图与索引匹配。一个关键的问题是否用到索引。
11、没有必要时不要用DISTINCT和ORDERBY，这些动作可以改在客户端执行。它们增加了额外的开销。这同UNION和UNIONALL一样的道理。
12、使用in时，在IN后面值的列表中，将出现最频繁的值放在最前面，出现得最少的放在最后面，这样可以减少判断的次数
13、当用SELECTINTO时，它会锁住系统表(sysobjects，sysindexes等等)，阻塞其他的连接的存取。创建临时表时用显示声明语句，在另一个连接中SELECT*fromsysobjects可以看到SELECTINTO会锁住系统表，Createtable也会锁系统表(不管是临时表还是系统表)。所以千万不要在事物内使用它！！！这样的话如果是经常要用的临时表请使用实表，或者临时表变量。
14、一般在GROUPBY和HAVING字句之前就能剔除多余的行，所以尽量不要用它们来做剔除行的工作。他们的执行顺序应该如下最优：select的Where字句选择所有合适的行，GroupBy用来分组个统计行，Having字句用来剔除多余的分组。这样GroupBy和Having的开销小，查询快.对于大的数据行进行分组和Having十分消耗资源。如果GroupBY的目的不包括计算，只是分组，那么用Distinct更快
15、一次更新多条记录比分多次更新每次一条快,就是说批处理好
16、慎用临时表，临时表存储于tempdb库中，操作临时表时，会引起跨库操作。尽量用结果集和表变量来代替它。
17、尽量将数据的处理工作放在服务器上，减少网络的开销，如使用存储过程。存储过程是编译好、优化过，并且被组织到一个执行规划里、且存储在数据库中的SQL语句，是控制流语言的集合，速度当然快。
18、不要在一段SQL或者存储过程中多次使用相同的函数或相同的查询语句，这样比较浪费资源,建议将结果放在变量里再调用。这样更快。
19、按照一定的次序来访问你的表。如果你先锁住表A，再锁住表B，那么在所有的存储过程中都要按照这个顺序来锁定它们。如果你（不经意的）某个存储过程中先锁定表B，再锁定表A，这可能就会导致一个死锁。
.
如何写出性能优良的SQL
（1）选择最有效率的表名顺序(只在基于规则的优化器中有效)：
ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表(基础表drivingtable)将被最先处理，在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。如果有3个以上的表连接查询,那就需要选择交叉表(intersectiontable)作为基础表,交叉表是指那个被其他表所引用的表.
（2）WHERE子句中的连接顺序．：ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前,那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.
（3）SELECT子句中避免使用‘*‘：ORACLE在解析的过程中,会将'*'依次转换成所有的列名,这个工作是通过查询数据字典完成的,这意味着将耗费更多的时间
（4）减少访问数据库的次数：ORACLE在内部执行了许多工作:解析SQL语句,估算索引的利用率,绑定变量,读数据块等；
（5）在SQL*Plus,SQL*Forms和Pro*C中重新设置ARRAYSIZE参数,可以增加每次数据库访问的检索数据量,建议值为200
（6）使用DECODE函数来减少处理时间：使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表.
（7）整合简单,无关联的数据库访问：如果你有几个简单的数据库查询语句,你可以把它们整合到一个查询中(即使它们之间没有关系)
（8）删除重复记录：最高效的删除重复记录方法(因为使用了ROWID)例子：
DELETEFROMEMPEWHEREE.ROWID>(SELECTMIN(X.ROWID)FROMEMPXWHEREX.EMP_NO=E.EMP_NO);
（9）用TRUNCATE替代DELETE：
当删除表中的记录时,在通常情况下,回滚段(rollbacksegments)用来存放可以被恢复的信息.如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况)而当运用TRUNCATE时,回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短.(译者按:TRUNCATE只在删除全表适用,TRUNCATE是DDL不是DML)
（10）尽量多使用COMMIT：
只要有可能,在程序中尽量多使用COMMIT,这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少:
COMMIT所释放的资源:
a.回滚段上用于恢复数据的信息.b.被程序语句获得的锁c.redologbuffer中的空间d.ORACLE为管理上述3种资源中的内部花费
（11）用Where子句替换HAVING子句：
避免使用HAVING子句,HAVING只会在检索出所有记录之后才对结果集进行过滤.这个处理需要排序,总计等操作.如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销.(非oracle中)on、where、having这三个都可以加条件的子句中，on是最先执行，where次之，having最后，因为on是先把不符合条件的记录过滤后才进行统计，它就可以减少中间运算要处理的数据，按理说应该速度是最快的，where也应该比having快点的，因为它过滤数据后才进行sum，在两个表联接时才用on的，所以在一个表的时候，就剩下where跟having比较了。在这单表查询统计的情况下，如果要过滤的条件没有涉及到要计算字段，那它们的结果是一样的，只是where可以使用rushmore技术，而having就不能，在速度上后者要慢如果要涉及到计算的字段，就表示在没计算之前，这个字段的值是不确定的，根据上篇写的工作流程，where的作用时间是在计算之前就完成的，而having就是在计算后才起作用的，所以在这种情况下，两者的结果会不同。在多表联接查询时，on比where更早起作用。系统首先根据各个表之间的联接条件，把多个表合成一个临时表后，再由where进行过滤，然后再计算，计算完后再由having进行过滤。由此可见，要想过滤条件起到正确的作用，首先要明白这个条件应该在什么时候起作用，然后再决定放在那里
（12）减少对表的查询：在含有子查询的SQL语句中,要特别注意减少对表的查询.例子：
SELECTTAB_NAMEFROMTABLESWHERE(TAB_NAME,DB_VER)=(SELECT
TAB_NAME,DB_VERFROMTAB_COLUMNSWHEREVERSION=604)
（13）通过内部函数提高SQL效率.：
复杂的SQL往往牺牲了执行效率.能够掌握上面的运用函数解决问题的方法在实际工作中是非常有意义的
（14）使用表的别名(Alias)：
当在SQL语句中连接多个表时,请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误.
（15）用EXISTS替代IN、用NOTEXISTS替代NOTIN：
在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下,使用EXISTS(或NOTEXISTS)通常将提高查询的效率.在子查询中,NOTIN子句将执行一个内部的排序和合并.无论在哪种情况下,NOTIN都是最低效的(因为它对子查询中的表执行了一个全表遍历).为了避免使用NOTIN,我们可以把它改写成外连接(OuterJoins)或NOTEXISTS.
例子：（高效）SELECT*FROMEMP(基础表)WHEREEMPNO>0ANDEXISTS(SELECT‘X'FROMDEPTWHEREDEPT.DEPTNO=EMP.DEPTNOANDLOC=‘MELB')
(低效)SELECT*FROMEMP(基础表)WHEREEMPNO>0ANDDEPTNOIN(SELECTDEPTNOFROMDEPTWHERELOC=‘MELB')
（16）识别'低效执行'的SQL语句：
虽然目前各种关于SQL优化的图形化工具层出不穷,但是写出自己的SQL工具来解决问题始终是一个最好的方法：
SELECTEXECUTIONS,DISK_READS,BUFFER_GETS,
ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2)Hit_radio,
ROUND(DISK_READS/EXECUTIONS,2)Reads_per_run,
SQL_TEXTFROMV$SQLAREWHEREEXECUTIONS>0ANDBUFFER_GETS>0AND(BUFFER_GETS-DISK_READS)/BUFFER_GETS<0.8ORDERBY4DESC;
（17）用索引提高效率：
索引是表的一个概念部分,用来提高检索数据的效率，ORACLE使用了一个复杂的自平衡B-tree结构.通常,通过索引查询数据比全表扫描要快.当ORACLE找出执行查询和Update语句的最佳路径时,ORACLE优化器将使用索引.同样在联结多个表时使用索引也可以提高效率.另一个使用索引的好处是,它提供了主键(primarykey)的唯一性验证.。那些LONG或LONGRAW数据类型,你可以索引几乎所有的列.通常,在大型表中使用索引特别有效.当然,你也会发现,在扫描小表时,使用索引同样能提高效率.虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价.索引需要空间来存储,也需要定期维护,每当有记录在表中增减或索引列被修改时,索引本身也会被修改.这意味着每条记录的INSERT,DELETE,UPDATE将为此多付出4,5次的磁盘I/O.因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.。定期的重构索引是有必要的.：
ALTERINDEX
REBUILD
（18）用EXISTS替换DISTINCT：
当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用DISTINCT.一般可以考虑用EXIST替换,EXISTS使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果.例子：
(低效):SELECTDISTINCTDEPT_NO,DEPT_NAMEFROMDEPTD,EMPEWHERED.DEPT_NO=E.DEPT_NO
(高效):SELECTDEPT_NO,DEPT_NAMEFROMDEPTDWHEREEXISTS(SELECT‘X'
FROMEMPEWHEREE.DEPT_NO=D.DEPT_NO);
（19）sql语句用大写的；因为oracle总是先解析sql语句，把小写的字母转换成大写的再执行
（20）在java代码中尽量少用连接符“＋”连接字符串！
（21）避免在索引列上使用NOT通常，
我们要避免在索引列上使用NOT,NOT会产生在和在索引列上使用函数相同的影响.当ORACLE”遇到”NOT,他就会停止使用索引转而执行全表扫描.
一道数据库的笔试题目
一道数据库的笔试题目
有两个表
表一AAA
种类mc库存总量s1
A997
B1234
表二BBB
种类mc出库数量s1
A105
A213
B116
B211
B303
用一条SQL语句求出A,B各剩下多少?
selectdistinctAA.Tkind,AA.S-(selectsum(BB.S)sumnumfromBBgroupbyThavingAA.T=BB.T)stockfromAA,BBwhereAA.T=BB.T
结果：
kindstock
A679
B604
selectdistinctaa.kingzhonglei,aa.zl-czl.sumkucunfromaa,bb,(selectking,sum(czl)sumfrombbgroupbyking)czlwhereaa.king=bb.kingandaa.king=czl.king;
结果：
zhongleikucun
a679
b604
selectdistinctaaa.mczhonglei,aaa.sl-kczl.kcslkucunfromaaa,(selectmc,sum(sl)kcslfrombbbgroupbymc)kczlwhereaaa.mc=kczl.mc;
