如何创建oracle函数索引 - javaeye - ITeye技术网站
博客分类：
如何创建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_fb
SQL&:analyze table sale_contact
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 fb
SQL&analyze table sale_contact
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
如何创建oracle函数索引
WHERE mycola = 55 AND UPPER(mycolb) = 'JONES';
限制和规则总结：
对于下面这些限制，不能创建FBI索引：
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的才可以。
浏览: 1046037 次
来自: 北京
标示对java很陌生！
Java中\是转意字符， 可是你的这句话我没看懂，只要把得到的 ...
可以参考最新的文档：如何在eclipse jee中检出项目并转 ...
，非常好。数据库的问题!Sql server-sql server 2012 数据库连接问题_经验分享网
你正在浏览： &>&
数据库的问题!Sql server
数据库的问题!Sql server
引. 源码 ？有没有个例题？只是为了查询搜索么？怎么创建索引.， 介绍一下？索引到底有什么好处.
可以用下面命令、聚集索引“IX_姓名”：为“学生”表创建基于“姓名”列的唯一。索引可以缩短查询速度。工具--向导--数据库--创建索引向导--确定例。创建索引可以用向导来创建索引，一般在数据量很大的查询中效果明显索引就相当于书的目录
GOIF EXISTS (SELECT name FROM with unique index&#39, State 1, State 1。其后有一个由索引处理的查询，从 POunces&#39, GetDate()).UnitMeasure 资料表中即使只有一个资料列违反 UNIQUE 索引约束条件;)
DROP INDEX IX_TransactionHistory_ReferenceOrderID
ON Production,OrderDate)= 3
AND DATEPART(yy、AddressLine2;GOCREATE INDEX IX_ProductVendor_VendorID
ON Purchasing,
QUOTED_IDENTIFIER: Msg 3604;GOCREATE TABLE #Test (C1 nvarchar(10),
OrderDate.D;GOCREATE NONCLUSTERED INDEX IX_SalesPerson_SalesQuota_SalesYTD
ON SOunces&#39.vOrders&#39. 使用 IGNORE_DUP_KEY 选项下列范例分别利用两种不同的选项设定 (先将选项设为 ON, 'GODROP TABLE #T)
DROP INDEX IXML_ProductModel_CatalogDescription_Path
ON PGOG. 建立主要 XML 索引下列范例会在 Production, N&#39.ProductVendor 资料表的 VendorID 资料行上建立非丛集索引; GOB;. 建立简单的非丛集复合索引下列范例会在 Sales.indexes
WHERE name = N&#39, Line 5 DupliGOJ. 建立次要 XML 索引下列范例会在 Production. The statement99999&#39.AddressWHERE PostalCode BETWEEN N&#39, State 1;PXML_ProductModel_CatalogDescription&#39.ProductModel (CatalogDescription)
USING XML INDEX PXML_ProductModel_CatalogDescription FOR PATH ;OC&#39, ModifiedDate)
VALUES (&#39。发出警告且忽略重复的资料列;GOH;IX_Address_PostalCode&#39，来测试唯一性条件约束, AddressLine2，请参阅＜Readme_PartitioningScript＞.WorkOrder(ProductID)
WITH (FILLFACTOR = 80;#Test&#39、CSales.SalesOrderID
AND DATEPART(mm。 复制程式码 SGOF。 复制程式码 USE AdventureWorksGOIF EXISTS (SELEC......索引可以缩短查询速度
数据库分别在s,sc上按sno列的唯一方式建立索引可能出现问题吗情况有什么不同……
您好,我看到您的问题很久没有人来回答,但是问题过期无人回答会被扣分的并且你的悬赏分也会被没收!所以我...Oracle数据库的问题……
losal和hisal 是表s的两个字段, 你如果看全部的语句,应该可以看到有个表是s,或是别名是s...数据库关系代数运算问题……
数据库关系代数运算问题(1)并(∪):两个关系需有相同的关系模式,并的对象是元组,由两个关系所有元组...SQL数据库查询问题求解……
select distant sname from s where s.sno not in (se...关于数据库的问题……
--2use gongchenggocreate user Tom for login Tomcre...数据库原理 问题设有关系R,S如下图,求 R S……
数据库原理 问题设有关系R,S如下图,求 R S_噴焱つ兎° 发布于 09:55...sql数据库crow‘s foot model问题……
参考:/RicCC/archive/...关于数据库的几道题目?求答案!……
SELECT T2.S#--, T2.COURSE_001, T2.COURSE_002FROM( ...(数据库问题)有关系R和S,R∩S的运算等价于?……
R,S都是集合,不是元素,^_^ 不好意思 R的元素是0,1,2,3 S的元素是2,3,4,5 而{...数据库范式问题……
答案是B,2NF。 S-&D,D-&M表示依赖关系,表示D依赖于S,尔M依赖于D,这表示M与S之间有...
你可能感兴趣的内容？MySQL中的explain命令显示了mysql如何使用索引来处理select语句以及连接表。explain显示的信息可以帮助选择更好的索引和写出更优化的查询语句。1.EXPLAIN的使用方法：在select语句前加上explain就可以了。如：explain&select&surname,first_name&form&a,b&where&a.id=b.id2.EXPLAIN列的解释：table：显示这一行的数据是关于哪张表的type：这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、&indexhe和ALLpossible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句key：&实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引。这种情况下，可以在SELECT语句中使用USE&INDEX（indexname）来强制使用一个索引或者用IGNORE&INDEX（indexname）来强制MYSQL忽略索引key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好ref：显示索引的哪一列被使用了，如果可能的话，是一个常数rows：MYSQL认为必须检查的用来返回请求数据的行数Extra：关于MYSQL如何解析查询的额外信息。坏的例子是Using&temporary和Using&filesort，意思MYSQL根本不能使用索引，结果是检索会很慢3.Extra列返回的描述的意义:Distinct:一旦MYSQL找到了与行相联合匹配的行，就不再搜索了Not&exists:&MYSQL优化了LEFT&JOIN，一旦它找到了匹配LEFT&JOIN标准的行，就不再搜索了Range&checked&for&each&Record（index&map:#）:没有找到理想的索引，因此对于从前面表中来的每一个行组合，MYSQL检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一Using&filesort:&看到这个的时候，查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行Using&index:&列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候Using&temporary&看到这个的时候，查询需要优化了。这里，MYSQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER&BY上，而不是GROUP&BY上Where&used&使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index，这就会发生，或者是查询有问题不同连接类型的解释（按照效率高低的顺序排序）system&表只有一行：system表。这是const连接类型的特殊情况const:表中的一个记录的最大值能够匹配这个查询（索引可以是主键或惟一索引）。因为只有一行，这个值实际就是常数，因为MYSQL先读这个值然后把它当做常数来对待eq_ref:在连接中，MYSQL在查询时，从前面的表中，对每一个记录的联合都从表中读取一个记录，它在查询使用了索引为主键或惟一键的全部时使用ref:这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分（比如，利用最左边前缀）时发生。对于之前的表的每一个行联合，全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好range:这个连接类型使用索引返回一个范围中的行，比如使用&或&查找东西时发生的情况index:&这个连接类型对前面的表中的每一个记录联合进行完全扫描（比ALL更好，因为索引一般小于表数据）ALL:这个连接类型对于前面的每一个记录联合进行完全扫描，这一般比较糟糕，应该尽量避免.附上在微博系统中数据查询性能分析的一个例子：默认查询，未使用索引：EXPLAIN&SELECT&*&FROM&microblogsGROUP&BY&authorrows&=&331&查询所有行去掉explain命令，执行select&*&from&microblogs&group&by&author，Query&took&0.0001&sec再看使用索引的效果：EXPLAIN&SELECT&*&FROM&microblogsUSE&INDEX&(&author&)&GROUP&BY&authorrows&=&7&查询只需7行去掉explain命令，执行select&*&from&microblogs&use&index&(author)&group&by&author,&Query&took&0.0001&sec通过比较可以得知，使用索引必须检查的行数由331变为7，是否数据库的工作量降低，还有待更多研究，毕竟可能由于数据规模不够大两者的执行时间似乎并无差异。
以上就介绍了explain的用法 MySQL中EXPLAIN解释命令详解，包括了explain的用法方面的内容，希望对MySql有兴趣的朋友有所帮助。
本文网址链接：/article/detail_4325677.html
上一篇： 下一篇：
explain的用法相关图片
explain的用法相关文章经典MySQL语句大全和常用SQL语句命令的作用_百度文库
两大类热门资源免费畅读
续费一年阅读会员，立省24元！
经典MySQL语句大全和常用SQL语句命令的作用
上传于|0|0|文档简介
&&MySQL语句大全,
你可能喜欢查看: 3565|回复: 8
怎样为SELECT语句指定特定的一个索引?
论坛徽章:2
一个表上我好几个索引，不想让ORACLE去指定索引。
谢了！！！
论坛徽章:117
论坛徽章:2
最初由 eygle 发布
[B]使用hints [/B]
谢谢，能否举个例子。
招聘 : 论坛徽章:39
最初由 Applmgr 发布
谢谢，能否举个例子。 [/B]
select * /*+ index(tb_a,col_1) */ from tb_a;
记不清楚了
认证徽章论坛徽章:168
select * /*+ index(&tablename& &indexname&gt */ from tb_a;
论坛徽章:2
最初由 husthxd 发布
[B]select * /*+ index(&tablename& &indexname&gt */ from tb_a; [/B]
谢谢这位老兄！
论坛徽章:1
在8i下可以用吗？
我在我的8i上试好像没效果
论坛徽章:36
Re: 在8i下可以用吗？
最初由 山翼 发布
[B]我在我的8i上试好像没效果 [/B]
论坛徽章:0
/*+index(table_name index_name)*/
itpub.net All Right Reserved. 北京皓辰网域网络信息技术有限公司版权所有　　　　
　北京市公安局海淀分局网监中心备案编号：　广播电视节目制作经营许可证：编号（京）字第1149号