一、索引简介
索引分b-tree索引和位图索引，若基数大，重复值小，update比较多则用b-tree索引，反之，用位图索引。
查看索引与约束信息
SQL> col index_name for a10;
SQL> col index_type for a10;
SQL> col table_name for a10;
SQL> col tablespace_name for a20;
SQL> col column_name for a10;
SQL> col constraint_name for a10;
SQL> col constraint_type for a10;
SQL> col constraint_column for a10;
--查看索引信息
SQL> select index_name,index_type,table_name,tablespace_name from dba_indexes where owner='SCOTT';
INDEX_NAME INDEX_TYPE TABLE_NAME TABLESPACE_NAME
---------- ---------- ---------- --------------------
PK_EMP NORMAL EMP USERS
PK_DEPT NORMAL DEPT USERS
--查看索引列信息
SQL> select index_name,table_name,column_name from dba_ind_columns where index_owner='SCOTT';
INDEX_NAME TABLE_NAME COLUMN_NAM
---------- ---------- ----------
PK_DEPT DEPT DEPTNO
PK_EMP EMP EMPNO
-查看约束信息
SQL> select constraint_name,constraint_type,table_name from dba_constraints where owner='SCOTT';
CONSTRAINT CONSTRAINT TABLE_NAME
---------- ---------- ----------
PK_DEPT P DEPT
PK_EMP P EMP
FK_DEPTNO R EMP
--查看约束列信息
SQL> select constraint_name,table_name,column_name from dba_cons_columns where owner='SCOTT';
CONSTRAINT TABLE_NAME COLUMN_NAM
---------- ---------- ----------
PK_DEPT DEPT DEPTNO
PK_EMP EMP EMPNO
FK_DEPTNO EMP DEPTNO
二、索引创建与分析
实例：创建lxtb表，并添加相应索引
--创建lxtb表
SQL> create table lxtb(id number(8),gender varchar2(2),name varchar2(30));
Table created.
--随机插入男1，女0约20000人
SQL> declare
2 v_num number(2);
3 begin
4 for i in 1..20000 loop
5 v_num:=round(dbms_random.value(0,1),0);
6 if v_num>0 then
7 insert into lxtb values(i,'m','male'||i);
8 else
9 insert into lxtb values(i,'f','female'||i);
10 end if;
11 if mod(i,1000)=0 then
12 commit;
13 end if;
14 end loop;
15 commit;
16 end;
17 /
PL/SQL procedure successfully completed.
SQL> select count(1) from lxtb;
COUNT(1)
----------
20000
SQL> select * from lxtb where rownum<=10;
ID GE NAME
---------- -- ------------------------------
1 f female1
2 m male2
3 m male3
4 m male4
5 m male5
6 f female6
7 f female7
8 f female8
9 m male9
10 f female10
10 rows selected.
--先查看此表的索引信息
SQL> select index_name,index_type,table_name,tablespace_name,blevel,leaf_blocks
2 from dba_indexes
3 where table_name='LXTB';
no rows selected
--创建索引表空间（索引表空间与表所在表空间最好分开，这样可以减少io抢占利用，提高查询性能，另外索引表空间怕碎片，最好采用uniform size方式）
SQL> col name for a50;
SQL> select file#,name from v$datafile;
FILE# NAME
---------- --------------------------------------------------
1 /u01/app/oracle/oradata/PROD/disk3/system01.dbf
2 /u01/app/oracle/oradata/PROD/disk4/undotbs01.dbf
3 /u01/app/oracle/oradata/PROD/disk4/sysaux01.dbf
4 /u01/app/oracle/oradata/PROD/disk3/users01.dbf
5 /u01/app/oracle/oradata/PROD/disk3/lstbs01.dbf
6 /u01/app/oracle/oradata/PROD/disk3/lstbs02.dbf
7 /u01/app/oracle/oradata/PROD/disk3/lxtbs03.dbf
8 /u01/app/oracle/oradata/PROD/disk5/undotbs02.dbf
8 rows selected.
SQL> create tablespace indx datafile '/u01/app/oracle/oradata/PROD/disk4/indx01.dbf' size 50m
2 autoextend on next 10m maxsize unlimited uniform size 1m;
Tablespace created.
SQL> select tablespace_name,contents,status from dba_tablespaces;
TABLESPACE_NAME CONTENTS STATUS
------------------------------ --------- ---------
SYSTEM PERMANENT ONLINE
UNDOTBS UNDO ONLINE
SYSAUX PERMANENT ONLINE
TEMPTS TEMPORARY ONLINE
USERS PERMANENT ONLINE
TEMPTS2 TEMPORARY ONLINE
LXTBS1 PERMANENT ONLINE
LXTBS2 PERMANENT ONLINE
LXTBS3 PERMANENT ONLINE
UNDOTBS2 UNDO ONLINE
TEMPTS3 TEMPORARY ONLINE
TABLESPACE_NAME CONTENTS STATUS
------------------------------ --------- ---------
INDX PERMANENT ONLINE
--为lxtb表创建索引（系统默认为b-tree索引）
SQL> create index i_gender on lxtb(gender) tablespace indx;
Index created.
--分析索引二种方式（注意与分析表的区别 分析表为analyze table tbname computer statistics）
方式一：
SQL> analyze index i_gender validate structure;
Index analyzed.
方式二：
SQL> exec dbms_stats.gather_index_stats('sys','i_gender');
PL/SQL procedure successfully completed.
推荐用方式二进行分析，因为分析的更全面
--查看此索引信息
SQL> select index_name,index_type,table_name,tablespace_name,blevel,leaf_blocks from dba_indexes where table_name='LXTB';
INDEX_NAME INDEX_TYPE TABLE_NAME TABLESPACE_NAME BLEVEL LEAF_BLOCKS
---------- ---------- ---------- -------------------- ---------- -----------
I_GENDER NORMAL LXTB INDX 1 37
以上信息可知，此索引有二层结构（blevel(=1)+1），37个叶结点。
--删除此索引，创建位图索引
SQL> drop index i_gender;
Index dropped.
SQL> create
bitmap index
i_gender on lxtb(gender) tablespace indx;
Index created.
--分析索引并查看索引信息
SQL> analyze index i_gender validate structure;
Index analyzed.
SQL> select index_name,index_type,table_name,tablespace_name,blevel,leaf_blocks from dba_indexes where table_name='LXTB';
INDEX_NAME INDEX_TYPE TABLE_NAME TABLESPACE_NAME BLEVEL LEAF_BLOCKS
---------- ---------- ---------- -------------------- ---------- -----------
I_GENDER BITMAP LXTB INDX 0 1
以上查询结果可知，此索引只有一层（blevel=0），一个叶节点，类似于一个矩阵。
注意：在oltp系统中，绝对不用位图索引，因为位图索引维护复杂，dml操作时很慢，只有数据仓库系统才用位图索引。
三、索引管理（索引碎片整理、开启/关闭索引监控、重建/迁移索引）
索引碎片整理
SQL> alter index i_gender coalesce;
Index altered.
索引监控
--查看此索引监控信息
SQL> select * from v$object_usage where index_name='I_GENDER';
no rows selected
--打开此索引的监控
SQL> alter index i_gender monitoring usage;
Index altered.
--再次查看此索引监控信息
SQL> select * from v$object_usage where index_name='I_GENDER';
INDEX_NAME TABLE_NAME MON
USE
START_MONITORING END_MONITORING
---------- ---------- --- --- ------------------- -------------------
I_GENDER LXTB YES
NO
04/16/2014 23:02:27
注意：开启索引监控，通过上面的use列可得知此索引是否用过，要是一直以来都是no，若能确保程序无问题，此说明此索引建的不合理，从未使用过，可以删除。
--利用此索引，执行查询操作，使此索引利用上
SQL>select * from lxtb where gender='m';
--查看索引监控信息
SQL> select * from v$object_usage where index_name='I_GENDER';
INDEX_NAME TABLE_NAME MON
USE
START_MONITORING END_MONITORING
---------- ---------- --- --- ------------------- -------------------
I_GENDER LXTB YES
YES
04/16/2014 23:02:27
--关闭索引监控信息
SQL> alter index i_gender nomonitoring usage;
Index altered.
SQL> select * from v$object_usage where index_name='I_GENDER';
INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING
---------- ---------- --- --- ------------------- -------------------
I_GENDER LXTB NO YES 04/16/2014 23:02:27 04/16/2014 23:12:49
--rebuild迁移索引表空间（注意与move迁移表的区别）
SQL> select index_name,index_type,table_name,tablespace_name,blevel,leaf_blocks from dba_indexes where table_name='LXTB';
INDEX_NAME INDEX_TYPE TABLE_NAME TABLESPACE_NAME BLEVEL LEAF_BLOCKS
---------- ---------- ---------- -------------------- ---------- -----------
I_GENDER BITMAP LXTB
INDX
0 1
SQL> alter index i_gender rebuild tablespace system;
Index altered.
SQL> select index_name,index_type,table_name,tablespace_name,blevel,leaf_blocks from dba_indexes where table_name='LXTB';
INDEX_NAME INDEX_TYPE TABLE_NAME TABLESPACE_NAME BLEVEL LEAF_BLOCKS
---------- ---------- ---------- -------------------- ---------- -----------
I_GENDER BITMAP LXTB
SYSTEM
0 1
--迁移回来
SQL> alter index i_gender rebuild tablespace indx
nologging online;
Index altered.
注意：生产库系统，创建或重建/迁移（尤其重建）索引的时候，最好加上nologging online条件，避免索引占用资源，影响实际业务操作需要。下面说下rebuild索引时，有没online的区别：
rebuild:直接读取原索引的数据，因为不读表，所以为了一致，加锁禁止dml操作。因为扫索引，时间短。
rebuild online:全表扫描表中数据，不阻塞dml操作，用临时日志表同步，时间较长。
当我们对索引进行rebuild时，如果不加online选项，oracle则直接读取原索引的数据，当我们添加online选项时，oracle是直接扫描表中的数据，维护索引段数据的一致性就是从索引开始创建到索引创建完成这段时间的数据改变的同步。
四、其它类型索引（函数索引、反向索引、复合索引）
--无索引情况下，执行计划
SQL> set autotrace traceonly;
SQL> select * from lxtb where name='male9999';
Execution Plan
----------------------------------------------------------
Plan hash value: 3274939490
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 33 |
19 (
0)| 00:00:01 |
|* 1 |
TABLE ACCESS FULL
| LXTB | 1 | 33 | 19 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("NAME"='male9999')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
134 consistent gets
0 physical reads
0 redo size
527 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--创建索引，再查看此查询的执行计划
SQL> create index i_name on lxtb(name) tablespace indx;
SQL> select * from lxtb where name='male9999';
Execution Plan
----------------------------------------------------------
Plan hash value: 3908778746
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 33 |
2
(0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| LXTB | 1 | 33 |
2
(0)| 00:00:01 |
|* 2 |
INDEX RANGE SCAN
| I_NAME | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("NAME"='male9999')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
9 recursive calls
0 db block gets
73 consistent gets
1 physical reads
0 redo size
531 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--修改查询语句，索引列使用函数后，不走索引，代价更高
SQL> select * from lxtb where
upper(name)
='MALE9999';
Execution Plan
----------------------------------------------------------
Plan hash value: 3274939490
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 33 |
20
(5)| 00:00:01 |
|* 1 |
TABLE ACCESS FULL
| LXTB | 1 | 33 | 20 (5)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(UPPER("NAME")='MALE9999')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
134 consistent gets
0 physical reads
0 redo size
527 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
函数索引
--创建函数索引后，再查看查询条件为函数列的语句执行计划（执行计划虽然走了函数索引，但代价仍很高）
SQL> create index i_upper_name on lxtb(upper(name)) tablespace indx;
Index created.
SQL> select * from lxtb where upper(name)='MALE9999';
Execution Plan
----------------------------------------------------------
Plan hash value: 2231413184
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 33 |
17 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| LXTB | 1 | 33 | 17 (0)| 00:00:01 |
|* 2 |
INDEX RANGE SCAN
| I_UPPER_NAME | 80 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(UPPER("NAME")='MALE9999')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
28 recursive calls
0 db block gets
72 consistent gets
1 physical reads
0 redo size
527 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
由此可见，函数索引效率低于一般索引，能不用函数索引的情况，尽量不要使用，如果要函数处理，可以在入参值上，先做函数转换，这样最后查询语句中就可以不要使用函数转换，就能正常走索引，若实在无法做替换，就可再建函数索引。
反向索引
有的记录插入时键值是连接的，导致都写在连续的索引块上，导致索引块的分裂，索引树就会很深，用反向索引就会使索引树分散在树中。
反向键索引也有它的局限性，如果在where语句中，需要对索引列的值进行范围性的搜索，如between，>,、=的比较操作时，其反向索引才会得到使用。实际应用中一般不用反向索引。
--lxtb表中id列创建一般索引
SQL> create index i_id on lxtb(id) tablespace indx;
Index created.
SQL> select index_name,index_type,table_name,tablespace_name,blevel,leaf_blocks from dba_indexes where table_name='LXTB';
INDEX_NAME INDEX_TYPE TABLE_NAME TABLESPACE_NAME BLEVEL LEAF_BLOCKS
---------- ---------- ---------- -------------------- ---------- -----------
I_GENDER BITMAP LXTB INDX 0 1
I_NAME NORMAL LXTB INDX 1 60
I_UPPER_NA FUNCTION-B LXTB INDX 1 60
ME ASED NORMA
L
I_ID
NORMAL
LXTB INDX
1 44
SQL> drop index i_id;
Index dropped.
--lxtb表中id列创建反向索引
SQL> create index i_id on lxtb(id)
reverse tablespace indx;
Index created.
SQL> select index_name,index_type,table_name,tablespace_name,blevel,leaf_blocks from dba_indexes where table_name='LXTB';
INDEX_NAME INDEX_TYPE TABLE_NAME TABLESPACE_NAME BLEVEL LEAF_BLOCKS
---------- ---------- ---------- -------------------- ---------- -----------
I_GENDER BITMAP LXTB INDX 0 1
I_NAME NORMAL LXTB INDX 1 60
I_UPPER_NA FUNCTION-B LXTB INDX 1 60
ME ASED NORMA
L
I_ID
NORMAL/REV
LXTB INDX
1 44
复合索引
索引可以包含一个或多个列，多个列上的索引称为复合索引。复合索引的第一列称为前导列，讲前导性和选择性。建索引的时候优先考虑建复合索引，复合索引一般不超过三列。复合索引走的前提是第一列一定要出现，另外第一列要求刷选数据能力比较强，不要如性别之类字段，一般建时间或姓名之类字段。
如：create index i_com on lxtb(id,name) tablespace indx;
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21251711/viewspace-1148932/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/21251711/viewspace-1148932/
593
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
