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 方式获取资源

    注：在同一个表的同一行进行更新时，这种方法尤为重要。可以对该记录是否被其他用户加锁进行预判断，如果已经被加锁了，则会报ora-00054错误。如果当前请求的资源被其他会话锁定时，会发生阻塞，nowait可以避免这一阻塞

 

    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）定了表别名后，原名会失效，但是列别名无所谓。