1 SQL规范
1.1 SQL操作符优化
1.1.1 IN操作符
用IN写出来的SQL的优点是比较容易写及清晰易懂,这比较适合现代软件开发的风格。但是用IN的SQL性能总是比较低的,从ORACLE执行的步骤来分析用IN的SQL与不用IN的SQL有以下区别:
ORACLE试图将其转换成多个表的连接,如果转换不成功则先执行IN里面的子查询,再查询外层的表记录,如果转换成功则直接采用多个表的连接方式查询。由此可见用IN的SQL至少多了一个转换的过程。一般的SQL都可以转换成功,但对于含有分组统计等方面的SQL就不能转换了。
推荐方案:在业务密集的SQL当中尽量不采用IN操作符。
1.1.2 NOTIN操作符
此操作是强列推荐不使用的,因为它不能应用表的索引。
推荐方案:用NOT EXISTS 或(外连接+判断为空)方案代替
1.1.3 <> 操作符(不等于)
不等于操作符是永远不会用到索引的,因此对它的处理只会产生全表扫描。
推荐方案:用其它相同功能的操作运算代替,如
a<>0 改为 a>0 or a<0
a<>’’ 改为 a>’’
1.1.4 IS NULL 或IS NOT NULL操作
判断字段是否为空一般是不会应用索引的,因为B树索引是不索引空值的。
推荐方案:
用其它相同功能的操作运算代替,如
a is not null 改为 a>0 或a>’’等。
不允许字段为空,而用一个缺省值代替空值。
建立位图索引(有分区的表不能建,位图索引比较难控制,如字段值太多索引会使性能下降,多人更新操作会增加数据块锁的现象)
1.1.5 > 及 < 操作符(大于或小于操作符)
大于或小于操作符一般情况下是不用调整的,因为它有索引就会采用索引查找,但有的情况下可以对它进行优化,如一个表有100万记录,一个数值型字段A,30万记录的A=0,30万记录的A=1,39万记录的A=2,1万记录的A=3。那么执行A>2与A>=3的效果就有很大的区别了,因为A>2时ORACLE会先找出为2的记录索引再进行比较,而A>=3时ORACLE则直接找到=3的记录索引。
1.1.6 LIKE操作符
LIKE操作符可以应用通配符查询,里面的通配符组合可能达到几乎是任意的查询,但是如果用得不好则会产生性能上的问题,如LIKE ‘%5400%’ 这种查询不会引用索引,而LIKE ‘X5400%’则会引用范围索引。一个实际例子:用YW_YHJBQK表中营业编号后面的户标识号可来查询营业编号 YY_BH LIKE ‘%5400%’ 这个条件会产生全表扫描,如果改成YY_BH LIKE ’X5400%’ OR YY_BH LIKE’B5400%’ 则会利用YY_BH的索引进行两个范围的查询,性能肯定大大提高。
1.1.7 UNION操作符
UNION在进行表链接后会筛选掉重复的记录,所以在表链接后会对所产生的结果集进行排序运算,删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录,最常见的是过程表与历史表UNION。如:
select * from gc_dfys
union
select * from ls_jg_dfys
这个SQL在运行时先取出两个表的结果,再用排序空间进行排序删除重复的记录,最后返回结果集,如果表数据量大的话可能会导致用磁盘进行排序。
推荐方案:采用UNION ALL操作符替代UNION,因为UNION ALL操作只是简单的将两个结果合并后就返回。
select * from gc_dfys
union all
select * fromls_jg_dfys
1.2 SQL书写的影响
1.2.1 同一功能同一性能不同写法SQL的影响
如一个SQL在A程序员写的为
Select* from zl_yhjbqk
B程序员写的为
Select* from dlyx.zl_yhjbqk(带表所有者的前缀)
C程序员写的为
Select* from DLYX.ZLYHJBQK(大写表名)
D程序员写的为
Select* from DLYX.ZLYHJBQK(中间多了空格)
以上四个SQL在ORACLE分析整理之后产生的结果及执行的时间是一样的,但是从ORACLE共享内存SGA的原理,可以得出ORACLE对每个SQL 都会对其进行一次分析,并且占用共享内存,如果将SQL的字符串及格式写得完全相同则ORACLE只会分析一次,共享内存也只会留下一次的分析结果,这不仅可以减少分析SQL的时间,而且可以减少共享内存重复的信息,ORACLE也可以准确统计SQL的执行频率。
1.2.2 WHERE后面的条件顺序影响
WHERE子句后面的条件顺序对大数据量表的查询会产生直接的影响,如
Select * from zl_yhjbqk where dy_dj = '1KV以下' and xh_bz=1
Select * from zl_yhjbqk where xh_bz=1 and dy_dj = '1KV以下'
以上两个SQL中dy_dj(电压等级)及xh_bz(销户标志)两个字段都没进行索引,所以执行的时候都是全表扫描,第一条SQL的dy_dj = '1KV以下'条件在记录集内比率为99%,而xh_bz=1的比率只为0.5%,在进行第一条SQL的时候99%条记录都进行dy_dj及xh_bz的比较,而在进行第二条SQL的时候0.5%条记录都进行dy_dj及xh_bz的比较,以此可以得出第二条SQL的CPU占用率明显比第一条低。
1.2.3 查询表顺序的影响
在FROM后面的表中的列表顺序会对SQL执行性能影响,在没有索引及ORACLE没有对表进行统计分析的情况下ORACLE会按表出现的顺序进行链接,由此因为表的顺序不对会产生十分耗服务器资源的数据交叉。(注:如果对表进行了统计分析,ORACLE会自动先进小表的链接,再进行大表的链接)。
1.2.4 别名使用
update bo_2_staff b2s
set b2s.staff_id = ?
where b2s.party_rela_role_cd = ?
and b2s.bo_id in
(select bo_id from order_list olwhere ol.ol_id = ?)
存过中的这个语句有什么问题?编译报错?编译成功但update数据有问题?
答案是后者,因为order_list没有bo_id,所以oracle认为是bo_2_staff的,自己in自己,所以条件是全表数据只要bo_id非空则都成立,最终的结果是把bo_2_staff全表的数据都给更新了。
正确应该是order_list换成busi_order。或者select ol.bo_id,这样编译时就会报错,避免造成数据被错误更新。
1.3 SQL语句索引的利用
1.3.1 对操作符的优化(见上节)
1.3.2 对条件字段的一些优化
1.3.2.1 采用函数处理的字段不能利用索引,如:
substr(hbs_bh,1,4)=’5400’,优化处理:hbs_bh like ‘5400%’
trunc(sk_rq)=trunc(sysdate), 优化处理:
sk_rq>=trunc(sysdate) andsk_rq<trunc(sysdate+1)
1.3.2.2 进行了显式或隐式的运算的字段不能进行索引,如:
ss_df+20>50,优化处理:ss_df>30
‘X’||hbs_bh>’X5400021452’,优化处理:hbs_bh>’5400021542’
sk_rq+5=sysdate,优化处理:sk_rq=sysdate-5
hbs_bh=5401002554,优化处理:hbs_bh=’5401002554’,注:此条件对hbs_bh 进行隐式的to_number转换,因为hbs_bh字段是字符型。
1.3.2.3 条件内包括了多个本表的字段运算时不能进行索引,如:
ys_df>cx_df,无法进行优化
qc_bh||kh_bh=’5400250000’,优化处理:qc_bh=’5400’and kh_bh=’250000’
1.3.2.4 字段类型和赋值数据类型不一致不能索引
so.busi_order_time的ol_id是varchar2, so.busi_order的ol_id是number。
则如下语句:
SELECT *FROM so.busi_order_timea, so.busi_order b
WHERE a.ol_id = b.ol_id
AND a.ol_id =100004131337;
a表用不上索引,如果a.ol_id = '100004131337'则可以。
如果把a.ol_id=100004131337改成b.ol_id=100004131337,则a也用不上索引。则需要改成:
SELECT *FROM so.busi_order_time a, so.busi_orderb
WHERE a.ol_id = to_char(b.ol_id)
AND b.ol_id =100004131337;
1.4 应用ORACLE的HINT(提示)处理
提示处理是在ORACLE产生的SQL分析执行路径不满意的情况下要用到的。它可以对SQL进行以下方面的提示
1.4.1 目标方面的提示
COST(按成本优化)
RULE(按规则优化)
CHOOSE(缺省)(ORACLE自动选择成本或规则进行优化)
ALL_ROWS(所有的行尽快返回)
FIRST_ROWS(第一行数据尽快返回)
1.4.2 执行方法的提示
USE_NL(使用NESTED LOOPS方式联合)
USE_MERGE(使用MERGE JOIN方式联合)
USE_HASH(使用HASH JOIN方式联合)
1.4.3 索引提示
INDEX(TABLE INDEX)(使用提示的表索引进行查询)
1.4.4 其它高级提示(如并行处理等等)
ORACLE的提示功能是比较强的功能,也是比较复杂的应用,并且提示只是给ORACLE执行的一个建议,有时如果出于成本方面的考虑ORACLE也可能不会按提示进行。根据实践应用,一般不建议开发人员应用ORACLE提示,因为各个数据库及服务器性能情况不一样,很可能一个地方性能提升了,但另一个地方却下降了,ORACLE在SQL执行分析方面已经比较成熟,如果分析执行的路径不对首先应在数据库结构(主要是索引)、服务器当前性能(共享内存、磁盘文件碎片)、数据库对象(表、索引)统计信息是否正确这几方面分析。
2 杂乱补充
遵循 ANSI 99年 SQL99 语法标准
sql优化:
1、能用列名时,不用*
2、oracle中where条件是从右向左解析(所以添加条件要注意顺序)
例如:where condition2 and condition1 解析顺序:condition1到condition2
3、类型转换过程:尽量使用显式转换,而非隐式转换(比较耗费性能)
4、where子句中不能有组函数,如果where跟having都可以作为判断条件,首选where
5、如果子查询跟多表查询都能实现同样的功能,尽量使用多表查询(理论上)
因为子查询操作数据库两次,多表查询操作数据库一次
6、使用高级分组函数跟集合运算能达到相同的效果,但优先选择高级分组函数
因为集合运算需要操作数据库多次
7、尽量使用绑定变量,使oracle加载一个执行计划
例如: select * from emp whereempno=7839;
select * from emp where empno=7566;
建议: select * from empwhere empno=&num;
笔记总结:
1、能用列名时,不用*
2、关于null:不是空格也不是0
2.1、包含空值的表达式都为null
以下为通用函数:
nvl(a,b) 当a为null时返回b
nvl2(a,b,c) 当a为null时返回c,否则返回b
nullif(a,b) 当a=b时,返回null,否则返回a
coalesce(a,b,c...) 从左到右,找到第一个不为null的值
2.2、null != null
用is null 或者 is not null判断,
2.3、排序时将空值排到最后,在排序后加nullslast
例如:order by comm desc nulls last
2.4、如果集合中含有null,不能使用not in操作符,但是可以使用in
例如:a not in(10,20,null) 此处错误
因为not in等同于<> all,in等同于= any,
故此处包含a <> null,null值不能参与比较运算符
2.5、组函数会自动虑空 例如:count(comm)
但count(*)*代表整行(即一条记录):只要一条记录中任何一个字段不为空,则该记录不为空
3、pl/sql 变量赋值符号:=
4、distinct去除重复项,作用于所有的列,同时满足相同时才去重
5、concat(a,b)字符串拼加 或者使用连接符 ||
6、查询基础数据格式设置:select *from v$nls_parmeters;
v$nls_parameters v$打头的数字字典:参数设置,性能相关
7、like中的转译字符 escape‘\’
8、oracle自动开启事务
9、and 逻辑与 or 逻辑或 not 逻辑否
10、oracle中where条件是从右向左解析
例如:where condition2 and condition1 解析顺序:condition1到condition2
11、order by后面可以跟 列名、表达式、别名、序号(字段的列数)
12、多列排序时,desc作用于离他最近的那一列,其余列默认为升序
13、层次查询:(树状结构)只有一张表
level:伪列 使用connect by startwith
例如:select level,empno,ename,mgr from emp connect by prior empno=mgr
start with mgr is null order by 1;
其中:prior表示前面的意思,start with表示开始查询的节点(从树根或者哪个枝干开始)
14、子查询:
不可以在主查询的group by后面放子查询
一般不在子查询中使用order by;但在Top—N分析中必须使用order by
一般情况下:先执行子查询,再执行主查询;但相关子查询除外
单行子查询只能使用单行操作符,多行子查询只能使用多行操作符(in,any,all)
select后面的子查询必须是单行子查询
相关子查询:将主查询的某个值,作为参数传递给子查询
15、行转列组函数:wm_concat()
16、集合运算:注意问题:
16.1、参与运算的各个集合必须列数相同,且类型一致,可以使用to_char(null)或者to_number(null)等补齐列数
16.2、最终的结果采用第一个集合的表头作为表头
16.3、如果要排序,必须在每个集合后使用相同的orderby
16.4、可以括号改变执行的顺序
17、打开/关闭sql执行时间 set timing on/off
18、sql的语言类型
18.1、DML语句(数据操作语言) 即增删改查 java中称为CRUD
18.2、DDL语句(数据定义语言) 例如:create/drop/alter/truncatetable,create/drop view/sequence等
18.3、DCL语句(数据控制语言) 例如:commit rollback
19、delete和truncate区别:(实际上delete操作更快,效率更高)
delete逐条删除,truncate先摧毁表,再重建
delete可能产生碎片,truncate不会
delete不会释放空间,truncate会
delete可以被闪回,truncate不会
20、创建保存点:(回滚完成后注意commit)
数据库中:savepoint a rollback tosavepoint a
JDBC中:conn.setSavepoint(Stringname) conn.rollback(Savepointsavepoint)
21、注意oracle有三种隔离级别
22、rowid:伪列(行地址)
23、oracle回收站
查看回收站:show recyclebin
清空回收站:purge recyclebin
彻底删除表:drop table test purge;(不经过回收站)
24、外键约束:特殊情况
on delete cascade:当删除父表时,级联删除子表记录,很少用
on delete set null:将子表的相关依赖记录的外检值置为null
25、索引(让不连续的记录连续起来)
create index myindex on emp(deptno,job);
会默认创建两个索引表,记录的是行地址rowid
26、同义词(别名)synonym
27、PLSQL
declare
说明部分(变量说明,光标说明,例外说明)
begin
语句序列(DML语句)
exception
例外处理语句
end;
27.1、
引用型变量
例如:myname emp.ename%type
赋值:select ename into myname from emp where empno=7839;
记录型变量(代表一行)
例如:emp_rec emp%rowtype
赋值:select * into emp_rec from emp where empno=7839;
27.2、
if语句:
if 条件 then 语句;
elsif 条件 then 语句;
else 语句;
end if;
循环1:while循环
while 条件
loop
语句;
end loop;
循环2:loop循环(相当于do while)
loop
exit when 条件;
语句;
end loop;
循环1:for循环
for i in 1..3
loop
语句;
end loop;
特殊用法:
for pename in (select enamefrom emp)
loop
语句;
end loop;
27.3、光标/游标(Cursor)==Result
定义光标:cursor c1 is select ename from emp;
打开光标:open c1;
取一行光标的值到变量中:fetch c1 into pename;
关闭光标:close c1;
注意:pename必须与emp表中的ename列类型一致
光标的属性:
%ISOPEN
%NOTFOUND
%ROUCOUNT已经取走了多少行
28、oracle的异常处理
exception
when Zero_Divide then dbns_output.put_line('0不能做被除数');
when Too_many_rows then dbns_output.put_line('Too_many_rows');
when Value_error then dbns_output.put_line('Value_error');
when others then dbns_output.put_line('其他例外');
系统定义例外:
No_data_found(没有找到数据)
Too_many_rows(select...into语句匹配多个行)
Zero_Divide(被零除)
Value_error(算数或转换错误)
Timeout_on_resource(在等待资源时发生超时)
用户定义的例外:
定义:no_emp_found exception;(已变量的形式定义)
抛出:raise no_emp_found;
29、存储过程
特别注意:不能在存储过程中提交或回滚事务
create or replace proceduce 过程名(参数列表)asPLSQL子程序体;
无参存储过程:举例如下
create or replace proceduce sayHello as
begin
dbms_output.put_line('Hello World');
end;
/
调用存储过程:
exec sayHello();
begin
sayHello();
end;
/
有参存储过程:举例如下
create or replace proceduce raiseSalary(enoin number) 其中in/out表示输入/输出
as psal emp.sal%type;
begin
select sal into psal from emp where empno=eno;
update emp set sal=sal+100 where empno=eno;
end;
/
30、存储函数:必须要有一个return子句
create or replace function 函数名(参数列表)return 函数值类型
as PLSQL子程序体;
原则:如果只有一个返回值,一般用存储函数;否则,用存储过程
31、触发器
定义在表上,特定的(insert,update,delete)
create or replace trigger 触发器名
{before|after}
{update of 列名|delete|insert}
on 表名
for each row when(条件)
PLSQL块
分为:语句级触发器,针对于表
行级触发器(for each row),针对于行
伪记录变量(代表一行) :old 操作之前的值 :new 操作之后的值
32、plsql抛出应用性异常写法
raise_application_error(-20001,'不能在非工作时间插入数据。')
注意异常类型代码取值范围:-20000到-20999之间
33、数据字典
dictionary
USER打头:用户自己的
ALL打头:用户可以访问的
DBA打头:管理员视图
V$打头:性能相关的数据
USER_OBJECTS:当前用户所创建的对象
ALL_OBJECTS:用户能访问的数据对象
USER_TABLE:当前用户所创建的表
USER_TAB_COLUMNS:当前用户所创建的列
USER_CONSTRAINTS:当前用户所创建的约束
USER_CONS_COLUMNS:当前用户创建的列约束(在哪些列上创建的约束)
34、注释comment
添加注释:
comment on table/column 表名/列名 is '描述';
注释相关的视图:
ALL_COL_COMMENTS
USER_COL_COMMENTS
ALL_TAB_COMMENTS
USET_TAB_COMMENTS
查询表的注释:
select * from user_tab_comments wheretable_name='';
35、闪回
闪回的类型
35.1、闪回表:将表回退到过去的一个时间上
错误地删除了数据,并且commin
35.2、闪回删除:操作oracle的回收站
错误的删除了表drop table
35.3、闪回版本查询:表上的历史记录
如何获取表上的历史记录
35.4、闪回事务查询:获取一个undo_sql
如何撤销一个已经提交了的事务
35.5、闪回数据库:将数据库回退到过去的一个时间上
35.6、闪回归档日志
36、导入导出
36.1、导出
表方式:只导出当前用户下的指定表
exp scott/tigger@192.168.1.104:1521/orclfile=d:/1.dmp log=d:/log.log tables=emp,dept
用户方式:导出当前用户的所有对象
exp scott/tigger@192.168.1.104:1521/orclfile=d:/1.dmp log=d:/log.log
全库方式:导出数据库中所有的对象(管理员登录)
exp scott/tigger@192.168.1.104:1521/orclfile=d:/1.dmp log=d:/log.log full=y
以上方式中:@192.168.1.104:1521/orcl可省略,默认为本地ip,端口为1521,数据库类型为orcl
36.2、导入
导入一张或几张表:
imp itcast/password@192.168.1.104:1521/orclfile=d:/1.dmp log=d:/log.log tables=emp,dept fromuser=scott touser=itcastcommit=y ignore=y;
导入用户下的表:
imp itcast/password@192.168.1.104:1521/orclfile=d:/1.dmp log=d:/log.log fromuser=scott touser=itcast commit=y ignore=y;
导入数据库:
imp system/itcast@itcastclientfile=d:/1.dmp log=d:/log.log full=y ignore=y destroy=y;
37、管理方案
38、管理用户安全
一个用户一个方案,方案即某一用户的所有对象的集合
sys账户(数据库拥有者)
有DBA权限、ADMIN OPTION的所有权限、有startup,shutdown,以及若干维护命令、拥有数字字典
system账户(拥有DBA权限)
38.1、用户权限
system:系统权限,允许用户执行对于数据库的特定行为,例如:创建表、创建用户等
object:对象权限,允许用户访问和操作一个特定的对象,例如:对其他方案下的表查询
权限的级联问题
ADMIN OPTION:撤销系统权限,不会产生级联问题
GRANT OPTION:撤销对象权限,会产生级联问题,只对DML语句起作用
39、分布数据库
数据库链路(单向的)
分布式数据库的跨节点更新
快照(snapshot):定义快照维护关系表的异步副本
指在主表修改后的指定时间内刷新副本,用户主表修改少,但频繁查询的表
触发器(tigger):利用触发器实现数据的同步备份