技术分享 | 独立思考

0%

oracle索引

发表于 更新于 分类于 ，
本文字数： 2.4k 阅读时长 ≈ 2 分钟

定义

  • 索引是以表相关的一个可选结构
  • 用以提高SQL语句执行的性能
  • 减少磁盘IO
  • 使用CREATE INDEX 语句创建索引
  • 在逻辑和物理上都独立于表数据
  • Oracle自动维护索引

分类

  • 创建标准索引
1
CREATE INDEX index1 on table_name(column1);
  • 查看索引
1
select * from user_indexes;
  • 查看索引信息
1
select * from user_ind_columns;
  • 分析索引：查看index_stats表中的pct_used列的值，如果pct_used的值过低，说明索引存在碎片，可以重建索引，提高pct_used值，减少碎片
1
analyze index <index_name> validate structure;

B(BLANCE)树索引

唯一索引

1
CREATE UNIQUE INDEX ind on table_name(c1);

组合索引

  • 如果经常发生where c1=xxx and c2=yyy
1
CREATE INDEX ind on table_name(c1,c2);

反向键索引

  • 反转索引列键值的每个字节
  • 通常建立在值是连续增长的列上，使数据均匀地分布在整个索引上
  • 创建索引时使用REVERSE关键字
    例如：有一列是0001 ，0002 ，0003 。。0010 ，反向索引变成1000,2000..反向列值存储
1
CREATE INDEX ind on table_name(c1) REVERSE;

基于函数索引

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

例如有些情况需要where upper(c1)=xxxx

位图索引

  • 适合创建在低基数列上
  • 位图索引不直接存储ROWID，而是存储字节位到ROWID的映射
  • 节省空间占用
  • 如果索引列被经常更新的话，不适合建立位图索引
  • 位图索引适合数据仓库，不适合OLTP
1
CREATE BITMAP INDEX ind on table_name(c1);

分析索引测试过程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
--创建测试表
CREATE TABLE test_index1 (c1 varchar2(8))

--插入130万数据
BEGIN
 FOR i IN 1..1300000 LOOP
  INSERT INTO TEST_INDEX1 values(ltrim(to_char(i,'00000009')));
  IF MOD (i,100)=0 THEN --提升性能
   COMMIT;
  END IF;
 END LOOP;
END;

--创建普通索引
CREATE INDEX ind_t ON test_index1(c1);

--分析索引
ANALYZE INDEX ind_t VALIDATE STRUCTURE;

-- 90%是初始值
SELECT a.pct_used,a.name FROM index_stats a WHERE a.name='IND_T';

1

1
2
3
4
5
6
7
8
9
--删掉单数行数据
DELETE test_index1 WHERE MOD (to_number(c1),2)=1;
COMMIT;

--重新分析索引
ANALYZE INDEX ind_t VALIDATE STRUCTURE;

--重建索引
ALTER INDEX ind_t rebuild;

删除索引

1
DROP INDEX ind_t;

索引分区

局部分区索引：在分区表上创建索引，在每个表分区上创建独立索引，索引的分区范围与表一致

1
2
3
4
5
6
7
8
9
10
11
--创建测试表
CREATE TABLE employee1(code NUMBER,name varchar2(10))
PARTITION BY RANGE (code)
(
 PARTITION p1 VALUES less than(1000),
 PARTITION p2 VALUES less than(2000),
 PARTITION p3 VALUES less than(MAXVALUE)
);

--建立局部分区索引
CREATE INDEX ind_1 ON employee1(code) LOCAL;

2

3

全局分区索引：在分区表或非分区表上创建索引，索引单独指定范围，与表的分区范围或是否分区无关

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
--创建测试表
CREATE TABLE employee2(code NUMBER,name varchar2(10))
PARTITION BY RANGE (code)
(
 PARTITION p1 VALUES less than(1000),
 PARTITION p2 VALUES less than(2000),
 PARTITION p3 VALUES less than(MAXVALUE)
);

--建立全局分区索引
CREATE INDEX ind_2 ON employee2(code)  
GLOBAL PARTITION BY RANGE (code)
(
 PARTITION p1 VALUES less than(2000),
 PARTITION p2 VALUES less than(MAXVALUE)
);

4

全局非分区索引：在分区表上创建的全局普通索引，索引没有被分区

1
2
3
4
5
6
7
8
9
10
11
--创建测试表
CREATE TABLE employee3(code NUMBER,name varchar2(10))
PARTITION BY RANGE (code)
(
 PARTITION p1 VALUES less than(1000),
 PARTITION p2 VALUES less than(2000),
 PARTITION p3 VALUES less than(MAXVALUE)
);

--建立全局非分区索引
CREATE INDEX ind_3 ON employee3(code) GLOBAL

并没有分区信息

5

获取索引信息

  • USER_INDEXES：用户创建的索引信息
  • USER_IND_PARTITIONS:用户创建的分区索引信息
  • USER_IND_COLUMNS:与索引相关的列表信息