第十三章： 索引
1、索引（Index）的功能：对记录进行排序，加快表的查询速度
2、索引的分类：
1） B-tree 索引（默认）
a、在一个大表上
b、建立在重复值比较少的列上 ，在做select查询时，返回记录的行数小于全部记录的4%
c、如果这一列经常用来做where子句和排序，也可以用来建立索引
d、一般用于OLTP
2） bitmap index
a、建立在重复值非常高的列上
b、 在做DML 操作时，代价值比较高
c、一般用于OLAP 或DSS
3、建立索引：默认建立B-tree index
4、建立索引表空间：
09:41:12 SQL> create tablespace indexes
09:41:18 2 datafile '/u01/app/oracle/oradata/prod/index01.dbf' size 10m
09:41:32 3 autoextend on next 10m maxsize 500m
09:41:44 4 extent management local uniform size 128k;
Tablespace created.
09:42:12 SQL> select file_id,file_name,tablespace_name from dba_data_files;
FILE_ID FILE_NAME TABLESPACE_NAME
---------- -------------------------------------------------- ------------------------------
4 /u01/app/oracle/oradata/prod/users01.dbf USERS
3 /u01/app/oracle/oradata/prod/sysaux01.dbf SYSAUX
2 /u01/app/oracle/oradata/users01.dbf USER01
1 /u01/app/oracle/oradata/prod/system01.dbf SYSTEM
5 /u01/app/oracle/oradata/prod/example01.dbf EXAMPLE
6 /u01/app/oracle/oradata/prod/test01.dbf TEST
7 /u01/app/oracle/oradata/prod/undo_tbs01.dbf UNDO_TBS
8 /u01/app/oracle/oradata/users02.dbf USER02
9 /u01/app/oracle/oradata/users03.dbf USER03
10 /u01/app/oracle/oradata/users04.dbf USER04
11 /u01/app/oracle/oradata/prod/index01.dbf INDEXES
11 rows selected.
09:42:17 SQL>
09:42:59 SQL> create table test (id int,name varchar2(10),sex varchar2(4));
Table created.
09:46:19 SQL> select count(*) from test;
COUNT(*)
----------
4000
09:46:22 SQL> select distinct sex from test;
SEX
----
M
F
09:46:32 SQL>
----------建立b-tree 索引
09:47:01 SQL> create index test_sex_ind on test(sex) tablespace indexes;
Index created.
---------分析索引结构
09:47:21 SQL> analyze index test_sex_ind validate structure;
Index analyzed.
09:49:20 SQL> COL TABLESPACE_NAME FOR A10
09:49:27 SQL>
1 select index_name,index_type,TABLESPACE_NAME,BLEVEl,LEAF_BLOCKS,NUM_ROWS from user_indexes
2* where index_name='TEST_SEX_IND'
INDEX_NAME INDEX_TYPE TABLESPACE BLEVEL LEAF_BLOCKS NUM_ROWS
------------------------------ --------------------------- ---------- ---------- ----------- ----------
TEST_SEX_IND NORMAL INDEXES 1 8 4000
09:49:27 SQL>
-----------BLEVEL 索引的深度（高度 =深度+1） LEAF_BLOCKS，使用的索引块
09:52:11 SQL> COL COLUMN_NAME FOR A30
09:52:16 SQL>
1 select index_name,table_name,coluMN_name from user_ind_columns
2* where index_name='TEST_SEX_IND'
INDEX_NAME TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------ ------------------------------
TEST_SEX_IND TEST SEX
09:52:16 SQL>
09:52:49 SQL> drop index test_sex_ind;
Index dropped.
--------------建立位图索引
09:53:19 SQL> create bitmap index test_sex_bitind
09:53:49 2 on test(sex) tablespace indexes;
Index created.
09:53:58 SQL> analyze index test_sex_bitind validate structure;
Index analyzed.
09:54:23 SQL> select index_name,index_type,TABLESPACE_NAME,BLEVEl,LEAF_BLOCKS,NUM_ROWS from user_indexes
09:54:28 2 where index_name='TEST_SEX_BITIND';
INDEX_NAME INDEX_TYPE TABLESPACE BLEVEL LEAF_BLOCKS NUM_ROWS
------------------------------ --------------------------- ---------- ---------- ----------- ----------
TEST_SEX_BITIND BITMAP INDEXES 0 1 2
-----------在重复值高的列上适合建立bitmap的索引
01:39:42 SQL> select /*+ index (testtb TEST_SEX_BITIND) */ name,sex from testtb where sex='F';
5000 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 112692374
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7500 | 67500 | 61 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | TEST | 7500 | 67500 | 61 (0)| 00:00:01 |
| 2 | BITMAP CONVERSION TO ROWIDS| | | | | |
|* 3 | BITMAP INDEX SINGLE VALUE | TEST_SEX_BITIND | | | | |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("SEX"='F')
Statistics
----------------------------------------------------------
2 recursive calls
3 db block gets
353 consistent gets
0 physical reads
560 redo size
143731 bytes sent via SQL*Net to client
4047 bytes received via SQL*Net from client
335 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5000 rows processed
01:39:57 SQL>
09:54:36 SQL> drop index TEST_SEX_BITIND
09:55:46 2 ;
Index dropped.
5、基于函数的索引
09:57:32 SQL> conn scott/tiger
Connected.
09:57:37 SQL>
09:57:37 SQL> set autotrace on;
09:57:43 SQL> create index emp_ename_ind on emp(ename) tablespace indexes;
Index created.
09:58:09 SQL> select * from emp where ename='SCOTT';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
Execution Plan
----------------------------------------------------------
Plan hash value: 3220259315
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 32 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 32 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_ENAME_IND | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ENAME"='SCOTT')
Statistics
----------------------------------------------------------
144 recursive calls
0 db block gets
24 consistent gets
0 physical reads
0 redo size
828 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
09:58:21 SQL>
---------通过函数访问，索引无效
09:58:21 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
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 32 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMP | 1 | 32 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(LOWER("ENAME")='scott')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
8 consistent gets
0 physical reads
0 redo size
824 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
09:59:00 SQL>
----------建立函数索引
09:59:00 SQL> create index emp_ename_funind on emp(lower(ename)) tablespace indexes;
Index created.
09:59:51 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
Execution Plan
----------------------------------------------------------
Plan hash value: 519807088
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 32 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 32 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_ENAME_FUNIND | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(LOWER("ENAME")='scott')
Statistics
----------------------------------------------------------
24 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
824 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
09:59:59 SQL>
6、索引监控
10:00:47 SQL> alter index EMP_ENAME_FUNIND monitoring usage;
Index altered.
10:01:08 SQL> select index_name,table_name,MONITORING,USED from v$object_usage;
INDEX_NAME TABLE_NAME MON USE
------------------------------ ------------------------------ --- ---
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
Execution Plan
----------------------------------------------------------
Plan hash value: 519807088
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 32 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 32 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_ENAME_FUNIND | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(LOWER("ENAME")='scott')
Statistics
----------------------------------------------------------
239 recursive calls
3 db block gets
55 consistent gets
0 physical reads
544 redo size
824 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed
10:01:42 SQL> select index_name,table_name,MONITORING,USED from v$object_usage;
INDEX_NAME TABLE_NAME MON USE
------------------------------ ------------------------------ --- ---
EMP_ENAME_FUNIND EMP YES YES
Execution Plan
----------------------------------------------------------
ERROR:
ORA-01039: insufficient privileges on underlying objects of the view
SP2-0612: Error generating AUTOTRACE EXPLAIN report
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
12 consistent gets
0 physical reads
0 redo size
613 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
10:01:47 SQL>
7、反向索引----------用于建立索引的列值是连续的或通过序列生成时，避免索引存放到集中的leaf block上,避免生成热块。
1）建立反向索引
03:08:46 SQL> create index r_empno_ind on test(empno) reverse;
Index created.
对于emp表里empno列来说，因为客户ID号顺序递增，所以为了均衡索引数据分布，应在该列上建立反向索引。
重建索引
03:09:46 SQL> alter index r_empno_ind rebuild reverse;
Index altered.
