SQL 书写规范

SQL 书 写 规 范 1 SQL 书 写 规 范... 2 1.1 选 择 最 有 效 率 的 表 名 顺 序 ( 只 在 基 于 规 则 的 优 化 器 中 有 效 )... 2 1.2 where 子 句 中 的 连 接 顺 序... 3 1.3 @Select 子 句 中 避 免 使 用 '*'... 4 1.4 减 少 访 问 数 据 库 的 次 数... 4 1.5 使 用 decode 函 数 来 减 少 处 理 时 间... 5 1.6 用 where 子 句 替 换 having 子 句... 5 1.7 减 少 对 表 的 查 询... 6 1.8 使 用 表 的 别 名 (alias)... 7 1.9 @ 用 exists 替 代 in... 7 1.10 @ 用 not exists 替 代 not in... 8 1.11 @ 用 表 连 接 替 换 exists... 8 1.12 @ 用 exists 替 换 distinct... 9 1.13 @ 使 用 union-all 和 union... 10 1.14 用 索 引 提 高 效 率... 11 1.14.1 索 引 的 操 作... 11 1.14.2 建 立 索 引... 12 1.14.3 索 引 失 效 :... 13 1.14.3.1 IN OR 子 句 常 会 使 用 工 作 表, 使 索 引 失 效... 13 1.14.3.2 使 用 IS NULL 或 IS NOT NULL... 13 1.14.3.3 @ 条 件 字 段 使 用 函 数 和 表 达 式... 14 1.14.3.4 @ 比 较 不 匹 配 的 数 据 类 型... 14 1.14.3.5 带 通 配 符 (%) 的 like 语 句... 15 1.14.3.6 Order by 语 句... 15 1.14.3.7 > 及 < 操 作 符 ( 大 于 或 小 于 操 作 符 )... 错 误! 未 定 义 书 签

数 据 库 的 优 化 通 常 可 以 通 过 对 网 络 硬 件 操 作 系 统 数 据 库 参 数 和 应 用 程 序 的 优 化 来 进 行 最 常 见 的 优 化 手 段 就 是 对 硬 件 的 升 级 根 据 统 计, 对 网 络 硬 件 操 作 系 统 数 据 库 参 数 进 行 优 化 所 获 得 的 性 能 提 升, 全 部 加 起 来 只 占 数 据 库 系 统 性 能 提 升 的 40% 左 右, 其 余 的 60% 系 统 性 能 提 升 来 自 对 应 用 程 序 的 优 化 许 多 优 化 专 家 认 为, 对 应 用 程 序 的 优 化 可 以 得 到 80% 的 系 统 性 能 的 提 升 因 此, 在 此 整 理 一 些 SQL 书 写 规 范, 以 期 通 过 优 化 SQL 达 到 提 升 系 统 性 能 的 目 的 1 SQL 书 写 规 范 1.1 选 择 最 有 效 率 的 表 名 顺 序 ( 只 在 基 于 规 则 的 优 化 器 中 有 效 ) ORACLE 的 解 析 器 按 照 从 右 到 左 的 顺 序 处 理 FROM 子 句 中 的 表 名, 因 此 FRO M 子 句 中 写 在 最 后 的 表 ( 基 础 表 driving table) 将 被 最 先 处 理. 在 FROM 子 句 中 包 含 多 个 表 的 情 况 下, 你 必 须 选 择 记 录 条 数 最 少 的 表 作 为 基 础 表. 当 ORACLE 处 理 多 个 表 时, 会 运 用 排 序 及 合 并 的 方 式 连 接 它 们. 首 先, 扫 描 第 一 个 表 (FROM 子 句 中 最 后 的 那 个 表 ) 并 对 记 录 进 行 派 序, 然 后 扫 描 第 二 个 表 (FROM 子 句 中 最 后 第 二 个 表 ), 最 后 将 所 有 从 第 二 个 表 中 检 索 出 的 记 录 与 第 一 个 表 中 合 适 记 录 进 行 合 并. Joefit 注 : 栈 表 TAB1 16,384 条 记 录 表 TAB2 1 条 记 录 选 择 TAB2 作 为 基 础 表 ( 最 好 的 方 法 ) select count(*) from tab1,tab2 选 择 TAB1 作 为 基 础 表 ( 不 佳 的 方 法 ) select count(*) from tab2,tab1 执 行 时 间 0.96 秒 执 行 时 间 26.09 秒 如 果 有 3 个 以 上 的 表 连 接 查 询, 那 就 需 要 选 择 交 叉 表 (intersection table) 作 为 基 础 表, 交 叉 表 是 指 那 个 被 其 他 表 所 引 用 的 表. EMP 表 描 述 了 LOCATION 表 和 CATEGORY 表 的 交 集.

