Oracle实现基于函数的索引

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了Oracle实现基于函数的索引，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含3885字，纯文字阅读大概需要6分钟。

内容图文

当一个查询运行很慢。通过检查where子句，发现其中的一列应用了sql lower函数，lower函数阻止使用该列上现有的索引。你想要创建一

使用场景：
当一个查询运行很慢。通过检查where子句，发现其中的一列应用了sql lower函数，lower函数阻止使用该列上现有的索引。你想要创建一个基于函数索引来支持这个查询，如下

SQL> select index_name,column_name from user_ind_columns where table_name='T1';

INDEX_NAME COLUMN_NAME
------------------------- ------------------------------
T1_PK OBJECT_ID

SQL> set autotrace trace explain;
SQL> select * from t1 where lower(object_name)='i_undo1';

执行计划
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 908 | 101K| 436 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 908 | 101K| 436 (1)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(LOWER("OBJECT_NAME")='i_undo1')
从以上可以看出即使该表中有索引也没有使用。

解决方案
1、创建一个基于函数的索引
2、如果使用Oracle database 11g或更高版本，创建一个索引虚拟列

下面实现基于函数的索引

创建索引可以通过以下方式来估计索引所使用空间以及所需要分配的空间

SQL> set serveroutput on
SQL> var used_bytes number;
SQL> var allo_bytes number;
SQL> exec dbms_space.create_index_cost('create index t1_object_name on t1(lower(object_name))',:used_bytes,:allo_bytes);

PL/SQL procedure successfully completed
used_bytes
---------
2269350
allo_bytes
---------
4194304

SQL>create index idx_lower on t1(lower(object_name)) tablespace index_nocompress;

SQL> select * from t1 where lower(object_name)='i_undo1';

执行计划
----------------------------------------------------------
Plan hash value: 2274688371

-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 908 | 101K| 193 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 908 | 101K| 193 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_LOWER | 363 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access(LOWER("OBJECT_NAME")='i_undo1')

注意：不能直接修改一个创建了基于函数索引的列。需要先删除索引，，然后修改列，最后再重建索引。不然会报ora-30556错误(在要修改的列上已定义函数索引或位图联接索引)

查看基于函数的索引定义dba/all/user_ind_expressions

SQL> select index_name,column_expression from user_ind_expressions;

INDEX_NAME COLUMN_EXPRESSION
------------------------- --------------------------------------------------
IDX_LOWER LOWER("OBJECT_NAME")

接着实现在虚拟列创建一个索引

使用场景

现在正使用一个基于函数的索引，但想要获得更好的性能，想将基于函数的索引替换为一个虚拟列，然后在虚拟列上创建索引（需要11g环境或更高版本）。

SQL>alter table t1 add(lower_object_name generated always as (lower(object_name)) virtual);
SQL>create index idx_lower on t1(lower_object_name) tablespace index_nocompress;

由Oracle索引来理解ArcSDE索引

Oracle索引技术之如何建立最佳索引

Oracle索引列NULL值引发执行计划该表的测试示例

Oracle索引 主键影响查询速度

Oracle索引扫描

本文永久更新链接地址：

内容总结

以上是互联网集市为您收集整理的Oracle实现基于函数的索引全部内容，希望文章能够帮你解决Oracle实现基于函数的索引所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

---