查看数据库的disk_reads中的top
sql中有一个sql语句如下：
SELECT a.id,
a.title,
a.pubdate,
a.lastupdated,
a.subject,
a.url,
w.displayname
AS
websitename,
a.pubuser,
a.similarity
FROM article a,
website
w
WHERE (FLOOR (similarity)
=
:1 or a.id =
:2) AND
a.HOST
=
w.id
ORDER
BY FLOOR (a.similarity),
pubdate ASC
|
Plan |
|||||
|
SELECT STATEMENT ALL_ROWS Cost:
180,029 Bytes: 12,877 Cardinality: 79 |
|||||
|
|
10 SORT ORDER BY Cost: 180,029 Bytes:
12,877 Cardinality: 79 |
||||
|
|
|
9 CONCATENATION |
|||
|
|
|
|
5 NESTED LOOPS Cost: 4 Bytes: 163
Cardinality: 1 |
||
|
|
|
|
|
2 TABLE ACCESS BY INDEX ROWID TABLE DESKTOP.ARTICLE Cost: 3 Bytes: 145 Cardinality: 1 |
|
|
|
|
|
|
|
1 |
|
|
|
|
|
4 TABLE ACCESS BY INDEX ROWID TABLE DESKTOP.WEBSITE Cost: 1 Bytes: 196,164 Cardinality: 10,898 |
|
|
|
|
|
|
|
3 INDEX UNIQUE SCAN INDEX (UNIQUE) DESKTOP.PK_WEBSITE Cost: 0 Cardinality: 1 |
|
|
|
|
8 HASH JOIN Cost: 180,024 Bytes: 12,714
Cardinality: 78 |
||
|
|
|
|
|
6 TABLE
ACCESS FULL TABLE DESKTOP.ARTICLE Cost: 179,982 Bytes:
11,310 Cardinality: 78 |
|
|
|
|
|
|
7 TABLE
ACCESS FULL TABLE DESKTOP.WEBSITE Cost: 41 Bytes: 196,164
Cardinality: 10,898 |
|
看出对desktop.article的大表进行了fts全表扫描，而取出的数据仅仅只有78rows，其实在articled表中FLOOR (similarity)和a.id 都建立了索引，而且索引选择率是比较高的。
由于or的存在导致了cbo选择了对article大表的fts，考虑对sql语句进行改下：可以看出cost的消耗由18w降到了75.
select * from
(SELECT a.id,
a.title,
a.pubdate,
a.lastupdated,
a.subject,
a.url,
w.displayname
AS
websitename,
a.pubuser,
a.similarity
FROM article a,
website
w
WHERE FLOOR (similarity)
=
:1 AND
a.HOST
=
w.id
union all
SELECT a.id,
a.title,
a.pubdate,
a.lastupdated,
a.subject,
a.url,
w.displayname
AS
websitename,
a.pubuser,
a.similarity
FROM article a,
website
w
WHERE FLOOR (similarity)<>:1 and a.id =
:2 AND a.HOST
=
w.id)a
ORDER BY FLOOR (a.similarity),
pubdate ASC
|
Plan |
||||||
|
SELECT STATEMENT ALL_ROWS Cost:
75 Bytes: 58,144 Cardinality: 79 |
||||||
|
|
12 SORT ORDER BY Cost: 75 Bytes: 58,144
Cardinality: 79 |
|||||
|
|
|
11 VIEW DESKTOP. Cost: 74 Bytes: 58,144 Cardinality:
79 |
||||
|
|
|
|
10 UNION-ALL |
|||
|
|
|
|
|
4 HASH JOIN Cost: 70 Bytes: 12,714 Cardinality:
78 |
||
|
|
|
|
|
|
2 TABLE ACCESS BY INDEX ROWID TABLE DESKTOP.ARTICLE Cost: 28 Bytes: 11,310 Cardinality: 78 |
|
|
|
|
|
|
|
|
1 |
|
|
|
|
|
|
3 TABLE
ACCESS FULL TABLE DESKTOP.WEBSITE Cost: 41 Bytes: 196,164
Cardinality: 10,898 |
|
|
|
|
|
|
9 NESTED LOOPS Cost: 4 Bytes: 163
Cardinality: 1 |
||
|
|
|
|
|
|
6 TABLE ACCESS BY INDEX ROWID TABLE DESKTOP.ARTICLE Cost: 3 Bytes: 145 Cardinality: 1 |
|
|
|
|
|
|
|
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/25362835/viewspace-1059109/ ，如需转载，请注明出处，否则将追究法律责任。 
请登录后发表评论
登录
全部评论
北京盛拓优讯信息技术有限公司. 版权所有 京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区 广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员 违法和不良信息举报电话:18101376593 | |