SELECT * FROM LOCATION L, CATEGORY C, EMP E WHERE E.EMP_NO BETWEEN 1000 AND 2000 AND E.CAT_NO = C.CAT_NO AND E.LOCN = L.LOCN 将 比 下 列 SQL 更 有 效 率 SELECT * FROM EMP E, LOCATION L, CATEGORY C WHERE E.CAT_NO = C.CAT_NO AND E.LOCN = L.LOCN AND E.EMP_NO BETWEEN 1000 AND 2000 1.2 where 子 句 中 的 连 接 顺 序 Oracle 采 用 自 下 而 上 的 顺 序 解 析 where 子 句 根 据 这 个 原 理, 表 之 间 的 连 接 必 须 写 在 其 他 where 条 件 之 前, 那 些 可 以 过 滤 掉 最 大 数 量 记 录 的 条 件 必 须 写 在 where 子 句 的 末 尾 ( 低 效, 执 行 时 间 156.3 秒 ) select * from emp e where sal > 50000 and job = 'manager' and 25 < (select count(*) from emp where mgr=e.empno); ( 高 效, 执 行 时 间 10.6 秒 ) select * from emp e where 25 < (select count(*) from emp where mgr=e.empno) and sal > 50000 and job = 'manager';

1.3 @Select 子 句 中 避 免 使 用 '*' 当 你 想 在 select 子 句 中 列 出 所 有 的 column 时, 使 用 动 态 sql 列 引 用 '*' 是 一 个 方 便 的 方 法, 不 幸 的 是, 这 是 一 个 非 常 低 效 的 方 法 实 际 上,oracle 在 解 析 的 过 程 中, 会 将 '*' 依 次 转 换 成 所 有 的 列 名 这 个 工 作 是 通 过 查 询 数 据 字 典 完 成 的, 这 意 味 着 将 耗 费 更 多 的 时 间 1.4 减 少 访 问 数 据 库 的 次 数 当 执 行 每 条 sql 语 句 时,oracle 在 内 部 执 行 了 许 多 工 作 : 解 析 sql 语 句, 估 算 索 引 的 利 用 率, 绑 定 变 量, 读 数 据 块 等 等 由 此 可 见, 减 少 访 问 数 据 库 的 次 数, 就 能 实 际 上 减 少 oracle 的 工 作 量 Joefit 注 : 是 否 可 以 认 为 是 减 少 select 子 句 的 数 量 以 下 有 两 种 方 法 可 以 检 索 出 雇 员 号 等 于 0342 或 0291 的 职 员 方 法 1 ( 低 效 ) select emp_name, salary, grade from emp where emp_no = 342; select emp_name, salary, grade from emp where emp_no = 291; 方 法 2 ( 高 效 ) select a.emp_name, a.salary, a.grade, b.emp_name, b.salary, b.grade from emp a, emp b where a.emp_no = 342 and b.emp_no = 291;

1.5 使 用 decode 函 数 来 减 少 处 理 时 间 Joefit 注 :DB2 中 无 此 函 数 使 用 decode 函 数 可 以 避 免 重 复 扫 描 相 同 记 录 或 重 复 连 接 相 同 的 表 select count(*), sum(sal) from emp where dept_no = '0020' and ename like 'smith%'; select count(*), sum(sal) from emp where dept_no = '0030' and ename like 'smith%'; 你 可 以 用 decode 函 数 高 效 地 得 到 相 同 结 果 select count(decode(dept_no, '0020', 'x', null)) d0020_count, count(decode(dept_no, '0030', 'x', null)) d0030_count, sum(decode(dept_no, '0020', sal, null)) d0020_sal, sum(decode(dept_no, 0030, sal, null)) d0030_sal from emp where ename like 'smith%'; 'x' 表 示 任 何 一 个 字 段 类 似 的,decode 函 数 也 可 以 运 用 于 group by 和 order by 子 句 中 1.6 用 where 子 句 替 换 having 子 句 避 免 使 用 having 子 句,having 只 会 在 检 索 出 所 有 记 录 之 后 才 对 结 果 集 进 行 过 滤, 这 个 处 理 需 要 排 序 统 计 等 操 作 Joefit 注 :having 去 过 滤 别 的 语 句 检 索 出 的 记 录 如 果 能 通 过 where 子 句 限 制 记 录 的 数 目, 那 就 能 减 少 这 方 面 的 开 销 低 效 select region, avg(log_size) from location

