ORACLE使用經驗（轉）

ORACLE使用經驗

[@more@]

作者：不詳 | 來自：不詳 | 點選：533 | 釋出：2001-11-8

1.having 子句的用法
having 子句對 group by 子句所確定的行組進行控制,having 子句條件中只允許涉及常量,聚組函式或group by 子句中的列.

返回

2.外部聯接"+"的用法

外部聯接"+"按其在"="的左邊或右邊分左聯接和右聯接.若不帶"+"運算子的表中的一個行不直接匹配於帶"+"預算符的表中的任何行,則前者的行與後者中的一個空行相匹配並被返回.若二者均不帶'+',則二者中無法匹配的均被返回.利用外部聯接"+",可以替代效率十分低下的 not in 運算,大大提高執行速度.例如,下面這條命令執行起來很慢

select a.empno from emp a where a.empno not in
(select empno from emp1 where job='SALE');

倘若利用外部聯接,改寫命令如下:

select a.empno from emp a ,emp1 b
where a.empno=b.empno(+) and b.empno is null and b.job='SALE';

可以發現,執行速度明顯提高.

返回

3.處理表中的重複記錄的方法

查詢重複記錄
SELECT DRAWING,DSNO
FROM EM5_PIPE_PREFAB
WHERE ROWID!=(SELECT MAX(ROWID) FROM EM5_PIPE_PREFAB D
WHERE EM5_PIPE_PREFAB.DRAWING=D.DRAWING AND EM5_PIPE_PREFAB.DSNO=D.DSNO);
---- 執行上述SQL語句後就可以顯示所有DRAWING和DSNO相同且重複的記錄。
---- 刪除重複記錄:

DELETE FROM EM5_PIPE_PREFAB
WHERE ROWID!=(SELECT MAX(ROWID) FROM EM5_PIPE_PREFAB D
WHERE EM5_PIPE_PREFAB.DRAWING=D.DRAWING AND EM5_PIPE_PREFAB.DSNO=D.DSNO);

---- 執行上述SQL語句後就可以刪除所有DRAWING和DSNO相同且重複的記錄。

可以利用這樣的命令來刪除表內重複記錄:

delete from table_name a
where rowid< (select max(rowid) from table_name
where column1=a.column1 and column2=a.column2
and colum3=a.colum3 and ...);

不過,當表比較大(例如50萬條以上)時,這個方法的效率之差令人無法忍受,需要另想辦法(可參看拙文《電信計費中長途重複話單的技術處理》,《計算機與通訊》,1999-07).

返回

4.set transaction 命令的用法

在執行大事務時,有時oracle會報出如下的錯誤:

ORA-01555:snapshot too old (rollback segment too small)

這說明oracle給此事務隨機分配的回滾段太小了,這時可以為它指定一個足夠大的回滾段,以確保這個事務的成功執行.例如

set transaction use rollback segment roll_abc;
delete from table_name where ...
commit;

回滾段roll_abc被指定給這個delete事務,commit命令則在事務結束之後取消了回滾段的指定.

在SQL中使用最大的回滾段的方法：

Dynamically Setting the Transaction to Use the Largest Rollback Segment in the Oracle Database

This Code of the Week entry comes from Neminath Gadgade, a Software Engineer for Eccubed Inc. in Wilton, Connecticut.

This script dynamically sets the transaction to use the largest rollback segment in PL/SQL. This is usefull for batch operations like buld data loads that contain long transactions thus eliminating the need to dynamically allocate additional extents which can reduce overall system performance.

Example

BEGIN

UseBigRollbackSegment;

. DMLS...

. DMLS...

END;

/* ***************************************************************************** */

/* Author : Neminath Gadagade. */

/* Email : ngadgade@eccubed.com */

/* Purpose: This procedure Dynamically sets transaction to use the largest */

/* available rollback Segment in the Oracle database */

/* ********************************************************************************/

CREATE OR REPLACE PROCEDURE UseBigRollbackSegment

AS

l_string VARCHAR2(200) := 'set transaction use rollback segment ';

l_open_cursor INTEGER;

l_rollback_segment VARCHAR2(30);

l_execute NUMBER ;

BEGIN

SELECT segment_name

INTO l_rollback_segment

FROM dba_rollback_segs

WHERE status='ONLINE'

AND ROWNUM=1

AND initial_extent IN (

SELECT MAX(initial_extent)

FROM dba_rollback_segs

WHERE status='ONLINE');

IF l_rollback_segment is not null then

l_string := l_string ||l_rollback_segment;

COMMIT;

l_open_cursor := dbms_sql.open_cursor;

dbms_sql.parse(l_open_cursor,l_string,dbms_sql.v7);

l_execute := dbms_sql.execute(l_open_cursor);

