Oracle SQL编写优化总结《一》

㈠ 选用合适的Oracle 优化器

缺省值：
9i默认是choose
10g默认是all_rows

     sys@EMREP> show parameter optimizer_mode          NAME                                 TYPE        VALUE     ------------------------------------ ----------- ------------------------------     optimizer_mode                       string      ALL_ROWS

参数：

ALL_ROWS：倾向于用最少的资源获取最大的结果集，也就是整体效果最佳
FIRST_ROWS：倾向最快反应速度
COST:选择CBO
RULE:选择RBO
CHOOSE:如果表被analyze，则走CBO；否则，RBO

修改：

session级：
alter session set optimizer_mode=xx;
实例级：
在init<SID>.ora里面修改
或者
alter system set optimizer_mode=xx；

虽然有RBO，但仍然建议收集统计信息，使用CBO
这种实例级的参数修改要经过相对全面的测试才能应用到生产系统的，否则风险太大
现在很多中间件或者三层架构，还是用ALL_ROWS更加合理
如果运行的很好，别碰它，局部调整，别大面积收集统计信息，小心有问题
收集统计信息很多策略的，什么时候收集，收集比例多少，哪些要收集直方图等等，这不光和数据库有关系还和业务有关系，制定好的统计信息收集策略不是那么容易的

㈡ 访问表的路径

Oracle访问表有两种路径：
① 全表扫描

全表扫描就是顺序地访问表中的每条记录
Oracle采用一次读入多个数据块的方式优化全表扫描
对大表尽量避免产生全表扫描

② 通过ROWID访问

ROWID包含了表中记录的物理位置信息
Oracle采用index实现数据和rowid之间的联系
Index提供快速访问rowid的方法