[每日一题] 11gOCP 1z0-052 :2013-09-27 bitmap index.................................................C37
来源:互联网 发布:js 如何获取标签文本 编辑:程序博客网 时间:2024/05/22 23:19
转载请注明出处:http://blog.csdn.net/guoyjoe/article/details/12106027
正确答案C
这道题目是需要我们掌握位图索引知识点。
一、首先我们来看位图索引的组成
位图索引由Key、Start ROWID、End ROWID、Bitmap组成,如下图所示:
二、其次我们来看位图索引适用范围
1.适用于低基数列
2.更新键列的成本非常高
3.使用 OR/AND/NOT IN/COUNT(*) 谓词进行查询时效率较
三、最后我们来分析这道题为什么选C
1:bitmap 索引是分段存储的,也就是说很多条记录可能是分做了N段来存储,也就是有N个begin/end,当新的记录 insert 而使用以前未曾使用过的物理地址的时候,会产生一个bitmap 段来存储,就算只有一条记录2: 当删除一条记录的时候,在bitmap索引上做了一个delete 的标记并用一新的记录来标记了,下面请看具体的演示
3: 当 dml发生的时候,会lock住某个值的存储bit的那一rowid所在的记录,参考下面的 row 中lock ,这样显然会影响并发
4、实验验证
(1)创建表
gyj@OCM> Create table gyj_bitmap_t1(id int ,name varchar2(10));Table created.
(2)插入数据
gyj@OCM> Begin 2 For i in 1 .. 10000 loop 3 Insert into gyj_bitmap_t1 values(i,'AAAAA'); 4 Commit; 5 end loop; 6 End; 7 /PL/SQL procedure successfully completed.gyj@OCM> Begin 2 For i in 10001 .. 20000 loop 3 Insert into gyj_bitmap_t1 values(i,'BBBBB'); 4 Commit; 5 end loop; 6 End; 7 /
(3)创建位图索引
gyj@OCM> create bitmap index b_idx_t1 on gyj_bitmap_t1(name);Index created.
(4)在125号会话下修改id=1的一行数据
gyj@OCM> select sid from v$mystat where rownum=1; SID---------- 125gyj@OCM> Update gyj_bitmap_t1 set name='BBBBB' where id=1;1 row updated.
(5)在125号会话下修改id=10001的一行数据,结果被阻塞了。。。
gyj@OCM> select sid from v$mystat where rownum=1; SID---------- 149gyj@OCM> Update gyj_bitmap_t1 set name='AAAAA' where id=10001;Update
(6)查看锁
sys@OCM> select * from v$lock where sid in(125,149);ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------00000000910D8760 00000000910D87B8 125 AE 100 0 4 0 1177 000000000910D9118 00000000910D9170 149 TX 65541 1235 0 4 194 000000000910DA1D0 00000000910DA228 149 AE 100 0 4 0 480 000002B7A1402D830 00002B7A1402D890 125 TM 77552 0 3 0 212 000002B7A1402D830 00002B7A1402D890 149 TM 77552 0 3 0 194 0000000008FB03048 000000008FB030C0 125 TX 65541 1235 6 0 212 1000000008FB1F928 000000008FB1F9A0 149 TX 131093 1616 6 0 194 0
可以看出149号会话请求4号锁,一直没有持有,这个4号锁是什么呢,锁的标记符是:TX -65541 -1235,从这里可以分析出是在哪个回滚段的第几个事务槽。
sys@OCM> select to_char('65541','xxxxxxxxxxxx') from dual;TO_CHAR('6554------------- 10005
转化成0001和0005即1号回滚段的第5号事务槽上。
我去dump一下这个1号回滚段的段头(上面有事务表信息)
sys@OCM> select * from v$rollname where usn=1; USN NAME---------- ------------------------------ 1 _SYSSMU1_1240252155$sys@OCM> alter system dump undo header "_SYSSMU1_1240252155$";System altered.
(7)转储出来的事务表如下:
Undo block address=0x00c00576,转化成地址是:3号文件的1398号块
sys@OCM> alter system dump datafile 3 block 1398;
System altered.
(8)转储出undo信息:
(9) 通过undo链接这里一层层往前找到事务开始:
*-----------------------------* Rec #0x1e slt: 0x05 objn: 77552(0x00012ef0) objd: 77552 tblspc: 7(0x00000007)* Layer: 11 (Row) opc: 1 rci 0x00 Undo type: Regular undo Begin trans Last buffer split: No Temp Object: No Tablespace Undo: No rdba: 0x00000000Ext idx: 0flg2: 0*-----------------------------uba: 0x00c00576.0145.1d ctl max scn: 0x0000.00243640 prv tx scn: 0x0000.00243658txn start scn: scn: 0x0000.00243606 logon user: 90 prev brb: 12584309 prev bcl: 0KDO undo record:KTB Redo op: 0x04 ver: 0x01 compat bit: 4 (post-11) padding: 1op: L itl: xid: 0x000a.018.00000495 uba: 0x00c007b8.015a.15 flg: C--- lkc: 0 scn: 0x0000.0023aa8cKDO Op code: URP row dependencies Disabled xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x018000c7 hdba: 0x018000c2itli: 2 ispac: 0 maxfr: 4858tabn: 0 slot: 0(0x0) flag: 0x2c lock: 0 ckix: 0ncol: 2 nnew: 1 size: 0Vector content: col 1: [ 5] 41 41 41 41 41 *-----------------------------* Rec #0x1f slt: 0x05 objn: 77553(0x00012ef1) objd: 77553 tblspc: 7(0x00000007)* Layer: 10 (Index) opc: 22 rci 0x1e Undo type: Regular undo Last buffer split: No Temp Object: No Tablespace Undo: No rdba: 0x00000000*-----------------------------index undo for leaf key operationsKTB Redo op: 0x04 ver: 0x01 compat bit: 4 (post-11) padding: 1op: L itl: xid: 0xffff.000.00000000 uba: 0x00000000.0000.00 flg: C--- lkc: 0 scn: 0x0000.002435d3Dump kdilk : itl=2, kdxlkflg=0x1 sdc=0 indexid=0x1c0008a block=0x01c0008b(kdxlup): update keydata in rowkey :(1469): 05 41 41 41 41 41 06 01 80 00 c3 00 00 06 01 80 00 db 01 3f 85 a7 cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cc ff ff ff ff 0f ff 1a ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cc ff ff ff ff 0f ff 1a ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cc ff ff ff ff 0f ff 1a ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cc ff ff ff ff 0f ff 1a ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cd ff ff ff ff ff 07 ff 19 ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cc ff ff ff ff 0f ff 1a ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cc ff ff ff ff 0f ff 1a ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cc ff ff ff ff 0f ff 1a ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cc ff ff ff ff 0f ff 1a ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cc ff ff ff ff 0f ff 1a ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cc ff ff ff ff 0f ff 1a ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cc ff ff ff ff 0f ff 1a ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cc ff ff ff ff 0f ff 77 ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cc ff ff ff ff 0f ff 1a ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cc ff ff ff ff 0f ff 1a ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cc ff ff ff ff 0f ff 1a ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cc ff ff ff ff 0f ff 1a ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cc ff ff ff ff 0f ff 1a ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cc ff ff ff ff 0f ff 1a ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cc ff ff ff ff 0f ff b1 02 ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff 01 *-----------------------------* Rec #0x20 slt: 0x05 objn: 77553(0x00012ef1) objd: 77553 tblspc: 7(0x00000007)* Layer: 10 (Index) opc: 37 rci 0x1f Undo type: Regular undo Last buffer split: No Temp Object: No Tablespace Undo: No rdba: 0x00000000*-----------------------------index undo for leaf key operationsindex change (kdxIndexlogicalNonkeyUpdate): count=6KTB Redo op: 0x02 ver: 0x01 compat bit: 4 (post-11) padding: 1op: C uba: 0x00c00576.0145.1fDump kdilk : len=29 != sizeof(kdilk)=20 :(29): 26 02 05 00 8a 00 c0 01 8b 00 c0 01 00 00 00 00 00 00 00 00 00 04 02 02 00 00 00 00 00itl=2, kdxlkflg=0x5 sdc=0 indexid=0x1c0008a block=0x01c0008b(kdxIndexLogicalNonkeyUpdate): index logical nonkey updatencol: 4 nvec: 2 nnew: 2 size: 0unique key: (13): 05 41 41 41 41 41 06 01 80 00 c3 00 00logical nonkey columns operation records: column 2: atom same length replace: from offset 0 replace 6 bytes: 01 80 00 db 01 3f column 3: atom replace: from offset 279 replace 10 bytes with 10 bytes: ff 1a ff ff ff ff ff ff ff ff *-----------------------------* Rec #0x21 slt: 0x05 objn: 77553(0x00012ef1) objd: 77553 tblspc: 7(0x00000007)* Layer: 10 (Index) opc: 22 rci 0x20 Undo type: Regular undo Last buffer split: No Temp Object: No Tablespace Undo: No rdba: 0x00000000*-----------------------------index undo for leaf key operationsKTB Redo op: 0x02 ver: 0x01 compat bit: 4 (post-11) padding: 1op: C uba: 0x00c00576.0145.20Dump kdilk : itl=2, kdxlkflg=0x1 sdc=0 indexid=0x1c0008a block=0x01c0008b(kdxlpu): purge leaf rowkey :(21): 05 42 42 42 42 42 06 00 00 00 00 00 00 06 00 00 00 00 00 00 ff *-----------------------------* Rec #0x22 slt: 0x05 objn: 77553(0x00012ef1) objd: 77553 tblspc: 7(0x00000007)* Layer: 10 (Index) opc: 37 rci 0x21 Undo type: Regular undo Last buffer split: No Temp Object: No Tablespace Undo: No rdba: 0x00000000*-----------------------------index undo for leaf key operationsindex change (kdxIndexlogicalNonkeyUpdate): count=6KTB Redo op: 0x02 ver: 0x01 compat bit: 4 (post-11) padding: 1op: C uba: 0x00c00576.0145.21Dump kdilk : len=29 != sizeof(kdilk)=20 :(29): 26 02 05 00 8a 00 c0 01 8b 00 c0 01 00 00 00 00 00 00 00 00 00 04 02 02 fa ff 00 00 00itl=2, kdxlkflg=0x5 sdc=0 indexid=0x1c0008a block=0x01c0008b(kdxIndexLogicalNonkeyUpdate): index logical nonkey updatencol: 4 nvec: 2 nnew: 2 size: -6unique key: (13): 05 42 42 42 42 42 06 00 00 00 00 00 00logical nonkey columns operation records: column 2: atom same length replace: from offset 0 replace 6 bytes: 00 00 00 00 00 00 column 3: atom delete: from offset 0 delete 6 bytes
(10)我们来dump一下id=1和id=10001这两行所在的数据块的数据。
gyj@OCM> select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block from gyj_bitmap_t1 where id in(1,10001); FILE# BLOCK---------- ---------- 6 199 6 219
(11)转储6号文件199号块
sys@OCM> alter system dump datafile 6 block 199;System altered.
gyj@OCM> select UTL_RAW.CAST_TO_NUMBER(replace('c1 02',' ')) from dual;UTL_RAW.CAST_TO_NUMBER(REPLACE('C3020102',''))---------------------------------------------- 1gyj@OCM> select UTL_RAW.CAST_TO_VARCHAR2(REPLACE('42 42 42 42 42',' ')) from dual;UTL_RAW.CAST_TO_VARCHAR2(REPLACE('4141414141',''))----------------------------------------------------------------------------------BBBBB
(11)转储6号文件219号块
gyj@OCM> select UTL_RAW.CAST_TO_NUMBER(replace('c3 02 01 02',' ')) from dual;UTL_RAW.CAST_TO_NUMBER(REPLACE('C3020102',''))---------------------------------------------- 10001gyj@OCM> select UTL_RAW.CAST_TO_VARCHAR2(REPLACE('41 41 41 41 41',' ')) from dual;UTL_RAW.CAST_TO_VARCHAR2(REPLACE('4141414141',''))----------------------------------------------------------------------------------AAAAA
具体这个不再说了,需要有一定的基础!!!
QQ:252803295
技术交流QQ群:
DSI&Core Search Ⅰ 群:127149411(2000人技术群:未满)
DSI&Core Search Ⅱ 群:177089463(1000人技术群:未满)
DSI&Core Search Ⅲ 群:284596437(500人技术群:未满)
DSI&Core Search Ⅳ 群:192136702(500人技术群:未满)
DSI&Core Search Ⅴ 群:285030382(500人闲聊群:未满)
MAIL:dbathink@hotmail.com
BLOG: http://blog.csdn.net/guoyjoe
WEIBO:http://weibo.com/guoyJoe0218
ITPUB: http://www.itpub.net/space-uid-28460966.html
OCM: http://education.oracle.com/education/otn/YGuo.HTM
- [每日一题] 11gOCP 1z0-052 :2013-09-27 bitmap index.................................................C37
- [每日一题]11gOCP 1z0-052 :2013-09-11 MGR_ROLE role..........................................A66
- [每日一题] 11gOCP 1z0-052 :2013-09-1 RMAN-- repair failure........................................A20
- [每日一题] 11gOCP 1z0-052 :2013-09-3 Because of frequent checkpoints...........................A30
- [每日一题] 11gOCP 1z0-052 :2013-09-4 block header grows............................................A33
- [每日一题] 11gOCP 1z0-052 :2013-09-6 DBMS_STATS.SET_TABLE_PREFS()使用...............................A37
- [每日一题] 11gOCP 1z0-052 :2013-09-9 PL/SQL package body...........................................A46
- [每日一题] 11gOCP 1z0-052 :2013-09-10 ABOUT ALERTS...............................................A50
- [每日一题] 11gOCP 1z0-052 :2013-09-12 SELECT_CATALOG_ROLE角色....................................A68
- [每日一题] 11gOCP 1z0-052 :2013-09-13 FAST_START_MTTR_TARGET.........................................A69
- [每日一题] 11gOCP 1z0-052 :2013-09-14 repeated parsing activity.................................A70
- [每日一题] 11gOCP 1z0-052 :2013-09-15 Enterprise Manager Support Workbench..................B9
- [每日一题] 11gOCP 1z0-052 :2013-09-16 shared server mode........................................B21
- [每日一题] 11gOCP 1z0-052 :2013-09-17 DRA--Data Recovery Advisor.............................B31
- [每日一题] 11gOCP 1z0-052 :2013-09-18 理解EXPDP/IMPDP.......................................B36
- [每日一题] 11gOCP 1z0-052 :2013-09-19 创建用户...................................................B41
- [每日一题] 11gOCP 1z0-052 :2013-09-20 BIGFILE | SMALLFILE TABLESPACE.....................B43
- [每日一题] 11gOCP 1z0-052 :2013-09-21 automatic maintenance window..........................B53
- Linux /dev/uinput
- 使用vector实现非递归快排
- SequoiaDB数据库的一般概念介绍
- 使用qtp需要注意的几个问题
- SQL SERVER – Enable xp_cmdshell using sp_configure
- [每日一题] 11gOCP 1z0-052 :2013-09-27 bitmap index.................................................C37
- eclipse配置tomcat
- Android CheckBox自定义图片没效果--解决办法
- Android 4.0 源码
- WAS配置MySQL数据源
- 初步了解SequoiaDB数据库
- Mysql 列转行统计查询 、行转列统计查询
- 在 Android 应用中使用 HTML Parser 便捷的解析 html 内容
- JAVA基础必会