group by region having region!= 'sydney' and region!= 'perth' 高 效 select region, avg(log_size) from location where region!= 'sydney' and region!= 'perth' group by region 1.7 减 少 对 表 的 查 询 在 含 有 子 查 询 的 sql 语 句 中, 要 特 别 注 意 减 少 对 表 的 查 询 低 效 SELECT TAB_NAME FROM TABLES WHERE TAB_NAME = ( SELECT TAB_NAME FROM TAB_COLUMNS WHERE VERSION = 604) AND DB_VER= ( SELECT DB_VER FROM TAB_COLUMNS WHERE VERSION = 604) 高 效 SELECT TAB_NAME FROM TABLES WHERE (TAB_NAME,DB_VER) = ( SELECT TAB_NAME,DB_VER FROM TAB_COLUMNS WHERE VERSION = 604) Update 多 个 Column 例 子 : 低 效 : UPDATE EMP SET EMP_CAT = (SELECT MAX(CATEGORY) FROM EMP_CATEGORIES), SAL_RANGE = (SELECT MAX(SAL_RANGE) FROM EMP_CATEGORIES) WHERE EMP_DEPT = 0020;

高 效 : UPDATE EMP SET (EMP_CAT, SAL_RANGE) = (SELECT MAX(CATEGORY), MAX(SAL_RANGE) FROM EMP_CATEGORIES) WHERE EMP_DEPT = 0020; 1.8 使 用 表 的 别 名 (alias) 上 当 在 sql 语 句 中 连 接 多 个 表 时, 请 使 用 表 的 别 名 并 把 别 名 前 缀 于 每 个 column 这 样 可 以 减 少 解 析 的 时 间 并 减 少 那 些 由 column 歧 义 引 起 的 语 法 错 误 1.9 @ 用 exists 替 代 in 在 许 多 基 于 基 础 表 的 查 询 中, 为 了 满 足 一 个 条 件, 往 往 需 要 对 另 一 个 表 进 行 联 接. 在 这 种 情 况 下, 使 用 EXISTS( 或 NOT EXISTS) 通 常 将 提 高 查 询 的 效 率. 低 效 : SELECT * FROM EMP ( 基 础 表 ) WHERE EMPNO > 0 AND DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE LOC = MELB ) 高 效 : SELECT * FROM EMP ( 基 础 表 ) WHERE EMPNO > 0 AND EXISTS (SELECT X FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = MELB )

1.10 @ 用 not exists 替 代 not in 魏 凯 讲 :not 关 键 字 会 使 索 引 失 效 在 子 查 询 中,NOT IN 子 句 将 执 行 一 个 内 部 的 排 序 和 合 并. 无 论 在 哪 种 情 况 下,NOT IN 都 是 最 低 效 的 ( 因 为 它 对 子 查 询 中 的 表 执 行 了 一 个 全 表 遍 历 ). 为 了 避 免 使 用 NOT IN, 我 们 可 以 把 它 改 写 成 外 连 接 (Outer Joins) 或 NOT EXISTS. SELECT FROM EMP WHERE DEPT_NO NOT IN (SELECT DEPT_NO FROM DEPT WHERE DEPT_CAT= A ); 为 了 提 高 效 率. 改 写 为 : ( 方 法 一 : 高 效 ) SELECT. FROM EMP A,DEPT B WHERE A.DEPT_NO = B.DEPT(+) AND B.DEPT_NO IS NULL AND B.DEPT_CAT(+) = A ( 方 法 二 : 最 高 效 ) SELECT. FROM EMP E WHERE NOT EXISTS (SELECT X FROM DEPT D WHERE D.DEPT_NO = E.DEPT_NO AND DEPT_CAT = A ); 1.11 @ 用 表 连 接 替 换 exists 通 常 来 说, 采 用 表 连 接 的 方 式 比 EXISTS 更 有 效 率 SELECT ENAME

