Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Index Full Scan Vs. Index Range Scan

Re: Index Full Scan Vs. Index Range Scan

From: Herman de Boer <h.de.boer_at_itcg.nl>
Date: Wed, 14 Aug 2002 07:42:12 GMT
Message-ID: <ajd1l5$qre$1@news1.xs4all.nl>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US