no_index优化sql一例-CSDN博客

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@]

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

转载于:http://blog.itpub.net/7916042/viewspace-964195/