1.索引的概念

索引是用于加速数据存取的数据库对象。合理地使用索引可以大大降低I/O次数。

2、索引的功能：对记录进行排序，加快表的查询速度。

3、索引的分类：

（1）单列索引和复合索引

单列索引是基于单个列所建立的索引；复合索引时基于两列或多列所建立的索引。

SQL>CREATE INDEX emp_idx1 on emp( ename, job);

SQL>CREATE INDEX emp_idx2 on emp (job , ename);

可以在相同列上建立不同的索引，列顺序不一致即可

（2）惟一索引和非唯一性索引

惟一索引是索引列值不能重复的索引；非惟一索引时索引列值可以重复的索引。

无论是惟一索引还是非惟一索引，索引都允许null；

当定义主键约束或惟一约束时，Oracle会自动在相应的约束列上建立惟一索引。

（3）B-tree 索引和位图索引

（a） B-tree 索引（默认）

a、在一个大表上

b、建立在重复值比较少的列上，在做select查询时，返回记录的行数小于全部记录的4%

c、如果这一列经常用来做where子句和排序，也可以用来建立索引

d、一般用于OLTP

（b）bitmap 索引

a、建立在重复值非常高的列上

b、在做DML 操作时，代价值比较高

c、一般用于OLAP 或DSS

注意： B-tree 索引不能使用or连接的语句，bitmap index可以使用

4、建立索引：默认建立B-tree index

5、建立索引表空间

建议创建专门的索引表空间去存放索引，便于管理。

SQL> conn /as sysdba

Connected.

SQL> create tablespace indexes

2 datafile '/u01/app/oracle/oradata/anny/index01.dbf' size 100m

3 autoextend on next 10m maxsize 500m

4 extent management local uniform size 128k;

Tablespace created.

SQL> select file_id,file_name,tablespace_name from dba_data_files;

FILE_ID FILE_NAME TABLESPACE_NAME

---------- -------------------------------------------------- ---------------

5 /u01/app/oracle/oradata/anny/text01.dbf TEXT

4 /u01/app/oracle/oradata/anny/user01.dbf USERS

3 /u01/app/oracle/oradata/anny/sysaux01.dbf SYSAUX

2 /u01/app/oracle/oradata/anny/lx02.dbf LX02

1 /u01/app/oracle/oradata/anny/system01.dbf SYSTEM

6 /u01/app/oracle/oradata/anny/lx01.dbf LX01

7 /u01/app/oracle/oradata/anny/undotbs01.dbf UNDOTBS

8 /u01/app/oracle/oradata/anny/lx03.dbf LX03

9 /u01/app/oracle/oradata/anny/lx04.dbf LX04

10 /u01/app/oracle/oradata/anny/index01.dbf INDEXES

10 rows selected.

6、与索引有关的视图

DBA_INDEXES

DBA_IND_COLUMNS

V$OBJECT_USAGE

2.索引的创建语法：

CREATE UNIUQE | BITMAP INDEX .

ON .

( | ASC | DESC,

| ASC | DESC,...)

TABLESPACE

STORAGE

LOGGING | NOLOGGING

COMPUTE STATISTICS

NOCOMPRESS | COMPRESS

NOSORT | REVERSE

PARTITION | GLOBAL PARTITION

相关说明

1） UNIQUE | BITMAP：指定UNIQUE为唯一值索引，BITMAP为位图索引，省略为B-Tree索引。

2） | ASC | DESC：可以对多列进行联合索引，当为expression时即“基于函数的索引”

3）TABLESPACE：指定存放索引的表空间(索引和原表不在一个表空间时效率更高)

4）STORAGE：可进一步设置表空间的存储参数

5）LOGGING | NOLOGGING：是否对索引产生重做日志(对大表尽量使用NOLOGGING来减少占用空间并提高效率)

6）COMPUTE STATISTICS：创建新索引时收集统计信息

7）NOCOMPRESS | COMPRESS：是否使用“键压缩”(使用键压缩可以删除一个键列中出现的重复值)

8）NOSORT | REVERSE：NOSORT表示与表中相同的顺序创建索引，REVERSE表示相反顺序存储索引值

9）PARTITION | NOPARTITION：可以在分区表和未分区表上对创建的索引进行分区

2、索引特点：

第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

第二，可以大大加快数据的检索速度，这也是创建索引的最主要的原因。

第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

第四，在使用分组和排序子句进行数据检索时，同样可以显着减少查询中分组和排序的时间。

第五，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

3、索引不足：

第一，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。

第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

4、应该建索引列的特点：

1）在经常需要搜索的列上，可以加快搜索的速度；

2）在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构；

3）在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；

4）在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；

