第十三章:  索引

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.