oracle bitmap index

 

SQL> create table bit1 as
  2  select rownum as id,trunc(dbms_random.value(1,4)) as bitcol
  3  from dba_objects where rownum<=20
  4  ;

表已创建。

SQL> desc bit1;
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ---------------

 ID                                                 NUMBER
 BITCOL                                             NUMBER

SQL> select * from bit1;

        ID     BITCOL
---------- ----------
         1          1
         2          2
         3          3
         4          3
         5          2
         6          3
         7          1
         8          1
         9          3
        10          1
        11          3

        ID     BITCOL
---------- ----------
        12          1
        13          1
        14          1
        15          1
        16          2
        17          2
        18          3
        19          2
        20          2

已选择20行。

SQL> alter session set events '10608 trace name context forever , level 10';

会话已更改。

SQL> create bitmap index idx_bit1 on bit1(bitcol);

索引已创建。

SQL> create bitmap index idx_bit1 on bit1(bitcol);

索引已创建。

SQL> select object_id from user_objects where object_name='IDX_BIT1';

 OBJECT_ID
----------
     71123

SQL> alter session set events 'immediate trace name TREEDUMP level 71123';

会话已更改。

SQL> show parameter dump

NAME                                 TYPE
------------------------------------ ----------------------VALUE------------------------------
background_core_dump                 string      partial
background_dump_dest                 string      c:\oracle11g\app\administrator\diag\rdbms\orcl\orcl\trace
core_dump_dest                       string      c:\oracle11g\app\administrator\diag\rdbms\orcl\orcl\cdump
max_dump_file_size                   string

NAME                                 TYPE
------------------------------------ ----------------------VALUE------------------------------
unlimited
shadow_core_dump                     string       none
user_dump_dest                       string       c:\oracle11g\app\administrator\diag\rdbms\orcl\orcl\trace

--在 C:\oracle11g\app\Administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_3516.trc 文件中

******************************************************************************************
Trace file c:\oracle11g\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_3516.trc
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Windows Server 2003 Version V5.2 Service Pack 1
CPU                 : 1 - type 586, 1 Physical Cores
Process Affinity    : 0x00000000
Memory (Avail/Total): Ph:129M/1023M, Ph+PgF:1638M/2469M, VA:1321M/2047M
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 44
Windows thread id: 3516, image: ORACLE.EXE (SHAD)

*** 2011-07-07 20:12:55.388
*** SESSION ID:(120.106) 2011-07-07 20:12:55.388
*** CLIENT ID:() 2011-07-07 20:12:55.388
*** SERVICE NAME:(orcl) 2011-07-07 20:12:55.388
*** MODULE NAME:(SQL*Plus) 2011-07-07 20:12:55.388
*** ACTION NAME:() 2011-07-07 20:12:55.388
 
qerbiARwo: bitmap size is 8168
qerbiIPI default pctfree=10
qerbiIPI length=0
qerbiAllocate pfree=127 space=8168
qerbiStart first start
qerbiRop: rid=0100019c.0000, new=Y , key: (2):  c1 02
qerbiCmpSz notfound pctfree=10
qerbiCmpSz adjblksize=7351 length=0
qerbiRop keysize=4 maxbm=3531
kdibcoinit(2cd1b6b4): srid=0100019c.0000
qerbiRop: rid=0100019c.0001, new=Y , key: (2):  c1 03
kdibcoinit(2cd1b638): srid=0100019c.0001
qerbiRop: rid=0100019c.0002, new=Y , key: (2):  c1 04
kdibcoinit(2cd1b5bc): srid=0100019c.0002
qerbiRop: rid=0100019c.0003, new=N, key: (2):  c1 04
qerbiRop: rid=0100019c.0004, new=N, key: (2):  c1 03
qerbiRop: rid=0100019c.0005, new=N, key: (2):  c1 04
qerbiRop: rid=0100019c.0006, new=N, key: (2):  c1 02
qerbiRop: rid=0100019c.0007, new=N, key: (2):  c1 02
qerbiRop: rid=0100019c.0008, new=N, key: (2):  c1 04
qerbiRop: rid=0100019c.0009, new=N, key: (2):  c1 02
qerbiRop: rid=0100019c.000a, new=N, key: (2):  c1 04
qerbiRop: rid=0100019c.000b, new=N, key: (2):  c1 02
qerbiRop: rid=0100019c.000c, new=N, key: (2):  c1 02
qerbiRop: rid=0100019c.000d, new=N, key: (2):  c1 02
qerbiRop: rid=0100019c.000e, new=N, key: (2):  c1 02
qerbiRop: rid=0100019c.000f, new=N, key: (2):  c1 03
qerbiRop: rid=0100019c.0010, new=N, key: (2):  c1 03
qerbiRop: rid=0100019c.0011, new=N, key: (2):  c1 04
qerbiRop: rid=0100019c.0012, new=N, key: (2):  c1 03
qerbiRop: rid=0100019c.0013, new=N, key: (2):  c1 03
kdibcoend(2cd1b6b4): erid=0100019c.000fstatus=3
qerbiCon: key: (2):  c1 02
 srid=0100019c.0 erid=0100019c.f bitmap: (3):  c9 c1 7a
kdibcoend(2cd1b638): erid=0100019c.0017status=3
qerbiCon: key: (2):  c1 03
 srid=0100019c.0 erid=0100019c.17 bitmap: (4):  ca 12 80 0d
kdibcoend(2cd1b5bc): erid=0100019c.0017status=3
qerbiCon: key: (2):  c1 04
 srid=0100019c.0 erid=0100019c.17 bitmap: (4):  ca 2c 05 02
  qerbiFreeMemory: Work heap is used.

*** 2011-07-07 20:16:53.310
----- begin tree dump
leaf: 0x10001a4 16777636 (0: nrow: 3 rrow: 3)
----- end tree dump

***************************************************************************

SQL> set linesize 1000;
SQL> show linesize;
linesize 1000

SQL> select dump(3),dump(2),dump(1) from dual;

DUMP(3)                              DUMP(2)                              DUMP(1)
------------------------------------ ------------------------------------ ------------------------------------
Typ=2 Len=2: 193,4                   Typ=2 Len=2: 193,3                   Typ=2Len=2: 193,2

rid=0100 019c.0000 中 其中 010 为16进制 转化为 10 进制为 16   ，16/4=4 
          019c 十进制为 412

SQL> select distinct dbms_rowid.rowid_relative_fno(rowid) as file#,
  2  dbms_rowid.rowid_block_number(rowid) as block#
  3  from bit1;

     FILE#     BLOCK#
---------- ----------
         0          0