5）在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；

6）在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。

5、不应该建索引列的特点：

第一，对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。

第二，对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。

第三，对于那些定义为blob数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。

第四，当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

3.创建索引

1、建立b-tree索引

（1）相关概念

根块（一个）：索引顶级块，它包含指向下一级节点（分支块或叶块）的信息。

分支块：它包含指向下一级的节点（分支块或叶块）的信息。

叶块：它包含索引入口数据，索引入口包含索引列值或受限ROWID

（2）建立索引

如果在where子句中要经常引用某列或某几列，应该给予这些列值建立B-*树索引

10:23:58 SQL> create index ind_ename on scott.emp(ename) pctfree 30

10:24:32 2 tablespace indexes;

Index created.

（3）使用索引

10:24:41 SQL> set autotrace on explain

10:26:54 SQL> select * from scott.emp where ename='SCOTT';

EMPNO ENAME JOB HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ------------------- ---------- ---------- ----------

7788 SCOTT ANALYST 1987-04-19 00:00:00 3000 20

Execution Plan

----------------------------------------------------------

Plan hash value: 48385638

-----------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 29 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 29 | 2 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | IND_ENAME | 1 | | 1 (0)| 00:00:01 |

-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("ENAME"='SCOTT')

2、建立位图索引

位图索引以为位值标识索引数据，它主要用于在DSS(Decision Support System)系统中执行数据统计、数据汇总等操作。

B*-树索引建立在重复值很少的列上，而位图索引建立在重复值很多，不同值相对固定的列上。

使用位图索引可以节省大量的磁盘空间，为B*-树索引所用空间1/20~1/10.

建立位图索引时，Oracle会基于每个不同值建立一个位图。

（1）建立位图索引

SQL> create bitmap index test_sex_bitind on test(sex) tablespace indexes;

Index created.

（2）分析索引结构

SQL> analyze index test_sex_bitind validate structure;

Index analyzed.

SQL> select index_name,index_type,tablespace_name,blevel,leaf_blocks,num_rows from user_indexes where index_name='TEST_SEX_BITIND';

INDEX_NAME INDEX_TYPE TABLESPACE_NAME BLEVEL LEAF_BLOCKS NUM_ROWS

--------------- --------------- ---------------- ------- ----------- ----------

TEST_SEX_BITIND BITMAP INDEXES 0 1 2

——在重复值高的列上适合建立bitmap的索引

（3）使用位图索引

SQL> select name,sex from test where sex='F';

10000 rows selected.

Execution Plan

----------------------------------------------------------

Plan hash value: 2624764158

------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 10000 | 97K| 85 (0)| 00:00:02 |

| 1 | TABLE ACCESS BY INDEX ROWID | TEST | 10000 | 97K| 85 (0)| 00:00:02 |

| 2 | BITMAP CONVERSION TO ROWIDS| | | | | |

------------------------------------------------------------------------------------------------

4.修改索引

1、修改索引段存储参数

03:31:28 SQL> alter index scott.indx_ename deallocate unused;

Index altered.

03:31:36 SQL> alter index scott.indx_ename allocate extent(size 1m);

Index altered.

2、重建索引

03:32:48 SQL> alter index indx_ename rebuild;

Index altered.

3、联机重建索引

使用rebuild选项重建索引时，如果其他用户正在表上执行DML操作，那么重建索引将会失败，并显示错误信息“ORA-00054:资源正忙，但指定一NOWAIT方式获取资源”。为了最小化DML操作的影响，重建索引时，可以使用REBUILD ONLINE选项。示例如下：

ALTER INDEX department_dname REBUILD ONLINE;

4、合并索引

03:37:40 SQL> alter index indx_ename coalesce;

Index altered.

5、删除索引

03:37:40 SQL> drop index indx_ename;

6、验证索引

ANALYZE INDEX orders_region_id_idx VALIDATE STRUCTURE;

7、索引监控

SQL> conn scott/tiger

Connected.

SQL> alter index emp_ename_funind monitoring usage;

Index altered.

SQL> select index_name,table_name,monitoring,used from v$object_usage;

INDEX_NAME TABLE_NAME MONITORIN USED

------------------------------ --------------- --------- ---------

EMP_ENAME_FUNIND EMP YES NO

10:01:29 SQL> select * from emp where LOWER(ename)='scott';

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

SQL> select * from emp where lower(ename)='scott';

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- --------------- --------------- ---------- ------------------- ---------- ---------- ----------

7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20

SQL> select index_name,table_name,monitoring,used from v$object_usage;

INDEX_NAME TABLE_NAME MONITORIN USED

------------------------------ --------------- --------- ---------

EMP_ENAME_FUNIND EMP YES YES