FROM EMP E WHERE EXISTS (SELECT X FROM DEPT WHERE DEPT_NO = E.DEPT_NO AND DEPT_CAT = A ); ( 更 高 效 ) SELECT ENAME FROM DEPT D,EMP E WHERE E.DEPT_NO = D.DEPT_NO AND DEPT_CAT = A ; 1.12 > 及 < 操 作 符 ( 大 于 或 小 于 操 作 符 ), 尽 量 使 用 大 于 等 于 大 于 或 小 于 操 作 符 一 般 情 况 下 是 不 用 调 整 的, 因 为 它 有 索 引 就 会 采 用 索 引 查 找, 但 有 的 情 况 下 可 以 对 它 进 行 优 化, 如 一 个 表 有 100 万 记 录, 一 个 数 值 型 字 段 A, 30 万 记 录 的 A=0,30 万 记 录 的 A=1,39 万 记 录 的 A=2,1 万 记 录 的 A=3 那 么 执 行 A>2 与 A>=3 的 效 果 就 有 很 大 的 区 别 了, 因 为 A>2 时 ORACLE 会 先 找 出 为 2 的 记 录 索 引 再 进 行 比 较, 而 A>=3 时 ORACLE 则 直 接 找 到 =3 的 记 录 索 引 1.13 @ 用 exists 替 换 distinct 当 提 交 一 个 包 含 一 对 多 表 信 息 ( 比 如 部 门 表 和 雇 员 表 ) 的 查 询 时, 避 免 在 SELECT 子 句 中 使 用 DISTINCT. 一 般 可 以 考 虑 用 EXIST 替 换 低 效 : SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D,EMP E WHERE D.DEPT_NO = E.DEPT_NO 高 效 : SELECT DEPT_NO,DEPT_NAME FROM DEPT D WHERE EXISTS ( SELECT X FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO); EXISTS 使 查 询 更 为 迅 速, 因 为 RDBMS 核 心 模 块 将 在 子 查 询 的 条 件 一 旦 满 足 后, 立 刻 返 回 结 果.

1.14 @ 使 用 union-all 和 union Joefit 注 : 两 者 无 可 比 性, 因 为 达 到 的 效 果 不 同 当 SQL 语 句 需 要 UNION 两 个 查 询 结 果 集 合 时, 这 两 个 结 果 集 合 会 以 UNIO N-ALL 的 方 式 被 合 并, 然 后 在 输 出 最 终 结 果 前 进 行 排 序. 如 果 用 UNION ALL 替 代 UNION, 这 样 排 序 就 不 是 必 要 了. 效 率 就 会 因 此 得 到 提 高. 举 例 : 低 效 : SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = 31-DEC-95 UNION SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = 31-DEC-95 高 效 : SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = 31-DEC-95 UNION ALL SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = 31-DEC-95 需 要 注 意 的 是,union all 将 重 复 输 出 两 个 结 果 集 合 中 相 同 记 录, 因 此 还 是 要 从 业 务 需 求 分 析 使 用 union all 的 可 行 性 关 于 索 引 下 列 经 验 请 参 考 : 1). 如 果 检 索 数 据 量 超 过 30% 的 表 中 记 录 数, 使 用 索 引 将 没 有 显 著 的 效 率 提 高 2). 在 特 定 情 况 下, 使 用 索 引 也 许 会 比 全 表 扫 描 慢, 但 这 是 同 一 个 数 量 级 上 的 差 距 ; 而 通 常 情 况 下, 使 用 索 引 比 全 表 扫 描 要 快 几 倍 乃 至 几 千 倍!

