通过分析SQL语句的执行计划优化SQL(总结)

zhangyg

学习一下

olivenan

str_sql string;
int_empno int;
int_empno = 2000;
str_sql = ‘SELECT * FROM emp WHERE empno = ‘ + int_empno;
…………
int_empno = 1000;
str_sql = ‘SELECT * FROM emp WHERE empno = ‘ + int_empno;
为什么说以上两句话不一样呢？
还有什么硬编码SQL？？

jiangsz

非常感谢！向你学习！

SunnyXu

对文中一句话的解释：
An Index full scan will not perform single block i/o's and so it may prove to be inefficient. （from metalink “Interpreting Explain plan”）

从metalink上找到了对这句话的进一步解释：
An Index full scan will perform single block i/o's and so under certain circumstances it be inefficient. Index Fast Full Scan will read using multiblock I/O. Range Scan can be used when the indexed column is used in the predicate. This may be more efficient depending on the amount of data the query retrieves.
Index full scan is the equivilant to a full table scan on an index. The columns needed are all contained in the index so it is faster to do a full index scan than a full table scan. 这一段是说明Index full scan为什么被证明为inefficient，因为该操作要读取索引中中全部数据，类似于全表扫描。不过它能利用多块读与并行读。

blue_prince

最初由 SunnyXu 发布
[B]对文中一句话的解释：
An Index full scan will not perform single block i/o's and so it may prove to be inefficient. （from metalink “Interpreting Explain plan”）

从metalink上找到了对这句话的进一步解释：
An Index full scan will perform single block i/o's and so under certain circumstances it be inefficient. Index Fast Full Scan will read using multiblock I/O. Range Scan can be used when the indexed column is used in the predicate. This may be more efficient depending on the amount of data the query retrieves.
Index full scan is the equivilant to a full table scan on an index. The columns needed are all contained in the index so it is faster to do a full index scan than a full table scan. 这一段是说明Index full scan为什么被证明为inefficient，因为该操作要读取索引中中全部数据，类似于全表扫描。不过它能利用多块读与并行读。 [/B]

这个理解有误，index full scan一次只能读取一个块，且结果要按排序结果出来的；index fast full scan才会一次读取多个块的，跟FTS一样，按db_file_multilblock_read_count来读取的，按extent中块的顺序，不管列的排序。

blue_prince

关于Index full scan&index fast full scan可以参考wanghai的文章：
http://spaces.msn.com/wzwanghai/ ... 37AFBD116!171.entry

olivenan

最初由 SunnyXu 发布
[B]对文中一句话的解释：
An Index full scan will not perform single block i/o's and so it may prove to be inefficient. （from metalink “Interpreting Explain plan”）

从metalink上找到了对这句话的进一步解释：
An Index full scan will perform single block i/o's and so under certain circumstances it be inefficient. Index Fast Full Scan will read using multiblock I/O. Range Scan can be used when the indexed column is used in the predicate. This may be more efficient depending on the amount of data the query retrieves.
Index full scan is the equivilant to a full table scan on an index. The columns needed are all contained in the index so it is faster to do a full index scan than a full table scan. 这一段是说明Index full scan为什么被证明为inefficient，因为该操作要读取索引中中全部数据，类似于全表扫描。不过它能利用多块读与并行读。 [/B]

在这句话中应该没有NOT才对。
An Index full scan will not perform single block i/o's and so it may prove to be inefficient. （from metalink “Interpreting Explain plan”）
可以查看原文Interpreting Explain plan
http://www.akadia.com/services/ora_interpreting_explain_plan.html

lawson.lin

收藏学习中，感觉楼主的辛勤劳动。

mhy119

精彩！

husthxd