oracle执行计划(4.5)--cost成本之快速索引扫描

索引和表查不多的物理对象,都是存储的数据.索引存储的是分支+索引列+ROWID+指针.

因为是快速全索引扫描,就没有必要从分支跳到另外个分支,直接从根块到叶块,从叶块的前后指针扫描到尾巴上!

因此其成本和全表扫描方式是一样的,那么公式也是一样的.

这样我们继续使用上面用到的表和数据.

快速索引扫描

SQL> create index t1_ind on t1(a);

Index created

SQL> select leaf_blocks from dba_indexes where index_name='T1_IND';

LEAF_BLOCKS

-----------

         21

SQL> select count(*) from t1 where a>6000;

已用时间:  00: 00: 00.01

执行计划

----------------------------------------------------------

Plan hash value: 2264155217

--------------------------------------------------------------------------------

| Id  | Operation             | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT      |        |     1 |     3 |     5   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE       |        |     1 |     3 |            |          |

|*  2 |   INDEX FAST FULL SCAN| T1_IND |  3972 | 11916 |     5   (0)| 00:00:01 |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter("A">6000)

 

MRDS=LEAF_BLOCKS/MRBC=21/12

 

SQL> select pname,pval1 from sys.aux_stats$ where sname='SYSSTATS_MAIN';

PNAME                               PVAL1

------------------------------ ----------

CPUSPEEDNW                     713.978494

IOSEEKTIM                              10

IOTFRSPEED                           4096

SREADTIM                           21.046

MREADTIM                           45.384

CPUSPEED                             1042

MBRC                                   12

 

Cost = ceil((SRds +MRds * mreadtim / sreadtim +(CPU_COST /cpuspeednw)/sreadtim*1000)+1)

=ceil((0+21/12*45.384/21.046+(1849550/713.978494)/ (21.046*1000)+1)

=ceil(3.773+2590.4841/21046+1)

=ceil(4.896)

=5

 

select count(*) from t1 where a<600;  的执行计划是啥呢?