END IF;

EXCEPTION

WHEN OTHERS THEN

NULL;

END;

/

show errors

返回

5.使用索引的注意事項

select,update,delete 語句中的子查詢應當有規律地查詢少於20%的錶行.如果一個語句查詢的行數超過總行數的20%,它將不能透過使用索引獲得效能上的提高.

---- 索引可能產生碎片,因為記錄從表中刪除時,相應也從表的索引中刪除.表釋放的空間可以再用,而索引釋放的空間卻不能再用.頻繁進行刪除操作的被索引的表,應當階段性地重建索引,以避免在索引中造成空間碎片,影響效能.在許可的條件下,也可以階段性地truncate表,truncate命令刪除表中所有記錄,也刪除索引碎片.

返回

6.資料庫重建應注意的問題
在利用import進行資料庫重建過程中,有些檢視可能會帶來問題,因為結構輸入的順序可能造成檢視的輸入先於它低層次表的輸入,這樣建立檢視就會失敗.要解決這一問題,可採取分兩步走的方法:首先輸入結構,然後輸入資料.命令舉例如下 (uesrname:jfcl,password:hfjf,host sting:ora1,資料檔案:expdata.dmp):

imp file=empdata.dmp rows=N
imp file=empdata.dmp full=Y buffer=64000
commit=Y ignore=Y

第一條命令輸入所有資料庫結構,但無記錄.第二次輸入結構和資料,64000位元組提交一次.ignore=Y選項保證第二次輸入既使物件存在的情況下也能成功.

返回

7: 快速編譯所有檢視

---- 當在把資料庫倒入到新的伺服器上後(資料庫重建)，需要將檢視重新編譯一遍，因為該表空間檢視到其它表空間的表的連線會出現問題，可以利用PL/SQL的語言特性，快速編譯。

SQL >SPOOL ON.SQL
SQL >SELECT ‘ALTER VIEW ‘||TNAME||’ COMPILE;’ FROM TAB;
SQL >SPOOL OFF
然後執行ON.SQL即可。
SQL >@ON.SQL
當然，授權和建立同義詞也可以快速進行，如：
SQL >SELECT ‘GRANT SELECT ON ’||TNAME||’ TO USERNAME;’ FROM TAB;
SQL >SELECT ‘CREATE SYNONYM ‘||TNAME||’ FOR USERNAME.’||TNAME||’;’ FROM TAB;

返回

8: 怎樣讀寫文字型作業系統檔案
在PL/SQL 3.3以上的版本中，UTL_FILE包允許使用者透過PL/SQL讀寫作業系統檔案。如下：
DECALRE
FILE_HANDLE UTL_FILE.FILE_TYPE;
BEGIN
FILE_HANDLE:=UTL_FILE.FOPEN(C:’,’TEST.TXT’,’A’);
UTL_FILE.PUT_LINE(FILE_HANDLE,HELLO,IT’S A TEST TXT FILE’);
UTL_FILE.FCLOSE(FILE_HANDLE);
END;

---- 相關UTL_FILE資料庫包詳細資訊可以參見相關資料。

返回

9: 怎樣在資料庫觸發器中使用列的新值與舊值

---- 在資料庫觸發器中幾乎總是要使用觸發器基表的列值，如果某條語句需要某列修改前的值，使用:OLD就可以了，使用某列修改後的新值，用:NEW就可以了。如:OLD.DEPT_NO,:NEW.DEPT_NO。

返回

10:有關truncate table 與delete table對空間的影響
在刪除一個表中的全部資料時，須使用TRUNCATE TABLE 表名;因為用DROP TABLE，DELETE * FROM 表名時，TABLESPACE表空間該表的佔用空間並未釋放，反覆幾次DROP，DELETE操作後，該TABLESPACE上百兆的空間就被耗光了。

返回

11: 資料庫檔案的移動方法

---- 當想將資料庫檔案移動到另外一個目錄下時，可以用ALTER DATABASE命令來移動(比ALTER TABLESPACE適用性強)：

---- A. 使用SERVER MANAGER關閉例項.

SVRMGR > connect internal;
SVRMGR > shutdown;
SVRMGR >exit;

---- B. 使用作業系統命令來移動資料庫檔案位置(假設這裡作業系統為SOLARIS 2.6). 在UNIX中用 mv命令可以把檔案移動到新的位置，

#mv /ora13/orarun/document.dbf /ora12/orarun

---- C. 裝載資料庫並用alter database命令來改變資料庫中的檔名.
SVRMGR > connect internal;
SVRMGR > startup mount RUN73;
SVRMGR > alter database rename file
> ‘/ ora13/orarun/document.dbf’
> ‘/ ora12/orarun/document.dbf’;