创建ORACLE索引

    【IT168 技术文档】Oracle8i的很重要的一个新特性就是增加了function-based index这种索引类型（后面简称为FBI）。有了这个特性后，Oracle DBA就可以在索引中使用函数或者表达式了。这些函数可以使Oracle自己的函数，也可以使用户自己的PL/SQL函数等。

     Oracle8i的很重要的一个新特性就是增加了function-based index这种索引类型（后面简称为FBI）。有了这个特性后，Oracle DBA就可以在索引中使用函数或者表达式了。这些函数可以使Oracle自己的函数，也可以使用户自己的PL/SQL函数等。

     DBA在SQL语句调优的过程中遇到的一个很常见的问题就是，如何优化那些在WHERE子句中使用了函数的语句。因为在以前，在WHERE子句中使用函数会使在这个表上创建的索引没法利用，从而难以提高这个语句的性能。

     例子：

使用基于成本的优化器，索引为标准的B树索引，建立在SURNAME列上。

 

  
  


   
   

    
    
    
    
    
        SQL
    
    >
    
    create
    
     
    
    index
    
     non_fbi 
    
    on
    
     sale_contacts (surname);
    SQL
    
    >
    
    analyze 
    
    index
    
     non_fbi 
    
    compute
    
     
    
    statistics
    
    ;
    SQL
    
    >
    
    :analyze 
    
    table
    
     sale_contacts 
    
    compute
    
     
    
    statistics
    
    ;
    SQL
    
    >
    
    SELECT
    
     
    
    count
    
    (
    
    *
    
    ) 
    
    FROM
    
     sale_contacts
    
    
    WHERE
    
     
    
    UPPER
    
    (surname) 
    
    =
    
     
    
    '
    
    ELLISON
    
    '
    
    ;

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

    
        
    
    0
    
     
    
    SELECT
    
     STATEMENT Optimizer
    
    =
    
    CHOOSE (Cost
    
    =
    
    3
    
     Card
    
    =
    
    1
    
     Bytes
    
    =
    
    17
    
    )
    
    
    1
    
     
    
    0
    
     SORT (AGGREGATE)
    
    
    2
    
     
    
    1
    
     
    
    TABLE
    
     ACCESS (
    
    FULL
    
    ) 
    
    OF
    
     
    
    '
    
    SALES_CONTACTS
    
    '
    
     (Cost
    
    =
    
    3
    
     Card
    
    =
    
    16
    
     Bytes
    
    =
    
    272
    
    ) 
   
   

  
  

 

    从SQL*PLUS的autotrace产生的执行路径可以看到，虽然我们在WHERE子句中用到的SURNAME列上创建了索引，但是仍然执行的是全表扫描。如果这张表很大的话，这回消耗大量的时间。

     现在我们试着建立一个FBI索引：

 

  
  


   
   

    
    
    
    
    
        SQL
    
    >
    
    create
    
     
    
    index
    
     fbi 
    
    on
    
     sale_contacts (
    
    UPPER
    
    (surname));
    SQL
    
    >
    
    analyze 
    
    index
    
     fbi 
    
    compute
    
     
    
    statistics
    
    ;
    SQL
    
    >
    
    analyze 
    
    table
    
     sale_contacts 
    
    compute
    
     
    
    statistics
    
    ;
    SQL
    
    >
    
    SELECT
    
     
    
    count
    
    (
    
    *
    
    ) 
    
    FROM
    
     sale_contacts 
    
    WHERE
    
     
    
    UPPER
    
    (surname) 
    
    =
    
     
    
    '
    
    ELLISON
    
    '
    
    ;
    Execution 
    
    Plan
    
    
    
    
    --
    
    -------------------------------------------------------- 
    
    

    
        
    
    0
    
     
    
    SELECT
    
     STATEMENT Optimizer
    
    =
    
    CHOOSE (Cost
    
    =
    
    2
    
     Card
    
    =
    
    1
    
     Bytes
    
    =
    
    17
    
    )
    
    
    1
    
     
    
    0
    
     SORT (AGGREGATE)
    
    
    2
    
     
    
    1
    
     
    
    INDEX
    
     (RANGE SCAN) 
    
    OF
    
     
    
    '
    
    FBI
    
    '
    
     (NON
    
    -
    
    UNIQUE
    
    ) (Cost
    
    =
    
    2
    
     Card
    
    =
    
    381
    
     Bytes
    
    =
    
    6477
    
    ) 
   
   

  
  

 

    从SQL*Plus返回的执行计划我们可以看到，这次，Oracle对表不再全表扫描，而是先扫描索引，因为优化器可以知道FBI索引得存在。使用FBI索引所能够带来的性能提升取决于表的大小、表中重复记录的量、在WHERE子句中使用的列等因素。有一点需要清楚，FBI索引并不真正在索引里边存储了表达式的结果，而是使用了一个“表达树”（expression tree）。

     由优化器来对SQL语句中的表达式进行解析，并且和FBI索引上面的表达式进行对比。这里，SQL函数的大小写时敏感的。因此要求SQL语句中使用的函数和创建FBI索引得时候的那个SQL函数的大小写一致，否则无法利用这个FBI索引。因此，在编程的时候要有一个良好的编程风格。

     Init.ora里边需要修改的参数，下面这几个参数必须在init.ora里边指定：

 

  
  


   
   

    
    
    
    
    
        QUERY_REWRITE_INTEGRITY 
    
    =
    
     TRUSTED
    QUERY_REWRITE_ENABLED 
    
    =
    
     TRUE
    COMPATIBLE 
    
    =
    
     
    
    8.1
    
    .
    
    0.0
    
    .
    
    0
    
     (
    
    or
    
     higher) 
   
   

  
  

 

    授权：

     要使一个用户能够创建FBI索引，他必须被授予以下权限：CREATE INDEX和QUERY REWRITE,或者CREATE ANY INDEX和GLOBAL QUERY REWRITE这两个权限。索引的使用者必须能够有那个FBI索引上使用的那个函数的执行权限。如果没有相应的权限，那么这个FBI索引得状态将变成DISABLED（DBA_INDEXES）。

     如果那个FBI索引得状态是DISABLED，那么DBA可以这样来处理：

     a：删除并重建
    B：ALTER INDEX index_name ENABLED。这个Enabled只能对FBI索引使用。
    C：ALTER INDEX UNUSABLE;

     注意：如果一个查询中使用到了这个索引，但是这个FBI索引的状态是DISABLED，但是优化器选择了使用这个索引，那么将会返回一个Oracle错误。

     例子：

     ORA error:
    ERROR at line 1: ORA-30554: function-based index MYUSER.FBI is disabled.

     而且，一旦这个FBI索引的状态是Disabled，那么这张表上所有涉及索引列的DML操作也将失败。除非这个索引得状态变成UNUSABLE，而且在初始化参数里边指定SKIP_UNUSABLE_INDEXES为TRUE。

     一些例子：

 

  
  


   
   

    
    
    
    
    
        SQL
    
    >
    
    CREATE
    
     
    
    INDEX
    
     expression_ndx
    
    
    ON
    
     mytable ((mycola 
    
    +
    
     mycolc) 
    
    *
    
     mycolb);

    SQL
    
    >
    
    SELECT
    
     mycolc 
    
    FROM
    
     mytable
    
    
    WHERE
    
     (mycola 
    
    +
    
     mycolc) 
    
    *
    
     mycolb 
    
    <=
    
     
    
    256
    
    ; 
   
   

  
  

     复合索引的例子：

 

  
  


   
   

    
    
    
    
    
        SQL
    
    >
    
    CREATE
    
     
    
    INDEX
    
     example_ndx
    
    
    ON
    
     myexample (mycola, 
    
    UPPER
    
    (mycolb), mycolc);

    SQL
    
    >
    
    SELECT
    
     mycolc 
    
    FROM
    
     myexample
    
    
    WHERE
    
     mycola 
    
    =
    
     
    
    55
    
     
    
    AND
    
     
    
    UPPER
    
    (mycolb) 
    
    =
    
     
    
    '
    
    JONES
    
    '
    
    ; 
   
   

  
  

 

    限制和规则总结：

     对于下面这些限制，不能创建FBI索引：

     a) LOB 列
    b) REF
    c) Nested table 列
    d) 包含上面数据类型的对象

     FBI索引必须遵守下面的规则：

     a) 必须使用基于成本的优化器，而且创建后必须对索引进行分析
    b) 不能存储NULL值。因为任何函数在任何情况下都不能返回NULL值。
    c)如果一个用户定义的PL/SQL例程失效了，而且这个例程被FBI索引用到了，那么相应的这个FBI索引会变成DISABLED
    d)创建FBI索引得函数必须是确定性的。即，对于指定的输入，总是会返回确定的结果。
    e) 索引的属主如果没有了在FBI索引里面使用的函数的执行权限，那么这个FBI索引会变成DISABLED.
    f) 在创建索引得函数里面不能使用SUM等总计函数。
    g)要把一个DISABLED了的索引重新变成ENABLED，这个函数必须首先是ENABLED的才可以。