VIEW OF 'index$_join$_002'

p570 oracle9204 rbo

SQL> set autotrace traceonly;

select a.czrkrybh,a.czrkxm,b.czrkpcs from t_person a ,t_huji b

where a.czrkryid=b.czrkryid and a.czrkgmsfhm='510214197501131748';

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=48337 Card=274445 By

tes=20583375)

1 0 HASH JOIN (Cost=48337 Card=274445 Bytes=20583375)

2 1 TABLE ACCESS (BY INDEX ROWID) OF 'T_PERSON' (Cost=2 Card

=272416 Bytes=17707040)

3 2 INDEX (RANGE SCAN) OF 'IND_T_PERSON_CZRKGMSFHM' (NON-U

NIQUE) (Cost=1 Card=108966)

4 1 VIEW OF 'index$_join$_002' (Cost=34791 Card=39567074 Byt

es=395670740)

5 4 HASH JOIN (Cost=48337 Card=274445 Bytes=20583375)

6 5 INDEX (FAST FULL SCAN) OF 'IND_T_HUJI_CZRKRYID' (NON

-UNIQUE) (Cost=33 Card=39567074 Bytes=395670740)

7 5 INDEX (FAST FULL SCAN) OF 'IND_T_HUJI_PCS' (NON-UNIQ

UE) (Cost=33 Card=39567074 Bytes=395670740)

Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

177037 consistent gets

491176 physical reads

0 redo size

645 bytes sent via SQL*Net to client

655 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

加这个后：alter session set "_INDEX_JOIN_ENABLED"=FALSE;

原来有些快的反而变慢了:(

--目前办法：

ANALYZE TABLE tablename ESTIMATE STATISTICS FOR ALL INDEXED COLUMNS SIZE 200；

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

转载于:http://blog.itpub.net/936/viewspace-60724/