关于索引类型引起的性能问题

今天有位同事说，某张表建了索引，而且查询时也走索引，但还是感觉慢，要5秒钟走左才能跑完

看执行计划
select count(*) from phone_number where an_type_cd = 103

SQL> set autotrace traceonly exp;
SQL> set autotrace traceonly exp stat;
SQL> select count(*) from PHONE_NUMBER pn WHERE pn.AN_TYPE_CD = 103;


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1851 Card=1 Bytes=4)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (FAST FULL SCAN) OF 'PHONE_NUMBER_FIR_IDX' (NON-UN
          IQUE) (Cost=1851 Card=1531395 Bytes=6125580)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      19317  consistent gets
      19283  physical reads
          0  redo size
        404  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

IO读取很高，索引走的是 INDEX (FAST FULL SCAN)
再看一下索引的大小，

select bytes / 1024 / 1024 from user_segments s where s.segment_name = 'PHONE_NUMBER_FIR_IDX';
120M

这样看来，快速索引扫描120M，需要5秒钟，但为什么没有走INDEX(RANGE SCAN)，就很奇怪了


因为原有的值AN_TYPE_CD，存储的数据变化不大，共153个变化值
所以在该字段上建bitmap索引

create bitmap index PHONE_NUMBER_FIR_IDX on PHONE_NUMBER(AN_TYPE_CD);

建完后查看索引大小
2M

这样再看select count(*) from PHONE_NUMBER pn WHERE pn.AN_TYPE_CD = 103;
花了0.297秒

表的数据量：phone_number 900W条
select count(*) from PHONE_NUMBER pn WHERE pn.AN_TYPE_CD = 103; 搜索出来的数据量 153条




然后同事又报
SELECT rownum row_id,
       PHONE_NUMBER_ID,
         AN_TYPE_CD,
         RSC_STATUS_CD,
         PG_TRP_FUNCTION.FUN_GETRSCSTATUSNAME(RSC_STATUS_CD) AS RSC_STATUS_CD_NAME,
         AREA_ID,
         PG_TRP_FUNCTION.FUN_GETAREANAME(AREA_ID) AS AREA_NAME,
         NUMBER_ELEMENT_ID,
         PN_LEVEL_ID,
         PHONE_NUMBER,
         TO_CHAR(CREATED_DT, 'YYYY-MM-DD') AS CREATED_DT,
         TO_CHAR(EXP_DT, 'YYYY-MM-DD') AS EXP_DT,
         TO_CHAR(VERSION, 'YYYY-MM-DD') AS VERSION,
         USE_WAY,
         CREATED_STAFF_ID
      FROM(
       SELECT * FROM PHONE_NUMBER
        WHERE AN_TYPE_CD = 103 order by CREATED_DT desc
       )where rownum >=1;

执行得相当慢

再看执行计划，
SELECT STATEMENT, GOAL = CHOOSE            104748    1531395    218989485    104748
 COUNT                        
  FILTER                        
   VIEW    RM        104748    1531395    218989485    
    SORT ORDER BY            104748    1531395    108729045    104748
     TABLE ACCESS FULL    RM    PHONE_NUMBER    8796    1531395    108729045    8796

使用的是全表扫描
加上暗示/*+INDEX(PHONE_NUMBER PHONE_NUMBER_FIR_IDX)*/


SELECT rownum row_id,
       PHONE_NUMBER_ID,
         AN_TYPE_CD,
         RSC_STATUS_CD,
         PG_TRP_FUNCTION.FUN_GETRSCSTATUSNAME(RSC_STATUS_CD) AS RSC_STATUS_CD_NAME,
         AREA_ID,
         PG_TRP_FUNCTION.FUN_GETAREANAME(AREA_ID) AS AREA_NAME,
         NUMBER_ELEMENT_ID,
         PN_LEVEL_ID,
         PHONE_NUMBER,
         TO_CHAR(CREATED_DT, 'YYYY-MM-DD') AS CREATED_DT,
         TO_CHAR(EXP_DT, 'YYYY-MM-DD') AS EXP_DT,
         TO_CHAR(VERSION, 'YYYY-MM-DD') AS VERSION,
         USE_WAY,
         CREATED_STAFF_ID
      FROM(
       SELECT /*+INDEX(PHONE_NUMBER PHONE_NUMBER_FIR_IDX)*/ * FROM PHONE_NUMBER
        WHERE AN_TYPE_CD = 103 order by CREATED_DT desc
       )where rownum >=1;
       
      
SELECT STATEMENT, GOAL = CHOOSE            132622    1531395    218989485    132622
 COUNT                       
  FILTER                       
   VIEW    RM        132622    1531395    218989485   
    SORT ORDER BY            132622    1531395    108729045    132622
     TABLE ACCESS BY INDEX ROWID    RM    PHONE_NUMBER    36669    1531395    108729045    36669
      BITMAP CONVERSION TO ROWIDS                       
       BITMAP INDEX SINGLE VALUE    RM    PHONE_NUMBER_FIR_IDX               

走位图索引，执行相当的快0.5秒

影响：
索引的类型和走的方式对于扫描数据来说，有相当大的影响，这个需要注意