又一个复合索引的SQL调优

昨天做了一个STATSPACK报告，今天看了一下，发现有这样一条SQL排在了逻辑读的前列：
select * from (select row_.*,rownum rownum_ from (
select * from (select ID,TOPIC,CATALOG_ID,POST_TIME, PLAYER_ID, TYPE from forum_thread where  type=5 and catalog_id=1392order by LAST_MODIFY_TIME desc)
union all
select * from (select ID,TOPIC,CATALOG_ID,POST_TIME, PLAYER_ID, TYPE from forum_thread where  type!=5 and catalog_id=1392order by LAST_MODIFY_TIME desc)
) row_ where rownum <= 12)  where rownum_ > 0;

首先觉得奇怪的地方是order by前没有空格，是不是问题啊？？但当我直接执行后居然没报语法错，以前只知道在from前不加空格是合法的。

执行计划如下：
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=577 Card=12 Bytes=1176)
   1    0   VIEW (Cost=577 Card=12 Bytes=1176)
   2    1     COUNT (STOPKEY)
   3    2       VIEW (Cost=577 Card=2561 Bytes=217685)
   4    3         UNION-ALL
   5    4           VIEW (Cost=195 Card=427 Bytes=36295)
   6    5             SORT (ORDER BY) (Cost=195 Card=427 Bytes=17507)
   7    6               TABLE ACCESS (BY INDEX ROWID) OF 'FORUM_THREAD' (Cost=189 Card=427 Bytes=17507)
   8    7                 BITMAP CONVERSION (TO ROWIDS)
   9    8                   BITMAP AND
  10    9                     BITMAP CONVERSION (FROM ROWIDS)
  11   10                       INDEX (RANGE SCAN) OF 'FORUM_THREAD_CATAID_IDX' (NON-UNIQUE) (Cost=13)
  12    9                     BITMAP CONVERSION (FROM ROWIDS)
  13   12                       INDEX (RANGE SCAN) OF 'FORUM_THREAD_TYPE_IDX' (NON-UNIQUE) (Cost=50)
  14    4           VIEW (Cost=382 Card=2134 Bytes=181390)
  15   14             SORT (ORDER BY) (Cost=382 Card=2134 Bytes=87494)
  16   15               TABLE ACCESS (FULL) OF 'FORUM_THREAD' (Cost=364 Card=2134 Bytes=87494)
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       4048  consistent gets
       1227  physical reads
          0  redo size
       1219  bytes sent via SQL*Net to client
        504  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
         12  rows processed

SQL> select index_name,column_name from user_ind_columns where table_name='FORUM_THREAD';
INDEX_NAME                     COLUMN_NAME
------------------------------ -----------------------------------
SYS_C003283                    ID
FORUM_THREAD_CATAID_IDX        CATALOG_ID
FORUM_THREAD_PTIME_IDX         POST_TIME
FORUM_THREAD_TYPE_IDX          TYPE

在网上查询BITMAP CONVERSION，得到提示，创建复合索引如下：
SQL> create index forum_thread_mix_idx on forum_thread(catalog_id,last_modify_time,type) online;

此时的执行计划如下：（逻辑读降到了172）
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=21 Card=12 Bytes=1176)
   1    0   VIEW (Cost=21 Card=12 Bytes=1176)
   2    1     COUNT (STOPKEY)
   3    2       VIEW (Cost=21 Card=2626 Bytes=223210)
   4    3         UNION-ALL
   5    4           VIEW (Cost=5 Card=438 Bytes=37230)
   6    5             TABLE ACCESS (BY INDEX ROWID) OF 'FORUM_THREAD' (Cost=5 Card=438 Bytes=17520)
   7    6               INDEX (RANGE SCAN DESCENDING) OF 'FORUM_THREAD_MIX_IDX' (NON-UNIQUE) (Cost=2 Card=438)
   8    4           VIEW (Cost=16 Card=2188 Bytes=185980)
   9    8             TABLE ACCESS (BY INDEX ROWID) OF 'FORUM_THREAD' (Cost=16 Card=2188 Bytes=87520)
  10    9               INDEX (RANGE SCAN DESCENDING) OF 'FORUM_THREAD_MIX_IDX' (NON-UNIQUE) (Cost=2 Card=2188)
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        172  consistent gets
          0  physical reads
          0  redo size
       1236  bytes sent via SQL*Net to client
        504  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         12  rows processed
        
由于，这个SQL存在数据的排序问题，所以不能将type=5和type!=5合并，也不能将type=5和type!=5的顺序颠倒，如果能颠倒（如下），逻辑读还能降到更低。

select * from (select row_.*,rownum rownum_ from (
select * from (select ID,TOPIC,CATALOG_ID,POST_TIME, PLAYER_ID, TYPE from forum_thread where  type!=5 and catalog_id=1392order by LAST_MODIFY_TIME desc)
union all
select * from (select ID,TOPIC,CATALOG_ID,POST_TIME, PLAYER_ID, TYPE from forum_thread where  type=5 and catalog_id=1392order by LAST_MODIFY_TIME desc)
) row_ where rownum <= 12)  where rownum_ > 0;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=21 Card=12 Bytes=1176)
   1    0   VIEW (Cost=21 Card=12 Bytes=1176)
   2    1     COUNT (STOPKEY)
   3    2       VIEW (Cost=21 Card=2626 Bytes=223210)
   4    3         UNION-ALL
   5    4           VIEW (Cost=16 Card=2188 Bytes=185980)
   6    5             TABLE ACCESS (BY INDEX ROWID) OF 'FORUM_THREAD' (Cost=16 Card=2188 Bytes=87520)
   7    6               INDEX (RANGE SCAN DESCENDING) OF 'FORUM_THREAD_MIX_IDX' (NON-UNIQUE) (Cost=2 Card=2188)
   8    4           VIEW (Cost=5 Card=438 Bytes=37230)
   9    8             TABLE ACCESS (BY INDEX ROWID) OF 'FORUM_THREAD' (Cost=5 Card=438 Bytes=17520)
  10    9               INDEX (RANGE SCAN DESCENDING) OF 'FORUM_THREAD_MIX_IDX' (NON-UNIQUE) (Cost=2 Card=438)
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         14  consistent gets
          0  physical reads
          0  redo size
       1224  bytes sent via SQL*Net to client
        504  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         12  rows processed

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17997/viewspace-260439/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/17997/viewspace-260439/