oracle性能优化之SQL语句

www.dbonline.cn

1 ORACLE 采用两种访问表中记录的方式:

a. 全表扫描

全表扫描就是顺序地访问表中每条记录. ORACLE 采用一次读入多个数据块(database block)的方式

优化全表扫描.

b. 通过 ROWID 访问表

你可以采用基于 ROWID 的访问方式情况,提高访问表的效率, , ROWID 包含了表中记录的物理位置

信息..ORACLE 采用索引(INDEX)实现了数据和存放数据的物理位置(ROWID)之间的联系. 通常索引

执 行 路 径 . ORACLE 的 这 个 功 能 大 大 地 提 高 了 SQL 的 执 行 性 能 并 节 省 了 内 存 的 使 用 .

可惜的是 ORACLE 只对简单的表提供高速缓冲(cache buffering) ,这个功能并不适用于多表连接查询.

数据库管理员必须在 init.ora 中为这个区域设置合适的参数,当这个内存区域越大,就可以保留更多的

语 句 , 当 然 被 共 享 的 可 能 性 也 就 越 大 了 .

当 你 向 ORACLE 提 交 一 个 SQL 语 句 ,ORACLE 会 首 先 在 这 块 内 存 中 查 找 相 同 的 语 句 .

这 里 需 要 注 明 的 是 ,ORACLE 对 两 者 采 取 的 是 一 种 严 格 匹 配 , 要 达 成 共 享 ,SQL 语 句 必 须

完 全 相 同 ( 包 括 空 格 , 换 行 等 ).

共 享 的 语 句 必 须 满 足 三 个 条 件 :

A. 字 符 级 的 比 较 :

当前被执行的语句和共享池中的语句必须完全相同.

不 能

每个用户都有一个 private synonym - sal_limit , 它们是不同的对象

select count(*0 from work_city where sdesc like 'NEW%';

能

两 个 用 户 访 问 相 同 的 对 象 public synonym - work_city

select a.sdesc,b.location from work_city a , plant_detail b where a.city_id = b.city_id

不 能

用户 jack 通过 private synonym 访问 plant_detail 而 jill 是表的所有者,对象不同.

C. 两 个 SQL 语 句 中 必 须 使 用 相 同 的 名 字 的 绑 定 变 量 (bind variables)

5. WHERE 子 句 中 的 连 接 顺 序 ．

ORACLE 采用自下而上的顺序解析 WHERE 子句,根据这个原理,表之间的连接必须写在其他 W

HERE 条件 之前 , 那 些 可 以 过 滤 掉 最 大 数 量 记 录 的 条 件 必 须 写 在 WHERE 子句 的末 尾.

例如:

( 高 效 , 执 行 时 间 10.6 秒 )

SELECT …

FROM EMP E

WHERE 25 < (SELECT COUNT(*) FROM EMP

WHERE MGR=E.EMPNO)

AND SAL > 50000

当执行每条 SQL 语句时, ORACLE 在内部执行了许多工作: 解析 SQL 语句, 估算索引的利用率,

绑定变量 , 读数据块等等. 由此可见, 减少访问数据库的次数 , 就能实际上减少 ORACLE 的工作

量 .

例 如 ,

以 下 有 三 种 方 法 可 以 检 索 出 雇 员 号 等 于 0342 或 0291 的 职 员 .

方 法 1 ( 最 低 效 )

SELECT EMP_NAME , SALARY , GRADE

FROM EMP

WHERE EMP_NO = 342;

SELECT EMP_NAME , SALARY , GRADE

FROM EMP

WHERE EMP_NO = E_NO;

BEGIN

OPEN C1(342);

FETCH C1 INTO …,..,.. ;

…..

OPEN C1(291);

FETCH C1 INTO …,..,.. ;

CLOSE C1;

END;

注 意 :

在 SQL*Plus , SQL*Forms 和 Pro*C 中重新设置 ARRAYSIZE 参数, 可以增加每次数据库访问的

检索数据量 ,建议值为 200

使 用 DECODE 函 数 可 以 避 免 重 复 扫 描 相 同 记 录 或 重 复 连 接 相 同 的 表 .

例 如 :

SELECT COUNT(*) ， SUM(SAL)

FROM　EMP

WHERE DEPT_NO = 0020

AND ENAME LIKE 　 ‘ SMITH%';

COUNT(DECODE(DEPT_NO,0030,'X',NULL)) D0030_COUNT,

SUM(DECODE(DEPT_NO,0020,SAL,0)) D0020_SAL,

SUM(DECODE(DEPT_NO,0030,SAL,0)) D0030_SAL

FROM EMP WHERE ENAME LIKE ‘SMITH%';

类似的,DECODE 函数也可以运用于 GROUP BY 和 ORDER BY 子句中.