Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Index Full Scan Vs. Index Range Scan
Viral Desai wrote:
>Hi Herman,
[stuff deleted]
Viral,
A hash join doesnot make sense in this situation, because the copa_mdo_srce_sys_map (cm alias) table is pruning the partitions. Note that for range partitioning, the leading columns must be restricted somewhere in the query.
When the use_nl hint has been replaced by use_hash, it is no longer possible to use the contents of the cm for pruning partitions. Hence, all partitions will be investigated.
Personally, I think that a FTS on the fact_dmnsn_q3 table would be faster than the index scan. The hash size is sufficient enough; the cm table is very small. Give it a try, specify the full(fd) hints instead of the index hint.
Kind Regards,
Herman de Boer
sr. consultant
IT Consultancy Group bv
Received on Wed Aug 14 2002 - 02:42:12 CDT