分享程序员开发的那些事...
更新时间：2022-09-11 15:17:56
[20140904]关于位图联结索引.txt
--昨天有人问这个问题，自己也忘了差不多，晚上翻书看了一下，做一个记录。
1.建立测试环境：
SCOTT@test> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
SCOTT@test> alter session set statistics_level=all;
Session altered.
SCOTT@test> select count(*) from emp,dept where dept.deptno=emp.deptno and dept.dname='SALES';
COUNT(*)
----------
6
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID f24ygm87y6ynz, child number 1
-------------------------------------
select count(*) from emp,dept where dept.deptno=emp.deptno and dept.dname='SALES'
Plan hash value: 1546158010
------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 8 (100)| 1 |00:00:00.01 | 14 | | | |
| 1 | SORT AGGREGATE | | 1 | 1 | | 1 |00:00:00.01 | 14 | | | |
|* 2 | HASH JOIN | | 1 | 5 | 8 (13)| 6 |00:00:00.01 | 14 | 1517K| 1517K| 393K (0)|
|* 3 | TABLE ACCESS FULL| DEPT | 1 | 1 | 4 (0)| 1 |00:00:00.01 | 8 | | | |
| 4 | TABLE ACCESS FULL| EMP | 1 | 14 | 3 (0)| 14 |00:00:00.01 | 6 | | | |
------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
3 - filter("DEPT"."DNAME"='SALES')
2.测试：
--如果建立位图联结索引：
create bitmap index ib_emp_dname on emp(d.dname) from emp e,dept d where e.deptno=d.deptno;
--可以发现语法怪怪的，对dept.dname列建立索引，这个索引不是指向dept表，而是指向emp表。
SCOTT@test> create bitmap index ib_emp_dname on emp(d.dname) from emp e,dept d where e.deptno=d.deptno;
Index created.
SCOTT@test> select count(*) from emp,dept where dept.deptno=emp.deptno and dept.dname='SALES';
COUNT(*)
----------
6
SCOTT@test> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID f24ygm87y6ynz, child number 1
-------------------------------------
select count(*) from emp,dept where dept.deptno=emp.deptno and dept.dname='SALES'
Plan hash value: 722388488
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 (100)| 1 |00:00:00.01 | 1 |
| 1 | SORT AGGREGATE | | 1 | 1 | | 1 |00:00:00.01 | 1 |
| 2 | BITMAP CONVERSION COUNT | | 1 | 5 | 1 (0)| 1 |00:00:00.01 | 1 |
|* 3 | BITMAP INDEX SINGLE VALUE| IB_EMP_DNAME | 1 | | | 1 |00:00:00.01 | 1 |
-------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("EMP"."SYS_NC00009$"='SALES')
--可以发现执行计划仅仅扫描IB_EMP_DNAME索引，就知道结果，没有扫描表dept以及emp。注意一些细节，access("EMP"."SYS_NC00009$"='SALES')。
SCOTT@test> select emp.*,SYS_NC00009$ from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO SYS_NC00009$
---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- --------------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
14 rows selected.
--可以发现SYS_NC00009$并没有信息输出。仅仅跟dept表联结后才有效。
SCOTT@test> select /*+ index(emp,ib_emp_dname) */emp.*,emp.sys_nc00009$,dept.dname from emp,dept where emp.deptno=dept.deptno and dname='SALES';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO SYS_NC00009$ DNAME
---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- -------------- --------------
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30 SALES SALES
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30 SALES SALES
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30 SALES SALES
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30 SALES SALES
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30 SALES SALES
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30 SALES SALES
6 rows selected.
3.转储索引看看。
SCOTT@test> select segment_name,header_file,header_block from dba_segments where owner=user and segment_name='IB_EMP_DNAME';
SEGMENT_NAME HEADER_FILE HEADER_BLOCK
-------------------- ----------- ------------
IB_EMP_DNAME 4 1914
SCOTT@test> alter system dump datafile 4 block 1915 ;
System altered.
--表很小，索引应该仅仅root节点，应该在HEADER_BLOCK的下1块。
Block header dump: 0x0100077b
Object id on Block? Y
seg/obj: 0x4704a csc: 0x02.a639a65a itc: 2 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x1000778 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x02 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0002.a639a65a
Leaf block dump
===============
header address 182929646180=0x2a9774e264
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 4
kdxcosdc 0
kdxconro 3
kdxcofbo 42=0x2a
kdxcofeo 7946=0x1f0a
kdxcoavs 7904
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8032
row#0[8001] flag: ------, lock: 0, len=31
col 0; len 10; (10): 41 43 43 4f 55 4e 54 49 4e 47
col 1; len 6; (6): 01 00 00 97 00 00
col 2; len 6; (6): 01 00 00 97 00 0f
col 3; len 3; (3): c9 40 21
row#1[7972] flag: ------, lock: 0, len=29
col 0; len 8; (8): 52 45 53 45 41 52 43 48
col 1; len 6; (6): 01 00 00 97 00 00
col 2; len 6; (6): 01 00 00 97 00 0f
col 3; len 3; (3): c9 89 14
row#2[7946] flag: ------, lock: 0, len=26
col 0; len 5; (5): 53 41 4c 45 53
col 1; len 6; (6): 01 00 00 97 00 00
col 2; len 6; (6): 01 00 00 97 00 0f
col 3; len 3; (3): c9 36 0a
----- end of leaf block dump -----
End dump data blocks tsn: 4 file#: 4 minblk 1915 maxblk 1915
SCOTT@test> select rowid,emp.* from emp;
ROWID EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------------ ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
AAAR3xAAEAAAACXAAA 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
...
SCOTT@test> @lookup_rowid AAAR3xAAEAAAACXAAA
OBJECT FILE BLOCK ROW DBA TEXT
---------- ---------- ---------- ---------- -------------------- ----------------------------------------
73201 4 151 0 4,151 alter system dump datafile 4 block 151 ;
SCOTT@test> select deptno,dname,dump(dname,16) c50 from dept ;
DEPTNO DNAME C50
---------- -------------- --------------------------------------------------
10 ACCOUNTING Typ=1 Len=10: 41,43,43,4f,55,4e,54,49,4e,47
20 RESEARCH Typ=1 Len=8: 52,45,53,45,41,52,43,48
30 SALES Typ=1 Len=5: 53,41,4c,45,53
40 OPERATIONS Typ=1 Len=10: 4f,50,45,52,41,54,49,4f,4e,53
SCOTT@test> select * from emp where deptno=40 ;
no rows selected
SCOTT@test> host cat /home/oracle11g/sqllaji/dfb.sql
select
dbms_utility.data_block_address_file(to_number('&1','xxxxxxxxxxxxxxxx')) rfile#,
dbms_utility.data_block_address_block(to_number('&&1','xxxxxxxxxxxxxxxx')) block#
from dual;
select 'alter system dump datafile '||dbms_utility.data_block_address_file(to_number('&1','xxxxxxxxxxxxxxxx'))||' block '||
dbms_utility.data_block_address_block(to_number('&&1','xxxxxxxxxxxxxxxx')) ||' ;' text
from dual;
SCOTT@test> @dfb 01000097
RFILE# BLOCK#
------- -------
4 151
TEXT
----------------------------------------
alter system dump datafile 4 block 151 ;
--对比上面发现dname='OPERATIONS'不在索引中，因为emp不存在deptno=40的记录。
--看看转储就很容易明白，col 0对应是dept.dname键值，col 1: 开始rowid，col 2: 结束rowid，col 3 : 对应的位图值。
3.位图索引引起阻塞的问题：
--一般位图索引不合适oltp系统，从前面看位图联结索引一样存在这样的问题，做1个简单的测试：
--session 1:
SCOTT@test> insert into emp(empno,hiredate,deptno) values(9999,sysdate,40);
1 row created.
--session 2:
SCOTT@test> insert into emp(empno,hiredate,deptno) values(9998,sysdate,40);
--可以发现会话2挂起！
--roollback插入操作。另外注意连接键值必须是主键。