ITPub博客

首页 > Linux操作系统 > Linux操作系统 > function-based index 测试

function-based index 测试

原创 Linux操作系统 作者:xhailiang 时间:2006-10-18 00:00:00 0 删除 编辑

oracle 官方文档上面说:

For the creation of a function-based index in your own schema, you must be granted the QUERY REWRITE system privileges. To create the index in another schema or on another schema's tables, you must have the CREATE ANY INDEX and GLOBAL QUERY REWRITE privileges.

You must have the following initialization parameters defined to create a function-based index:

  • QUERY_REWRITE_INTEGRITY set to TRUSTED
  • QUERY_REWRITE_ENABLED set to TRUE
  • COMPATIBLE set to 8.1.0.0.0 or a greater value

Additionally, to use a function-based index:

  • The table must be analyzed after the index is created.
  • The query must be guaranteed not to need any NULL values from the indexed expression, since NULL values are not stored in indexes.


SQL*Plus: Release 9.2.0.1.0 - Production on 星期三 10月 18 21:24:23 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


SQL> conn /as sysdba
已连接。

SQL> show user
USER 为"SYS"
SQL> show parameter QUERY_REWRITE_INTEGRITY

NAME TYPE VALUE
------------------------------------ ----------- ---------
query_rewrite_integrity string enforced

SQL> show parameter QUERY_REWRITE_ENABLED

NAME TYPE VALUE
------------------------------------ ----------- --------
query_rewrite_enabled string FALSE

SQL> show parameter COMPATIBLE

NAME TYPE VALUE
------------------------------------ ----------- -------------
compatible string 9.2.0.0.0


SQL> desc hr.test
名称 是否为空? 类型
----------------------------------------- -------- -------------
EMPLOYEE_ID NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)

select count(*) from hr.test;
conut(*)
14024704


SQL> drop index hr.idxtest;

索引已丢弃。

select * from hr.test where :employee_id*5>551;
explain plan:
SELECT STATEMENT, GOAL = CHOOSE
FILTER
TABLE ACCESS FULL Object owner=HR Object name=TEST

create function base index:

SQL> conn hr/xhl
已连接。

SQL> create index hr.idxtest on hr.test(employee_id*5);
create index hr.idxtest on hr.test(employee_id*5)
*
ERROR 位于第 1 行:
ORA-01031: 权限不足

SQL> conn /as sysdba
已连接。

create index hr.idxtest on hr.test(employee_id*5);

索引已创建

测试在没有分析TABLE时,不会走函数索引:

select * from hr.test where :employee_id*5>551;
explain plan:SELECT STATEMENT, GOAL = CHOOSE
FILTER
TABLE ACCESS FULL Object owner=HR Object name=TEST

分析TEST 表以后,走函数索引:

analyze table :
begin
dbms_stats.gather_table_stats(ownname=> 'HR', tabname=> 'TEST', partname=> NULL);
end;

select * from hr.test where :employee_id*5>551;
explain plan:
SELECT STATEMENT, GOAL = CHOOSE Cost=748 Cardinality=12675157 Bytes=912611304
TABLE ACCESS BY INDEX ROWID Object owner=HR Object name=TEST Cost=748 Cardinality=12675157 Bytes=912611304
INDEX RANGE SCAN Object owner=HR Object name=IDXTEST Cost=24 Cardinality=12675157

是不是以上测试说明:

  • QUERY_REWRITE_INTEGRITY set to TRUSTED
  • QUERY_REWRITE_ENABLED set to TRUE
  • 不是跟文档上讲的一样也可以用到FUNCTION-BASED INDEX????

    来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/308563/viewspace-171903/,如需转载,请注明出处,否则将追究法律责任。

    请登录后发表评论 登录
    全部评论

    注册时间:2008-02-17

    • 博文量
      270
    • 访问量
      415500