建索引让SQL飞起来

今天帮助看了一个哥们的数据库，帮他抓了一下等待事件，刚好有一个sql在等待事件中，顺便看看

监控等待事件

select
                   a.SID,
                   a.EVENT,
                   b.OSUSER,
                   b.username,
                   b.MACHINE,
                   b.PROGRAM,
                   b.MODULE,
                   b.SQL_ID,
                   b.sql_id
              from v$session_wait a, v$session b
             where a.sid = b.sid
               and a.sid in (select sid
                               from v$session_wait
                              where event not like 'SQL%'
                                and event not like '%message%'
                                and event not like 'Streams AQ%'
                                and event not in ('jobq slave wait',
                                                  'class slave wait',
                                                  'DIAG idle wait',
                                                  'pmon timer',
                                                  'ASM background timer',
                                                  'smon timer'))
select * from v$sql where sql_id='sql_id';

有问题的sql如下

SELECT count(*) AS rown 
FROM ( 
select t.KSGCXH,t.LSH,t.KSKM,t.KSXM,t.KSXH,t.XMKSXH,t.ZP,
t.CS,to_char(t.ZPSJ,'yyyy-mm-dd hh24:mi:ss') ZPSJ,
to_char(t.XTZPSJ,'yyyy-mm-dd hh24:mi:ss') XTZPSJ,
t.CQBJ,t.JYW,t.FZJG,
dbms_lob.getlength(zp) zplen 
from MON_ADMIN.DRV_MON_EXAM_PHOTO t 
where t.Fzjg='辽F' 
and zp is not null 
and dbms_lob.getlength(zp)<204800 
and t.cqbj='1' 
and t.Xtzpsj>to_date('2015-09-17 10:38:22','yyyy-mm-dd hh24:mi:ss') 
and t.Xtzpsj<=to_date('2015-09-17 11:08:11','yyyy-mm-dd hh24:mi:ss')) pagetable

这个sql超级简单，就是简单的查询，连个多表连接都没有，运行4s，返回7行数据

那问题一看就是没有建立索引，并且全表扫描了

查看索引信息，发现现有的索引跟他鸟关系都没有

select * from dba_indexes where owner='MON_ADMIN' and table_name='DRV_MON_EXAM_PHOTO';
select t.*,i.index_type from dba_ind_columns t,dba_indexes i where t.index_name = i.index_name and
    owner='MON_ADMIN' and t.table_name='DRV_MON_EXAM_PHOTO';

查看一下这个表的执行计划

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2462891873

-----------------------------------------------------------------------------------------
| Id  | Operation          | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                    |     1 |   101 | 29460   (1)| 00:05:54 |
|   1 |  SORT AGGREGATE    |                    |     1 |   101 |            |          |
|*  2 |   TABLE ACCESS FULL| DRV_MON_EXAM_PHOTO |     1 |   101 | 29460   (1)| 00:05:54 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("T"."XTZPSJ">TO_DATE(' 2015-09-17 10:38:22', 'syyyy-mm-dd
              hh24:mi:ss') AND "T"."FZJG"='辽F' AND "DBMS_LOB"."GETLENGTH"("ZP")<204800 AND
              "T"."CQBJ"='1' AND "T"."XTZPSJ"<=TO_DATE(' 2015-09-17 11:08:11', 'syyyy-mm-dd
              hh24:mi:ss'))

已选择17行。

果然走了全表扫描，查询这个表的总行数有5017932行，查询体积为896M

结果集返回7条数据，那么7/5017932约等于0，相当于全表扫描每一行数据才能找到这七条数据

我们知道全表扫描是多快读，读取1M的数据大概15ms，全读完大概13s，现在用时4s，看来还是很快的哦。哈哈哈

现在就是建立索引就可以解决问题

create index IDX_DRV_MON_EXAM_PHOTO3 on  MON_ADMIN.DRV_MON_EXAM_PHOTO(Fzjg,cqbj,Xtzpsj,KSGCXH,LSH,KSKM,KSXM,KSXH,XMKSXH,CS,ZPSJ,JYW);

创建完索引之后再看执行计划

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2782271437

--------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                         |     1 |   101 |     4   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE              |                         |     1 |   101 |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID| DRV_MON_EXAM_PHOTO      |     1 |   101 |     4   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX_DRV_MON_EXAM_PHOTO3 |     1 |       |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("DBMS_LOB"."GETLENGTH"("ZP")<204800)
   3 - access("T"."FZJG"='辽F' AND "T"."CQBJ"='1' AND "T"."XTZPSJ">TO_DATE(' 2015-09-17
              10:38:22', 'syyyy-mm-dd hh24:mi:ss') AND "T"."XTZPSJ"<=TO_DATE(' 2015-09-17 11:08:11',
              'syyyy-mm-dd hh24:mi:ss'))

已选择18行。

执行计划变成了索引范围扫描

运行时间有4s编程现在的0.03s，绝对秒杀

对于这种单表运行速度慢，主要看有没有建立索引。