Oracle DBA课程系列笔记（13）

原创

客居天涯 2012-02-28 15:13:49 博主文章分类：数据库技术 ©著作权

文章标签 oracle 职场休闲 Oracle study 文章分类 Oracle 数据库

©著作权归作者所有：来自51CTO博客作者客居天涯的原创作品，请联系作者获取转载授权，否则将追究法律责任

第十三章：索引

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';

-----------在重复值高的列上适合建立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

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.