又一個複合索引的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/，如需轉載，請註明出處，否則將追究法律責任。