5.重建索引

（1）检查需要重建的索引

根据以下几方面进行检查，确定需要重建的索引。

第一，查看SYSTEM表空间中的用户索引

为了避免数据字典的碎片出现，要尽量避免在SYSTEM表空间出现用户的表和索引。

select index_name from dba_indexes where tablespace_name="SYSTEM" and owner not in ("SYS","SYSTEM")

第二，确保用户的表和索引不在同一表空间内

表和索引对象的第一个规则是把表和索引分离。把表和相应的索引建立在不同的表空间中，最好在不同的磁盘上。这样可以避免在数据管理和查询时出现的许多I/O冲突。

SELECT i.owner "OWNER",

i.index_name "INDEX",

t.table_name "TABLE",

i.tablespace_name "TABLESPACE"

FROM dba_indexes i, dba_tables t

WHERE i.owner = t.owner

AND i.table_name = t.table_name

AND i.tablespace_name = t.tablespace_name

AND i.owner NOT IN ('SYS', 'SYSTEM')

第三，查看数据表空间里有哪些索引

用户的默认表空间应该不是SYSTEM表空间，而是数据表空间。在建立索引时，如果不指定相应的索引表空间名，那么，该索引就会建立在数据表空间中。这是程序员经常忽略的一个问题。应该在建索引时，明确的指明相应的索引表空间。

SELECT owner, segment_name, SUM (bytes)

FROM dba_segments

WHERE tablespace_name ='SYSTEM' AND segment_type = 'INDEX'

GROUP BY owner, segment_name

第四，查看哪个索引被扩展了超过10次

随着表记录的增加，相应的索引也要增加。如果一个索引的next extent值设置不合理（太小），索引段的扩展变得很频繁。索引的extent 太多，检索时的速度和效率就会降低。

（1）查看索引扩展次数

SELECT COUNT ( * ),

owner,

segment_name,

tablespace_name

FROM dba_extents

WHERE segment_type = 'INDEX' AND owner NOT IN ('SYS', 'SYSTEM')

GROUP BY owner, segment_name, tablespace_name

HAVING COUNT ( * ) > 10

ORDER BY COUNT ( * ) DESC

（2）找出需要重建的索引后，需要确定索引的大小，以设置合理的索引存储参数。

SELECT owner "OWNER",

segment_name "INDEX",

tablespace_name "TABLESPACE",

bytes "BYTES/COUNT",

SUM (bytes) "TOTAL BYTES",

ROUND (SUM (bytes) / (1024 * 1024), 0) "TOTAL M",

COUNT (bytes) "TOTAL COUNT"

FROM dba_extents

WHERE segment_type = 'INDEX'

AND segment_name IN ('INDEX_NAME1', 'INDEX_NAME2')

GROUP BY owner,

segment_name,

segment_type,

tablespace_name,

bytes

ORDER BY owner, segment_name

（3）确定索引表空间还有足够的剩余空间

确定要把索引重建到哪个索引表空间中。要保证相应的索引表空间有足够的剩余空间。

SELECT ROUND (bytes / (1024 * 1024), 2) "free(M)"

FROM sm$ts_free

WHERE tablespace_name = '表空间名'

（4）重建索引

重建索引时要注意以下几点：

a.如果不指定tablespace名，索引将建在用户的默认表空间。

b.如果不指定nologging，将会写日志，导致速度变慢。由于索引的重建没有恢复的必要，所以，可以不写日志。

c.如果出现资源忙，表明有进程正在使用该索引，等待一会再提交。

alter index 索引名 rebuild tablespace 索引表空间名 storage(initial 初始值 next 扩展值) nologging

（5）检查索引

对重建好的索引进行检查。

select * from dba_extents where segment_name="索引名"

（6）根据索引进行查询，检查索引是否有效

使用相应的where条件进行查询，确保使用该索引。看看使用索引后的效果如何。

select * from dba_ind_columns where index_name='索引名'

然后，根据相应的索引项进行查询。

select * from "表名" where ...

（7）找出有碎片的表空间，并收集其碎片。

重建索引后，原有的索引被删除，这样会造成表空间的碎片。

SELECT 'alter tablespace ' || tablespace_name || ' coalesce;'

FROM dba_free_space_coalesced

WHERE percent_blocks_coalesced != 100

查看索引占用空间大小：

select (sum(bytes)/1024/1024)||'MB' from dba_segments where segment_name = 'INDBILLLOG5_CALLEND';

查看表占用空间大小

select (sum(bytes)/1024/1024)||'MB' from dba_segments where segment_name = 'TBILLLOG5';

ITPub博客

oracle数据库逻辑对象管理——Oracle索引管理