BITMAP索引实验

SQL>create table emp as select *  from scott.emp;

Table created.

SQL> create bitmap index job_idx on emp(job);

Index created.

SQL> column bits format a30
with jobs as (select distinct job from emp),
  from jobs, emps
       )
     emps as (select job, row_number() over (order by rowid) rn from emp),
     cnt  as (select count(*) cnt from emp)
select job,
       ltrim(sys_connect_by_path( bit, '-' ),'-') bits
  from (
select jobs.job,
       case when jobs.job = emps.job then '1' else '0' end bit,
   emps.rn
  from jobs, emps
       )
 where level = (select cnt from cnt)
 start with rn=1
 connect by prior job = job and prior rn = rn-1
  /

JOB       BITS
--------- ------------------------------
ANALYST   0-0-0-0-0-0-0-1-0-0-0-0-1-0
CLERK     1-0-0-0-0-0-0-0-0-0-1-1-0-1
MANAGER   0-0-0-1-0-1-1-0-0-0-0-0-0-0
PRESIDENT 0-0-0-0-0-0-0-0-1-0-0-0-0-0
SALESMAN  0-1-1-0-1-0-0-0-0-1-0-0-0-0

SQL> select count(1) from emp;

  COUNT(1)
----------
        14

 

SQL> set autot traceonly
SQL> select * from emp where job = 'CLERK' or job = 'ANALYST';

6 rows selected.

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

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     6 |   522 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     6 |   522 |     2   (0)| 00:00:01 |                                       因为数据量少，全表扫描更划算，CBO成本优化算法来的，所以走的是全表扫描。
--------------------------------------------------------------------------

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

   1 - filter("JOB"='ANALYST' OR "JOB"='CLERK')

Note
-----
   - dynamic sampling used for this statement

Statistics
----------------------------------------------------------
        148  recursive calls
          0  db block gets
         22  consistent gets
          0  physical reads
          0  redo size
       1208  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          6  rows processed

SQL> set autot off
SQL> exec dbms_stats.gather_table_stats( user, 'EMP', cascade=>true );

PL/SQL procedure successfully completed.

SQL> set autot traceonly
SQL> select * from emp where job = 'CLERK' or job = 'ANALYST';

6 rows selected.

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

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

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

   1 - filter("JOB"='ANALYST' OR "JOB"='CLERK')

Statistics
----------------------------------------------------------
         42  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
       1208  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          6  rows processed

SQL> set autot off

SQL> insert into emp select * from emp;

14 rows created.

SQL> insert into emp select * from emp;

28 rows created.

SQL> insert into emp select * from emp;

56 rows created.

SQL> insert into emp select * from emp;

112 rows created.

SQL> insert into emp select * from emp;

224 rows created.

SQL> insert into emp select * from emp;

448 rows created.

SQL> insert into emp select * from emp;

896 rows created.

SQL> insert into emp select * from emp;

1792 rows created.

SQL> insert into emp select * from emp;

3584 rows created.

SQL> insert into emp select * from emp;

7168 rows created.

SQL> insert into emp select * from emp;

14336 rows created.

SQL> insert into emp select * from emp;

28672 rows created.

SQL> insert into emp select * from emp;

57344 rows created.

SQL> insert into emp select * from emp;

114688 rows created.

SQL> commit;

Commit complete.

SQL> update emp set job='shujukuai' where rownum < 2;

1 row updated.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats( user, 'EMP', cascade=>true );

PL/SQL procedure successfully completed.

SQL> set autot traceonly
SQL> set linesize 180
SQL> select * from emp where job = 'shujukuai';

Execution Plan
----------------------------------------------------------
Plan hash value: 825902848

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     1 |    37 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID | EMP     |     1 |    37 |     1   (0)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS|         |       |       |            |          |
|*  3 |    BITMAP INDEX SINGLE VALUE | JOB_IDX |       |       |            |          |
----------------------------------------------------------------------------------------

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

   3 - access("JOB"='shujukuai')

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        986  bytes sent via SQL*Net to client
        492  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

位图索引的AND和OR运算相当地方便

 

位图索引的并发

 

SQL> update emp set job = 'CLERK' where ename = 'SCOTT';

16384 rows updated.

Execution Plan
----------------------------------------------------------
Plan hash value: 1494045816

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |      | 16673 |   227K|   301   (2)| 00:00:04 |
|   1 |  UPDATE            | EMP  |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| EMP  | 16673 |   227K|   301   (2)| 00:00:04 |
---------------------------------------------------------------------------

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

   2 - filter("ENAME"='SCOTT')

Statistics
----------------------------------------------------------
         93  recursive calls
      17012  db block gets
       1482  consistent gets
          0  physical reads
    4664068  redo size
        833  bytes sent via SQL*Net to client
        743  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
      16384  rows processed

SQL> set autot off
declare
       pragma autonomous_transaction;
   begin
  update emp set job = 'ANALYST' where ename = 'SMITH';
      commit;
   end;
  7     /
declare
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 4

采用位图索引，一个键指向多行，可能数以百计甚至更多。

如果更新一个位图索引键，那么这个键指向的数百条记录会与你实际更新的那一行一同被有效地锁定。 位图索引是针对那些值不经常改变的字段的，在实际应用中，如果某个字段的值需要频繁更新，那么就不适合在它上面创建位图索引。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21158541/viewspace-668779/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21158541/viewspace-668779/