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_PROGSQL> break on job_idSQL> /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月 -99SQL> clear breakbreaks 已清除

    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 cSQL> 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）定了表別名後，原名會失效，但是列別名無所謂。