溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
通過案例學調優之--Index FULL SCAN和Index FAST FULL SCAN
Index FULL SCAN 和ndex FAST FULL SCAN工作原理：
Index FULL SCAN 和Index FAST FULL SCAN的適用情況：適用于我們想選擇的列都包含在索引里邊時，這時候就可以使用IFS或者FFS來代替全表掃描來得到想要的結果。
INDEX FULL SCAN：
HINT寫法：INDEX(表名 索引名)
原理：ORACLE定位到索引的ROOT BLOCK，然后到BRANCH BLOCK（如果有的話），再定位到第一個LEAF BLOCK, 然后根據LEAF BLOCK的雙向鏈表順序讀取。它所讀取的塊都是有順序的，也是經過排序的。
INDEX FAST FULL SCAN：
HINT寫法：INDEX_FFS(表名 索引名)
原理：從段頭開始，讀取包含位圖塊，ROOT BLOCK，所有的BRANCH BLOCK，LEAF BLOCK，讀取的順序完全有物理存儲位置決定，并采取多塊讀，每次讀取DB_FILE_MULTIBLOCK_READ_COUNT個塊。查詢某個表記錄總數的時候，往往基于PRIMARY KEY的INDEX FAST FULL SCAN是最有效的。
Fast Full Index Scans :
Fast full index scans are an alternative to a full table scan when the index contains all the columns that are needed for the query, and at least one column in the index key has the NOT NULL constraint. A fast full scan accesses the data in the index itself, without accessing the table. It cannot be used to eliminate a sort operation, because the data is not ordered by the index key. It reads the entire index using multiblock reads, unlike a full index scan, and can be parallelized.
Fast full scan is available only with the CBO. You can specify it with the initialization parameter OPTIMIZER_FEATURES_ENABLE or the INDEX_FFS hint. Fast full index scans cannot be performed against bitmap indexes.
A fast full scan is faster than a normal full index scan in that it can use multiblock I/O and can be parallelized just like a table scan.
http://download-west.oracle.com/doc…imops.htm#51111
Full Table Scans :
This type of scan reads all rows from a table and filters out those that do not meet the selection criteria. During a full table scan, all blocks in the table that are under the high water mark are scanned. Each row is examined to determine whether it satisfies the statement’s WHERE clause.
When Oracle performs a full table scan, the blocks are read sequentially. Because the blocks are adjacent, I/O calls larger than a single block can be used to speed up the process. The size of the read calls range from one block to the number of blocks indicated by the initialization parameter DB_FILE_MULTIBLOCK_READ_COUNT. Using multiblock reads means a full table scan can be performed very efficiently. Each block is read only once.
http://download-west.oracle.com/doc…imops.htm#44852
案例分析：
1、創建表和索引
16:02:10 SYS@ prod >create table t as select * from dba_objects where 1=2;
Table created.
16:05:43 SYS@ prod >insert into t select * from dba_objects where object_id is not null;
73025 rows created.
16:06:46 SYS@ prod >select count(*) from t;
COUNT(*)
----------
73025
16:06:56 SYS@ prod >commit;
Commit complete.
16:13:48 SYS@ prod >exec dbms_stats.gather_table_stats('SYS','T',cascade=>true);
PL/SQL procedure successfully completed.
16:14:33 SYS@ prod >set autotrace trace
16:15:32 SYS@ prod >select object_id from t;
73025 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 73025 | 356K| 284 (1)| 00:00:04 |
| 1 | TABLE ACCESS FULL| T | 73025 | 356K| 284 (1)| 00:00:04 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
141 recursive calls
0 db block gets
5857 consistent gets
1038 physical reads
0 redo size
1060958 bytes sent via SQL*Net to client
53963 bytes received via SQL*Net from client
4870 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
73025 rows processed
從上面的執行計劃中可知，此時走了全表掃描。
--由于我們需要查詢的列為object_id，因此理論上只需要讀取索引就應該可以返回所有數據，而此時為什么是全表掃描呢？
--這是因為NULL值與索引的特性所決定的。即null值不會被存儲到B樹索引。因此應該為表 t 的列 object_id 添加 not null 約束。
16:16:14 SYS@ prod >desc t;
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
NAMESPACE NUMBER
EDITION_NAME VARCHAR2(30)
在object_id上添加not null約束
16:16:42 SYS@ prod >alter table t modify(object_id not null);
Table altered.
Elapsed: 00:00:00.34
16:16:46 SYS@ prod >desc t
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NOT NULL NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
NAMESPACE NUMBER
EDITION_NAME VARCHAR2(30)2、對Index_FS和Index_FFS對比
16:16:49 SYS@ prod >select object_id from t; 73025 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1220328745 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 73025 | 356K| 46 (0)| 00:00:01 | | 1 | INDEX FAST FULL SCAN| T_ID | 73025 | 356K| 46 (0)| 00:00:01 | ----------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 5028 consistent gets 0 physical reads 0 redo size 1060958 bytes sent via SQL*Net to client 53963 bytes received via SQL*Net from client 4870 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 73025 rows processed 16:17:20 SYS@ prod >select * from t; 73025 rows selected. Elapsed: 00:00:01.99 Execution Plan ---------------------------------------------------------- Plan hash value: 1601196873 -------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 73025 | 6917K| 284 (1)| 00:00:04 | | 1 | TABLE ACCESS FULL| T | 73025 | 6917K| 284 (1)| 00:00:04 | -------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 284 recursive calls 0 db block gets 5885 consistent gets 27 physical reads 0 redo size 8096826 bytes sent via SQL*Net to client 53963 bytes received via SQL*Net from client 4870 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 73025 rows processed 16:20:19 SYS@ prod >select /*+ index(t t_id) */ object_id from t; 73025 rows selected. Elapsed: 00:00:00.56 Execution Plan ---------------------------------------------------------- Plan hash value: 2842924753 ------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 73025 | 356K| 163 (0)| 00:00:02 | | 1 | INDEX FULL SCAN | T_ID | 73025 | 356K| 163 (0)| 00:00:02 | ------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 5021 consistent gets 0 physical reads 0 redo size 1060958 bytes sent via SQL*Net to client 53963 bytes received via SQL*Net from client 4870 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 73025 rows processed
從以上（full table，index full scan，index fast full scan）付出的cost進行比較，index_ffs的cost最小(46)
3、在對查詢做排序時對比
16:20:48 SYS@ prod >select object_id from t order by object_id ; 73025 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 2842924753 ------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 73025 | 356K| 163 (0)| 00:00:02 | | 1 | INDEX FULL SCAN | T_ID | 73025 | 356K| 163 (0)| 00:00:02 | ------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 5021 consistent gets 0 physical reads 0 redo size 1060958 bytes sent via SQL*Net to client 53963 bytes received via SQL*Net from client 4870 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 73025 rows processed 16:21:28 SYS@ prod >select /*+ index_ffs(t t_id) */ object_id from t order by object_id; 73025 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 2317820129 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 73025 | 356K| | 271 (2)| 00:00:04 | | 1 | SORT ORDER BY | | 73025 | 356K| 872K| 271 (2)| 00:00:04 | | 2 | INDEX FAST FULL SCAN| T_ID | 73025 | 356K| | 46 (0)| 00:00:01 | -------------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 170 consistent gets 0 physical reads 0 redo size 1060958 bytes sent via SQL*Net to client 53963 bytes received via SQL*Net from client 4870 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 73025 rows processed 16:23:02 SYS@ prod >select /*+ full(t) */ object_id from t order by object_id; 73025 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 961378228 ----------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ----------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 73025 | 356K| | 508 (1)| 00:00:07 | | 1 | SORT ORDER BY | | 73025 | 356K| 872K| 508 (1)| 00:00:07 | | 2 | TABLE ACCESS FULL| T | 73025 | 356K| | 284 (1)| 00:00:04 | ----------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 1043 consistent gets 32 physical reads 0 redo size 1060958 bytes sent via SQL*Net to client 53963 bytes received via SQL*Net from client 4870 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 73025 rows processed
從上面的執行計劃中可以看出，只要是涉及到排序操作，Oracle會毫不猶豫地選擇INDEX FULL SCAN，因為INDEX FULL SCAN方式掃描一定是 按創建索引是的方式來排序的。
4、對index_fs 和 index_ffs查看trace
INDEX_FS: 16:45:24 sys@ prod >alter session set events '10046 trace name context forever,level 12'; 16:32:34 sys@ prod >set autotrace trace 16:31:42 sys@ prod >select /*+ index (t t_id) */ object_id from t; Execution Plan ---------------------------------------------------------- Plan hash value: 2842924753 ------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 19 | 247 | 1 (0)| 00:00:01 | | 1 | INDEX FULL SCAN | T_ID | 19 | 247 | 1 (0)| 00:00:01 | ------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 753 bytes sent via SQL*Net to client 426 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 19 rows processed 16:33:00 sys@ prod >alter session set events '10046 trace name context off'; Session altered. 查看trace文件內容（節選） select /* index(t t_id) */ object_id from t END OF STMT PARSE #4:c=5000,e=5235,p=7,cr=9,cu=0,mis=1,r=0,dep=0,og=1,plh=2842924753,tim=1416818316519023 EXEC #4:c=0,e=25,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2842924753,tim=1416818316519139 WAIT #4: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=76546 tim=1416818316519211 WAIT #4: nam='db file sequential read' ela= 0 file#=4 block#=139 blocks=1 obj#=76547 tim=1416818316519280 FETCH #4:c=999,e=58,p=1,cr=1,cu=0,mis=0,r=1,dep=0,og=1,plh=2842924753,tim=1416818316519303 16:44:09 SYS@ prod >select object_name,object_id,object_type from dba_objects 16:44:30 2 where object_id='76547'; OBJECT_NAME OBJECT_ID OBJECT_TYPE -------------------- ---------- ------------------- T_ID 76547 INDEX WAIT #4: nam='db file sequential read'，在T_ID的index上，產生了單塊讀得wait。 INDEX_FFS: 16:45:24 sys@ prod >alter session set events '10046 trace name context forever,level 12'; Session altered. 16:46:10 SCOTT@ prod >set autotrace trace 16:46:16 SCOTT@ prod >select /*+ index_ffs(t t_id) */ object_id from t 19 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1220328745 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 19 | 247 | 2 (0)| 00:00:01 | | 1 | INDEX FAST FULL SCAN| T_ID | 19 | 247 | 2 (0)| 00:00:01 | ----------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement (level=2) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 6 consistent gets 0 physical reads 0 redo size 753 bytes sent via SQL*Net to client 426 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 19 rows processed 16:46:17 SCOTT@ prod >alter session set events '10046 trace name context off'; Session altered. 查看trace文件內容（節選） select /*+ index_ffs(t t_id) */ object_id from t END OF STMT PARSE #19:c=1000,e=1050,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=1220328745,tim=1416818962627696 EXEC #19:c=0,e=28,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1220328745,tim=1416818962627788 WAIT #19: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=19 tim=1416818962627824 WAIT #19: nam='db file sequential read' ela= 10 file#=1 block#=91000 blocks=1 obj#=76545 tim=1416818962627888 WAIT #19: nam='db file scattered read' ela= 20 file#=1 block#=91001 blocks=7 obj#=76545 tim=1416818962627977 FETCH #19:c=0,e=181,p=8,cr=8,cu=0,mis=0,r=1,dep=0,og=1,plh=1220328745,tim=1416818962628030 WAIT #19: nam='SQL*Net message from client' ela= 235 driver id=1650815232 #bytes=1 p3=0 obj#=76545 tim=1416818962630284 16:53:45 SYS@ prod >select object_name,object_type,object_id from dba_objects 16:54:02 2 where object_id=76545; OBJECT_NAME OBJECT_TYPE OBJECT_ID -------------------- ------------------- ---------- T_ID INDEX 76545 WAIT #19: nam='db file sequential read' block#=91000 blocks=1 ,對索引段的頭部塊，做了單塊讀 WAIT #19: nam='db file scattered read' block#=91001 blocks=7，對index的其余的塊，做了多塊的讀取 進一步驗證： 1）查看T_ID索引段分配的block，其中block#91000為段頭塊 16:55:12 SYS@ prod >col segment_name for a20 16:55:18 SYS@ prod >select segment_name,segment_type,file_id,block_id,blocks from dba_extents 16:55:50 2 where segment_name='T_ID' and owner='SYS'; SEGMENT_NAME SEGMENT_TYPE FILE_ID BLOCK_ID BLOCKS -------------------- ------------------ ---------- ---------- ---------- T_ID INDEX 1 91000 8 T_ID INDEX 1 92032 8 T_ID INDEX 1 92040 8 T_ID INDEX 1 92048 8 T_ID INDEX 1 92056 8 T_ID INDEX 1 92064 8 T_ID INDEX 1 92072 8 T_ID INDEX 1 92080 8 T_ID INDEX 1 92088 8 T_ID INDEX 1 92096 8 T_ID INDEX 1 92104 8 T_ID INDEX 1 92112 8 T_ID INDEX 1 92120 8 T_ID INDEX 1 92128 8 T_ID INDEX 1 92136 8 T_ID INDEX 1 92144 8 T_ID INDEX 1 92160 128 17 rows selected. 2）對block#91000做dump 16:56:19 SYS@ prod >alter system dump datafile 1 block 91000; System altered. [oracle@RH6 ~]$ more /u01/app/oracle/diag/rdbms/prod/prod/trace/prod_ora_3415.trc Trace file /u01/app/oracle/diag/rdbms/prod/prod/trace/prod_ora_3415.trc Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1 System name: Linux Node name: RH6 Release: 2.6.32-71.el6.i686 Version: #1 SMP Wed Sep 1 01:26:34 EDT 2010 Machine: i686 Instance name: prod Redo thread mounted by this instance: 1 Oracle process number: 17 Unix process pid: 3415, p_w_picpath: oracle@RH6 (TNS V1-V3) *** 2014-11-24 16:57:55.182 *** SESSION ID:(45.143) 2014-11-24 16:57:55.182 *** CLIENT ID:() 2014-11-24 16:57:55.182 *** SERVICE NAME:(SYS$USERS) 2014-11-24 16:57:55.182 *** MODULE NAME:(sqlplus@RH6 (TNS V1-V3)) 2014-11-24 16:57:55.182 *** ACTION NAME:() 2014-11-24 16:57:55.182 Start dump data blocks tsn: 0 file#:1 minblk 91000 maxblk 91000 Block dump from cache: Dump of buffer cache at level 4 for tsn=0, rdba=4285304 BH (0x28beb940) file#: 1 rdba: 0x00416378 (1/91000) class: 4 ba: 0x28974000 set: 3 pool 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,19 dbwrid: 0 obj: 76545 objn: 76545 tsn: 0 afn: 1 hint: f hash: [0x32a97bd0,0x32a97bd0] lru: [0x27fe9a74,0x287ef23c] ckptq: [NULL] fileq: [NULL] objq: [0x30baa69c,0x287ef254] st: XCURRENT md: NULL tch: 3 flags: LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [65535] cr pin refcnt: 0 sh pin refcnt: 0 Block dump from disk: buffer tsn: 0 rdba: 0x00416378 (1/91000) scn: 0x0000.00811496 seq: 0x02 flg: 0x04 tail: 0x14961002 frmt: 0x02 chkval: 0xaa58 type: 0x10=DATA SEGMENT HEADER - UNLIMITED Hex dump of block: st=0, typ_found=1 Dump of memory from 0x00E49600 to 0x00E4B600 E49600 0000A210 00416378 00811496 04020000 [....xcA.........] E49610 0000AA58 00000000 00000000 00000000 [X...............] E49620 00000000 00000011 000000FF 00001020 [............ ...] E49630 00000010 00000024 00000080 00416824 [....$.......$hA.] E49640 00000000 00000010 00000000 000000A3 [................] E49650 00000000 00000000 00000000 00000011 [................] E49660 00000000 00012B01 40000000 00416379 [.....+.....@ycA.] E49670 00000007 00416780 00000008 00416788 [.....gA......gA.] E49680 00000008 00416790 00000008 00416798 [.....gA......gA.] E49690 00000008 004167A0 00000008 004167A8 [.....gA......gA.] E496A0 00000008 004167B0 00000008 004167B8 [.....gA......gA.] E496B0 00000008 004167C0 00000008 004167C8 [.....gA......gA.] E496C0 00000008 004167D0 00000008 004167D8 [.....gA......gA.] E496D0 00000008 004167E0 00000008 004167E8 [.....gA......gA.] E496E0 00000008 004167F0 00000008 00416800 [.....gA......hA.] E496F0 00000080 00000000 00000000 00000000 [................] E49700 00000000 00000000 00000000 00000000 [................] Repeat 242 times E4A630 00000000 00010000 00020001 00000000 [................] E4A640 00000000 00000000 00000000 00000000 [................] Repeat 250 times E4B5F0 00000000 00000000 00000000 14961002 [................] Extent Control Header ----------------------------------------------------------------- Extent Header:: spare1: 0 spare2: 0 #extents: 17 #blocks: 255 last map 0x00000000 #maps: 0 offset: 4128 Highwater:: 0x00416824 ext#: 16 blk#: 36 ext size: 128 #blocks in seg. hdr's freelists: 0 #blocks below: 163 mapblk 0x00000000 offset: 16 Unlocked Map Header:: next 0x00000000 #extents: 17 obj#: 76545 flag: 0x40000000 Extent Map ----------------------------------------------------------------- 0x00416379 length: 7 0x00416780 length: 8 0x00416788 length: 8 0x00416790 length: 8 0x00416798 length: 8 0x004167a0 length: 8 0x004167a8 length: 8 0x004167b0 length: 8 0x004167b8 length: 8 0x004167c0 length: 8 0x004167c8 length: 8 0x004167d0 length: 8 0x004167d8 length: 8 0x004167e0 length: 8 0x004167e8 length: 8 0x004167f0 length: 8 0x00416800 length: 128 nfl = 1, nfb = 1 typ = 2 nxf = 0 ccnt = 0 SEG LST:: flg: UNUSED lhd: 0x00000000 ltl: 0x00000000 End dump data blocks tsn: 0 file#: 1 minblk 91000 maxblk 91000 從以上dump可以看出，block#91000為索引T_ID的段頭塊。
總結
1、當select和where中出現的列都存在索引是發生index full scan與index fast full scan的前提
2、查詢返回的數據行總數占據整個索引10%以上的比率
3、基于上述前提count(*)操作幾乎總是選擇index fast full scan，而索引列上的order by子句幾乎總是選擇index full scan
4、index fast full scan使用多塊讀的方式讀取索引塊，產生db file scattered reads 事件，讀取時高效，但為無序讀取
5、index full scan使用單塊讀方式有序讀取索引塊，產生db file sequential reads事件，當采用該方式讀取大量索引全掃描，效率低下
6、絕大多數情況下，index fast full scan性能優于index full scan，但前者在有order by時，一定會存在對讀取的塊重新排序的過程
7、index fast full scan通過犧牲內存與臨時表空間換取性能，因此在內存不足或飽和狀態應進行合理權衡
附錄：
尋找INDEX FULL SCAN的sql語句
SELECT p.sql_id,sql_text
FROM v$sqlarea t, v$sql_plan p
WHERE t.hash_value = p.hash_value AND p.operation = 'INDEX' AND p.options = 'FULL SCAN'
and p.object_owner not in('SYS','SYSTEM');
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
