oracle 查询优化器(optimizer)

1.优化器分类:
基于规则的优化器[Rule-based optimizer] ==> 10g中已经不使用
基于成本的优化器[Cost-Based Optimizer]

2.设置优化器模式:
查看数据库缺省优化:show parameter mode

基于实例:
alter system set optimizer_mode ={Choose|Rule|First_rows|First_rows_n|All_rows}

基于会话:
ALTER SESSION SET optimizer_mode ={Choose|Rule|First_rows|First_rows_n|All_rows}

参数意义:
CHOOSE:仅在9i及之前版本中被支持,10g已经废除。
RULE:仅在9i及之前版本中被支持,10g已经废除。
ALL_ROWS:在10g中为默认值,以吞吐量为基础
FIRST_ROWS:CBO尽可能快速的返回结果集的前面少数行记录,以响应速度为基础
FIRST_ROWS_n:不论是否存在统计信息,都使用CBO优化器,并以最快的速度返回前n行记录,n可以是1,10,100,1000。

3.影响执行计划的因素:

(1).访问路径
优化器可用的访问路径如下:

a.全表扫描(Full Table Scans)
注:访问数据量大,返回数据超过5%,不建议使用索引
oracle扫描时,多块读(一次I/O,读多个块,块必须连续,默认为16个块,具体查看 show parameter db ==> db_file_multiblock-read-count)

b.Rowid扫描(Rowid Scans):最快

c.索引扫描(Index Scans):唯一性索引,索引中最快的一种[空值不入索引,需要加一常量,使空值进入索引]
索引扫描类型:
唯一索引扫描(Index Unique Scans)
索引范围扫描(Index Range Scans):基于规则的,不支持组合索引
索引降序范围扫描(Index Range Scans Descending)
跳跃式索引扫描(Index Skip Scans):数据类型不一致
全索引扫描(Full Index Scans)
快速全索引扫描(Fast Full Index Scans)
索引连接(Index Joins)
注:组合索引中,是将组合看成列,如果查询中包涵非索引的列,则只走组合索引的第一列

d.簇扫描(Cluster Scans):
在被索引的簇中,有着相同簇键值的行存储在同一数据块中。执行簇扫描时,首先通过扫描簇索引获得被检索行的Rowid,然后使用Rowid来定位具体的行。

e.散列扫描(Hash Scans):
散列扫描就是在一个散列簇中定位数据行。在一个散列簇中,具有相同散列值的行存储在相同的数据块中。
在执行散列扫描时,首先通过一个散列函数来获得散列值,然后用散列值在数据块中定位具体行

(2).连接(join)
连接方式:

嵌套循环:[Nested loop join] ==>USE_NL(table1 table2)hint is used.(/*+ use_nl(e d) */)

hash循环:[Hash join] ==> USE_HASH(table1 table2)

排序合并:[Sort-merge join] ==>use_merge(table1 table2)

(3).物化视图(materialized views)
物化视图创建格式:

  1. CREATE MATERIALIZED VIEW mview_name  
  2. [TABLESPACE ts_name]  
  3. [PARALLEL (DEGREE n)]  
  4. [BUILD {IMMEDIATE|DEFERRED}]  
  5. [{ REFRESH {FAST|COMPLETE|FORCE}  
  6. [{ON COMMIT|ON DEMAND}] | NEVER REFRESH } ]  
  7. [{ENABLE|DISABLE} QUERY REWRITE]  
  8. AS SELECT … FROM …  

注:
refresh [fast|complete|force] 视图刷新的方式:
fast: 增量刷新.
假设前一次刷新的时间为t1,那么使用fast模式刷新物化视图时,只向视图中添加t1到当前时间段内,主表变化过的数据.
为了记录这种变化,建立增量刷新物化视图还需要一个物化视图日志表。
create materialized view log on 表名 with ……
complete:全部刷新。相当于重新执行一次创建视图的查询语句。
force: 这是默认的数据刷新方式。当可以使用fast模式时,数据刷新将采用fast方式;否则使用complete方式。
范例:

  1. sys> conn hr/hr  
  2. hr_1>create table e as select * from employees;  
  3. hr_1>create table d as select * from departments;  
  4. hr_1>insert into e select * from e;(54784 row)  
  5. hr_1>insert into d select * from d;(55296 row)  
  6.   
  7. hr_2>set autot on  
  8. hr_2>set timing on  
  9. hr_2>select dd.department_name,sum(ee.salary) from e ee,d dd where ee.department_id=dd.department_id group by dd.department_name;  
  10.   
  11. sys> grant CREATE MATERIALIZED VIEW to hr;  
  12.   
  13. sys> CREATE MATERIALIZED VIEW LOG ON e WITH SEQUENCE, ROWID  
  14.      (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID)  
  15. INCLUDING NEW VALUES;  
  16.   
  17. sys> CREATE MATERIALIZED VIEW LOG ON d WITH SEQUENCE, ROWID(DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID)  
  18. INCLUDING NEW VALUES;  
  19.   
  20. hr_1>create materialized view depart_sal_sum  
  21.      refresh fast  
  22.      on commit  
  23.      ENABLE QUERY REWRITE as  
  24.      select dd.department_name,sum(ee.salary) from e ee,d dd where ee.department_id=dd.department_id group by dd.department_name;  
  25.   
  26. hr_2>select dd.department_name,sum(ee.salary) from e ee,d dd where ee.department_id=dd.department_id group by dd.department_name;  

删除物化视图: drop materialized view 视图名称

物化视图表:user_mviews

查看相应信息:
select MVIEW_NAME,QUERY from user_mviews;