1 程式优化概览
1.1 概览
1.1.1 影响效率原因
数据量大
使用的FOREACH 架构内部的处理逻辑太多
1.1.2 优化思路
尽量减少程序进出数据库的次数 ：即整合多个 SQL到一个SQL中
尽量不要使用FOREACH架构
使用索引，提高SQL 执行效率
1.2 临时表
1.2.1 临时表 –建立
一般把需要建立的所有临时表的代码写到一个函数中
临时表如果数据量大也需要建立索引
BEGIN WORK 之前调用建立临时表的函数
方法一
CREATE TEMP TALBE r111_tmp(
tlf01 varchar(40),
tlf02 number(5))
方法二
CREATE TEMP TALBE r111_tmp(
tlf01 LIKE tlf_file.tlf01,
tlf02 LIKE tlf_file.tlf02)
方法三
SELECT tlf01,tlf02 FROM tlf_file WHERE 1=2
INTO TEMP r111_tmp
1.2.2 临时表 –清空
一般把需要清空的所有临时表的代码写到一个函数中
写入数据之前调用清空临时表的函数
语法：
TRUNCATE TABLE [ { database_name .[ schema_name ] . | schema_name . } ] table_name [ ; ]
用途：
DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一个项。TRUNCATE
TABLE 通过释放用于存储表数据的数据页来删除数据，并且在事务日志中只记录页释放。
1.2.3 临时表 –查看
#例如在程式中新建了一张临时表:INB_tmp
使用TOAD 或者PL/SQL连入sys(sys的初始密码为 sys),然后运行以下的SQL（注意表名要大写）
select * from dba_objects where object_name LIKE ‘TT%INB_TMP’
#得到结果如下：
其中OWNER：为建立的临时表所在的 DB，要连入此DB才能使用和该临时表相关的 SQL
OBJECT_NAME：为通过TIPTOP后生成的真正的的表名，是增加了 TT和SID号的。
Ø 写入临时表
语法：
Insert into Table2(field1,field2,…)
select value1,value2,…
from Table1
1.2.4 临时表 –删除
#一般把需要清空的所有临时表的代码写到一个函数中
#退出程式之前或者 COMMIT WORK 之前调用清空临时表的函数
语法
DROP TABLE table_name
删除表定义及该表的所有数据、索引、触发器、约束和权限规范
。
1.3 MERGE 用法
1.3.1 用途
在一个SQL语句中对一个表同时执行 inserts和updates操作. MERGE 命令从一个或多个数据
源中选择行来 updating或inserting到一个或多个表.
1.3.2 语法
MERGE [hint] INTO [schema .] table [t_alias]
USING [schema .] { table | view | subquery } [t_alias]
ON ( condition )
WHEN MATCHED THEN merge_update_clause
WHEN NOT MATCHED THEN merge_insert_clause;
using 子句
在 using 子句中指定用来修改或者插入的数据源。数据源可以是表、视图或者一个子查询
语句
on 子句
在 on 子句中指定执行插入或者修改的满足条件。在目标表中符合条件的每一行，oracle 用
数据源中的相应数据修改这些行。对于不满足条件的那些行，oracle 则插入数据源中相应
数据
when matched | not matched
用该子句通知 oracle 如何对满足或不满足条件的结果做出相应的操作。
2 程式优化范例讲解
2.1 使用USING
例：在foreach中每次循环都需要运行某个语句，尤其是耗时的语句，可以用USING XX，XX这类语句替代。好处是这种语句，可以做到多次循环只读一次数据库。
FOREACH ffffff_cs11 INTO l_xxx
SELECT SUM(afb10) into l_xxx FROM afb_file WHERE afb00 = ’00’
AND afb041= l_afc.afc041,
AND afb01 = l_afc.afc01
这样写会每次循环都读取一次数据库，如果一次循环还有很多这种语句，就会拖慢整个程序。
改进：
LET l_sql = “SELECT SUM(afb10) into l_xxx FROM afb_file WHERE afb00 = ’00’ “,
” WHERE afb041= ? “,
” AND afb01 = ? ”
PREPARE show_ref_p3 FROM l_sql
DECLARE show_ref_c3 CURSOR FOR show_ref_p3
FOREACH ffffff_cs11 INTO l_xxx
OPEN show_ref_c1 USING l_afc.afc041,l_afc.afc01
FETCH show_ref_c1 INTO l_xxx
IF STATUS THEN
let l_xxx=0
END IF
CLOSE show_ref_c1
2.2 能一条语句解决的，就不要用两条。
2.3 如果if太多，就要考虑用case
2.4 尽可能的减少读取数据库次数
转载请注明：赫非域 » 程序优化（以tiptop为例）