【疑惑二解答】
在绝大多数时候，都会认为【全表扫描】的 SQL 会慢于【索引快速扫描】的 SQL ，我们应该相信 Oracle 的选择，在【疑惑一解答】中已经排除了【统计信息】错误的可能，同时统计信息也给我们提供了一个重要的线索，不知道大家主要到了没有，数据库创建的时候默认块大小应该是 8Kb 。 【 TBNC_P 】表存储了 3 万记录，而数据块使用了 244 块。 【 TBNC_A 】表存储了 4 万记录，而数据块使用了 19535 块。 且不说合理与否，但是这样巨大的差异，肯定隐藏这什么玄机。 我的第一个感觉，数据很有可能存在 Lob 字段，或是其他。 先改造了一下【 SQL1 】语句， 强制 使用全表扫描并查看执行计划： 【 SQL1 全表扫描】 Select art.article_id, art.article_title, aps.adminaccount from TBNC_A art, TBNC_P aps where art.column_id = aps.scopestr and aps.funcnodepath = 'A001B002C002D002E003' and aps.adminaccount = ' lgm '; 我又重新自己看了一下执行计划每步的 cost ，发现个的疑问，执行计划片段如下： 【 SQL1 】 步骤 成本 估计返回行 估计返回字节 5 Select statement 4291 888 64.172 4 Hash Join 4291 888 64.172 2 TBNC_P TABLE ACCESS [BY INDEX ROWID] 3 11 0.322 1 WEB.SYS_LGM INDEX [RANGE SCAN] 1 87 -- 3 TBNC_A TABLE ACCESS [FULL] 4288 40782 1752.352 【 SQL1 全表扫描】 步骤 成本 估计返回行 估计返回字节 4 Select statement 4344 44229 3196.236 3 Hash Join 4344 44229 3196.236 1 TBNC_P TABLE ACCESS [FULL] 56 541 15.85 2 TBNC_A TABLE ACCESS [FULL] 4288 40782 1752.352 【 SQL2 】 步骤 成本 估计返回行 估计返回字节 4 Select statement 4344 44229 3196.236 3 Hash Join 4344 44229 3196.236 1 TBNC_P TABLE ACCESS [FULL] 56 541 15.85 2 TBNC_A TABLE ACCESS [FULL] 4288 40782 1752.352 【 SQL1 全表扫描】和【 SQL2 】执行计划【总成本】都是 4344 ，但从 myepoch 的反馈， 【 SQL1 全表扫描】比改造前能有所改善，但【 SQL1 全表扫描】还是明显比【 SQL2 】慢。 到此【 SQL1 全表扫描】改造失败，未能彻底解决问题。 从另一条线索继续前行 ( 数据块疑问 ) ，为什么【 TBNC_A 】表存储了 4 万记录， 为什么 Oraclde 使用了那么多数据块呢？如果一条记录被存储在多个数据块中， 那么 Oracle 在读取此条数据的时候会如何处理呢？带着好多猜想继续调查。 我们可以清楚看到【 SQL1 】成本陡变的地方是【步骤 3 】，所以 Oracle 选择【 SYS_LGM 】从成本角度确实可以降低成本，【 SYS_LGM 】属于【 TBNC_P 】表，但是因为【 TBNC_A 】的全表扫描， 导致整个成本急剧增加，占据了整个成本的 99% ，很显然问题存在这里， 消灭【 TBNC_A 】的全表扫描，变成了首要目标 。 为了加快进度， email 联络了 myepoch 联络后，得到了完整表结构，结构如下： 为了看出重点，我把有问题的地方都是表结构上需要注意的地方标上红色。 create table TBNC_A
(
ARTICLE_ID NUMBER(10) not null,
ARTICLE_TITLE VARCHAR2(255) not null,
ARTICLE_CONTENT CLOB not null,
COLUMN_ID VARCHAR2(255) not null,
ARTICLE_INDATETIME DATE,
ARTICLE_ORDER NUMBER(10),
ARTICLE_MENU CLOB,
ARTICLE_BODY CLOB not null,
ARTICLE_ATTACHMENT CLOB,
ARTICLE_CHECKID VARCHAR2(1),
ARTICLE_PROOFREADID VARCHAR2(1),
ARTICLE_CHECKTEXT CLOB,
LOCKACCOUNT VARCHAR2(255),
ARTICLE_RELATIVEFROM VARCHAR2(500)
)
tablespace WEB
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 160
minextents 1
maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table TBNC_A
add primary key (ARTICLE_ID)
using index
tablespace WEB
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 768K
minextents 1
maxextents unlimited
);
-- Create/Recreate indexes
create index INDEX_COLUMN on TBNC_A (COLUMN_ID)
tablespace WEB
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
); 可以清楚看出，【 TBNC_A 】表设计，它拥有两个索引，一个是主键【ARTICLE_ID 】， 另换一个就是我们检索条件中的【COLUMN_ID 】，从使用的角度考虑，索引建立正确。 从表设计角度考虑，发现表中大量的使用了 5 个 CLOB 字段，到此困惑了半天的问题终于明白了点为什么 4 万记录要使用 2 万个数据块了。 所以为什么使用索引比全表扫描慢的原因也能推断个差不多了，一条记录存储在多个数据库块的事情在【 TBNC_A 】中是比较多，这样对数据库的全表扫描必须大量的读取物理数据块，谁都知道 IO 是最慢的操作之一， IO 多了， SQL 快不了。 所以综合上述，使用索引【 SYS_LGM 】没有错，只不过【 TBNC_A 】表的【 TBNC_A 】索引没有被使用，导致整个 SQL 性能急剧下降。 到此【疑惑二】基本就能解释的通了，但还需要继续求索。 接 -> SQL 优化-同SQL不同执行计划(三) 2008-05-15 早 凌 蓝风
![SQL优化SQL语句优化的目的[图]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)