1.15 用 索 引 提 高 效 率 索 引 是 表 的 一 个 概 念 部 分, 用 来 提 高 检 索 数 据 的 效 率. 实 际 上,ORACLE 使 用 了 一 个 复 杂 的 自 平 衡 B-tree 结 构. 通 常, 通 过 索 引 查 询 数 据 比 全 表 扫 描 要 快. 当 ORACLE 找 出 执 行 查 询 和 Update 语 句 的 最 佳 路 径 时, ORACLE 优 化 器 将 使 用 索 引. 同 样 在 联 结 多 个 表 时 使 用 索 引 也 可 以 提 高 效 率. 另 一 个 使 用 索 引 的 好 处 是, 它 提 供 了 主 键 (primary key) 的 唯 一 性 验 证. 除 了 那 些 LONG 或 LONG RAW 数 据 类 型, 你 可 以 索 引 几 乎 所 有 的 列. 通 常, 在 大 型 表 中 使 用 索 引 特 别 有 效. 当 然, 你 也 会 发 现, 在 扫 描 小 表 时, 使 用 索 引 同 样 能 提 高 效 率. 虽 然 使 用 索 引 能 得 到 查 询 效 率 的 提 高, 但 是 我 们 也 必 须 注 意 到 它 的 代 价. 索 引 需 要 空 间 来 存 储, 也 需 要 定 期 维 护, 每 当 有 记 录 在 表 中 增 减 或 索 引 列 被 修 改 时, 索 引 本 身 也 会 被 修 改. 这 意 味 着 每 条 记 录 的 INSERT, DELETE, UPDATE 将 为 此 多 付 出 4, 5 次 的 磁 盘 I/O. 因 为 索 引 需 要 额 外 的 存 储 空 间 和 处 理, 那 些 不 必 要 的 索 引 反 而 会 使 查 询 反 应 时 间 变 慢. 1.15.1 索 引 的 操 作 ORACLE 对 索 引 有 两 种 访 问 模 式. 索 引 唯 一 扫 描 ( INDEX UNIQUE SCAN) 大 多 数 情 况 下, 优 化 器 通 过 WHERE 子 句 访 问 INDEX. 表 LODGING 有 两 个 索 引 : 建 立 在 LODGING 列 上 的 唯 一 性 索 引 LODGING_P K 和 建 立 在 MANAGER 列 上 的 非 唯 一 性 索 引 LODGING$MANAGER. SELECT * FROM LODGING WHERE LODGING = ROSE HILL ; 在 内 部, 上 述 SQL 将 被 分 成 两 步 执 行, 首 先, LODGING_PK 索 引 将 通 过 索 引 唯 一 扫 描 的 方 式 被 访 问, 获 得 相 对 应 的 ROWID, 通 过 ROWID 访 问 表 的 方 式 执 行 下 一 步 检 索. 如 果 被 检 索 返 回 的 列 包 括 在 INDEX 列 中,ORACLE 将 不 执 行 第 二 步 的 处 理 ( 通 过 ROWID 访 问 表 ). 因 为 检 索 数 据 (SELECT 后 面 的 字 段 ) 保 存 在 索 引 中, 单 单 访 问 索 引 就 可 以 完 全 满 足 查 询 结 果. 下 面 SQL 只 需 要 INDEX UNIQUE SCAN 操 作. SELECT LODGING FROM LODGING WHERE LODGING = ROSE HILL ;

索 引 范 围 查 询 (INDEX RANGE SCAN) 适 用 于 两 种 情 况 : 1. 基 于 一 个 范 围 的 检 索 2. 基 于 非 唯 一 性 索 引 的 检 索 例 1: SELECT LODGING FROM LODGING WHERE LODGING LIKE M% ; WHERE 子 句 条 件 包 括 一 系 列 值, ORACLE 将 通 过 索 引 范 围 查 询 的 方 式 查 询 LODG ING_PK. 由 于 索 引 范 围 查 询 将 返 回 一 组 值, 它 的 效 率 就 要 比 索 引 唯 一 扫 描 低 一 些. 例 2: SELECT LODGING FROM LODGING WHERE MANAGER = BILL GATES ; 这 个 SQL 的 执 行 分 两 步, LODGING$MANAGER 的 索 引 范 围 查 询 ( 得 到 所 有 符 合 条 件 记 录 的 ROWID) 和 下 一 步 同 过 ROWID 访 问 表 得 到 LODGING 列 的 值. 由 于 LODGI NG$MANAGER 是 一 个 非 唯 一 性 的 索 引, 数 据 库 不 能 对 它 执 行 索 引 唯 一 扫 描. 由 于 SQL 返 回 LODGING 列, 而 它 并 不 存 在 于 LODGING$MANAGER 索 引 中, 所 以 在 索 引 范 围 查 询 后 会 执 行 一 个 通 过 ROWID 访 问 表 的 操 作. WHERE 子 句 中, 如 果 索 引 列 所 对 应 的 值 的 第 一 个 字 符 由 通 配 符 (WILDCARD) 开 始, 索 引 将 不 被 采 用. SELECT LODGING FROM LODGING WHERE MANAGER LIKE %HANMAN ; 在 这 种 情 况 下,ORACLE 将 使 用 全 表 扫 描. 1.15.2 建 立 索 引 表 的 主 键 外 键 必 须 有 索 引 ;

