文档章节

-Index FULL SCAN和Index FAST FULL SCAN

左龙龙
 左龙龙
发布于 2017/04/07 13:49
字数 3787
阅读 5
收藏 0

转载http://www.tuicool.com/articles/RJj2iy

总结

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

 

Index FULL SCAN  和 ndex FAST FULL SCAN工作原理:     

   Index FULL SCAN和Index FAST FULL SCAN的适用情况:适用于我们想选择的列都包含在索引里边时,这时候就可以使用IFS或者FFS来代替全表扫描来得到想要的结果。

   I NDEX 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, image: 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');

 

本文转载自:http://www.tuicool.com/articles/RJj2iy

共有 人打赏支持
左龙龙
粉丝 1
博文 100
码字总数 72428
作品 0
崇明
私信 提问
INDEX FULL SCAN和INDEX FAST FULL SCAN的区别

关于INDEX FULL SCAN和INDEX FAST FULL SCAN的区别在于,前者在对索引进行扫描的时候会考虑大索引的结构,而且会按照索引的排序, 而后者则不会,INDEX FAST FULL SCAN不会去扫描根块和分支块...

Zero零_度
2014/12/17
0
0
聚合函数的优化

--创建测试表和索引 drop table t purge; create table t as select * from dba_objects; create index idxobjectid on t(object_id); --全表扫描TABLE ACCESS FULL select count(*) from t;......

reminis
2014/03/16
0
0
PostgreSQL 9.4 存储 JSON 的速度已经比 MongoDB 更快

不翻译了,有兴趣的自己看看:) 英文原文:http://obartunov.livejournal.com/175235.html I returned back home from PGCon.EU-2013 conference, Dublin, Ireland, where we gave two tal......

红薯
2013/11/08
11.3K
24
Oracle 等待事件之 db file scattered read

db file scattered read 官网解释: This event signifies that the user process is reading buffers into the SGA buffer cache and is waiting for a physical I/O call to return. A is......

wangergui
2017/04/04
0
0
[Oracle]-[索引][HINT][执行计划]-带HINT的索引执行计划

谭老师的《Oracle 10g 性能分析与优化思路》第六章hint部分介绍: 举例: create table t(id int); create index t_idx on t(id); SQL> select /+ index(t t_idx) / count(*) from t; Execut......

bisal
2013/06/09
0
0

没有更多内容

加载失败,请刷新页面

加载更多

以太坊Token通证或者代币的真正作用是什么?

上一篇文章以太坊(Ethereum)与以太(Ether)中,你应该很好地理解以太坊是如何构建以太坊应用程序网络的,这些应用程序需要一个名为Ether的加密货币来运行。现在是时候引入一个更深层的概念...

笔阁
15分钟前
1
0
PHP - 利用P3P实现跨域

P3P是什么 P3P Platform for Privacy Preferences, 是W3C公布的一项隐私保护推荐标准,以为用户提供隐私保护。 P3P标准的构想是:Web 站点的隐私策略应该告之访问者该站点所收集的信息类型、...

hansonwong
16分钟前
1
0
传统SSM框架之微信授权登陆

1、jdbc.properties (下面瞎写的值) WEIXIN_APP_ID=wx5438496a99c8d26acbWEIXIN_APP_SECRET=d070fjcwiefhwr38942dw 2、获取配置属性 /** * @Author:Mujiutian * @Description:微信参......

木九天
16分钟前
0
0
以太坊(Ethereum)与以太(Ether)为什么容易混淆?

客观的来说,以太坊与以太这两个概念很容易混淆。 当我们听到以太坊Ethereum这个词时,我们通常会将它与加密货币(比如比特币)联系起来。虽然这个定义并不完全错误。但重要的是要理解以太坊...

geek12345
18分钟前
1
0
ROS实操笔记四 msg 和srv

msg: msg files are simple text files that describe the fields of a ROS message. They are used to generate source code for messages in different languages. srv: an srv file descr......

placido
19分钟前
1
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部