首页 » ORACLE 9i-23c » oracle 11g index IFS/IFFS
oracle 11g index IFS/IFFS
oracle对于全索引的扫描支持两种，一种为index full scan简称IFS，另一种是index fast full scan简称IFFS， 后者带了fast是因为前者是单块有序读，而后者是多块无序读，所以多块读对于全扫来说要快于单块读， 对于两者的区别TOM在OTN上描述如下
An index fast full scan reads the ENTIRE index, unsorted, as it exists on disk. It is basically using the index as a “skinny” version of the table. The query in question would only be accessing attributes in the index (we are not using the index as a way to get to the table, we are using the index INSTEAD of the table) We use multiblock IO and read all of the leaf, branch and the root block. We ignore the branch and root blocks and just process the (unordered) data on the leaf blocks.
An index full scan is when we read the index a block at a time – from start to finish. We’ll read the root block, navigate down the left hand side of the index (or right if we are doing a descending full scan) and then when we hit the leaf block – we’ll read across the entire bottom of the index – a block at a time – in sorted order. We use single block IO, not multiblock IO for this operation.
下面简单演示和查看索引结构
SQL> conn anbob/anbob
已连接。
SQL> select count(*) from all_objects;
COUNT(*)
----------
53769
SQL> create table testidx as select * from all_objects where 1=0;
表已创建。
SQL> insert into testidx select * from all_objects where object_id is not null order by object_id desc;
已创建53770行。
SQL> create index ind_testidx_oid on testidx(object_id);
索引已创建。
SQL> analyze table test compute statistics for table for all columns for all indexes;
表已分析。
SQL> select /*+index(t)*/ object_id from testidx t;
已选择53770行。
执行计划
----------------------------------------------------------
Plan hash value: 3045815126
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 48334 | 613K| 135 (1)| 00:00:02 |
| 1 | INDEX FULL SCAN | IND_TESTIDX_OID | 48334 | 613K| 135 (1)| 00:00:02 |
------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
3699 consistent gets
0 physical reads
0 redo size
783831 bytes sent via SQL*Net to client
39840 bytes received via SQL*Net from client
3586 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
53770 rows processed
SQL> select object_id from testidx t;
已选择53770行。
执行计划
----------------------------------------------------------
Plan hash value: 3014170145
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 48334 | 613K| 38 (0)| 00:00:01 |
| 1 | INDEX FAST FULL SCAN| IND_TESTIDX_OID | 48334 | 613K| 38 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
3705 consistent gets
0 physical reads
0 redo size
783831 bytes sent via SQL*Net to client
39840 bytes received via SQL*Net from client
3586 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
53770 rows processed
SQL> col segment_name for a30
SQL> select segment_name,segment_type,bytes,blocks from user_segments where segment_name='IND_TESTIDX_OID'
SEGMENT_NAME SEGMENT_TYPE BYTES BLOCKS
------------------------------ ------------------ ---------- ----------
IND_TESTIDX_OID INDEX 2097152 256
SQL> select object_id from testidx where rownum<10;
OBJECT_ID
----------
99
115
116
271
356
358
359
361
362
已选择9行。
执行计划
----------------------------------------------------------
Plan hash value: 3647722124
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 117 | 2 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | INDEX FAST FULL SCAN| IND_TESTIDX_OID | 48334 | 613K| 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<10)
Note ----- -
dynamic sampling used for this statement
统计信息 ----------------------------------------------------------
7 recursive calls
0 db block gets
138 consistent gets
0 physical reads
0 redo size
523 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed
SQL> select /*+ index(testidx)*/object_id from testidx where rownum<10;
OBJECT_ID
----------
99
115
116
271
356
358
359
361
362
已选择9行。
执行计划
----------------------------------------------------------
Plan hash value: 2506675983
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9 | 117 | 2 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | INDEX FULL SCAN| IND_TESTIDX_OID | 48334 | 613K| 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<10) Note
----- -
dynamic sampling used for this statement
统计信息
----------------------------------------------------------
7 recursive calls
0 db block gets
133 consistent gets
0 physical reads
0 redo size
523 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
9 rows processed
SQL> run
1* select /*+index_ffs(testidx ind_testidx_oid)*/object_id from testidx order by object_id
已选择53770行。
执行计划
----------------------------------------------------------
Plan hash value: 3496029220
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 48334 | 613K| | 271 (2)| 00:00:04 |
| 1 | SORT ORDER BY | | 48334 | 613K| 1912K| 271 (2)| 00:00:04 |
| 2 | INDEX FAST FULL SCAN| IND_TESTIDX_OID | 48334 | 613K| | 38 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
127 consistent gets
0 physical reads
0 redo size
783831 bytes sent via SQL*Net to client
39840 bytes received via SQL*Net from client
3586 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
53770 rows processed
SQL> select object_id from testidx order by object_id;
已选择53770行。
执行计划
----------------------------------------------------------
Plan hash value: 3045815126
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 48334 | 613K| 135 (1)| 00:00:02 |
| 1 | INDEX FULL SCAN | IND_TESTIDX_OID | 48334 | 613K| 135 (1)| 00:00:02 |
------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
3699 consistent gets
0 physical reads
0 redo size
783831 bytes sent via SQL*Net to client
39840 bytes received via SQL*Net from client
3586 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
53770 rows processed
SQL> select * from v$version where rownum<2;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
SQL> col segment_name for a30
SQL> run
1 select segment_name,segment_type,bytes,blocks from user_segments
2* where segment_name='IND_TESTIDX_OID'
SEGMENT_NAME SEGMENT_TYPE BYTES BLOCKS
------------------------------ ------------------ ---------- ----------
IND_TESTIDX_OID INDEX 2097152 256
SQL> select object_id from user_objects where object_name='IND_TESTIDX_OID';
OBJECT_ID
----------
71073
SQL> ALTER SESSION SET EVENTS 'immediate trace name TREEDUMP level 71073';
ERROR:
ORA-01031: 权限不足
SQL> conn / as sysdba
已连接。
SQL> ALTER SESSION SET EVENTS 'immediate trace name TREEDUMP level 71073';
会话已更改。
trace file 内容如下：
Trace file d:\oracle\diag\rdbms\orcl\orcl\trace\orcl_ora_2880.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 XP Version V5.1 Service Pack 3 CPU : 2 - type 586, 2 Physical Cores Process Affinity : 0x00000000 Memory (Avail/Total): Ph:477M/2008M, Ph+PgF:1270M/2859M, VA:1281M/2047M Instance name: orcl Redo thread mounted by this instance: 1 Oracle process number: 20 Windows thread id: 2880, image: ORACLE.EXE (SHAD) *** 2011-06-24 15:40:07.334 *** SESSION ID:(555.17) 2011-06-24 15:40:07.334 *** CLIENT ID:() 2011-06-24 15:40:07.334 *** SERVICE NAME:(SYS$USERS) 2011-06-24 15:40:07.334 *** MODULE NAME:(sqlplus.exe) 2011-06-24 15:40:07.334 *** ACTION NAME:() 2011-06-24 15:40:07.334 ----- begin tree dump branch: 0x10003b4 16778164 (0: nrow: 120, level: 1) leaf: 0x10003b5 16778165 (-1: nrow: 479 rrow: 479) leaf: 0x10003b6 16778166 (0: nrow: 479 rrow: 479) leaf: 0x10003b7 16778167 (1: nrow: 479 rrow: 479) leaf: 0x10003b8 16778168 (2: nrow: 479 rrow: 479) leaf: 0x10003b9 16778169 (3: nrow: 479 rrow: 479) leaf: 0x10003ba 16778170 (4: nrow: 479 rrow: 479) leaf: 0x10003bb 16778171 (5: nrow: 479 rrow: 479) leaf: 0x10003bc 16778172 (6: nrow: 479 rrow: 479) leaf: 0x10003bd 16778173 (7: nrow: 479 rrow: 479) leaf: 0x10003be 16778174 (8: nrow: 472 rrow: 472) leaf: 0x10003bf 16778175 (9: nrow: 449 rrow: 449) leaf: 0x10003c0 16778176 (10: nrow: 449 rrow: 449) leaf: 0x10003c2 16778178 (11: nrow: 449 rrow: 449) leaf: 0x10003c3 16778179 (12: nrow: 449 rrow: 449) leaf: 0x10003c4 16778180 (13: nrow: 449 rrow: 449) leaf: 0x10003c5 16778181 (14: nrow: 449 rrow: 449) leaf: 0x10003c6 16778182 (15: nrow: 449 rrow: 449) leaf: 0x10003c7 16778183 (16: nrow: 449 rrow: 449) leaf: 0x10003c8 16778184 (17: nrow: 449 rrow: 449) leaf: 0x10003c9 16778185 (18: nrow: 449 rrow: 449) leaf: 0x10003ca 16778186 (19: nrow: 449 rrow: 449) leaf: 0x10003cb 16778187 (20: nrow: 449 rrow: 449) leaf: 0x10003cc 16778188 (21: nrow: 449 rrow: 449) leaf: 0x10003cd 16778189 (22: nrow: 449 rrow: 449) leaf: 0x10003ce 16778190 (23: nrow: 449 rrow: 449) leaf: 0x10003cf 16778191 (24: nrow: 449 rrow: 449) leaf: 0x10003d0 16778192 (25: nrow: 449 rrow: 449) leaf: 0x10003d2 16778194 (26: nrow: 449 rrow: 449) leaf: 0x10003d3 16778195 (27: nrow: 449 rrow: 449) leaf: 0x10003d4 16778196 (28: nrow: 449 rrow: 449) leaf: 0x10003d5 16778197 (29: nrow: 449 rrow: 449) leaf: 0x10003d6 16778198 (30: nrow: 449 rrow: 449) leaf: 0x10003d7 16778199 (31: nrow: 449 rrow: 449) leaf: 0x10003d8 16778200 (32: nrow: 449 rrow: 449) leaf: 0x10003d9 16778201 (33: nrow: 449 rrow: 449) leaf: 0x10003da 16778202 (34: nrow: 449 rrow: 449) leaf: 0x10003db 16778203 (35: nrow: 449 rrow: 449) leaf: 0x10003dc 16778204 (36: nrow: 449 rrow: 449) leaf: 0x10003dd 16778205 (37: nrow: 449 rrow: 449) leaf: 0x10003de 16778206 (38: nrow: 449 rrow: 449) leaf: 0x10003df 16778207 (39: nrow: 449 rrow: 449) leaf: 0x10003e0 16778208 (40: nrow: 449 rrow: 449) leaf: 0x10003e2 16778210 (41: nrow: 449 rrow: 449) leaf: 0x10003e3 16778211 (42: nrow: 449 rrow: 449) leaf: 0x10003e4 16778212 (43: nrow: 449 rrow: 449) leaf: 0x10003e5 16778213 (44: nrow: 449 rrow: 449) leaf: 0x10003e6 16778214 (45: nrow: 449 rrow: 449) leaf: 0x10003e7 16778215 (46: nrow: 449 rrow: 449) leaf: 0x10003e8 16778216 (47: nrow: 449 rrow: 449) leaf: 0x10003e9 16778217 (48: nrow: 449 rrow: 449) leaf: 0x10003ea 16778218 (49: nrow: 449 rrow: 449) leaf: 0x10003eb 16778219 (50: nrow: 449 rrow: 449) leaf: 0x10003ec 16778220 (51: nrow: 449 rrow: 449) leaf: 0x10003ed 16778221 (52: nrow: 449 rrow: 449) leaf: 0x10003ee 16778222 (53: nrow: 449 rrow: 449) leaf: 0x10003ef 16778223 (54: nrow: 449 rrow: 449) leaf: 0x10003f0 16778224 (55: nrow: 449 rrow: 449) leaf: 0x10003f2 16778226 (56: nrow: 449 rrow: 449) leaf: 0x10003f3 16778227 (57: nrow: 449 rrow: 449) leaf: 0x10003f4 16778228 (58: nrow: 449 rrow: 449) leaf: 0x10003f5 16778229 (59: nrow: 449 rrow: 449) leaf: 0x10003f6 16778230 (60: nrow: 449 rrow: 449) leaf: 0x10003f7 16778231 (61: nrow: 449 rrow: 449) leaf: 0x10003f8 16778232 (62: nrow: 449 rrow: 449) leaf: 0x10003f9 16778233 (63: nrow: 449 rrow: 449) leaf: 0x10003fa 16778234 (64: nrow: 449 rrow: 449) leaf: 0x10003fb 16778235 (65: nrow: 449 rrow: 449) leaf: 0x10003fc 16778236 (66: nrow: 449 rrow: 449) leaf: 0x10003fd 16778237 (67: nrow: 449 rrow: 449) leaf: 0x10003fe 16778238 (68: nrow: 449 rrow: 449) leaf: 0x10003ff 16778239 (69: nrow: 449 rrow: 449) leaf: 0x1000400 16778240 (70: nrow: 449 rrow: 449) leaf: 0x1000402 16778242 (71: nrow: 449 rrow: 449) leaf: 0x1000403 16778243 (72: nrow: 449 rrow: 449) leaf: 0x1000404 16778244 (73: nrow: 449 rrow: 449) leaf: 0x1000405 16778245 (74: nrow: 449 rrow: 449) leaf: 0x1000406 16778246 (75: nrow: 449 rrow: 449) leaf: 0x1000407 16778247 (76: nrow: 449 rrow: 449) leaf: 0x1000408 16778248 (77: nrow: 449 rrow: 449) leaf: 0x1000709 16779017 (78: nrow: 449 rrow: 449) leaf: 0x100070a 16779018 (79: nrow: 449 rrow: 449) leaf: 0x100070b 16779019 (80: nrow: 449 rrow: 449) leaf: 0x100070c 16779020 (81: nrow: 449 rrow: 449) leaf: 0x100070d 16779021 (82: nrow: 449 rrow: 449) leaf: 0x100070e 16779022 (83: nrow: 449 rrow: 449) leaf: 0x100070f 16779023 (84: nrow: 449 rrow: 449) leaf: 0x1000710 16779024 (85: nrow: 449 rrow: 449) leaf: 0x1000712 16779026 (86: nrow: 449 rrow: 449) leaf: 0x1000713 16779027 (87: nrow: 449 rrow: 449) leaf: 0x1000714 16779028 (88: nrow: 449 rrow: 449) leaf: 0x1000715 16779029 (89: nrow: 449 rrow: 449) leaf: 0x1000716 16779030 (90: nrow: 449 rrow: 449) leaf: 0x1000717 16779031 (91: nrow: 449 rrow: 449) leaf: 0x1000718 16779032 (92: nrow: 449 rrow: 449) leaf: 0x1000719 16779033 (93: nrow: 449 rrow: 449) leaf: 0x100071a 16779034 (94: nrow: 449 rrow: 449) leaf: 0x100071b 16779035 (95: nrow: 449 rrow: 449) leaf: 0x100071c 16779036 (96: nrow: 449 rrow: 449) leaf: 0x100071d 16779037 (97: nrow: 449 rrow: 449) leaf: 0x100071e 16779038 (98: nrow: 449 rrow: 449) leaf: 0x100071f 16779039 (99: nrow: 449 rrow: 449) leaf: 0x1000720 16779040 (100: nrow: 449 rrow: 449) leaf: 0x1000722 16779042 (101: nrow: 449 rrow: 449) leaf: 0x1000723 16779043 (102: nrow: 449 rrow: 449) leaf: 0x1000724 16779044 (103: nrow: 449 rrow: 449) leaf: 0x1000725 16779045 (104: nrow: 449 rrow: 449) leaf: 0x1000726 16779046 (105: nrow: 449 rrow: 449) leaf: 0x1000727 16779047 (106: nrow: 449 rrow: 449) leaf: 0x1000728 16779048 (107: nrow: 449 rrow: 449) leaf: 0x1000729 16779049 (108: nrow: 449 rrow: 449) leaf: 0x100072a 16779050 (109: nrow: 449 rrow: 449) leaf: 0x100072b 16779051 (110: nrow: 449 rrow: 449) leaf: 0x100072c 16779052 (111: nrow: 449 rrow: 449) leaf: 0x100072d 16779053 (112: nrow: 449 rrow: 449) leaf: 0x100072e 16779054 (113: nrow: 449 rrow: 449) leaf: 0x100072f 16779055 (114: nrow: 449 rrow: 449) leaf: 0x1000730 16779056 (115: nrow: 449 rrow: 449) leaf: 0x100078b 16779147 (116: nrow: 449 rrow: 449) leaf: 0x100078c 16779148 (117: nrow: 449 rrow: 449) leaf: 0x100078d 16779149 (118: nrow: 46 rrow: 46) ----- end tree dump
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。