no_index最佳化sql一例

a和b表都分別有350W條資料,在a表和b表都以username為主鍵;

這個查詢是找出存在在a表但不存在在b表的username

select username,mobilephone from a where username not in (select username from b );

Statistics
----------------------------------------------------------
403 recursive calls
91 db block gets
10804761 consistent gets
30631 physical reads
13748 redo size
184 bytes sent via SQL*Net to client
224 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

結果a和b都走了"INDEX FAST FULL SCAN"的索引掃描,速度奇慢,花了12094秒(3個多小時,因為這個是作為一個job在晚上3點鐘執行的,這是在user_jobs裡顯示的時間)

select /*+ no_index(a) */ username,mobilephone from a where username not in (select /*+ no_index(b) */ username from b );

Statistics
----------------------------------------------------------
480 recursive calls
93 db block gets
80524 consistent gets
24934 physical reads
30000 redo size
184 bytes sent via SQL*Net to client
224 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

加上了no_index之後,執行時間為00:00:14.82秒,執行效率得到了大大提高!!("consistent gets"也的到很大幅度的降低)

這時候可以體現出全表掃描比索引掃描快!!

[@more@]