Oracle中索引的心得体会

最新推荐文章于 2024-06-07 19:20:40 发布

我只想做第一

最新推荐文章于 2024-06-07 19:20:40 发布

阅读量431

点赞数

分类专栏： oracle 文章标签： Oracle 索引

oracle 专栏收录该内容

6 篇文章 3 订阅

订阅专栏

第一步：什么是索引及作用

数据库索引好比是一本书前面的目录，能加快数据库的查询速度。
索引的一个主要目的就是加快检索表中数据。

第二步：索引基本介绍

借鉴原文(https://blog.csdn.net/bibibrave/article/details/80876967)

具体如下：

类型图：

在这里插入图片描述

（1）唯一索引：

	唯一索引确保在定义索引的列中没有重复值 
	Oracle 自动在表的主键列上创建唯一索引 
	使用CREATE UNIQUE INDEX语句创建唯一索引

语法：create unique index index_name on table_name (column_name); 
具体列值： 索引相关列上的值必须唯一，但可以不限制NULL值。

（2）组合索引：

    组合索引是在表的多个列上创建的索引 
    索引中列的顺序是任意的 
    如果 SQL 语句的 WHERE 子句中引用了组合索引的所有列或大多数列，则可以提高检索速度

语法：create index index_name on table_name (column_name1，column_name2); 
具体列值：该表中的元组由两列共同确定一行，例如班级号 学号 唯一确定一个学生。

（3）反向键索引：

    反向键索引反转索引列键值的每个字节，为了实现索引的均匀分配，避免b树不平衡 
    通常建立在值是连续增长的列上，使数据均匀地分布在整个索引上 
    创建索引时使用REVERSE关键字

语法：create index index_name on table_name (column_name) reverse; 
具体列值： 适用于某列值前面相同，后几位不同的情况，例如 
sno：    1001 1002 1003 1004 1005 1006 1007 
索引转化：1001 2001 3001 4001 5001 6001 7001

（4）位图索引：

     位图索引适合创建在低基数列上 
     位图索引不直接存储ROWID，而是存储字节位到ROWID的映射 
     节省空间占用 
     如果索引列被经常更新的话，不适合建立位图索引 
     总体来说，位图索引适合于数据仓库中，不适合OLTP中

语法：create bitmap index index_name on table_name (column_name); 
具体列值： 不适用于经常更新的列，适用于条目多但取值类别少的列，例如性别列。

（5）基于函数的索引：

      基于一个或多个列上的函数或表达式创建的索引 
      表达式中不能出现聚合函数 
      不能在LOB类型的列上创建 
      创建时必须具有 QUERY REWRITE 权限

语法：create index index_name on table_name (函数（column_name)）; 
具体列值： 不能在LOB类型的列上创建，用户在该列上对该函数有经常性的要求。 
例如：用户不知道存储时候姓名是大写还是小写，使用 
select * from student where upper(sname)=’TOM’；

四、索引的变动，查询索引碎片（优化）

由于我们在对表的使用过程中，必然引发增删查改等操作，当表中的数据不存在，但其索引仍然存在，极大的影响了查询速度，降低了索引的利用率。
我们可以通过查看index_stats表中的pct_used列的值，如果pct_used的值过低，说明在索引中存在碎片，可以重建索引，来提高pct_used的值，减少索引中的碎片。
对索引碎片的查询，在该语句前后都要使用分析索引语句，已使得索引利用率发生改变。

分析：
alter index index_name validate structure;
查询碎片：
select name,pct_used from index_stats where name=‘index_name’;

当表中数据发生变化时，我们可以通过两种方式来对索引进行更新，一是通过删除该索引，再建立新的索引来提高索引的利用率。二是通过重建索引来提高索引利用率。

①删除索引 ： drop index index_name （同时也证明表和索引之间相互独立） 
            create index index_name on 表名（列名） tablespace tname；

②重建索引： alter index index_name rebuild REBUILD [ONLINE] [NOLOGGING] [COMPUTE STATISTICS];

其中：

ONLINE使得在重建索引过程中，用户可用对原来的索引进行修改，也就是其他的用户同时可以对表进行增删改操作；
NOLOGGING表示在重建过程中产生最少的重做条目redo Entry，加快重建的速度；
COMPUTE STATISTICS表示在重建过程中就生成了oracle优化器所需的统计信息，避免了索引重建之后再进行analyze或dbms_stats来收集统计信息。

第三步：注意事项

索引是建立在数据库表中的某些列的上面。在创建索引的时候，应该考虑到哪些列上可以创建索引，在哪些列上不能创建索引。一般来说，应该在这些列上创建索引：

1、在经常需要搜索的列上，可以加快搜索的速度；
2、在作为主键的列上，强制改列的唯一性和组织表中数据的排列结构；
3、在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；
4、在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
5、在经常使用在WHERE字句中的列上面创建索引，加快条件的判断速度。

同样，对于有些列不应该创建索引。一般来说，不应该创建索引的这些列具有下列特点：

第一，对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
第二，对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比列很大。增加索引，并不能明显加快检索速度。
第三，对于那些定义为text，image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少，不利于使用索引。
第四，当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能时相互矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改操作远远多于检索操作时，不应该创建索引。

第四步：索引失效的情况总结

1．隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误.

由于表的字段tu_mdn定义为varchar2(20),但在查询时把该字段作为number类型以where条件传给Oracle,这样会导致索引失效.

错误的例子：select * from test where tu_mdn=13333333333;

正确的例子：select * from test where tu_mdn=‘13333333333’;

2. 对索引列进行运算导致索引失效,我所指的对索引列进行运算包括(+，-，*，/，! 等)

错误的例子：select * from test where id-1=9;

正确的例子：select * from test where id=10;

3. 使用Oracle内部函数导致索引失效.对于这样情况应当创建基于函数的索引.

错误的例子：select * from test where round(id)=10; 说明，此时id的索引已经不起作用了

正确的例子：首先建立函数索引，create index test_id_fbi_idx on test(round(id));然后
select * from test where round(id)=10; 这时函数索引起作用了

4. 以下使用会使索引失效，应避免使用；

a. 使用 <> 、not in 、not exist、!=

b. like “%_” 百分号在前（可采用在建立索引时用reverse(columnName)这种方法处理，可参考
https://blog.csdn.net/lsx2017/article/details/84834973），like有两个%%需要使用全文索引

c. 单独引用复合索引里非第一位置的索引列.应总是使用索引的第一个列，如果索引是建立在多个列上,
只有在它的第一个列被where子句引用时，优化器才会选择使用该索引。

d. 字符型字段为数字时在where条件里不添加引号.

e. 当变量采用的是times变量，而表的字段采用的是date变量时.或相反情况。

5. 不要将空的变量值直接与比较运算符（符号）比较。

如果变量可能为空，应使用 IS NULL 或 IS NOT NULL 进行比较，或者使用 ISNULL 函数。

6. 不要在 SQL 代码中使用双引号。

因为字符常量使用单引号。如果没有必要限定对象名称，可以使用（非 ANSI SQL 标准）括号将名称括起来。

7. 将索引所在表空间和数据所在表空间分别设于不同的磁盘chunk上，有助于提高索引查询的效率。

8. Oracle默认使用的基于代价的SQL优化器（CBO）非常依赖于统计信息，一旦统计信息不正常，会导致数据库查询时不使用索引或使用错误的索引。

一般来说，Oracle的自动任务里面会包含更新统计信息的语句，但如果表数据发生了比较大的变化（超过20%）,可以考虑立即手动更新统计信息，例如：analyze table abc compute statistics，但注意，更新统计信息比较耗费系统资源，建议在系统空闲时执行。

9. Oracle在进行一次查询时，一般对一个表只会使用一个索引.

因此，有时候过多的索引可能导致Oracle使用错误的索引，降低查询效率。例如某表有索引1（Policyno）和索引2（classcode），如果查询条件为policyno
= ‘xx’ and classcode = ‘xx’，则系统有可能会使用索引2，相较于使用索引1，查询效率明显降低。

10. 优先且尽可能使用分区索引。

我只想做第一

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
Oracle中索引的心得体会

第一步：什么是索引及作用数据库索引好比是一本书前面的目录，能加快数据库的查询速度。索引的一个主要目的就是加快检索表中数据。第二步：索引基本介绍借鉴原文(https://blog.csdn.net/bibibrave/article/details/80876967)具体如下：类型图：（1）唯一索引：唯一索引确保在定义索引的列中没有重复值 Oracle 自动在表的主键列上...
复制链接

扫一扫