Re: index range scan vs. an index full scan

The shortest possible answer to your question is "optimizer". Slightly longer answer to your question is the following: 0) CBO selects possible plans.
1) CBO looks for histograms and and values in the query to determine the fraction

   of the values in index you are trying to retrieve. If it cannot find histograms,    or if bind variables are used, CBO assumes (to my knowledge) that you are trying    to retrieve 25% of the table.
2) Based on the values from the step 1, CBO calculates how many table I/O requests and

   do how many index I/O requests you need for any of your plans. 3) CBO calculates the costs of the I/O requests in accordance with the instance parameters. 4) If the version supports CPU costing and if the system statistics has been gathered, CPU

   costs for each plan are wighed in.
5) The cheapest plan is returned.
You can also try enabling the event 10053, level 10, to see what exactly is going on with your optimizer.

On 05/04/2004 11:59:25 AM, ryan.gaffuri_at_cox.net wrote:
> Sometimes when oracle performs a join it does an index range scan which tend be perform well.
>
> However, sometimes oracle performs an index full scan which does not perform well.
>
> what influences oracles decision in choose what type of index scan to use?
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>

-- 
Mladen Gogala
Oracle DBA



Note:
This message is for the named person's use only.  It may contain confidential, proprietary or legally privileged information.  No confidentiality or privilege is waived or lost by any mistransmission.  If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender.  You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks.
Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity.

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------