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程式优化记录