索引不起作用的几种情况

 （2）条件列包含函数但没有创建函数索引。

 
 

  
  
  
  
  
  
  
    SQL
  
  >
  
   
  
  select
  
   
  
  /**/
  
  /*+ RULE */
  
  *
  
   
  
  FROM
  
   test.testindex 
  
  where
  
   
  
  upper
  
  (a)
  
  =
  
   
  
  '
  
  A
  
  '
  
  ;（使用了函数upper()在列a上）;
    A B
    
  
  --
  
   ----------
  
  

  
      a 
  
  2
  
  
    Execution 
  
  Plan
  
  
    
  
  --
  
  --------------------------------------------------------
  
  

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

  
  
    创建基于函数的索引
    SQL
  
  >
  
   
  
  create
  
   
  
  index
  
   test.ind_fun 
  
  on
  
   test.testindex(
  
  upper
  
  (a));
    索引已创建。
    SQL
  
  >
  
   
  
  insert
  
   
  
  into
  
   testindex 
  
  values
  
  (
  
  '
  
  a
  
  '
  
  ,
  
  2
  
  );
    已创建1行。
    SQL
  
  >
  
   
  
  commit
  
  ;
    提交完成。
    SQL
  
  >
  
   
  
  select
  
   
  
  /**/
  
  /*+ RULE*/
  
  *
  
   
  
  FROM
  
   test.testindex 
  
  where
  
   
  
  upper
  
  (a)
  
  =
  
  '
  
  A
  
  '
  
  ;
    A B
    
  
  --
  
   ----------
  
  

  
      a 
  
  2
  
  
    Execution 
  
  Plan
  
  
    
  
  --
  
  --------------------------------------------------------
  
  

  
      
  
  0
  
   
  
  SELECT
  
   STATEMENT Optimizer
  
  =
  
  HINT: 
  
  RULE
  
  
    
  
  1
  
   
  
  0
  
   
  
  TABLE
  
   ACCESS (
  
  FULL
  
  ) 
  
  OF
  
   
  
  '
  
  TESTINDEX
  
  '
  
  
    (在RULE优化器下忽略了函数索引选择了全表扫描)
    
  
  --
  
  ---------------------------------------------------------
  
  

  
      SQL
  
  >
  
   
  
  select
  
   
  
  *
  
   
  
  FROM
  
   test.testindex 
  
  where
  
   
  
  upper
  
  (a)
    
  
  =
  
  '
  
  A
  
  '
  
  ;
    A B
    
  
  --
  
   ----------
  
  

  
      a 
  
  2
  
  
    Execution 
  
  Plan
  
  
    
  
  --
  
  --------------------------------------------------------
  
  

  
      
  
  0
  
   
  
  SELECT
  
   STATEMENT Optimizer
  
  =
  
  CHOOSE (Cost
  
  =
  
  2
  
   Card
  
  =
  
  1
  
   Bytes
  
  =
  
  5
  
  )
    
  
  1
  
   
  
  0
  
   
  
  TABLE
  
   ACCESS (
  
  BY
  
   
  
  INDEX
  
   ROWID) 
  
  OF
  
   
  
  '
  
  TESTINDEX
  
  '
  
   (Cost
  
  =
  
  2
  
   Card
  
  =
  
  
    
  
  1
  
   Bytes
  
  =
  
  5
  
  )
    
  
  2
  
   
  
  1
  
   
  
  INDEX
  
   (RANGE SCAN) 
  
  OF
  
   
  
  '
  
  IND_FUN
  
  '
  
   (NON
  
  -
  
  UNIQUE
  
  ) (Cost
  
  =
  
  1
  
   Car
    d
  
  =
  
  1
  
  )(CBO优化器使用了ind_fun索引)
 
 

      （3）复合索引中的前导列没有被作为查询条件。

 
 

  
  
  
  
  
     创建一个复合索引
    SQL
  
  >
  
   
  
  create
  
   
  
  index
  
   ind_com 
  
  on
  
   test.testindex(a,b);
    索引已创建。
    SQL
  
  >
  
   
  
  select
  
   
  
  /**/
  
  /*+ RULE*/
  
  *
  
   
  
  from
  
   test.testindex 
  
  where
  
   a
  
  =
  
  '
  
  1
  
  '
  
  ;
    A B
    
  
  --
  
   ----------
  
  

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

  
      
  
  0
  
   
  
  SELECT
  
   STATEMENT Optimizer
  
  =
  
  HINT: 
  
  RULE
  
  
    
  
  1
  
   
  
  0
  
   
  
  INDEX
  
   (RANGE SCAN) 
  
  OF
  
   
  
  '
  
  IND_COM
  
  '
  
   (NON
  
  -
  
  UNIQUE
  
  )(条件列表包含前导列时使用索引ind_com)
    SQL
  
  >
  
   
  
  select
  
   
  
  /**/
  
  /*+ RULE*/
  
  *
  
   
  
  from
  
   test.testindex 
  
  where
  
   b
  
  =
  
  1
  
  ;
    未选定行
    Execution 
  
  Plan
  
  
    
  
  --
  
  --------------------------------------------------------
  
  

  
      
  
  0
  
   
  
  SELECT
  
   STATEMENT Optimizer
  
  =
  
  HINT: 
  
  RULE
  
  
    
  
  1
  
   
  
  0
  
   
  
  TABLE
  
   ACCESS (
  
  FULL
  
  ) 
  
  OF
  
   
  
  '
  
  TESTINDEX
  
  '
  
  (条件列表不包括前导列是选择全表扫描)
    
  
  --
  
  ---------------------------------------------------------
 
 

     （4）CBO模式下选择的行数比例过大，优化器采取了全表扫描。

 
 

  
  
  
  
  
      SQL
  
  >
  
   
  
  select
  
   
  
  *
  
   
  
  from
  
   test.testindex 
  
  where
  
   a
  
  =
  
  '
  
  1
  
  '
  
  ;
    A B
    
  
  --
  
   ----------
  
  

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

  
      
  
  0
  
   
  
  SELECT
  
   STATEMENT Optimizer
  
  =
  
  CHOOSE (Cost
  
  =
  
  1
  
   Card
  
  =
  
  1
  
   Bytes
  
  =
  
  5
  
  )
    
  
  1
  
   
  
  0
  
   
  
  TABLE
  
   ACCESS (
  
  FULL
  
  ) 
  
  OF
  
   
  
  '
  
  TESTINDEX
  
  '
  
   (Cost
  
  =
  
  1
  
   Card
  
  =
  
  1
  
   Bytes
  
  =
  
  5
  
  )
    （表一共2行，选择比例为50
  
  %
  
  ,所以优化器选择了全表扫描）
    ――――――――――――――――――――――――――――――――――
    下面增加表行数
    SQL
  
  >
  
   
  
  declare
  
   i 
  
  number
  
  ;
    
  
  2
  
   
  
  begin
  
  
    
  
  3
  
   
  
  for
  
   i 
  
  in
  
   
  
  1
  
   .. 
  
  100
  
   loop
    
  
  4
  
   
  
  insert
  
   
  
  into
  
   test.testindex 
  
  values
  
   (to_char(i),i);
    
  
  5
  
   
  
  end
  
   loop;
    
  
  6
  
   
  
  end
  
  ;
    
  
  7
  
   
  
  /
  
  
    PL
  
  /
  
  SQL 过程已成功完成。
    SQL
  
  >
  
   
  
  commit
  
  ;
    提交完成。
    SQL
  
  >
  
   
  
  select
  
   
  
  count
  
  (
  
  *
  
  ) 
  
  from
  
   test.testindex;
    
  
  COUNT
  
  (
  
  *
  
  )
    
  
  --
  
  --------
  
  

  
      
  
  102
  
  
    SQL
  
  >
  
   
  
  select
  
   
  
  *
  
   
  
  from
  
   test.testindex 
  
  where
  
   a
  
  =
  
  '
  
  1
  
  '
  
  ;
    A B
    
  
  --
  
  -- ----------
  
  

  
      
  
  1
  
   
  
  1
  
  
    
  
  1
  
   
  
  2
  
  
    Execution 
  
  Plan
  
  
    
  
  SELECT
  
   STATEMENT Optimizer
  
  =
  
  CHOOSE (Cost
  
  =
  
  1
  
   Card
  
  =
  
  1
  
   Bytes
  
  =
  
  5
  
  )
    
  
  1
  
   
  
  0
  
   
  
  INDEX
  
   (RANGE SCAN) 
  
  OF
  
   
  
  '
  
  IND_COM
  
  '
  
   (NON
  
  -
  
  UNIQUE
  
  ) (Cost
  
  =
  
  1
  
   Card
  
  =
  
  1
  
   Bytes
  
  =
  
  5
  
  )
    （表一共102行，选择比例为2
  
  /
  
  102
  
  =
  
  2
  
  %
  
  ,所以优化器选择了索引扫描）