Re: Finding matching ranges

I found the following with a very nice execution plan:

set echo on
set linesize 1000
set pagesize 0
set trimspool on

drop table tab;

create table tab (dat varchar2 (50), cmp number); create unique index tab_pk on tab (cmp);

insert into tab (dat, cmp) values ('abc', 11);
insert into tab (dat, cmp) values ('def', 21);
insert into tab (dat, cmp) values ('ghi', 31);
insert into tab (dat, cmp) values ('jkl', 41);
insert into tab (dat, cmp) values ('mno', 51);
insert into tab (dat, cmp) values ('pqr', 61);
insert into tab (dat, cmp) values ('stu', 71);
insert into tab (dat, cmp) values ('vwx', 81);
insert into tab (dat, cmp) values ('yz', 91);

set autotrace on

variable givenvalue number
execute :givenvalue:= 55

select	--+ index_desc (t)
	dat
	, cmp
from	tab t
where	1 = 1
and	:givenvalue >= t.cmp
and	rownum <= 1

It gives

mno                                                        51

Execution Plan

   0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=9 Bytes=360)    1 0 COUNT (STOPKEY)

   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'TAB' (Cost=1 Card=9 By
          tes=360)

   3    2       INDEX (RANGE SCAN DESCENDING) OF 'TAB_PK' (UNIQUE) (Co
          st=2 Card=9)

which should be okay.

Martin

Martin Drautzburg wrote:
>
> I have a table with to columns x and y that describe range
> boundaries. I want to find all rows, whose ranges cover a given value,
> e.g.
>
> select * from table
> where givenValue betwen x and y
> ;
>
> Now the question is: how can I do this fast ? Typically oracle would
> use an index range scan on either x or y and compare ALL the resulting
> rows against givenValue (this could be all the rows in the table).
>
> I was wondering If I could use a redundant column "g" to speed things
> up. Ideally I would transform the problem
>
> x < given Value < y
>
> to something like
>
> f1(givenValue) < g(x,y) < f2(givenValue).
>
> This would reduce the problem to a simple range scan on the redundant
> column g(x,y). But I have no idea what functions f1, f2 and g to use
> (apart from the obvious trivial solution f1=0, g=1, f2=2, which would
> be logically correct, but would provide absolutely no selectivity).
Received on Sat Jun 09 2001 - 01:10:42 CEST