Re: Indexes with WHERE ... BETWEEN

David North wrote:
>
> I have a table in Oracle 7.3 that has a beg_code/end_code column pair
> containing medical codes. I want to return all rows where a certain code is
> between beg_code/end_code pairs.
>
> ex: SELECT diag_code FROM diagproc WHERE :h_code
> BETWEEN beg_code AND end_code
>
> I have attempted to index this table based on the beg_code,end_code columns.
> One of the things I have noted is that the CBO will process for some values
> >25,000 index rows that feed into a table access that returns the desired
> number of rows (often around 45).
>
> This table contains >65,000 rows and will grow. What is an appropriate
> index strategy to to eliminate the fairly intense number of index range
> scans?
>
> Any help would be appreciated! Is there any general guidelines on index
> strategies when using the BETWEEN operator in a WHERE condition?
 David North wrote:
>
> I have a table in Oracle 7.3 that has a beg_code/end_code column pair
> containing medical codes. I want to return all rows where a certain code is
> between beg_code/end_code pairs.
>
> ex: SELECT diag_code FROM diagproc WHERE :h_code
> BETWEEN beg_code AND end_code
>
> I have attempted to index this table based on the beg_code,end_code columns.
> One of the things I have noted is that the CBO will process for some values
> >25,000 index rows that feed into a table access that returns the desired
> number of rows (often around 45).
>
> This table contains >65,000 rows and will grow. What is an appropriate
> index strategy to to eliminate the fairly intense number of index range
> scans?
>
> Any help would be appreciated! Is there any general guidelines on index
> strategies when using the BETWEEN operator in a WHERE condition?

"Between" is always resolved into > and < conditions by the parser.
Did you create one index on beg_code,end_code or one on each of beg_code and end_code? Actually, it probably won't make a difference. You could create a simple hashing type function based on beg_code and end_code and populate a column with the hashed value. Apply a similar function to :h_code to drive the search.
Any function used wouldn't actually have to exactly identify the correct rows, perhaps just narrow it down so that the "between" operates on the result set and not on the indexes.
Very general answer, I now, but it's hard to find a proper approach without more details.
If it's still a problem, send some more info. It's an interesting tuning problem.

Cheers,
Terry Received on Sat Nov 01 1997 - 00:00:00 CST