经 常 与 其 他 表 进 行 连 接 的 表, 在 连 接 字 段 上 应 该 建 立 索 引 ; 索 引 应 该 建 立 在 查 询 条 件 中 进 行 比 较 的 字 段 上, 而 不 是 建 立 在 我 们 要 找 出 来 并 且 显 示 的 字 段 上 ; 在 条 件 表 达 式 中 经 常 用 到 的 不 同 值 较 多 的 列 上 建 立 检 索, 在 不 同 值 少 的 列 上 不 要 建 立 索 引 ; 比 如 在 雇 员 表 的 性 别 列 上 只 有 男 与 女 两 个 不 同 值, 因 此 就 无 必 要 建 立 索 引 如 果 建 立 索 引 不 但 不 会 提 高 查 询 效 率, 反 而 会 严 重 降 低 更 新 速 度 组 合 索 引 要 尽 量 使 关 键 查 询 形 成 索 引 覆 盖, 其 前 导 列 一 定 是 使 用 最 频 繁 的 列 1.15.3 索 引 失 效 : 1.15.3.1 IN OR 子 句 常 会 使 用 工 作 表, 使 索 引 失 效 如 果 不 产 生 大 量 重 复 值, 可 以 考 虑 把 子 句 拆 开 拆 开 的 子 句 中 应 该 包 含 索 引 例 子 如 下 : 如 果 在 fields1 和 fields2 上 同 时 建 立 了 索 引,fields1 为 主 索 引 以 下 sql 会 用 到 索 引 select * from tablename1 where fields1= value1 and fields2= value2 以 下 sql 不 会 用 到 索 引 select * from tablename1 where fields1= value1 or fields2= value2 1.15.3.2 使 用 IS NULL 或 IS NOT NULL, 使 索 引 失 效 使 用 IS NULL 或 IS NOT NULL 同 样 会 限 制 索 引 的 使 用 因 为 NULL 值 并 没 有 被 定 义 在 SQL 语 句 中 使 用 NULL 会 有 很 多 的 麻 烦 因 此 建 议 开 发 人 员 在 建 表 时, 把 需 要 索 引 的 列 设 成 NOT NULL 如 果 被 索 引 的 列 在 某 些 行 中 存 在 NULL 值, 就 不 会 使 用 这 个 索 引 避 免 在 索 引 中 使 用 任 何 可 以 为 空 的 列,oracle 将 无 法 使 用 该 索 引 对 于 单 列 索 引, 如 果 列 包 含 空 值, 索 引 中 将 不 存 在 此 记 录 ; 对 于 复 合 索 引, 如 果 每 个 列 都 为 空, 索 引 中 同 样 不 存 在 此 记 录 如 果 至 少 有 一 个 列 不 为 空, 则 记 录 存 在 于 索 引 中 如 果 唯 一 性 索 引 建 立 在 表 的 a 列 和 b 列 上, 并 且 表 中 存 在 一 条 记 录 的 a,b 值 为 (123,null), oracle 将 不 接 受 下 一 条 具 有 相 同 a,b 值 (123,null) 的 记 录 插 入

