【疑惑二解答】
在绝大多数时候,都会认为【全表扫描】的
SQL
会慢于【索引快速扫描】的
SQL
,我们应该相信
Oracle
的选择,在【疑惑一解答】中已经排除了【统计信息】错误的可能,同时统计信息也给我们提供了一个重要的线索,不知道大家主要到了没有,数据库创建的时候默认块大小应该是
8Kb
。
【
TBNC_P
】表存储了
3
万记录,而数据块使用了
244
块。
【
TBNC_A
】表存储了
4
万记录,而数据块使用了
19535
块。
且不说合理与否,但是这样巨大的差异,肯定隐藏这什么玄机。
我的第一个感觉,数据很有可能存在
Lob
字段,或是其他。
先改造了一下【
SQL1
】语句,
强制
使用全表扫描并查看执行计划:
【
SQL1
全表扫描】
Select
/*+ FULL(aps) */
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
);
(
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
性能急剧下降。
到此【疑惑二】基本就能解释的通了,但还需要继续求索。
2008-05-15
早
凌
蓝风