oracle开发注意事项小结

1）统计信息里面，有两行和性能有关。

 15  SQL*Net roundtrips to/from client

 198  rows processed

第一行表示，从buffer cache到PGA的结果集的往返次数

第二行表示，访问到的数据块里面的行的个数

计算：

SQL*Net roundtrips=[ rows processed/arraysize]+2，当且仅当，rows processed为0，则SQL*Net roundtrips等于1.

比如：15=[198/13]+2

注释：在sql*plus中，arraysize缺省为15，命令set arraysize n 可设定arraysize的值。

2）L99999，999.000

a）L：表示继承nls_territory的属性

b）9：表示替换符

c）0：表示占位符

d），：表示千分位

f）若是$999999，则查询输出便总是$打头

3）break on 字段名

作用：去掉单列重复值

SQL> select employee_id,hire_date,job_id from employees;

EMPLOYEE_ID HIRE_DATE      JOB_ID
----------- -------------- --------------------
        198 21-6月 -99     SH_CLERK
        199 13-1月 -00     SH_CLERK
        200 17-9月 -87     AD_ASST
        201 17-2月 -96     MK_MAN
        202 17-8月 -97     MK_REP
        203 07-6月 -94     HR_REP
        204 07-6月 -94     PR_REP
        205 07-6月 -94     AC_MGR
        206 07-6月 -94     AC_ACCOUNT
        100 17-6月 -87     AD_PRES
        101 21-9月 -89     AD_VP
        102 13-1月 -93     AD_VP
        103 03-1月 -90     IT_PROG
        104 21-5月 -91     IT_PROG
        105 25-6月 -97     IT_PROG
        106 05-2月 -98     IT_PROG
        107 07-2月 -99     IT_PROG
SQL> break on job_id
SQL> /

EMPLOYEE_ID HIRE_DATE      JOB_ID
----------- -------------- --------------------
        198 21-6月 -99     SH_CLERK
        199 13-1月 -00
        200 17-9月 -87     AD_ASST
        201 17-2月 -96     MK_MAN
        202 17-8月 -97     MK_REP
        203 07-6月 -94     HR_REP
        204 07-6月 -94     PR_REP
        205 07-6月 -94     AC_MGR
        206 07-6月 -94     AC_ACCOUNT
        100 17-6月 -87     AD_PRES
        101 21-9月 -89     AD_VP
        102 13-1月 -93
        103 03-1月 -90     IT_PROG
        104 21-5月 -91
        105 25-6月 -97
        106 05-2月 -98
        107 07-2月 -99
SQL> clear break
breaks 已清除

4）dba视图和user视图通常是成双成对的。如：

dba_cons_columns

user_cons_columns

5）DML的注意事项

insert和插入顺序，只要有空间就插入，随意。

update和行迁移，保留的pctfree不足，可能会有行溢出

delete无法改变HWM，全表扫描时仍然效率低下

6）update在开发中要注意：须先select ...... for update nowait；再来update

会话1：

SQL> select * from t;

        ID NAME
---------- --------------------
         1 a
         2 b
         3 c

SQL> update t set name='d' where id=1;

已更新 1 行。

会话2：

SQL> select * from t for update nowait;
select * from t for update nowait
*
第 1 行出现错误:
ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源


SQL> update t set name='www' where id=3;

已更新 1 行。

注：在同一个表的不同行进行更新时，这种方法尤为重要。

7）死锁本身是oracle自动探测，自动维护的。当发生死锁时，我们需要把最先探测到死锁的会话commit，然后，执行同一个语句；去另一个会话：commit；最后，回到原来会话，commit。

8）外键是关系型数据库的第三维。

9）DDL，DCL不仅隐式提交了自己，并且，也把前面所有交易都给提交了。

10）保存点：savepoint x1；

这里的x1是行锁的名字，也可以说是行锁的小名。

11）oracle 有行级独占锁和表级共享锁。但我们可以人为的添加：

表急独占锁：lock table table_name in exclusive mode

12)

内模式：表空间、段、区、块等

外模式：对象，如表、索引、物化视图等

oracle通过这种内/外模式的二级映像，保证了数据的独立性。

13）各种RDBMS在互相导入导出的时候，特别，也最令人疏忽的一点是，它们的命名规则可能存在差异，如表名，rman等，导致无法导入或导出。oracle很多命名都不得超过30个字符！

14）一个新建的用户，要想创建表，需要：

a）操作create table的权限

b）空间权限

注意：在角色resource中，会包含unlimited quota，但是，不推荐使用resource直接赋给用户，原因有二：

i）unlimited quota违背了最小权限的原则，甚至能在system表空间上为所欲为。

ii）角色会延迟生效，同样滴，也是延迟回收。倘若x君跳槽了，若他可以获得原公司内网ip，他仍然有权限去获取他原来能做的事。

综上：建议采纳：

alter user user_name quota Xm on tablespace_name;

如：alter user think quota 1m on users；

15）严重提醒：not in (........)里面的null，如果存在null，则返回的绝对是空值。因为，in本是或的关系，加上not，则任何值和null，逻辑与，其结果都是空。

16）set echo on：可用以显示脚本的执行内容

17）几种常见索引失效的情况，如下：

i）凡是有not的，则索引都会失效。因为，索引只会告诉你，有什么，而不会告诉你没有什么。就像书的目录一样，只会告诉你，在哪一页有什么内容。

ii）千万别用函数什么的把列给污染，否则，索引可能失效。

iii）oracle优化器基于CBO的认为，索引的代价比全表扫描来得大，那么也会废弃索引。

18）group by用于降维，但是，被它降下来的是一条直线，而不是一点。

19）聚合函数也是用于降低维度，被它降下来的就只是一点，会自动跳过空值。

20）学习oracle函数，只要记住两点：

a）悟空

b）降维

21）count（*）：会考虑空值

count（字段名）：不会考虑空值

22）建议having只用于过滤聚合函数的结果集，非聚合函数的结果集先用where过滤掉

23）定了表别名后，原名会失效，但是列别名无所谓。