sql查询中尽量减少的使用

addison_lin

在数据库中抓到了下面的sql，每小时执行6次，查询条件中大部分是status字段，我推断应该是监控程序，每10分钟运行一次。监控程序一般都是提取异常数据，数据量很少，要不然监控就失去意义了，像这种sql一般是要通过索引迅速定位到数据的。
原始sql：
SQL> select  *
  2    from tc_xx_trade t
  3   where t.xx_status <> 4
  4     and t.xx_status <> 5
  5     and t.cs_status = 1
  6     and t.timeout_type <> 'STOP_TIMEOUT'
  7     and t.gmt_xiaoer_inter is not null
  8     and t.gmt_xiaoer_inter < sysdate
  9     and rownum < 50;

Execution Plan
--------------------------------------------------
SELECT STATEMENT Optimizer=CHOOSE (Cost=9655 Card=49 Bytes=15092)
  COUNT (STOPKEY)
    TABLE ACCESS (FULL) OF 'TC_xx_TRADE' (Cost=9655 Card
=263124 Bytes=81042192)

Statistics
----------------------------------------------------------
          1  recursive calls
          1  db block gets
     109922  consistent gets
     101640  physical reads
        120  redo size
       5552  bytes sent via SQL*Net to client
        656  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          9  rows processed

sql走全表扫描，根据上面的判断我认为cs_status=1的数据量应该很小，走索引会很快。

加上hint：   
SQL> select/*+index(t IDX_TC_REF_TRA_GMT_XIAOER)*/  *
  2    from tc_xx_trade t
  3   where t.xx_status <> 4
  4     and t.xx_status <> 5
  5     and t.cs_status = 1
  6     and t.timeout_type <> 'STOP_TIMEOUT'
  7     and t.gmt_xiaoer_inter is not null
  8     and t.gmt_xiaoer_inter < sysdate
  9     and rownum < 50;

Execution Plan
---------------------------------------------------
   SELECT STATEMENT Optimizer=CHOOSE (Cost=113996 Card=49 Bytes   =15092)
0   COUNT (STOPKEY)
1     TABLE ACCESS (BY INDEX ROWID) OF 'TC_xx_TRADE' (Cost
   =113996 Card=263208 Bytes=81068064)
2       INDEX (RANGE SCAN) OF 'IDX_TC_REF_TRA_GMT_XIAOER' (NON
   -UNIQUE) (Cost=32246 Card=273964)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      61745  consistent gets
      34586  physical reads
      65124  redo size
      11553  bytes sent via SQL*Net to client
        667  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         28  rows processed

逻辑读还是很大，这表示cs_status=1的数据量应该是很多的，过滤性很差。
监控一般只会取很少的一部分数据，哪个条件具有过滤性呢，不了解业务的情况下我们只能去推测，把xx_status<>转换为 in（）试试看。
<>转化为in：
SQL> select *
  2    from tc_xx_trade t
  3    where t.xx_status in (1, 2, 3, 6)
  4     and t.cs_status = 1
  5     and t.timeout_type <> 'STOP_TIMEOUT'
  6     and t.gmt_xiaoer_inter is not null
  7     and t.gmt_xiaoer_inter < sysdate
  8     and rownum < 50;

Execution Plan
----------------------------------------------------------
   SELECT STATEMENT Optimizer=CHOOSE (Cost=541550 Card=49 Bytes =15092)
0   COUNT (STOPKEY)
1     INLIST ITERATOR
2       TABLE ACCESS (BY INDEX ROWID) OF 'TC_xx_TRADE' (Co
   st=541550 Card=1462696 Bytes=450510368)
3         INDEX (RANGE SCAN) OF 'IDX_TC_REF_TRA_GMT_XIAOER' (N
   ON-UNIQUE) (Cost=4475 Card=1462696)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         898  consistent gets
          0   physical reads
          0  redo size
      15366  bytes sent via SQL*Net to client
        689  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         49  rows processed

把<>转化成in字段，效果好了很多，原理我就不多解释了，实际上和用<>不走索引，会去扫描整个表是一样的道理。