位图索引 - 数据库编程

位图索引

今天是2014-01-15，继续进行索引的相关技术学习，今天学习内容为位图索引；
位图索引使用指南：
1、一般位图索引使用在基数列比较小的列
2、最好用于数据仓库和dss系统
3、最好用于星型模式
4、对于在索引列上有很多链接查询或是过滤查询的情况，位图索引有很高的性能

5、位图索引对dml操作支持性不好，建议在进行dml操作时删掉位图索引，之后再重建（同样适用于在分区上建立的位图索引）
位图索引创建：
create bitmap index index_name on table_name(table_column_name) nologging;
注意因为位图索引对dml语句支持性不好需要使用nologging操作。
另外位图索引不像B树索引一样不存储null值。B树索引对于单列字段是不存储null值的，对于多列字段其中一例不为null，另一列为null的索引是存储null值的。因此在B树索引列上如果存在null可能出现不走索引的情况，但是位图索引不会这样，因为它实际存储null值。
测试如下：

SQL> conn amy/rhys 
Connected.
SQL> select index_name,column_name,column_position,table_name from user_ind_columns where table_name='EMP';

INDEX_NAME                     COLUMN_NAME          COLUMN_POSITION TABLE_NAME
------------------------------ -------------------- --------------- ------------------------------
EMP_BTIDX1                     ENAME                              1 EMP

SQL> drop index emp_btidx1;

Index dropped.

SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10
      9888            xiaohai

15 rows selected.


SQL>  create index emp_idx1 on emp(ename);

Index created.

SQL> set autotrace trace

SQL> select * from emp where ename is null;


Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    37 |     6   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    37 |     6   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ENAME" IS NULL)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         18  consistent gets
          0  physical reads
          0  redo size
       1004  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
SQL> set autotrace off

SQL> drop index emp_idx1;

Index dropped.

SQL> create bitmap index emp_btidx1 on emp(ename);

Index created.


SQL> set autotrace trace  

SQL> select * from emp where ename is null;


Execution Plan
----------------------------------------------------------
Plan hash value: 448664046

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

位图索引(一)