Re: Indexes with WHERE ... BETWEEN

Hi,

	Create 2 indexes,
		beg_col	beg_col_i
		end_col	end_col_i

Try to play with the hints.

        select /*+ AND-EQUAL(beg_col_i end_col_i ) index_desc( diaproc end_col_i )*/

		diag_code
	from diagproc
	where beg_code >= :h_code
	and end_code <= :h_code

The ideal situation is a

select

	and_equal
		index range scan		beg_col_i
		index_desc range scan		end_col_i

The between is replace by:

	select 
		diag_code
	from diaprog
	where beg_code >= :h_code -- range index scan OK
	and end_code <= :h_code -- Without desc in the hint index not used !

-- 
Regards LANGE Francois.
flange_at_pt.lu
29A Route du vin.
L5450 Stadtbredimus.
Grand Duchy Of Luxembourg.
TEL (int) 352 697412.(*)
CEL(int) 352 021193652(*)
Phone numbers are variable length in Luxembourg.
Grand Duchy Of Luxembourg is a small country 80 miles on 60 miles between
France,Germany and Belgium.

David North <dnorth_at_inquo.net> wrote in article
<63975h$4h1$1_at_quartz.inquo.net>...


> 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?

> 

> 

>