免费做外贸的网站,制作logo网站,购物商城app下载,库存管理软件永久免费版Index Full Scan vs Index Fast Full Scanindex full scan和index fast fullscan是指同样的东西吗#xff1f;答案是no。两者虽然从字面上看起来差不多#xff0c;但是实现的机制完全不同。我们一起来看看两者的区别在哪里?首先来看一下IFS,FFS能用在哪里#xff1a;在一句…Index Full Scan vs Index Fast Full Scanindex full scan和index fast fullscan是指同样的东西吗答案是no。两者虽然从字面上看起来差不多但是实现的机制完全不同。我们一起来看看两者的区别在哪里?首先来看一下IFS,FFS能用在哪里在一句sql中如果我们想搜索的列都包含在索引里面的话那么indexfull scan 和 index fast full scan 都可以被采用代替fulltable scan。比如以下语句SQL CREATE TABLE TEST AS SELECT * FROMdba_objects WHERE 01;SQL CREATE INDEX ind_test_id ONTEST(object_id);SQL INSERT INTO TESTSELECT *FROM dba_objectsWHERE object_id IS NOT NULL AND object_id 10000ORDER BY object_id DESC;17837 rows created.SQL analyze table test compute statistics fortable for all columns for all indexes;Table analyzed.SQL set autotrace trace;SQL select object_id from test;17837 rows selected.Execution Plan----------------------------------------------------------0 SELECT STATEMENT OptimizerCHOOSE (Cost68 Card17837Bytes71348)1 0 TABLE ACCESS (FULL) OF TEST(Cost68 Card17837 Bytes71348)这时候 Oracle会选择全表扫描因为 object_id列默认是可以为null的来修改成 not null:SQLalter table test modify(object_id notnull);SQL select object_id from test;17837 rows selected.Execution Plan----------------------------------------------------------0 SELECT STATEMENT OptimizerCHOOSE (Cost11 Card17837Bytes71348)1 0 INDEX (FAST FULL SCAN) OFIND_TEST_ID (NON-UNIQUE) (Cost11 Card17837 Bytes71348)当然我们也可以使用index full scan:SQL select object_id from test;17837 rows selected.Execution Plan----------------------------------------------------------0 SELECT STATEMENT OptimizerCHOOSE (Cost41 Card17837Bytes71348)1 0 INDEX (FULL SCAN) OFIND_TEST_ID (NON-UNIQUE) (Cost101 Card17837 Bytes71348)我们看到了两者都可以在这种情况下使用那么他们有什么区别呢有个地方可以看出两者的区别来看一下两者的输出结果为了让大家看清楚一点我们只取10行。INDEX FAST FULL SCANSQL select object_id from test whererownum11;OBJECT_ID----------6626666267662686626966270662716627266273662746627510 rows selected.INDEX FULL SCANSQL select object_id from test where rownum11;OBJECT_ID----------1061612177121781217912301134951353613539139231650310 rows selected.可以看到两者的结果完全不一样这是为什么呢这是因为当进行indexfull scan的时候 oracle定位到索引的rootblock然后到branchblock(如果有的话)再定位到第一个leaf block,然后根据leafblock的双向链表顺序读取。它所读取的块都是有顺序的也是经过排序的。而index fast fullscan则不同它是从段头开始读取包含位图块rootblock,所有的branch block, leafblock读取的顺序完全有物理存储位置决定并采取多块读没次读取db_file_multiblock_read_count个块。这就是为什么两者的结果区别如此之大的原因我们再仔细跟踪一下这两条语句。首先来看一下索引的结构SQL select object_id from dba_objects whereobject_nameIND_TEST_ID;OBJECT_ID----------70591索引的object_id为70591使用treedump可以看到索引树的结构SQL ALTER SESSION SET EVENTS immediate trace nameTREEDUMP level 70591;----- begin tree dumpbranch: 0x6809b8d 109091725 (0: nrow: 100, level: 1)leaf: 0x6809b96 109091734 (-1:nrow: 294 rrow: 0)leaf: 0x6c07ec1 113278657 (0:nrow: 262 rrow: 0)leaf: 0x6c07ebd 113278653 (1:nrow: 518 rrow: 0)leaf: 0x6c07eb1 113278641 (2:nrow: 524 rrow: 0)leaf: 0x6c07ead 113278637 (3:nrow: 524 rrow: 0)leaf: 0x6c07ea9 113278633 (4:nrow: 524 rrow: 0)leaf: 0x6c07ea5 113278629 (5:nrow: 524 rrow: 0)leaf: 0x6c07ea1 113278625 (6:nrow: 524 rrow: 0)leaf: 0x6c07e9d 113278621 (7:nrow: 524 rrow: 0)leaf: 0x6c07e99 113278617 (8:nrow: 524 rrow: 0)leaf: 0x6c07e95 113278613 (9:nrow: 532 rrow: 0)leaf: 0x6c07e91 113278609 (10:nrow: 524 rrow: 0)leaf: 0x6c07e8d 113278605 (11:nrow: 524 rrow: 0)leaf: 0x6c07ec8 113278664 (12:nrow: 524 rrow: 0)leaf: 0x6c07ec4 113278660 (13:nrow: 524 rrow: 0)leaf: 0x6c07ec0 113278656 (14:nrow: 524 rrow: 0)leaf: 0x6c07ebc 113278652 (15:nrow: 524 rrow: 0)leaf: 0x6809bb2 109091762 (16:nrow: 524 rrow: 0)leaf: 0x6c07eb8 113278648 (17:nrow: 524 rrow: 0)leaf: 0x6c07eb4 113278644 (18:nrow: 524 rrow: 0)leaf: 0x6c07eb0 113278640 (19:nrow: 524 rrow: 0)leaf: 0x6c07eac 113278636 (20:nrow: 524 rrow: 0)leaf: 0x6809bae 109091758 (21:nrow: 524 rrow: 0)leaf: 0x6c07ea8 113278632 (22:nrow: 524 rrow: 0)leaf: 0x6c07ea4 113278628 (23:nrow: 524 rrow: 0)leaf: 0x6c07ea0 113278624 (24:nrow: 105 rrow: 105)leaf: 0x6c07e9c 113278620 (25:nrow: 129 rrow: 129)leaf: 0x6c07eb9 113278649 (26:nrow: 123 rrow: 123)leaf: 0x6809baa 109091754 (27:nrow: 246 rrow: 246)leaf: 0x6c07e98 113278616 (28:nrow: 246 rrow: 246)leaf: 0x6c07e94 113278612 (29:nrow: 246 rrow: 246)leaf: 0x6809ba6 109091750 (30:nrow: 246 rrow: 246)leaf: 0x6809bce 109091790 (31:nrow: 246 rrow: 246)leaf: 0x6809bca 109091786 (32:nrow: 246 rrow: 246)leaf: 0x6809c05 109091845 (33:nrow: 248 rrow: 248)leaf: 0x6809c01 109091841 (34:nrow: 246 rrow: 246)leaf: 0x6809bfd 109091837 (35:nrow: 246 rrow: 246)leaf: 0x6809bf9 109091833 (36:nrow: 246 rrow: 246)leaf: 0x6809bf5 109091829 (37:nrow: 246 rrow: 246)leaf: 0x6809bf1 109091825 (38:nrow: 246 rrow: 246)leaf: 0x6809bed 109091821 (39:nrow: 246 rrow: 246)leaf: 0x6809be9 109091817 (40:nrow: 246 rrow: 246)leaf: 0x6809be5 109091813 (41:nrow: 246 rrow: 246)leaf: 0x6809be1 109091809 (42:nrow: 246 rrow: 246)leaf: 0x6809bdd 109091805 (43:nrow: 246 rrow: 246)leaf: 0x6809bd9 109091801 (44:nrow: 246 rrow: 246)leaf: 0x6809bd5 109091797 (45:nrow: 246 rrow: 246)leaf: 0x6809bd1 109091793 (46:nrow: 248 rrow: 248)leaf: 0x6809bcd 109091789 (47:nrow: 246 rrow: 246)leaf: 0x6809bc9 109091785 (48:nrow: 246 rrow: 246)leaf: 0x6809c08 109091848 (49:nrow: 246 rrow: 246)leaf: 0x6809c04 109091844 (50:nrow: 246 rrow: 246)leaf: 0x6809c00 109091840 (51:nrow: 246 rrow: 246)leaf: 0x6809bfc 109091836 (52:nrow: 246 rrow: 246)leaf: 0x6809bf8 109091832 (53:nrow: 246 rrow: 246)leaf: 0x6809bf4 109091828 (54:nrow: 246 rrow: 246)leaf: 0x6809bf0 109091824 (55:nrow: 246 rrow: 246)leaf: 0x6809bec 109091820 (56:nrow: 246 rrow: 246)leaf: 0x6809be8 109091816 (57:nrow: 246 rrow: 246)leaf: 0x6809be4 109091812 (58:nrow: 246 rrow: 246)leaf: 0x6809be0 109091808 (59:nrow: 248 rrow: 248)leaf: 0x6809bdc 109091804 (60:nrow: 246 rrow: 246)leaf: 0x6809bd8 109091800 (61:nrow: 246 rrow: 246)leaf: 0x6809bd4 109091796 (62:nrow: 246 rrow: 246)leaf: 0x6809bd0 109091792 (63:nrow: 246 rrow: 246)leaf: 0x6809bcc 109091788 (64:nrow: 246 rrow: 246)leaf: 0x6809c07 109091847 (65:nrow: 246 rrow: 246)leaf: 0x6809c03 109091843 (66:nrow: 246 rrow: 246)leaf: 0x6809bff 109091839 (67:nrow: 246 rrow: 246)leaf: 0x6809bfb 109091835 (68:nrow: 246 rrow: 246)leaf: 0x6809bf7 109091831 (69:nrow: 246 rrow: 246)leaf: 0x6809bf3 109091827 (70:nrow: 246 rrow: 246)leaf: 0x6809bef 109091823 (71:nrow: 246 rrow: 246)leaf: 0x6809beb 109091819 (72:nrow: 248 rrow: 248)leaf: 0x6809be7 109091815 (73:nrow: 246 rrow: 246)leaf: 0x6809be3 109091811 (74:nrow: 246 rrow: 246)leaf: 0x6809bdf 109091807 (75:nrow: 246 rrow: 246)leaf: 0x6809bdb 109091803 (76:nrow: 246 rrow: 246)leaf: 0x6809bd7 109091799 (77:nrow: 246 rrow: 246)leaf: 0x6809bd3 109091795 (78:nrow: 246 rrow: 246)leaf: 0x6809bcf 109091791 (79:nrow: 246 rrow: 246)leaf: 0x6809bcb 109091787 (80:nrow: 246 rrow: 246)leaf: 0x6809c06 109091846 (81:nrow: 246 rrow: 246)leaf: 0x6809c02 109091842 (82:nrow: 246 rrow: 246)leaf: 0x6809bfe 109091838 (83:nrow: 246 rrow: 246)leaf: 0x6809bfa 109091834 (84:nrow: 246 rrow: 246)leaf: 0x6809ba2 109091746 (85:nrow: 129 rrow: 129)leaf: 0x6c07eb5 113278645 (86:nrow: 123 rrow: 123)leaf: 0x6809bf6 109091830 (87:nrow: 246 rrow: 246)leaf: 0x6809bf2 109091826 (88:nrow: 246 rrow: 246)leaf: 0x6809bee 109091822 (89:nrow: 246 rrow: 246)leaf: 0x6809bea 109091818 (90:nrow: 246 rrow: 246)leaf: 0x6809b9e 109091742 (91:nrow: 246 rrow: 246)leaf: 0x6809be6 109091814 (92:nrow: 246 rrow: 246)leaf: 0x6809be2 109091810 (93:nrow: 246 rrow: 246)leaf: 0x6809bde 109091806 (94:nrow: 246 rrow: 246)leaf: 0x6809bda 109091802 (95:nrow: 246 rrow: 246)leaf: 0x6809b9a 109091738 (96:nrow: 246 rrow: 246)leaf: 0x6809bd6 109091798 (97:nrow: 246 rrow: 246)leaf: 0x6809bd2 109091794 (98:nrow: 246 rrow: 246)----- end tree dumpindex full scan读取的是0x6c07ea0 这个块而index fastfull scan读取的是0x6809b9a这个块也就是包含数据的物理存储位置最前的块。分别看一下这两个块的内容0x6c07ea0 十进制的1132786240x6809b9a 十进制的109091738SQL selectdbms_utility.data_block_address_file(113278624)file,dbms_utility.data_block_address_block(113278624)block from dual;file block---------- ----------27 32416SQL selectdbms_utility.data_block_address_file(109091738)file,dbms_utility.data_block_address_block(109091738)block from dual;file block---------- ----------26 39834SQL alter system dump datafile 27 block 32416;SQL alter system dump datafile 26 block39834;block 32416的前10行row#0[6564] flag: -----, lock: 2col 0; len 4; (4): c3 02 07 11col 1; len 6; (6): 07 00 7c 20 00 2brow#1[6578] flag: -----, lock: 2col 0; len 4; (4): c3 02 16 4ecol 1; len 6; (6): 07 00 7c 20 00 2arow#2[6592] flag: -----, lock: 2col 0; len 4; (4): c3 02 16 4fcol 1; len 6; (6): 07 00 7c 20 00 29row#3[6606] flag: -----, lock: 2col 0; len 4; (4): c3 02 16 50col 1; len 6; (6): 07 00 7c 20 00 28row#4[6620] flag: -----, lock: 2col 0; len 4; (4): c3 02 18 02col 1; len 6; (6): 07 00 7c 20 00 27row#5[6634] flag: -----, lock: 2col 0; len 4; (4): c3 02 23 60col 1; len 6; (6): 07 00 7c 20 00 26row#6[6648] flag: -----, lock: 2col 0; len 4; (4): c3 02 24 25col 1; len 6; (6): 07 00 7c 20 00 25row#7[6662] flag: -----, lock: 2col 0; len 4; (4): c3 02 24 28col 1; len 6; (6): 07 00 7c 20 00 24row#8[6676] flag: -----, lock: 2col 0; len 4; (4): c3 02 28 18col 1; len 6; (6): 07 00 7c 20 00 23row#9[6690] flag: -----, lock: 2col 0; len 4; (4): c3 02 42 04col 1; len 6; (6): 07 00 7c 20 00 22block 39834的前10行row#0[4591] flag: -----, lock: 2col 0; len 4; (4): c3 07 3f 43col 1; len 6; (6): 02 81 71 f6 00 36row#1[4605] flag: -----, lock: 2col 0; len 4; (4): c3 07 3f 44col 1; len 6; (6): 02 81 71 f6 00 35row#2[4619] flag: -----, lock: 2col 0; len 4; (4): c3 07 3f 45col 1; len 6; (6): 02 81 71 f6 00 34row#3[4633] flag: -----, lock: 2col 0; len 4; (4): c3 07 3f 46col 1; len 6; (6): 02 81 71 f6 00 33row#4[4647] flag: -----, lock: 2col 0; len 4; (4): c3 07 3f 47col 1; len 6; (6): 02 81 71 f6 00 32row#5[4661] flag: -----, lock: 2col 0; len 4; (4): c3 07 3f 48col 1; len 6; (6): 02 81 71 f6 00 31row#6[4675] flag: -----, lock: 2col 0; len 4; (4): c3 07 3f 49col 1; len 6; (6): 02 81 71 f6 00 30row#7[4689] flag: -----, lock: 2col 0; len 4; (4): c3 07 3f 4acol 1; len 6; (6): 02 81 71 f6 00 2frow#8[4703] flag: -----, lock: 2col 0; len 4; (4): c3 07 3f 4bcol 1; len 6; (6): 02 81 71 f6 00 2erow#9[4717] flag: -----, lock: 2col 0; len 4; (4): c3 07 3f 4ccol 1; len 6; (6): 02 81 71 f6 00 2d对照一下前面的结果集block32416的第一行为10616,数据内的存储格式应该为SQL select dump(10616,16) from dual;DUMP(10616,16)----------------------Typ2 Len4: c3,2,7,11确实等于dump block所看到的row#0[6564] flag: -----, lock: 2col 0; len 4; (4): c3 02 07 11col 1; len 6; (6): 07 00 7c 20 00 2b再看block 39834的第1行SQL select dump(66266,16) from dual;DUMP(66266,16)-----------------------Typ2 Len4: c3,7,3f,43跟dump 的结果也一样row#0[4591] flag: -----, lock: 2col 0; len 4; (4): c3 07 3f 43col 1; len 6; (6): 02 81 71 f6 00 36这就证明了上面所说的index full scan和index fast fullscan的不同。我们也可以用10046事件去跟踪两者走的路径。SQL ALTER SESSION SET EVENTS immediate trace nameflush_cache;(清空buffer cache,以便观看db file sequential read,dbfile scattered read事件)。SQL alter session set events10046 trace namecontext forever,level 12;Session altered.SQL select object_id from test whererownum11;OBJECT_ID----------6626666267662686626966270662716627266273662746627510 rows selected.SQL alter session setevents10046 trace name context off;Session altered.[oraclecsdbc udump]$ grep read cs-dbc_ora_15596.trcRedo thread mounted by this instance: 1WAIT #1: namdb file sequential read ela 33 p126 p239820p31WAIT #1: namdb file sequential read ela 21 p126 p239817p31WAIT #1: namdb file sequential read ela 17 p126 p239819p31WAIT #1: namdb file parallel read ela 53 p12 p22 p32WAIT #1: namdb file scattered read ela 466 p126 p239821p316最前面的db file sequentialread是由于读段头等操作我们来关注db filescattered read事件因为index fast fullscan是采用多块读从39821开始读取db_file_multiblock_read_count个块(本例里设置为16)。我们关心的39834块正位于其中。再来看index full scan的10046 traceSQL ALTER SESSION SET EVENTS immediate trace nameflush_cache;(清空buffer cache,以便观看db file sequential read,dbfile scattered read事件)。SQL alter session set events10046 trace namecontext forever,level 12;Session altered.SQLOBJECT_ID----------1061612177121781217912301134951353613539139231650310 rows selected.SQL alter session setevents10046 trace name context off;Session altered.[oraclecsdbc udump]$ grep read cs-dbc_ora_15609.trcRedo thread mounted by this instance: 1WAIT #1: namdb file sequential read ela 49 p126 p239821p31root block,正是先前索引 dump里面的 0x6809b8dWAIT #1: namdb file sequential read ela 32 p126 p239830p31WAIT #1: namdb file sequential read ela 40 p127 p232449p31WAIT #1: namdb file sequential read ela 35 p127 p232445p31WAIT #1: namdb file sequential read ela 28 p127 p232433p31WAIT #1: namdb file sequential read ela 19 p127 p232429p31WAIT #1: namdb file sequential read ela 34 p127 p232425p31WAIT #1: namdb file sequential read ela 32 p127 p232421p31WAIT #1: namdb file sequential read ela 33 p127 p232417p31WAIT #1: namdb file sequential read ela 29 p127 p232413p31WAIT #1: namdb file sequential read ela 37 p127 p232409p31WAIT #1: namdb file sequential read ela 32 p127 p232405p31WAIT #1: namdb file sequential read ela 35 p127 p232401p31WAIT #1: namdb file sequential read ela 34 p127 p232397p31WAIT #1: namdb file sequential read ela 31 p127 p232456p31WAIT #1: namdb file sequential read ela 29 p127 p232452p31WAIT #1: namdb file sequential read ela 31 p127 p232448p31WAIT #1: namdb file sequential read ela 30 p127 p232444p31WAIT #1: namdb file sequential read ela 38 p126 p239858p31WAIT #1: namdb file sequential read ela 31 p127 p232440p31WAIT #1: namdb file sequential read ela 32 p127 p232436p31WAIT #1: namdb file sequential read ela 35 p127 p232432p31WAIT #1: namdb file sequential read ela 31 p127 p232428p31WAIT #1: namdb file sequential read ela 29 p126 p239854p31WAIT #1: namdb file sequential read ela 36 p127 p232424p31WAIT #1: namdb file sequential read ela 32 p127 p232420p31WAIT #1: namdb file sequential read ela 36 p127 p232416p31index fullscan走的路径正是文章开始所提到的定位到rootblock然后根据leafblock链表一路读取块。看到这里大家应该比较了解indexfull scan 和index fast full scan的区别了最后补充一下index full scan 和 index fast full scan在排序上的不同。SQL set autotrace trace;SQL select object_id from test order byobject_id;17837 rows selected.Execution Plan----------------------------------------------------------0 SELECT STATEMENT OptimizerCHOOSE (Cost41 Card17837Bytes71348)1 0 INDEX (FULL SCAN) OFIND_TEST_ID (NON-UNIQUE) (Cost101 Card17837 Bytes71348)由于有排序所以oracle自动选择了index fullscan避免了排序。那么强制用index fast full scan呢SQL selectobject_id from test order byobject_id;17837 rows selected.Execution Plan----------------------------------------------------------0 SELECT STATEMENT OptimizerCHOOSE (Cost59 Card17837Bytes71348)1 0 SORT (ORDER BY) (Cost59Card17837 Bytes71348)2 1 INDEX (FAST FULL SCAN) OF IND_TEST_ID (NON-UNIQUE) (Cost11Card17837 Bytes71348)index fast full scan会多一步sort orderby相信仔细看过这篇文章的人能知道其中结果了吧还不知道的人请在文章中自己找答案吧。
