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 添加索引

1.说明   1)索引是数据库对象之一,用于加快数据的检索,类似于书籍的索引。在数据库中索引可以减少数据库程序查询结果时需要读取的数据量,类似于在书籍中我们利用索引可以不用翻阅整本书即可找到想要...
  • james570
  • james570
  • 2017年10月25日 13:53
  • 603

Oracle 索引的分类,分析与比较....(转载)

逻辑上: Single column 单行索引 Concatenated 多行索引 Unique 唯一索引 NonUnique 非唯一索引 Function-based函数索引 Domai...
  • feng1211111
  • feng1211111
  • 2017年01月16日 21:45
  • 994

oracle的索引类型及sql优化总结(一)

从大的方面来说,一般从业务上和技术上(oracle索引)来优化。 本文重点总结下oracle技术层面的优化: 索引类型的选择,索引列的选择,表之间的联结类型对于实现最优性能有着很高的重要性。 从算法维...
  • abasen
  • abasen
  • 2016年03月13日 16:13
  • 1004

在Oracle中索引的使用

索引是由Oracle维护的可选结构,为数据提供快速的访问。准确地判断在什么地方需要使用索引是困难的,使用索引有利于调节检索速度。 当建立一个索引时,必须指定用于跟踪的表名以及一个或多个表列。一旦建立了...
  • ysyn1209
  • ysyn1209
  • 2016年04月22日 23:44
  • 7244

Oracle索引类型-参考

Oracle提供了大量索引选项。知道在给定条件下使用哪个选项对于一个应用程序的性能来说非常重要。一个错误的选择可能会引发死锁,并导致数据库性能急剧下降或进程终止。而如果做出正确的选择,则可以合理使用资...
  • htofly
  • htofly
  • 2014年04月30日 17:05
  • 9844

oracle创建使用索引,查询索引状态

SQL> conn tudou/111; //插入测试数据 SQL> insert into temp1 values('z壮','23'); SQL> insert into temp...
  • yaerfeng
  • yaerfeng
  • 2011年12月06日 00:01
  • 7805

oracle索引(函数索引,以时间区分每天)

不是啊,建太多会影响性能的,维护成本也很大 一般在关键字段上建立 例如 where 后面有很多字段,其中有 logtime 字段,你在这个字段上建立索引, 查询是根据 logtime 字...
  • wanglilin
  • wanglilin
  • 2012年02月02日 11:24
  • 3869

oracle索引碎片

oracle索引碎片
  • wyzxg
  • wyzxg
  • 2010年05月31日 18:48
  • 9524

oracle 禁用索引

同步数据的时候 有索引会比较慢 可以暂时禁用索引 --禁用索引 ALTER INDEX PK_T_AUTH_USERROLE_ID UNUSABLE; --恢复索引 ALTER IND...
  • yanhui007
  • yanhui007
  • 2017年06月13日 11:30
  • 1331

oracle数据库建立索引的原则

数据库建立索引的原则 1,确定针对该表的操作是大量的查询操作还是大量的增删改操作。 2,尝试建立索引来帮助特定的查询。检查自己的sql语句,为那些频繁在where子句中出现的字段建立索引。 3,尝...
  • zbyluxzing
  • zbyluxzing
  • 2016年08月08日 23:16
  • 2857
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Oracle 索引
举报原因:
原因补充:

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