如何让你的sql运行得更快 - gprime的专栏 - csdnblog.htm

想学oracle_sql优化的程序员有福了

style="FONT-SIZE: 10pt">接条件：</SPAN></FONT></DIV>
<DIV><FONT size=2><SPAN style="FONT-SIZE: 10pt">例：表</SPAN></FONT><FONT 
size=2><SPAN style="FONT-SIZE: 10pt">card</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">有</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">7896</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">行，在</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">card_no</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">上有一个非聚集索引，表</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">account</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">有</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">191122</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">行，在</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">account_no</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">上有一个非聚集索引，</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">试</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">看在不同的表</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">连</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">接条件下，两个</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">SQL</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">的</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">执</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">行情况：</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff><FONT face="Comic Sans MS"><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">select sum(a.amount) from account a,card b where 
a.card_no = b.card_no</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">（</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">20</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">秒）</SPAN></FONT></FONT></FONT></DIV>
<DIV><FONT color=#0000ff><FONT face="Comic Sans MS"><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">select sum(a.amount) from account a,card b where 
a.card_no = b.card_no and a.account_no=b.account_no</SPAN></FONT><FONT 
size=2><SPAN style="FONT-SIZE: 10pt">（</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">&lt; 1</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">秒）</SPAN></FONT></FONT></FONT></DIV>
<DIV><FONT size=2><SPAN style="FONT-SIZE: 10pt">---- </SPAN></FONT><FONT 
size=2><SPAN style="FONT-SIZE: 10pt">分析：</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">---- </SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">在第一个</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">连</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">接条件下，最佳</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">查询</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">方案是将</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">account</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">作外</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">层</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">表，</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">card</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">作内</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">层</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">表，利用</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">card</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">上的索引，其</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">I/O</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">次数可由以下公式估算</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">为</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">：</SPAN></FONT></DIV>
<DIV><FONT size=2><SPAN style="FONT-SIZE: 10pt">外</SPAN></FONT><FONT 
size=2><SPAN style="FONT-SIZE: 10pt">层</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">表</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">account</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">上的</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">22541</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">页</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">+</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">（外</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">层</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">表</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">account</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">的</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">191122</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">行</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">*</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">内</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">层</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">表</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">card</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">上</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">对应</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">外</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">层</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">表第一行所要</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">查</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">找的</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">3</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">页</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">）</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">=595907</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">次</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">I/O</SPAN></FONT></DIV>
<DIV><FONT size=2><SPAN style="FONT-SIZE: 10pt">在第二个</SPAN></FONT><FONT 
size=2><SPAN style="FONT-SIZE: 10pt">连</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">接条件下，最佳</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">查询</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">方案是将</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">card</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">作外</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">层</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">表，</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">account</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">作内</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">层</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">表，利用</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">account</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">上的索引，其</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">I/O</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">次数可由以下公式估算</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">为</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">：外</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">层</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">表</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">card</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">上的</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">1944</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">页</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">+</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">（外</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">层</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">表</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">card</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">的</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">7896</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">行</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">*</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">内</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">层</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">表</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">account</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">上</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">对应</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">外</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">层</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">表</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">每</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">一行所要</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">查</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">找的</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">4</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">页</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">）</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">= 33528</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">次</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">I/O</SPAN></FONT></DIV>
<DIV><FONT size=2><SPAN style="FONT-SIZE: 10pt">可</SPAN></FONT><FONT 
size=2><SPAN style="FONT-SIZE: 10pt">见</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">，只有充份的</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">连</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">接条件，真正的最佳方案才会被</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">执</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">行。</SPAN></FONT></DIV>
<DIV><FONT size=2><SPAN style="FONT-SIZE: 10pt">总结</SPAN></FONT><FONT 
size=2><SPAN style="FONT-SIZE: 10pt">：</SPAN></FONT></DIV>
<DIV><FONT size=2><SPAN style="FONT-SIZE: 10pt">1.</SPAN></FONT><FONT 
size=2><SPAN style="FONT-SIZE: 10pt">多表操作在被</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">实际执</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">行前，</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">查询优</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">化器会根据</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">连</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">接条件，列出几</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">组</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">可能的</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">连</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">接方案并从中找出系</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">统开销</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">最小的最佳方案。</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">连</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">接条件要充份考</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">虑带</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">有索引的表、行数多的表；内外表的</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">选择</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">可由公式：外</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">层</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">表中的匹配行数</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">*</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">内</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">层</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">表中</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">每</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">一次</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">查</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">找的次数确定，乘</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">积</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">最小</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">为</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">最佳方案。</SPAN></FONT></DIV>
<DIV><FONT size=2><SPAN style="FONT-SIZE: 10pt">2.</SPAN></FONT><FONT 
size=2><SPAN style="FONT-SIZE: 10pt">查</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">看</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">执</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">行方案的方法</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">-- </SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">用</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">set showplan</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">on</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">，打</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">开</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">showplan</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">选项</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">，就可以看到</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">连</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">接</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">顺</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">序、使用何</SPAN></FONT><FONT size=2><SPAN 
style="FONT-SIZE: 10pt">种</SPAN></FONT><FONT size=2><SPAN