Re: select value within an interval

Vladimir.Usov_at_gmail.com wrote:
> Hi Daniel
> Well the goal is to create an efficient SQL :-) that requires minimum
> of consistent gets.
> I need to get a product type from range of products based on particular
> product id.
> It is like salary table in Oracle HR Demo Scheme that stores salary
> ranges and if I want to report to manager salary grade I have to select
> emp.salary between low_value and high_value.
> Any idea why Oracle reads the whole table or does index FFS if I use
> INDEX hint?
> Does Oracle have problem with such selects?
> Regards
> Vladimir

Oracle doesn't "have a problem" but you seem to be making the assumption that reading the whole table is a bad idea. Might be ... might not be. The optimizer, based on the information it has available, certainly thinks it is taking the lowest cost path. I don't see any history of this thread above so it is hard to be too specific but I would suggest two things:

1. Explain Plan
2. If you are going to hint "fully" hint as recommended by Jonathan Lewis.

Be sure that the information you are providing to the optimizer is what it needs to make a good decision.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)