使用oracle经验梳理
转眼之间，从事ORACLE数据开发已经多年了，在此与各位一同分享一下相关经验吧，也算是对个人以往工作的内容的一次梳理。
一、 EXISTS和IN的区别
这是我以前参与过的一个项目的经验，SQL（这只是一个示意语句）语句如下
update A cd
set cd. FLAG='0001', cd.DATE = sysdate
where exists(
select 1
from (
select tc.dno,tc.gno
from B tc,C ad
where tc.GNO = ad.DNO
and ad.DNO = 'sssssssssssssssss'
) a
where a.dno = cd.dno
and a.gno = cd.gno
)
这条语句的基本功能就是两张表中的数据，查找到一条符合条件的数据，并根据这条数据的ID，修改第三张表里面的某些值，但是这三张表都是百万级的数据记录，在使用这条语句的时候，根据客户反应，大概需要20几分钟，才能正常完成，这是用户无法接受的。
我们仔细分析的SQL语句，发现使用的关键字是EXISTS，而exists是对外表作loop，每次loop再对那表进行查询，这样，多次进行大表查询，难免会变慢，再考虑一下功能相似IN关键字，IN是对表进行HASH JOIN，比较适合内外表都比较大的情形，而EXISTS适合外表比较小的情形，经过分析，大家一致通过修改为IN操作，修改后的语句如下：
update A cd
set cd.FLAG='0001',DATE = sysdate
where (dno,gno) in
(
select tc.dno,tc.gno
from B tc,C ad
where tc.GNO = ad.DNO
and ad.DECL_NO = ‘ssssssssssss’
)
修改之后，客户反映性能大大提升，现在数据量没变，但是完成相同的功能，却只需要1分钟不到的时间。
综上所述，IN 和 EXISTS都是有自己的适用环境的，并不存在哪个关键字优于哪一个的情况，同时，也要求我们在具体使用的时候，要选择适合自己需要的情况使用。
二、 ROWNUM的用法
现在ROWNUM这个字段已经大范围的使用在我们的利用数据完成分页功能中，下面，我简单说一下ROWNUM的理解吧。
rownum是oracle系统顺序分配为从查询返回的行的编号，返回的第一行分配的是1，第二行是2，依此类推，这个伪字段可以用于限制查询返回的总行数，而且rownum不能以任何表的名称作为前缀。
举例说明：
例如表：users(用户)表，表结构为：
ID varchar(6) --工号
name varchar2(10) --姓名
create table users (ID varchar(6), name VARCHAR2(100));
insert into users values('201101',‘张一’);
insert into users values('201102',‘王二’);
insert into users values('201103',‘李三’);
insert into users values('201104',‘赵四’);
commit;
(1) rownum 对于等于某值的查询条件
如果希望找到学生表中第一条学生的信息，可以使用rownum=1作为条件。但是想找到学生表中第二条学生的信息，使用rownum=2结果查不到数据。因为rownum都是从1开始，但是1以上的自然数在rownum做等于判断是时认为都是false条件，所以无法查到rownum = n（n>1的自然数）。
SQL> select rownum,id,name from student where rownum=1;（可以用在限制返回记录条数的地方，保证不出错，如：隐式游标）
SQL> select rownum,id,name from users where rownum=1;
ROWNUM ID NAME
---------------------------------------------------
1 201101 张一
SQL> select rownum,id,name from users where rownum =2;
ROWNUM ID NAME
---------------------------------------------------
（2）rownum对于大于某值的查询条件
如果想找到从第二行记录以后的记录，当使用rownum>2是查不出记录的，原因是由于rownum是一个总是从1开始的伪列，Oracle 认为rownum> n(n>1的自然数)这种条件依旧不成立，所以查不到记录
SQL> select rownum,id,name from users where rownum >2;
ROWNUM ID NAME
---------------------------------------------------
那如何才能找到第二行以后的记录呀。可以使用以下的子查询方法来解决。注意子查询中的rownum必须要有别名，否则还是不会查出记录来，这是因为rownum不是某个表的列，如果不起别名的话，无法知道rownum是子查询的列还是主查询的列。
SQL>select * from(select rownum no ,id,name from users) where no>2;
NO ID NAME
---------------------------------------------------
3 201103 李三
4 201104 赵四
SQL> select * from(select rownum,id,name from users)where rownum>2;
ROWNUM ID NAME
---------------------------------------------------
（3）rownum对于小于某值的查询条件
如果想找到第三条记录以前的记录，当使用rownum<3是能得到两条记录的。显然rownum对于rownum<n（(n>1的自然数）的条件认为是成立的，所以可以找到记录。
SQL> select rownum,id,name from users where rownum <3;
ROWNUM ID NAME
---------------------------------------------------
1 201101 张一
2 201102 王二
综上几种情况，可能有时候需要查询rownum在某区间的数据，那怎么办呀从上可以看出rownum对小于某值的查询条件是人为true的，rownum对于大于某值的查询条件直接认为是false的，但是可以间接的让它转为认为是true的。那就必须使用子查询。例如要查询rownum在第二行到第三行之间的数据，包括第二行和第三行数据，那么我们只能写以下语句，先让它返回小于等于三的记录行，然后在主查询中判断新的rownum的别名列大于等于二的记录行。但是这样的操作会在大数据集中影响速度。
SQL> select * from (select rownum no,id,name from users where rownum<=3 ) where no >=2;
NO ID NAME
---------------------------------------------------
2 201102 王二
3 201103 李三
（4）rownum和排序
Oracle中的rownum的是在取数据的时候产生的序号，所以想对指定排序的数据去指定的rowmun行数据就必须注意了。
SQL> select rownum ,id,name from users order by name;
ROWNUM ID NAME
---------------------------------------------------
3 201103 李三
2 201102 王二
1 201101 张一
4 201104 赵四
可以看出，rownum并不是按照name列来生成的序号。系统是按照记录插入时的顺序给记录排的号，rowid也是顺序分配的。为了解决这个问题，必须使用子查询
SQL> select rownum ,id,name from (select * from users order by name);
ROWNUM ID NAME
---------------------------------------------------
1 201103 李三
2 201102 王二
3 201101 张一
4 201104 赵四
这样就成了按name排序，并且用rownum标出正确序号（有小到大）
三、 结合电商项目的一个例子
在进入电商项目组后，实际发生过的例子，也拿出来与大家分享一下。大家知道电子客票号对于电商网站来说，是非常的索引，所以大家都会把这一列作为索引列（如TKTNO）。电子客票号是由前三位结算号加上10位票号构成，两者之间可以通过一个横线连接，有时候也可能直接连接。所以，我们在实际写代码的时候，希望利用票号查询一条记录，就有如下几种方式：
1、 select * from passenger where substr(tktno,4)=?
2、 Select * from passenger where tktno like ‘%’||?
3、 Select * from passenger where tktno = ‘999’||?（由于网站一旦确定，航空公司就确定，相当于结算号就是确定的）
4、 Select * from passenger where tktno = ?（这需要在前面JAVA代码中拼接结算号）
当然，可能还有很多其他方式，此处，我们就分析一下以上几种方式，可能对于大家以后开发项目比较有实际用处。
第一种是不能使用的，因为我们的数据表中一般没有建立函数索引，所以，如果使用第一种方式，tktno索引将不能使用，会带来全表扫描，导致数据库大量IO操作，再加上这种表数据量也是比较大的，所以不仅对当前业务有影响，同时数据库响应减慢，严重时导致进程等待，最后让应用服务器崩溃，停止应用。
第二种，索引同样不能使用，因为通配符（%）放在前面了，索引只支持放在后面的，所以导致的情况，将和第一种一样。
第三种，索引是利用上了，性能将会得到大大提升，但是，让数据库进行了一项连接运算，不是不可以，这也会影响数据库的性能，原因就是电商的数据库是放在一起的，每一点性能都要考虑，数据库擅长的是数据检索，对于运算，这种尽量交给前端服务器完成。
第四种，正是在第三种问题的基础上，完善得到了第四种，这一种是目前效果较好的。
我简单总结了一下数据库的相关内容，起到一个抛砖引玉的作用，如有不正确的地方，望大家不吝指教。以后大家多分享，共同进步，一起努力，让我们的项目越来越完善，中心明天越来越好。
浙公网安备 33010602011771号