如何创建oracle函数索引
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
复合索引的例子:
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的才可以。
================================================================================================
这个语句还能优化嘛?
原语句:
select b.*
from test1 a,test2 b
where a.sflag ='-3'
and instr(','||a.id||',',','||b.certiid||',')>0;
通过执行发现test2表执行了全表扫描,需要120秒通过分析发现是test2的certiid列上实际上是有索引的。
但是它不能使用这个索引,不能通过索引的ROWID来搜索表,因为这里并没有b.certiid等于的条件,而是通过
条件instr(','||a.id||',',','||b.certiid||',')>0进行的连接。
然后考虑到需要返回的值是B.*而索引只包含了CERTIID列,索引INDEX FAST SCAN也用不到。
所以我们这里考虑修改如下:
select * from test2 where certiid in(
select b.certiid
from test1 a,test2 b
where a.sflag ='-3'
and instr(','||a.id||',',','||b.certiid||',')>0);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID gsha1gj68gacg, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from test2 where certiid in (select
b.certiid from test1 a, test2 b where a.sflag = '-3' and
instr(',' || a.id || ',', ',' || b.certiid || ',') > 0)
Plan hash value: 4074250259
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 54 |00:00:32.69 | 133K|
| 1 | NESTED LOOPS | | 1 | 1 | 54 |00:00:32.69 | 133K|
| 2 | VIEW | VW_NSO_1 | 1 | 1 | 54 |00:00:32.69 | 133K|
| 3 | HASH UNIQUE | | 1 | 1 | 54 |00:00:32.69 | 133K|
| 4 | NESTED LOOPS | | 1 | 1 | 54 |00:00:28.67 | 133K|
| 5 | TABLE ACCESS BY INDEX ROWID| test1 | 1 | 1 | 38 |00:00:00.01 | 49 |
|* 6 | INDEX RANGE SCAN | INDEX_SFLAG | 1 | 1 | 38 |00:00:00.01 | 3 |
|* 7 | INDEX FAST FULL SCAN | PK_test2 | 38 | 24575 | 54 |00:00:25.65 | 133K|
| 8 | TABLE ACCESS BY INDEX ROWID | test2 | 54 | 1 | 54 |00:00:00.01 | 167 |
|* 9 | INDEX UNIQUE SCAN | PK_test2 | 54 | 1 | 54 |00:00:00.01 | 113 |
----------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("A"."SFLAG"='-3')
7 - filter(INSTR(','||"A"."ID"||',',','||"B"."CERTIID"||',')>0)
9 - access("CERTIID"="$nso_col_1")
这里使用了一个子查询,子查询中使用的条件不是B.*而是B.certiid.可以看到这里使用了 INDEX FAST FULL SCAN,相当于把
索引当表来进行扫描,不考虑索引的结构。但是这里的一列的‘表’比所有字段的表要小得多所以也加快了查询。
修改后得语句任然有性能问题,看到这里的
INDEX FAST FULL SCAN | PK_test2 | 38
在NESTED LOOP的机制下运行了38次。本来想通过HASH JION来代替NESTED LOOPS 但是这里条件是INSTR(','||"A"."ID"||',',','||"B"."CERTIID"||',')>0,所以不好修改了。
修改后语句运行的时间由120秒降低到48秒,不知道还有优化的方法没?
原执行计划:
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1212030027
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3943K| 1921M| 1384K (1
| 1 | NESTED LOOPS | | 3943K| 1921M| 1384K (1
| 2 | TABLE ACCESS BY INDEX ROWID| test1 | 71 | 1278 | 5 (0
|* 3 | INDEX RANGE SCAN | INDEX_SFLAG | 71 | | 3 (0
|* 4 | TABLE ACCESS FULL | test2 | 55470 | 26M| 19504 (1
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"."SFLAG"='-3')
4 - filter(INSTR(','||"A"."ID"||',',','||"B"."CERTIID"||',')>0)
17 rows selected
http://www.itpub.net/thread-1477884-1-9.html
分享到:
相关推荐
oracle_函数索引_oracle函数速查[参考].pdf
1 Oracle:索引 5分 2 Oracle:plsql,游标 6分 3 Oracle:sql命令 8分 4 Oracle:数据库创建,授权 6分 5 Oracle:plsql块 5分 6 Oracle:过程与函数 6分 7 Oracle:plsql块 6分 8 Oracle:plsql,过程与函数 8分 9 Oracle:...
oracle 时间函数
Oracle Spatial是甲骨文公司针对空间数据管理的一组插件, 其针对存储在Oracle Spatial数据库中空间元素提供了一种SQL 模式和便于存储、检索、更新、查询...Oracle Spatial提供了两种索引机制 即R树索引和四叉树索引。
④:索引 七.PL/SQL基础 ①:什么是PL/SQL ②:集合 ③:动态SQL ④:条件语句 ⑤:循环语句 ⑥:执行计划 八.游标 ①:隐式游标 ②:显式游标 ③:REF游标 ④:使用游标的好处和坏处 十.子程序[proc,udf],自主事务...
在Oracle中,索引基本分为以下几种:B*Tree索引,反向索引,降序索引,位图索引,函数索引,interMedia全文索引 等。本文主要就前6种索引进行分析
由自己手敲代码已验证好用,里面有索引,存储过程,函数
第一章:数据库管理员命令参考 第二章:数据库的创建和管理 第三章:回滚/撤销段.控制文件和重做日志 第四章:表空间 第五章:表 第六章:索引 第七章:视图 第八章:集群 ...附录C:Oracle sql语法
oracle函数库参考手册。 函数大全不是很全,SQL和Oracle通解很管用啊,包含了各种触发器、存储过程、约束、索引、查询等等的示例。 老实说就是来赚分的,但是瞅瞅文件大小就知道不会亏咯。我也是花了时间和积分来找...
Oracle Exception汇总(自定义Oracle异常) 使用方法举例: Exception When no_data_found then Dbms_output.put_line(‘no_data_found’); ACCESS_INTO_NULL 为对象赋值前必需初始化对象。对应ORA-06530错误。 CASE...
Oracle 11g 索引的详细解析!B-tree Index,反向索引,函数索引,bitmap 索引等等的详细介绍。
函数索引 视图 序列 利用OEM操作 第九章:备份与恢复 脱机备份与恢复 联机备份与恢复 逻辑备份与恢复 第十章:sqlplus基础 设置SQL*PLUS的运行环境 格式化查询命令 第十一章:分区表 概述 范围分区 ...
Les01 : 基本SQL SELECT语句 Les02 : 过滤和排序数据...Les16 : Oracle9i 日期函数 Les17 : 对 GROUP BY 子句的扩展 Les18 : 高级子查询 Les19 : Oracle9i 对 DML 和 DDL 语句的扩展 Les20 : 对 DML 和 DDL 语句的扩展
第十一章:索引 第十二章:约束 第十三章:视图 第十四章:同义词 第十五章:序列 第三部分:SQL语言的扩展 第十六章:INSERT语句总结 第十七章:DML和DDL语句的其他用法 第十八章 通过GROUP BY产生统计...
附录部分内容丰富、便于参照,包括Oracle命令、关键字、功能以及函数等. 作译者 本书提供作译者介绍 Kevin Loney是Oracle数据库设计、开发、管理和调整方面的国际知名专家。作为金融界的一名企业数据库架构师,...
查看系统表中的用户索引 在Oracle中,SYSTEM表是安装数据库时自动建立的,它包含数据库的全部数据字典,存储过程、包、函数和触发器的定义以及系统回滚段。 一般来说,应该尽量避免在SYSTEM表中存储非SYSTEM用户的...
expimp导出导入工具的使用.doc,Oracle 单记录函数.doc,...SQLServer和Oracle常用函数对比.doc,关系型数据库及其设计方法.doc,视图、同义词和序列.doc,索引与约束.doc,详细讲述Oracle中的to_date参数含义.doc
Day05-Oracle常用函数.pdf Day06-Oracle高级查询一.pdf Day07-Oracle高级查询二.pdf Day08-Oracle分页查询和视图.pdf Day09-存储过程.pdf Day10-变量定义和循环控制.pdf Day11-plsql游标和函数.pdf Day12-触发器.pdf...