查看数据库的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 INDEX RANGE SCAN INDEX DESKTOP.IDX_ART_ID Cost: 2 Cardinality: 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 INDEX RANGE SCAN INDEX DESKTOP.INDEX_ARTICLE_SIMILARITY Cost: 3 Cardinality: 82 |
|
|
|
|
|
| 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博客 ” ，链接：http://blog.itpub.net/25362835/viewspace-1059109/，如需转载，请注明出处，否则将追究法律责任。 
请登录后发表评论
登录
全部评论
<%=items[i].createtime%>
<%=items[i].content%> <%if(items[i].items.items.length) { %>
<%for(var j=0;j
<%}%> <%}%>
<%=items[i].items.items[j].createtime%>
回复
<%=items[i].items.items[j].username%> 回复 <%=items[i].items.items[j].tousername%>： <%=items[i].items.items[j].content%>
还有<%=items[i].items.total-5%>条评论
) data-count=1 data-flag=true>点击查看
<%}%>
北京盛拓优讯信息技术有限公司. 版权所有 京ICP备09055130号-4 北京市公安局海淀分局网监中心备案编号：11010802021510 广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员 | |
转载于:http://blog.itpub.net/25362835/viewspace-1059109/
2315
到【灌水乐园】发言
