索引的两个知识点

5 附表（索引什么时候不工作）

    首先要声明两个知识点：

    （1）RBO&CBO。
    Oracle有两种执行优化器，一种是RBO(Rule Based Optimizer)基于规则的优化器，这种优化器是基于sql语句写法选择执行路径的；另一种是CBO（Cost Based Optimizer）基于规则的优化器，这种优化器是Oracle根据统计分析信息来选择执行路径，如果表和索引没有进行分析，Oracle将会使用RBO代替CBO；如果表和索引很久未分析，CBO也有可能选择错误执行路径，不过CBO是Oracle发展的方向，自8i版本来已经逐渐取代RBO.

    （2）AUTOTRACE。
    要看索引是否被使用我们要借助Oracle的一个叫做AUTOTRACE功能,它显示了sql语句的执行路径，我们能看到Oracle内部是怎么执行sql的，这是一个非常好的辅助工具，在sql调优里广泛被运用。我们来看一下怎么运用AUTOTRACE：
    ① 由于AUTOTRACE自动为用户指定了Execution Plan,因此该用户使用AUTOTRACE前必须已经建立了PLAN_TABLE。如果没有的话，请运行utlxplan.sql脚本（它在$ORACLE_HOME/rdbms/admin目录中）。
    ② AUTOTRACE可以通过运行plustrce.sql脚本(它在$ORACLE_HOME/sqlplus/admin目录中)来设置，用sys用户登陆然后运行plustrce.sql后会建立一个PLUSTRACE角色，然后给相关用户授予PLUSTRACE角色，然后这些用户就可以使用AUTOTRACE功能了。
    ③ AUTOTRACE的默认使用方法是set autotrace on，但是这方法不总是适合各种场合，特别当返回行数很多的时候。Set autotrace traceonly提供了只查看统计信息而不查询数据的功能。

 
 

  
  
  
  
  
      SQL
  
  >
  
   
  
  set
  
   autotrace 
  
  on
  
  
    SQL
  
  >
  
   
  
  select
  
   
  
  *
  
   
  
  from
  
   test;
    A
    
  
  --
  
  --------
  
  

  
      
  
  1
  
  
    Execution 
  
  Plan
  
  
    
  
  --
  
  --------------------------------------------------------
  
  

  
      
  
  0
  
   
  
  SELECT
  
   STATEMENT Optimizer
  
  =
  
  CHOOSE
    
  
  1
  
   
  
  0
  
   
  
  TABLE
  
   ACCESS (
  
  FULL
  
  ) 
  
  OF
  
   
  
  '
  
  TEST
  
  '
  
  
    
  
  Statistics
  
  
    
  
  --
  
  --------------------------------------------------------
  
  

  
      
  
  0
  
   recursive calls
    
  
  0
  
   db block gets
    
  
  0
  
   consistent gets
    
  
  0
  
   physical reads
    
  
  0
  
   redo size
    
  
  0
  
   bytes sent via SQL
  
  *
  
  Net 
  
  to
  
   client
    
  
  0
  
   bytes received via SQL
  
  *
  
  Net 
  
  from
  
   client
    
  
  0
  
   SQL
  
  *
  
  Net roundtrips 
  
  to
  
  /
  
  from
  
   client
    
  
  0
  
   sorts (memory)
    
  
  0
  
   sorts (
  
  disk
  
  )
    rows processed

    SQL
  
  >
  
   
  
  set
  
   autotrace traceonly
    SQL
  
  >
  
   
  
  select
  
   
  
  *
  
   
  
  from
  
   test.test;

    Execution 
  
  Plan
  
  
    
  
  --
  
  --------------------------------------------------------
  
  

  
      
  
  0
  
   
  
  SELECT
  
   STATEMENT Optimizer
  
  =
  
  CHOOSE
    
  
  1
  
   
  
  0
  
   
  
  TABLE
  
   ACCESS (
  
  FULL
  
  ) 
  
  OF
  
   
  
  '
  
  TEST
  
  '
  
  

    
  
  Statistics
  
  
    
  
  --
  
  --------------------------------------------------------
  
  

  
      
  
  0
  
   recursive calls
    
  
  0
  
   db block gets
    
  
  0
  
   consistent gets
    
  
  0
  
   physical reads
    
  
  0
  
   redo size
    
  
  0
  
   bytes sent via SQL
  
  *
  
  Net 
  
  to
  
   client
    
  
  0
  
   bytes received via SQL
  
  *
  
  Net 
  
  from
  
   client
    
  
  0
  
   SQL
  
  *
  
  Net roundtrips 
  
  to
  
  /
  
  from
  
   client
    
  
  0
  
   sorts (memory)
    
  
  0
  
   sorts (
  
  disk
  
  )
    rows processed
 
 

    Hints是Oracle提供的一个辅助用法，按字面理解就是‘提示’的意思，确实它起得作用也是提示优化器按它所提供的关键字来选择执行路径，特别适用于sql调整的时候。使用方法如下：

    {DELETE|INSERT|SELECT|UPDATE} /*+ hint [text] [hint[text]]... */

    具体可参考Oracle SQL Reference。有了前面这些知识点，接下来让我们来看一下什么时候索引是不起作用的。以下列出几种情况。

    （1）类型不匹配时。

 
 

  
  
  
  
  
      SQL
  
  >
  
   
  
  create
  
   
  
  table
  
   test.testindex (a 
  
  varchar
  
  (
  
  2
  
  ),b 
  
  number
  
  );
    表已创建。
    SQL
  
  >
  
   
  
  create
  
   
  
  index
  
   ind_cola 
  
  on
  
   test.testindex(a);
    索引已创建。
    SQL
  
  >
  
   
  
  insert
  
   
  
  into
  
   test.testindex 
  
  values
  
  (
  
  '
  
  1
  
  '
  
  ,
  
  1
  
  );
    已创建 
  
  1
  
   行。
    SQL
  
  >
  
   
  
  commit
  
  ;
    提交完成。
    SQL
  
  >
  
   analyze 
  
  table
  
   test.testindex 
  
  compute
  
   
  
  statistics
  
   
  
  for
  
   
  
  all
  
   indexes;
    表已分析。
    SQL
  
  >
  
   
  
  set
  
   autotrace 
  
  on
  
  ;
    SQL
  
  >
  
   
  
  select
  
   
  
  /**/
  
  /*+RULE */
  
  *
  
   
  
  FROM
  
   test.testindex 
  
  where
  
   a
  
  =
  
  '
  
  1
  
  '
  
  ;(使用基于rule的优化器，数据类型匹配的情况下)
    A B
    
  
  --
  
   ----------
  
  

  
      
  
  1
  
   
  
  1
  
  
    Execution 
  
  Plan
  
  
    
  
  --
  
  --------------------------------------------------------
  
  

  
      
  
  0
  
   
  
  SELECT
  
   STATEMENT Optimizer
  
  =
  
  HINT: 
  
  RULE
  
  
    
  
  1
  
   
  
  0
  
   
  
  TABLE
  
   ACCESS (
  
  BY
  
   
  
  INDEX
  
   ROWID) 
  
  OF
  
   
  
  '
  
  TESTINDEX
  
  '
  
  
    
  
  2
  
   
  
  1
  
   
  
  INDEX
  
   (RANGE SCAN) 
  
  OF
  
   
  
  '
  
  IND_COLA
  
  '
  
   (NON
  
  -
  
  UNIQUE
  
  )（使用了索引ind_cola）
    ――――――――――――――――――――――――――――――――――
    SQL
  
  >
  
   
  
  select
  
   
  
  /**/
  
  /*+RULE */
  
  *
  
   
  
  FROM
  
   test.testindex 
  
  where
  
   a
  
  =
  
  1
  
  ;（数据类型不匹配的情况）
    A B
    
  
  --
  
   ----------
  
  

  
      
  
  1
  
   
  
  1
  
  
    Execution 
  
  Plan
  
  
    
  
  --
  
  --------------------------------------------------------
  
  

  
      
  
  0
  
   
  
  SELECT
  
   STATEMENT Optimizer
  
  =
  
  HINT: 
  
  RULE
  
  
    
  
  1
  
   
  
  0
  
   
  
  TABLE
  
   ACCESS (
  
  FULL
  
  ) 
  
  OF
  
   
  
  '
  
  TESTINDEX
  
  '
  
  （优化器选择了全表扫描）