基本环境
racle10g release 1
windows 2003
有两个表,一个表叫ORD,它是一个CLUSTER表,位于CLUSTER名为ord$cluster内,大约在100万数据,
另外一个表叫ctrpty,是非CLUSTER表,大约有数据200.现在两个表要连接,即一个
cluster表和非cluster连接
Select a.ordseq, a.ordno, b.lname client, a.orddt, a.season
from ord a, ctrpty b
where b.code(+)= a.ctrpty and a.ordty
IN ('R','T','G','P') AND A.STFG = 'A';
37547 rows selected.
Elapsed: 00:03:37.65
Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=ALL_ROWS (Cost=38314 Card=29159 B
ytes=2186925)
10 HASH JOIN (RIGHT OUTER) (Cost=38314 Card=29159 Bytes=21869
25)
21 TABLE ACCESS (FULL) OF 'CTRPTY' (TABLE) (Cost=10 Card=94
3 Bytes=29233)
31 TABLE ACCESS (FULL) OF 'ORD' (CLUSTER) (Cost=38304 Card=
29159 Bytes=1282996)
Statistics
----------------------------------------------------------
325recursive calls
0db block gets
175713consistent gets
170496physical reads
668redo size
2555291bytes sent via SQL*Net to client
28045bytes received via SQL*Net from client
2505SQL*Net roundtrips to/from client
22sorts (memory)
0sorts (disk)
37547rows processed
大表ORD的连接列ctrpty上有一个索引名叫ORD$IDX1,因为这是个一大表和一个小
表连接,而已大表的连接列上有索引,因为我想到了用nest loop,因此加了hint如下
Select /*+ruleleading(b) use_nl(a b)*/
a.ordseq, a.ordno, b.lname client, a.orddt, a.season
fromord a, ctrpty b
whereb.code (+)= a.ctrpty and a.ordty
IN ('R','T','G','P') AND A.STFG = 'A' ;
37541 rows selected.
Elapsed: 00:04:48.82
Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=HINT: RULE
10 NESTED LOOPS (OUTER)
21 TABLE ACCESS (BY INDEX ROWID) OF 'ORD' (CLUSTER)
32 INDEX (RANGE SCAN) OF 'ORD$IDX9' (INDEX)
41 TABLE ACCESS (BY INDEX ROWID) OF 'CTRPTY' (TABLE)
54 INDEX (RANGE SCAN) OF 'CTRPTY$PK' (INDEX)
Statistics
----------------------------------------------------------
1recursive calls
0db block gets
132272consistent gets
47396physical reads
72redo size
2554907bytes sent via SQL*Net to client
28034bytes received via SQL*Net from client
2504SQL*Net roundtrips to/from client
0sorts (memory)
0sorts (disk)
37541rows processed
发现效果不理想,而且还有一个奇怪的现象,就是开始的前一小部分数据很快就出来了,但以后的数据很难出来.
我发现到了,它所用的索引不是用连接列上的索引,而是其它cluster列上的索引,我如何加hint都用不上
|
