Oracle-Sql规范

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）：利用触发器实现数据的同步备份