查看: 14341|回复: 33
怎样创建函数索引？
论坛徽章:1
我的数据库中的表很大，建立了很多的索引，但还是不够用，我知道索引建立得太多也是不好的，我好像听所过函数索引，不知道到底该怎么建立？
还有，对一个表来说，建立多少索引效果才会受到影响。
论坛徽章:1
有一个表table_name，有col_name1,col_name2,现在对col_name1做upper函数索引
这样就行了
CREATE INDEX index_name
ON table_name(upper(col_name1));
论坛徽章:1
论坛徽章:4
接上面那位大哥的继续..
如果使用如下sql语句
select&&col_name1,col_name2 from
table_name where
upper(upper(col_name1) = :value
那么就可以用到刚刚对col_name1所建的函数索引了
使用跟正常的索引类似.
论坛徽章:26
最初由 ww_ww_ww 发布
[B]好像不会直接就会走函数索引，它是基于CBO方式的，如果是RULE方式就不会直接走，需要强指才行 [/B]
基于function 的索引是在8I时出现的，他是基于CBO的，你的表必须经过analyze才可以使用，或者使用hints才可以
论坛徽章:117
Have a simple test, you can understand it clearly:
SQL& connect scott/tiger
Connected.
SQL& CREATE INDEX upper_dept_name_idx
&&2&&ON dept(UPPER(dname));
Index created.
SQL& set autotrace on
SQL& SELECT *
&&2&&FROM dept
&&3&&WHERE UPPER(dname) = 'SALES';
& & DEPTNO DNAME& && && & LOC
---------- -------------- -------------
& && &&&30 SALES& && && & CHICAGO
Execution Plan
----------------------------------------------------------
& &0& && &SELECT STATEMENT Optimizer=CHOOSE
& &1& & 0& &TABLE ACCESS (FULL) OF 'DEPT'
SQL& analyze table dep
Table analyzed.
SQL& SELECT *
&&2&&FROM dept
&&3&&WHERE UPPER(dname) = 'SALES';
& & DEPTNO DNAME& && && & LOC
---------- -------------- -------------
& && &&&30 SALES& && && & CHICAGO
Execution Plan
----------------------------------------------------------
& &0& && &SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=18)
& &1& & 0& &TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (Cost=2 Card=1 Bytes=18)
& &2& & 1& &&&INDEX (RANGE SCAN) OF 'UPPER_DEPT_NAME_IDX' (NON-UNIQUE) (Cost=1 Card=1)
论坛徽章:1
那我在写程序代码的时候，每次要用到该索引之前，必须要对该表analyze一遍吗？
论坛徽章:4
eygle麻烦看一下，为什么我分析了表，却没有改变执行计划？
SQL& connect system/manager
Connected.
SQL& create table dept (deptno number, dname varchar2(10),loc varchar2(10));
表已创建。
SQL& insert into dept values(30, 'SALES', 'CHICAGO');
已创建 1 行。
SQL& CREATE INDEX upper_dept_name_idx
2 ON dept(UPPER(dname));
Index created.
SQL& set autotrace on
SQL& SELECT *
2 FROM dept
3 WHERE UPPER(dname) = 'SALES';
& & DEPTNO DNAME& && &LOC
---------- ---------- ----------
& && &&&30 SALES& && &CHICAGO
Execution Plan
----------------------------------------------------------
& &0& && &SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=14)
& &1& & 0& &TABLE ACCESS (FULL) OF 'DEPT' (Cost=1 Card=1 Bytes=14)
Statistics
----------------------------------------------------------
& && && & 0&&recursive calls
& && && & 4&&db block gets
& && && & 1&&consistent gets
& && && & 0&&physical reads
& && && & 0&&redo size
& && &&&480&&bytes sent via SQL*Net to client
& && &&&425&&bytes received via SQL*Net from client
& && && & 2&&SQL*Net roundtrips to/from client
& && && & 1&&sorts (memory)
& && && & 0&&sorts (disk)
& && && & 1&&rows processed
SQL& analyze table dep
Table analyzed.
SQL& SELECT *
2 FROM dept
3 WHERE UPPER(dname) = 'SALES';
& & DEPTNO DNAME& && &LOC
---------- ---------- ----------
& && &&&30 SALES& && &CHICAGO
Execution Plan
----------------------------------------------------------
& &0& && &SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=14)
& &1& & 0& &TABLE ACCESS (FULL) OF 'DEPT' (Cost=1 Card=1 Bytes=14)
Statistics
----------------------------------------------------------
& && && & 0&&recursive calls
& && && & 4&&db block gets
& && && & 1&&consistent gets
& && && & 0&&physical reads
& && && & 0&&redo size
& && &&&480&&bytes sent via SQL*Net to client
& && &&&425&&bytes received via SQL*Net from client
& && && & 2&&SQL*Net roundtrips to/from client
& && && & 1&&sorts (memory)
& && && & 0&&sorts (disk)
& && && & 1&&rows processed
论坛徽章:117
没错，应该用全表扫描
你只有一条记录全表扫描当然优于索引扫描！这就是CBO的智能之处！
论坛徽章:117
最初由 hosia 发布
[B]那我在写程序代码的时候，每次要用到该索引之前，必须要对该表analyze一遍吗？ [/B]
不需要每次都分析表，只要存在适当的分析数据，CBO就能选择较为优化的执行计划，但是如果分析数据不及时，CBO往往会选择错误的执行计划。
所以DBA的职责之一就是定期Aanalyze 表
itpub.net All Right Reserved. 北京皓辰网域网络信息技术有限公司版权所有　　　　
　北京市公安局海淀分局网监中心备案编号：　广播电视节目制作经营许可证：编号（京）字第1149号Oracle（7）
基于函数的索引除了对使用内置函数（如UPPER、LOWER等）的查询显然有帮助外、还可以用来有选择的只对表中的某些行建立索引。
从这里可以下载big_table.sql文件。
如不方便下载，可以直接从这里copy.
create table big_table
select rownum id, a.*
from all_objects a
alter table big_
l_rows number := &1;
insert /*+ append */
into big_table
select rownum, a.*
from all_objects a
where rownum &= &1;
l_cnt := sql%
while (l_cnt & l_rows)
insert /*+ APPEND */ into big_table
select rownum+l_cnt,
OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID,
OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS,
TEMPORARY, GENERATED, SECONDARY, NAMESPACE, EDITION_NAME
from big_table
where rownum &= l_rows-l_
l_cnt := l_cnt + sql%
alter table big_table add constraint
big_table_pk primary key(id);
exec dbms_stats.gather_table_stats( user,
进入文件所在的目录，执行如下命令，创建一个行的表。
F:\BaiduYunDownload&sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on 星期日 9月 25 17:36:10 2016
Copyright (c) 1982, 2010, Oracle.
All rights reserved.
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL& @big_table.sql 1000000
表已创建。
表已更改。
l_rows number := &1;
l_rows number := 1000000;
where rownum &= &1;
where rownum &= 1000000;
PL/SQL 过程已成功完成。
表已更改。
PL/SQL 过程已成功完成。
更新temporary列，在此将Y变成N，以及把N变为Y：
SQL& update big_table set temporary = decode(temporary,'N','Y','N');
已更新1000000行。
提交完成。
检查Y与N的比例
SQL& select temporary , cnt,
round((ratio_to_report(cnt) over())*100,2) rtr
select temporary, count(*) cnt
from big_table
group by temporary
- ---------- ----------
如果对temporary使用传统索引，会发现索引有1 000 000个条目，占用差不多14MB的空间，其高度为3：
SQL& create index processed_flag_idx
on big_table(temporary);
索引已创建。
SQL& analyze index processed_flag_idx
索引已分析
SQL& select name, btree_space, lf_rows, height
from index_
BTREE_SPACE
------------------------------ ----------- ---------- ----------
PROCESSED_FLAG_IDX
通过这个索引获取任何数据都会带来3个I/O才能到达子叶块。这个索引不仅很宽，还很高。要得到第一个未处理的记录，必须至少执行4个I/O（其中3个是对索引的I/O，另外一个是对表的I/O）。
怎么能让索引更小呢？而且更新期间开销更小呢？
采用基于函数的索引，我们可以编写一个函数，如果不想对某个给定行家索引，则这个函数就返回NULL；而对想加索引的行则返回一个非NULL值。（B*树索引对于完全为NULL的键没有对应的条目）
例如，我们只对列值为N的记录感兴趣，所以只对这些记录加索引：
SQL& drop index processed_flag_
索引已删除。
SQL& create index processed_flag_idx
on big_table( case temporary when 'N' then 'N' end);
索引已创建。
SQL& analyze index processed_flag_idx
索引已分析
SQL& select name, btree_space, lf_rows, height
from index_
BTREE_SPACE
------------------------------ ----------- ---------- ----------
PROCESSED_FLAG_IDX
这就有很大不同了，这个索引只有48K大小，而不是14MB。高度也有所降低，与先前那个更高的索引相比，使用这个索引能少执行一个I/O。
下面进行测试，通过使用函数索引进行查询。
注意：函数索引，必须使用函数表达式，才能通过索引访问。如(case temporary when ‘N’ then ‘N’ end)=’N’.（中间的打印记录省略了）
（关于autotrace，）
SQL& select * from big_table where (case temporary when
'N' then 'N' end)='N';
00: 00: 19.98
Plan hash value:
--------------------------------------------------------------------------------------------------
| Operation
| Bytes | Cost(%CPU)| Time
--------------------------------------------------------------------------------------------------
0 | SELECT STATEMENT
(0)| 00:00:07 |
TABLE ACCESS BY INDEX ROWID| BIG_TABLE
(0)| 00:00:07 |
INDEX RANGE SCAN
| PROCESSED_FLAG_IDX |
(0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(CASE "TEMPORARY" WHEN 'N' THEN 'N' END ='N')
recursive calls
db block gets
consistent gets
physical reads
bytes sent via SQL*Net to client
bytes received via SQL*Net from client
SQL*Net roundtrips to/from client
sorts (memory)
sorts (disk)
rows processed
可以看到，I/O总共842次。
如果没有使用函数索引访问呢？
可以看到总I/O增加，共计15091
SQL& select * from big_table where temporary='N';
00: 00: 17.64
Plan hash value:
-------------------------------------------------------------------------------
| Operation
| Bytes | Cost (%CPU)| Time
-------------------------------------------------------------------------------
0 | SELECT STATEMENT
(1)| 00:00:49 |
1 - filter("TEMPORARY"='N')
recursive calls
db block gets
consistent gets
physical reads
bytes sent via SQL*Net to client
bytes received via SQL*Net from client
SQL*Net roundtrips to/from client
sorts (memory)
sorts (disk)
rows processed
关于cost和执行时间，
本文参考《Oracle_Database_9i10g11g编程艺术深入数据库体系结构》第2版，Thomas Kyte著，苏金国 王小振等译，422~424页
参考知识库
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：8316次
排名：千里之外
原创：28篇
(3)(3)(1)(6)(4)(1)(2)(2)(8)(1)1577人阅读
Oracle索引（30）
使用Oracle函数索引 提高查询效率
10:55 佚名 互联网&&字号：&|&
想要提高Oracle数据库的查询检索的效率，使用函数索引无疑是一个非常好的方法。下文对Oracle函数索引的使用作了详尽的阐述，供您参考。
使用Oracle函数索引，无疑是提高查询效率的有效方法之一。下面就为您详细介绍函数索引的使用方法，希望对您能有所帮助。
谈到任何对列的操作都可能导致全表扫描，例如：
select&*&from&emp&where&substr(ename,1,2)=’SM’;&
但是这种查询在客服系统又经常使用，我们可以创建一个带有substr函数的基于Oracle函数索引，
create&index&emp_ename_substr&on&eemp&(&substr(ename,1,2)&);&
但是这种查询在客服系统又经常使用，我们可以创建一个带有substr函数的Oracle函数索引，
create&index&emp_ename_substr&on&eemp&(&substr(ename,1,2)&);&
这样在执行上面的查询语句时，这个基于函数的索引将排上用场，执行计划将是（INDEX RANGE SCAN）。
上面的例子中，我们创建了基于函数的索引，但是如果执行下面的查询：
select&*&from&emp&where&substr(ename,1,1)=’S’&
得到的执行计划将还是（TABLE ACCESS FULL），因为只有当数据列能够等式匹配时，基于函数的索引才能生效，这样对于这种索引的计划和维护的要求都很高。请注意，向表中添加索引是非常危险的操作，因为这将导致许多查询执行计划的变更。然而，如果我们使用基于函数的索引就不会产生这样的问题，因为Oracle只有在查询使用了匹配的内置函数时才会使用这种类型的索引。
=======================================================================
oracle函数索引
如果一个函数（function）或表达式（expression）使用了一个表的一列或多
列，则用户可以依据这些函数或表达式为表建立索引，这样的索引被称为函
数索引（Function-Based Index）。函数索引能够计算出函数或表达式的值，
并将其保存在索引中。用户创建的函数索引既可以是平衡树类型（B-tree
index）的，也可以是位图类型（bitmap index）的。
用于创建索引的函数可以是一个数学表达式（arithmetic expression），也可以
是使用了 PL/SQL 函数（PL/SQL function），包函数（package function），C
外部调用（C callout），或
函数（SQL function）的表达式。用于创建索
引的函数不能包含任何聚合函数（ggregate function），如果为用户自定义函
数，则在声明中必须使用 DETERMINISTIC 关键字。例如，
create&or&replace&function&f_david(p_value&varchar2)&return&varchar2&&&deterministic&is&&&begin&&&return&p_&&& &
如果在一个使用对象类型（object type）的列上建立函数索引，则可以使用此对象的方法（method）
作为函数，例如此对象的 map 方法。用户不能在数据类型为 LOB，REF，或嵌套表（nested table）的列上
建立函数索引，也不能在包含 LOB，REF，或嵌
套表等数据类型的对象类型列上建立函数索引。
使用函数索引
如果一个 SQL 语句的 WHERE 子句中使用了函数，那么建立相应的函数索引
（function-based index）是提高数据访问性能的有效机制。表达式
（expression）的结果经过计算后将被存储在索引中。但是当执行 INSERT 和
UPDATE 语句时，Oracle 需要进行函数运算以便维护索引。
例如，如果用户创建了以下函数索引：
CREATE INDEX idx ON table_1 (a + b * (c - 1), a, b);
当 Oracle 处理如下查询时就可以使用之前建立的索引：
SELECT a FROM table_1 WHERE a + b * (c - 1) & 100;
使用 UPPER(column_name) 或 LOWER(column_name) 函数建立函数索引
（function-based index）有助于与大小写无关（case-insensitive）的查询。例如
创建以下函数索引：
CREATE INDEX uppercase_idx ON employees (UPPER
(first_name));
有助于提高以下查询的性能：
SELECT * FROM employees WHERE UPPER(first_name) =
'RICHARD';
函数索引的依赖性
函数索引（function-based index）依赖于索引定义表达式中使用的函数。如果
此函数为 PL/SQL 函数（PL/SQL function）或包函数（package function），当
函数声明（function specification）发生变化时，索引将失效（disabled）。
用户需要被授予（grant）CREATE INDEX 或 CREATE ANY INDEX 权限才能
创建函数索引（function-based index）。
要想使用函数索引（function-based index）：
● 建立索引后，表必须经过分析（analyze）。
● 必须保证查询的条件表达式不是 NULL 值, 因为 NULL 值不会被存储到
以下各节将讲述使用函数索引的其他需求。
定义函数的权限
函数索引（function-based index）的所有者（owner）必须具备此索引定义中
使用的函数的 EXECUTE 权限。当 EXECUTE 权限被收回（revoke）后，
Oracle 则将索引标识为 DISABLED。索引的所有者无须具备此函数的
EXECUTE WITH GRANT OPTION 权限，即可将索引所在表的 SELECT 权限
授予（grant）其他用户。
解决函数索引的依赖性问题
函数索引（function-based index）依赖于她使用的所有函数。如果函数或函数
所在包的声明（specification）被修改过（或索引所有者对函数的 EXECUTE
权限被收回)，将会出现以下情况：
● 索引被标记为 DISABLED。
● 如果优化器（optimizer）选择了在标记为 DISABLED 的索引上执行查
询，那么此查询将失败
● 使用标记为 DISABLED 的索引而执行的 DML 操作将失败，除非此索引
同时被标记为 UNUSABLE 且初始化参数（initialization parameter）
SKIP_UNUSABLE_INDEXES 被设为 TRUE。
函数被修改之后，用户可以使用 ALTER INDEX ... ENABLE 语句将索引重新置
为 ENABLE 状态。
参考知识库
* 以上用户言论只代表其个人观点，不代表CSDN网站的观点或立场
访问：2655675次
积分：25888
积分：25888
排名：第185名
原创：355篇
转载：1189篇
评论：63条
(2)(1)(12)(3)(25)(37)(9)(32)(19)(7)(5)(11)(26)(37)(29)(76)(26)(19)(41)(63)(76)(32)(33)(88)(41)(16)(25)(37)(73)(63)(92)(75)(120)(159)(55)(67)(13)(1)oracle普通索引介绍(单字段索引) - NaOH - ITeye技术网站
博客分类：
oracle复合索引介绍(多字段索引) :
(1).门牌号码:ROWID
欲介绍索引,先介绍一个概念:ROWID.在数据库中,每条记录都有自己的物理地址,叫做ROWID,包括所属的数据文件号,数据块号,以及在该数据块中的具体位置信息等.ROWID就相当于一个门牌号码,也相当于一本书的页码.
(2).索引就是目录:
索引就是一本书的目录.索引包括被索引的字段值和所对应的ROWID,分别相当于目录中的书条目和对应的页码.
20/80规则: 80%的性能问题可以由20%的优化技术所解决.
错误的SQL语句会导致索引的不执行.如下:
to_char(DJ_SZ.JDRQ,'yyyy-mm-dd') between '' and ''
-- 即便在DJ_SZ.JDRQ字段建立了索引,该索引仍然无法启动,依然是全表扫描.因为索引树中记录的是DJ_SZ.JDRQ字段值,而不是to_char(DJ_SZ.JDRQ,'yyyy-mm-dd')数值.因此DJ_SZ.JDRQ索引无法使用.上述语句片段应该修改成:
DJ_SZ.JDRQ between to_date('','yyyy-mm-dd') and to_date('','yyyy-mm-dd')
开发规范之一 : 不要轻易在字段前增加函数.
-- oracle其实挺笨的,如果这样写:
DJ_NSRZT_BG.YXQ_Z + 7 & sysdate
-- oracle 也不会启动YXQ_Z字段上的索引,必须修改成这样:
DJ_NSRZT_BG.YXQ_Z & sysdate - 7 -- 原因同上
开发规范之二 : 尽量不要将字段嵌入表达式之中.
针对前面问题,为什么不可以直接使用oracle函数索引呢?例如:
create index idx_1 on DZ_SJ(to_char(JDRQ,'yyyy-mm-dd'));
的确,只要数据库运行在基于成本的优化器(CBO)模式下,并且将参数query_rewrite_enabled设置为true,就可以启动函数索引.而且不需要进行复杂的语句转换,提高语句的可读性.但本人建议,尽量少使用函数索引,而将函数进行转换.原因如下:
(1).函数索引是需要维护的.当数据库每次进行该表的DML操作时,oracle都需要维护函数索引,也就是说需要进行一次计算,维护成本将高于普通索引.
(2).函数索引的计算值可能大于原字段值,将消耗更多的索引存储空间.
ysj5125094
浏览: 627511 次
来自: 沈阳
Java实现导入导出excel文件，利用poi实现感觉代码比较 ...
部署之后启动有问题org.springframework.be ...
谢谢分享。部署之后启动有问题org.springframewo ...
beancopier只实现了浅拷贝的功能，如果要实现深拷贝，则 ...