函数索引的使用

官方文档地址:http://docs.oracle.com/cd/B19306_01/server.102/b14211/data_acc.htm#i9946

15.2 Using Function-based Indexes forPerformance

 

A function-based index includes columns thatare either transformed by a function, such as theUPPER function, or included in an expression, such ascol1 + col2. With a function-based index,you can store computation-intensive expressions in the index.

 

Defining afunction-based index on the transformed column or expression allowsthat data to be returned using the index when that functionor expression is used in a WHERE clause or anORDER BY clause.--当函数或者表达式用于WHERE或者ORDER BY子句当中,

定义在转换列或者表达式上的一个表达式函数允许使用索引返回数据。

 

This allows Oracle to bypass computing the value of theexpression when processing SELECT andDELETE statements. Therefore, a function-based index can be beneficial whenfrequently-executed SQL statements include transformed columns, orcolumns in expressions, in a WHERE orORDER BY clause.--用于WHERE或者ORDER BY子句当中

 

Oracle treats descending indexes as function-based indexes. Thecolumns marked DESC are sorted in descendingorder.

For example, function-based indexes defined with theUPPER(column_name) orLOWER(column_name) keywords allowcase-insensitive searches. The index created in the followingstatement:

CREATE INDEX uppercase_idx ON employees (UPPER(last_name));

facilitates processing queries such as:

SELECT * FROM employees
    WHERE UPPER(last_name) = 'MARKSON';
文档地址:http://docs.oracle.com/cd/B19306_01/appdev.102/b14251/adfns_indexing.htm#i1006463
 
 

Restrictions for Function-Based Indexes


--对于基于函数的索引的限制

 

Note the following restrictions for function-based indexes:
  • Only cost-based optimization can use function-based indexes.Remember to call DBMS_STATS.GATHER_TABLE_STATISTICS orDBMS_STATS.GATHER_SCHEMA_STATISTICS, for thefunction-based index to be effective.

  • Any top-level or package-level PL/SQL functions that are used inthe index expression must be declared asDETERMINISTIC. That is, they always return the sameresult given the same input, for example, the UPPERfunction. You must ensure that the subprogram really isdeterministic, because Oracle Database does not check that theassertion is true.

    The following semantic rules demonstrate how to use the keywordDETERMINISTIC:

    • You can declare a top level subprogram asDETERMINISTIC.

    • You can declare a PACKAGE level subprogram asDETERMINISTIC in the PACKAGEspecification but not in the PACKAGEBODY. Errors are raised if DETERMINISTICis used inside a PACKAGE BODY.

    • You can declare a private subprogram (declared inside anothersubprogram or a PACKAGE BODY) asDETERMINISTIC.

    • A DETERMINISTIC subprogram can call anothersubprogram whether the called program is declared asDETERMINISTIC or not.

  • If you change the semantics of a DETERMINISTICfunction and recompile it, then existing function-based indexes andmaterialized views report results for the prior version of thefunction. Thus, if you change the semantics of a function, you mustmanually rebuild any dependent function-based indexes andmaterialized views.

  • Expressions in a function-based index cannot contain anyaggregate functions. The expressions should reference only columnsin a row in the table.

  • You must analyze the table or index before the index isused.

  • Bitmap optimizations cannot use descending indexes.

  • Function-based indexes are notused when OR-expansion is done.

  • The index function cannot be marked NOT NULL. Toavoid a full table scan, you must ensure that the query cannotfetch null values.

  • Function-based indexes cannot use expressions that returnVARCHAR2 or RAW data types of unknownlength from PL/SQL functions. A workaround is to limit the size ofthe function's output by indexing a substring of known length:

    -- INITIALS() might return 1 letter, 2 letters, 3 letters, and so on.
    -- We limit the return value to 10 characters for purposes of the index.
    CREATE INDEX func_substr_index ON
      emp_tab(substr(initials(ename),1,10);
    
    -- Call SUBSTR both when creating the index and when referencing
    -- the function in queries.
    SELECT SUBSTR(initials(ename),1,10) FROM emp_tab;
    
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值