ORACLE使用经验（转）

最新推荐文章于 2024-08-03 16:11:51 发布

转载最新推荐文章于 2024-08-03 16:11:51 发布 · 72 阅读

本文分享了Oracle数据库使用过程中的多项优化技巧，包括having子句的使用、外部联接+的应用、处理重复记录的方法、使用大事务时的注意事项、索引管理、数据库重建策略、快速编译视图、文件移动方法等。

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...

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

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 jfcl/hfjf@ora1 file=empdata.dmp rows=N
imp jfcl/hfjf@ora1 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’;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9417382/viewspace-932294/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9417382/viewspace-932294/