如 果 所 有 的 索 引 列 都 为 空,oracle 将 认 为 整 个 键 值 为 空, 而 空 不 可 能 等 于 空, 因 此 你 可 以 插 入 1000 条 具 有 相 同 键 值 的 记 录, 当 然 它 们 都 是 空! 因 为 空 值 不 存 在 于 索 引 列 中, 所 以 where 子 句 中 对 索 引 列 进 行 空 值 比 较 将 使 oracle 停 用 该 索 引 低 效 ( 索 引 失 效 ) select from department where dept_code is not null 1.15.3.3 @ 条 件 字 段 使 用 函 数 和 表 达 式 如 果 不 使 用 基 于 函 数 的 索 引, 那 么 在 SQL 语 句 的 WHERE 子 句 中 对 存 在 索 引 的 列 使 用 函 数 时, 会 使 优 化 器 忽 略 掉 这 些 索 引 下 面 的 查 询 不 会 使 用 索 引 ( 只 要 它 不 是 基 于 函 数 的 索 引 ) select empno,ename,deptno from emp where trunc(hiredate)='01-may-81'; 把 上 面 的 语 句 改 成 下 面 的 语 句, 这 样 就 可 以 通 过 索 引 进 行 查 找 select empno,ename,deptno from emp where hiredate<(to_date('01-may-81')+0.9999); 请 务 必 注 意, 检 索 中 不 要 对 索 引 列 进 行 处 理, 如 :trim,to_date, 类 型 转 换 等 操 作, 破 坏 索 引, 使 用 全 表 扫 描, 影 响 sql 执 行 效 率 1.15.3.4 @ 比 较 不 匹 配 的 数 据 类 型 比 较 不 匹 配 的 数 据 类 型 也 是 比 较 难 于 发 现 的 性 能 问 题 之 一 注 意 下 面 查 询 的 例 子,account_number 是 一 个 VARCHAR2 类 型, 在 account_number 字 段 上 有 索 引 下 面 的 语 句 将 执 行 全 表 扫 描 select bank_name,address,city,state,zip from banks where account_number = 990354; Oracle 可 以 自 动 把 where 子 句 变 成 to_number(account_number)=990354, 这 样 就 限 制 了 索 引 的 使 用, 改 成 下 面 的 查 询 就 可 以 使 用 索 引 : select bank_name,address,city,state,zip from banks where account_number ='990354';

特 别 注 意 : 不 匹 配 的 数 据 类 型 之 间 比 较 会 让 Oracle 自 动 限 制 索 引 的 使 用, 即 便 对 这 个 查 询 执 行 Explain Plan 也 不 能 让 您 明 白 为 什 么 做 了 一 次 全 表 扫 描 1.15.3.5 带 通 配 符 (%) 的 like 语 句 同 样 以 上 面 的 例 子 来 看 这 种 情 况 目 前 的 需 求 是 这 样 的, 要 求 在 职 工 表 中 查 询 名 字 中 包 含 cliton 的 人 可 以 采 用 如 下 的 查 询 SQL 语 句 : select * from employee where last_name like '%cliton%'; 这 里 由 于 通 配 符 (%) 在 搜 寻 词 首 出 现, 所 以 Oracle 系 统 不 使 用 last_name 的 索 引 在 很 多 情 况 下 可 能 无 法 避 免 这 种 情 况, 但 是 一 定 要 心 中 有 底, 通 配 符 如 此 使 用 会 降 低 查 询 速 度 然 而 当 通 配 符 出 现 在 字 符 串 其 他 位 置 时, 优 化 器 就 能 利 用 索 引 在 下 面 的 查 询 中 索 引 得 到 了 使 用 : select * from employee where last_name like 'c%'; 1.15.3.6 Order by 语 句 ORDER BY 语 句 决 定 了 Oracle 如 何 将 返 回 的 查 询 结 果 排 序 Order by 语 句 对 要 排 序 的 列 没 有 什 么 特 别 的 限 制, 也 可 以 将 函 数 加 入 列 中 ( 象 联 接 或 者 附 加 等 ) 任 何 在 Order by 语 句 的 非 索 引 项 或 者 有 计 算 表 达 式 都 将 降 低 查 询 速 度 仔 细 检 查 order by 语 句 以 找 出 非 索 引 项 或 者 表 达 式, 它 们 会 降 低 性 能 解 决 这 个 问 题 的 办 法 就 是 重 写 order by 语 句 以 使 用 索 引, 也 可 以 为 所 使 用 的 列 建 立 另 外 一 个 索 引, 同 时 应 绝 对 避 免 在 order by 子 句 中 使 用 表 达 式