Oracle 索引

原创 2015年07月07日 11:27:28

-- Start

不可否认,提高性能最直接有效的方式就是创建索引,正因为如此,好多人把它当做救命的良药,随意创建索引,殊不知维护索引的代价是非常大的。Oracle 官方给了一个大约的数字,维护一个索引所需要的代价大约是操作本身的 3 倍。另外,索引也有好多种类型,不同的索引适应的场景也不同。


索引组织表(Index-Organized Tables)

创建索引时,如何选择索引列呢?其实就是查询用到的列,包括 SELECT,WHERE,JOIN, ORDER 等用到的列,但是列的顺序是有讲究的,应该把那些重复值最少的列放在最前面。如果你创建一个索引包含一个表的所有列,那么你应该将该表创建为索引组织表(IOT),如果一个表的列比较少,这么做是可以的。对于列多的表,千万别这么干。因为索引组织表的记录存储在索引的叶子节点上,当我们向表中插入数据时,Oracle 为了维护索引需要移动数据,这会大大降低插入速度。下面是一个简单的例子。

CREATE TABLE test
(
  id    NUMBER(10),
  name  VARCHAR2(30),
  CONSTRAINT pk_test PRIMARY KEY (id)
) ORGANIZATION INDEX;


B 树索引(B-Tree Indexes)

B 树索引是默认的索引类型,特别适合主键,或重复值比较少的列或列的组合,如何判断重复值得多少呢?看看下面的公式吧,下面的值越高越好,主键是 1.0

SELECT COUNT(DISTINCT COLUMN) / COUNT(*) FROM TEST;

下面是一个简单的例子。

--普通索引
CREATE INDEX test_idx_name ON test (name);

--唯一索引
CREATE UNIQUE INDEX test_idx_id ON test (id);


位图索引(Bitmap Indexes)

位图索引和 B 树索引正好相反,非常适合重复值比较多的列,最好是只有几项,如:国籍,性别,省份等等,而且这些值基本上不会频繁更新。注意,频繁更新的列不适合位图索引,如,订单表有个列表示是否被处理,只有两个值,YES, NO。下面是一个简单的例子。

--位图索引
CREATE BITMAP INDEX test_idx_country ON test (country);
CREATE BITMAP INDEX test_idx_gender ON test (gender);
CREATE BITMAP INDEX test_idx_province ON test (province);

基于函数的索引(Function-based Indexes)

在索引字段上使用函数会使索引失效,有时候,我们可以通过把它转化为范围扫描来避免这个问题,但是,有时候,我们必须要使用函数,如:忽略大小写查询,这个时候,我们可以在创建基于函数的索引,如下是一个简单的例子。
CREATE INDEX test_idx ON test(UPPER(NAME));

分区索引(Partitioned Indexes)

分区索引的思想是将大的索引分成多个小索引,这样索引扫描时就可以减少IO。对于普通表,我们可以创建下面两种类型的分区索引。

-- 范围全局分区索引
CREATE INDEX test_idx ON test (amount)
GLOBAL PARTITION BY RANGE (amount)
(
	PARTITION p1 VALUES LESS THAN (1000),
	PARTITION p2 VALUES LESS THAN (2500),
	PARTITION p3 VALUES LESS THAN (MAXVALUE)
);

-- 哈希(散列)全局分区索引
CREATE INDEX cust_last_name_ix ON customers (cust_last_name)
GLOBAL PARTITION BY HASH (cust_last_name)
PARTITIONS 4;

对于分区表,除了可以创建上面两种类型的分区索引外,我们还可以给某个表分区或所有分区创建分区索引。

-- 创建分区表
CREATE TABLE test
(
        id number,  
        year number,  
        month number  
)
PARTITION BY RANGE (year)    
(
        PARTITION p1 VALUES LESS THAN (2013),   
        PARTITION p2 VALUES LESS THAN (2014),    
        PARTITION p3 VALUES LESS THAN (2015),    
        PARTITION p4 VALUES LESS THAN (3000)
);

-- 给所有表分区创建分区索引
CREATE INDEX test_idx ON test (year, month) LOCAL;

-- 给 p1分区创建分区索引
CREATE INDEX test_idx ON test (year, month) LOCAL (PARTITION p1);

反向索引(Reverse Key Indexes)



反向索引就是将正常的键值头尾调换后再进行存储,比如原值是'abc',将会以'cba'形式进行存储,为什么要这样做呢?原因是有些值是根据一定的规则生成的,如时间,序列等,当我们插入大量数据时,它们都需要同时插入到索引的某个区域,Oracle 称之为热点区域(hot spot),如果我们使用反向存储,可以有效的避免这个问题,但是有利就有弊,我们不能使用反向索引进行范围扫描,所以使用它要慎重。这里给我们一个非常重要的启示,在设计自动生成的值时,如果有可能,每两次生成的值范围要广。


复合索引

复合索引就是有多个列的索引,索引列的顺序很关键,如果索引包含 A,B,C 三列,而你的查询条件只包含 B,C 两列,Oracle 就无法使用索引。

重建索引

如果让你重建索引该怎么办呢?大多数人都会先删除索引,然后再创建新索引,其实通过下面的方式重建索引更快,因为 Oracle 可以利用现有的索引重建索引。

ALTER INDEX ... REBUILD

有关索引的视图

如果让你查询一下某个表都定义了哪些索引该怎么办呢?呵呵,很简单,只需查询一下下面的视图即可。

all_indexes
all_ind_columns
all_ind_expressions
all_ind_partitions
all_ind_subpartitions
all_ind_statistics

那如果让你查询一下某个索引是否被用到,该怎么办呢?首先,你需要让 Oracle 帮你监控一下索引,等过一段时间后,你可以查询下面的视图查看索引是否被用到。

-- 让 Oracle 监控索引
ALTER INDEX <indx name> MONITORING USAGE;

-- 查询是否被用到
select * from v$object_usage

--更多参见:Oracle SQL 优化精萃

-- 声明:转载请注明出处

-- Last edited on 2015-08-24

-- Created by ShangBo on 2015-07-07

-- End

Oracle索引技术

  • 2017年10月26日 08:56
  • 14.05MB
  • 下载

Oracle为sdo_geometry创建空间索引

  • 2016年02月03日 12:14
  • 14KB
  • 下载

oracle查询表 索引、唯一约束、字段等相关信息

查询表的索引情况 select t.Index_Name,t.table_name,t.column_name,i.tablespace_name,i.uniqueness from user_...
  • cuker919
  • cuker919
  • 2013年10月17日 17:14
  • 10868

ORACLE 索引技术

  • 2015年10月25日 00:21
  • 17.27MB
  • 下载

oracle索引

  • 2014年11月09日 10:29
  • 128KB
  • 下载

Oracle 唯一 约束(unique constraint) 与 索引(index) 关系说明

一. 官网对Unique Constraints说明http://download.oracle.com/docs/cd/E11882_01/server.112/e16508/datainte.ht...

oracle索引分析与比较

  • 2011年07月20日 13:39
  • 28KB
  • 下载

Oracle全文索引之四 维护

索引的维护对于CTXSYS.CONTEXT索引,当应用程序对基表进行DML操作后,对基表的索引维护是必须的。索引维护包括索引同步和索引优化。在索引建好后,我们可以在该用户下查到Oracle自动产生了以...

oracle的索引初步学习.doc

  • 2012年11月24日 08:26
  • 258KB
  • 下载
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Oracle 索引
举报原因:
原因补充:

(最多只允许输入30个字)