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 子程序内联

-- Start 如果子程序 A 调用 B,内联可以把 B 的代码合并到 A 中,从而减少子程序调用,提高性能,下面是一个简单的例子。 -- 子程序 A PROCEDURE A IS BEG...

Oracle 分开执行耗时操作

-- Start 首先,我们看一个故事,联合利华引进了一条香皂包装生产线,结果发现这条生产线有个缺陷:常常会有盒子里没装入香皂。总不能把空盒子卖给顾客啊,他们只得请了一个学自动化的博士后设计一个方案...

我是如何成为一名python大咖的?

人生苦短,都说必须python,那么我分享下我是如何从小白成为Python资深开发者的吧。2014年我大学刚毕业..

Oracle 查看执行计划

-- Start 我们可以通过 EXPLAIN PLAN 语句生成执行计划,该语句把执行计划保存到一个叫做 PLAN_TABLE 的表中,我们可以通过查询这个表来查看执行计划。下面是一个简单例子。 ...

Oracle 如何写出高效的 SQL

-- Start 要想写出高效的SQL 语句需要掌握一些基本原则,如果你违反了这些原则,一般情况下SQL 的性能将会很差。 1. 减少数据库访问次数 连接数据库是非常耗时的,虽然应用程序会采...

Oracle 查询阻塞

-- Start 如果你的 SQL 或系统突然 hang 了,很有可能是因为一个 session 阻塞了另一个 session,如何查询是否发生阻塞了呢?看看下面的 SQL吧。 select ...

Oracle 面向对象

-- Start 我们都知道,传统数据库都是关系型数据库,随着 Java 和 面向对象的流行,Oracle也与时俱进,加入了面向对象的特性,最典型的就是嵌套表,嵌套表使查询变得复杂,同时它的...

Oracle 视图

-- Start 视图有好多优点,如它可以简化开发。但是有一点特别需要注意,最好不要使用多个视图做联合查询,因为优化器将很难优化这样的查询。 --更多参见:Oracle SQL 优化精...

Oracle 查看收集统计信息

-- Start 统计信息相当于情报,对 Oracle 至关重要,如果统计信息不准确,Oracle 就会做出错误的判断。通常,Oracle 会在每天固定时间段自动维护统计信息。但是对于某些表,这是远远...

Oracle 减少数据库访问次数

-- Start 连接数据库是非常耗时的,虽然应用程序会采用连接池技术,但与数据库交互依然很耗时,这就要求我们尽量用一条语句干完所有的事,尤其要避免把 SQL 语句写在循环中,如果你遇到这样的人...

Oracle SQL 优化精萃

-- Start -- 更多参见:Oracle  精萃 -- 声明:转载请注明出处 -- Last edited on 2015-06-29 -- Created by ShangBo o...
返回顶部
收藏助手
不良信息举报
您举报文章:深度学习:神经网络中的前向传播和反向传播算法推导
举报原因:
原因补充:

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