function-based index 测试

最新推荐文章于 2023-11-04 09:30:00 发布

congku8444

最新推荐文章于 2023-11-04 09:30:00 发布

阅读量150

点赞数

文章标签：数据库

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

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/308563/viewspace-171903/