-- Start

# 索引组织表(Index-Organized Tables)

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)

--位图索引
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)

-- 范围全局分区索引
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);

# 重建索引

ALTER INDEX ... REBUILD

# 有关索引的视图

all_indexes
all_ind_columns
all_ind_expressions
all_ind_partitions
all_ind_subpartitions
all_ind_statistics

-- 让 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

10-10 66
03-03 2万+
11-11 2167
12-30
07-11 3698
09-20 116
10-10 139
04-18 490
11-19 50
04-21
12-17 8252
06-06 2767
06-15 1万+