rt
我在一个表中建了位图索引,月份字段为a('yyyy-mm')
当查询条件用between的时候查询计划就是全表扫描,当用in的时候就会使用索引.
具体如下:
查询语句:
1、使用IN的查询
Select Al1.Aae043, Al1.Nlms, Al1.Qzrs, Al1.Zprs, Al1.Gzms, Al3.Whcd, Al4.Jsdj, Al5.Xb, Al2.Xzqhmc
From Gdlddw.Dw_Gqgx Al1, Gdlddw.v_Zyjs_Xzqh Al2, Gdlddw.v_Zyjs_Whcd Al3, Gdlddw.v_Zyjs_Jsdj Al4, Gdlddw.v_Zyjs_Xb Al5
Where (Al3.Whcddm = Al1.Aac011 And Al4.Jsdjdm = Al1.Aac015 And Al5.Xbdm = Al1.Aac004 And Al2.Xzqhdm = Al1.Bae001)
And Al1.Aae043 In ('2006-09', '2006-10', '2006-11', '2006-12')
计划:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=427 Card=4669 Bytes= 1302651)
1 0 VIEW (Cost=427 Card=4669 Bytes=1302651)
2 1 SORT (UNIQUE) (Cost=427 Card=4669 Bytes=1536101)
3 2 HASH JOIN (Cost=202 Card=4669 Bytes=1536101)
4 3 TABLE ACCESS (FULL) OF 'AA10' (Cost=4 Card=29 Bytes= 2552)
5 3 HASH JOIN (Cost=197 Card=653 Bytes=157373)
6 5 TABLE ACCESS (BY INDEX ROWID) OF 'AA10' (Cost=3 Card=29 Bytes=1914)
7 6 INDEX (RANGE SCAN) OF 'PK_AA10' (UNIQUE) (Cost=2 Card=99)
8 5 HASH JOIN (Cost=193 Card=229 Bytes=40075)
9 8 TABLE ACCESS (BY INDEX ROWID) OF 'AA10' (Cost=3 Card=29 Bytes=1711)
10 9 INDEX (RANGE SCAN) OF 'PK_AA10' (UNIQUE) (Cost=2 Card=99)
11 8 HASH JOIN (Cost=189 Card=368 Bytes=42688)
12 11 TABLE ACCESS (FULL) OF 'X_ZHCS' (Cost=2 Card=7 Bytes=511)
13 11 TABLE ACCESS (BY INDEX ROWID) OF 'DW_GQGX' (Cost=186 Card=851 Bytes=36578)
14 13 BITMAP CONVERSION (TO ROWIDS)
15 14 BITMAP AND
16 15 BITMAP MERGE
17 16 BITMAP KEY ITERATION
18 17 TABLE ACCESS (FULL) OF 'AA10' (Cost=4 Card=29 Bytes=2552)
19 17 BITMAP INDEX (RANGE SCAN) OF 'AAC004_IDX'
20 15 BITMAP MERGE
21 20 BITMAP KEY ITERATION
22 21 TABLE ACCESS (BY INDEX ROWID) OF 'AA10' (Cost=3 Card=29 Bytes=1914)
23 22 INDEX (RANGE SCAN) OF 'PK_AA10' (UNIQUE) (Cost=2 Card=11)
24 21 BITMAP INDEX (RANGE SCAN) OF 'AAC015_IDX'
25 15 BITMAP MERGE
26 25 BITMAP KEY ITERATION
27 26 TABLE ACCESS (BY INDEX ROWID) OF 'AA10' (Cost=3 Card=29 Bytes=1711)
28 27 INDEX (RANGE SCAN) OF 'PK_AA10' (UNIQUE) (Cost=2 Card=11)
29 26 BITMAP INDEX (RANGE SCAN) OF 'AAC011_IDX'
30 15 BITMAP OR
31 30 BITMAP INDEX (SINGLE VALUE) OF 'AAE043_IDX'
32 30 BITMAP INDEX (SINGLE VALUE) OF 'AAE043_IDX'
33 30 BITMAP INDEX (SINGLE VALUE) OF 'AAE043_IDX'
34 30 BITMAP INDEX (SINGLE VALUE) OF 'AAE043_IDX'
2、使用between的查询
Select Al1.Aae043, Al1.Nlms, Al1.Qzrs, Al1.Zprs, Al1.Gzms, Al3.Whcd, Al4.Jsdj, Al5.Xb, Al2.Xzqhmc
From Gdlddw.Dw_Gqgx Al1, Gdlddw.v_Zyjs_Xzqh Al2, Gdlddw.v_Zyjs_Whcd Al3, Gdlddw.v_Zyjs_Jsdj Al4, Gdlddw.v_Zyjs_Xb Al5
Where (Al3.Whcddm = Al1.Aac011 And Al4.Jsdjdm = Al1.Aac015 And Al5.Xbdm = Al1.Aac004 And Al2.Xzqhdm = Al1.Bae001) and
(1 = 1 And Al1.Aae043 Between '2002-01' And '2007-01')
查询计划为:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=152469 Card=2082031
Bytes=580886649)
1 0 VIEW (Cost=152469 Card=2082031 Bytes=580886649)
2 1 SORT (UNIQUE) (Cost=152469 Card=2082031 Bytes=684988199)
3 2 HASH JOIN (Cost=251 Card=2082031 Bytes=684988199)
4 3 TABLE ACCESS (FULL) OF 'AA10' (Cost=4 Card=29 Bytes=
2552)
5 3 HASH JOIN (Cost=237 Card=291295 Bytes=70202095)
6 5 TABLE ACCESS (BY INDEX ROWID) OF 'AA10' (Cost=3 Ca
rd=29 Bytes=1914)
7 6 INDEX (RANGE SCAN) OF 'PK_AA10' (UNIQUE) (Cost=2
Card=99)
8 5 HASH JOIN (Cost=231 Card=101917 Bytes=17835475)
9 8 TABLE ACCESS (BY INDEX ROWID) OF 'AA10' (Cost=3
Card=29 Bytes=1711)
10 9 INDEX (RANGE SCAN) OF 'PK_AA10' (UNIQUE) (Cost
=2 Card=99)
11 8 HASH JOIN (Cost=225 Card=164225 Bytes=19050100)
12 11 TABLE ACCESS (FULL) OF 'X_ZHCS' (Cost=2 Card=7
Bytes=511)
13 11 TABLE ACCESS (FULL) OF 'DW_GQGX' (Cost=220 Car
d=12972 Bytes=557804)
还有个问题,查询的时候很多递归SQL,不知道为什么? 使用位图索引也有112 recursive calls,不知道为什么?
谢谢!