Re: Indexes with WHERE ... BETWEEN

David,

If your table is not already clustered on another key, you might try creating a cluster on
diag_code. I'm not sure if this will improve the performance, but it's worth a try and not hard to do while your table is still small. But be sure to collect performance stats, execution plans, etc. before creating the cluster so you'll have a benchmark.

I hope some of the experts in this group weigh in on this question.

Hope this helps,

Ken

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?
  Received on Thu Oct 30 1997 - 00:00:00 CST