如果对Oracle不了解,你可能深信别人说的应杜绝全表扫描和Not IN的谎言
如果真是这样,Oracle就根本不应该让这种问题继续存在于产品中.
今天正在好遇到一个应用调整,以前曾担心过全表扫描会影响性能,但没有具体的对比数据,只是在猜测,结果表明没有根据的猜测,往往是错的.
下面,我通过一个实验来说明全表扫描更好.
通过这个实验,证明,利用Hash连接进行全表扫描,由于利用了多块读,比利用索引进行嵌套连接更好.
通过这个实验,证明,利用Hash连接进行全表扫描,通过更少的一致性读,减少栓锁,
再利用了多块读,在多用户并发下,将会比利用索引进行嵌套连接更好.
Autrace对比实验表明,
使用索引再使用嵌套连接,0块物理读,但逻辑读达75块
全表扫描再Hash连接,0块物理读,13块逻辑读
Hash连接是在UGA中进行,没有栓锁问题,在大量并发情况下,将会表现得更快.
SQL> Select a.名称, To_Char(a.登记日期, 'YYYY-MM-DD') 登记日期
2 From 用户目录 a, 用户分类信息 b
3 Where a.登记日期 > Sysdate - 60 And a.显示类型 = 1 And a.Id = b.用户id And b.用户基本类型 In (01, 02, 03) And
4 Rownum <= 5
5 Order By 登记日期 Desc;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=51)
1 0 SORT (ORDER BY) (Cost=4 Card=1 Bytes=51)
2 1 COUNT (STOPKEY)
3 2 NESTED LOOPS (Cost=2 Card=1 Bytes=51)
4 3 TABLE ACCESS (BY INDEX ROWID) OF '用户目录' (Cost=1 Card=1 Bytes=35)
5 4 INDEX (RANGE SCAN) OF '用户目录_IX_登记日期' (NON-UNIQUE) (Cost=2 Card=1)
6 3 TABLE ACCESS (BY INDEX ROWID) OF '用户分类信息' (Cost=1 Card=10 Bytes=160)
7 6 INDEX (UNIQUE SCAN) OF 'SYS_C002908' (UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
75 consistent gets
0 physical reads
0 redo size
735 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
5 rows processed
********************************************************************************
SQL> Select a.名称, To_Char(a.登记日期, 'YYYY-MM-DD') 登记日期
2 From 用户目录 a, 用户分类信息 b
3 Where a.显示类型 = 1 And a.Id = b.用户id And b.用户基本类型 In (01, 02, 03) And
4 Rownum <= 5
5 Order By 登记日期 Desc;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=1 Bytes=51)
1 0 SORT (ORDER BY) (Cost=5 Card=1 Bytes=51)
2 1 COUNT (STOPKEY)
3 2 HASH JOIN (Cost=3 Card=1 Bytes=51)
4 3 TABLE ACCESS (FULL) OF '用户分类信息' (Cost=1 Card=10 Bytes=160)
5 3 TABLE ACCESS (FULL) OF '用户目录' (Cost=1 Card=4 Bytes=140)
Statistics
----------------------------------------------------------
0 recursive calls
8 db block gets
5 consistent gets
0 physical reads
0 redo size
720 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
5 rows processed
10046Trace文件分析表明:
全表扫描在Fetch阶段,因为物理读0块,13块逻辑读数据,而多用了0.02毫秒
但相对于索引嵌套的75块逻辑读的几乎不消耗时间,虽然单用户实验下,慢了点,但多用户下可以避免大量栓锁带来的等待
(如果是生产机,性能更高,多块物理读将表现出比逻辑顺序读更少的时间)
********************************************************************************
Select a.名称, To_Char(a.登记日期, 'YYYY-MM-DD') 登记日期
From 用户目录 a, 用户分类信息 b
Where a.登记日期 > Sysdate - 60 And a.显示类型 = 1 And a.Id = b.用户id And b.用户基本类型 In (01, 02, 03) And
Rownum <= 5
Order By 登记日期 Desc
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.03 0 0 0 0
Execute 1 0.01 0.02 0 0 0 0
Fetch 2 0.00 0.00 0 75 0 5
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.03 0.05 0 75 0 5
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 92
Rows Row Source Operation
------- ---------------------------------------------------
5 SORT ORDER BY
5 COUNT STOPKEY
5 NESTED LOOPS
22 TABLE ACCESS BY INDEX ROWID 用户目录
25 INDEX RANGE SCAN (object id 29202)
5 TABLE ACCESS BY INDEX ROWID 用户分类信息
43 INDEX UNIQUE SCAN (object id 32295)
********************************************************************************
Select a.名称, To_Char(a.登记日期, 'YYYY-MM-DD') 登记日期
From 用户目录 a, 用户分类信息 b
Where a.显示类型 = 1 And a.Id = b.用户id And b.用户基本类型 In (01, 02, 03) And
Rownum <= 5
Order By 登记日期 Desc
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.03 0.03 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.01 0.02 0 5 8 5
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.04 0.05 0 5 8 5
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 92
Rows Row Source Operation
------- ---------------------------------------------------
5 SORT ORDER BY
5 COUNT STOPKEY
5 HASH JOIN
499 TABLE ACCESS FULL 用户分类信息
12 TABLE ACCESS FULL 用户目录
********************************************************************************
