Select from ranges, seek faster then Between solution

[random]

Hello!

I have a problem with sql, and trying to create the most efficient query in
speed terms, because the easiest solution I have implemented, is not
very fast.
It is not slow, if you use it once in a while, but the total time
is considerable if you call the same function,
thousands of times per day.

So, we have 2 tables: table Ranges with 50.000 records, and table Atoms with 2 to 30+ million records

the main fields we use...

Atoms:
------
atom_id number PRIMARY KEY
xcode   varchar2(10)

Ranges:
----------
range_id   number PRIMARY KEY
xcode_from varchar2(10)
xcode_to   varchar2(10)
xvalue    varchar2(100)

the atoms.xcode must be between ranges.xcode_from and ranges.xcode_to so, to get the
correct ranges.xvalue for every atoms.atom_id

so imagine, i have a batch processing job, with a cursor fetching every (or most of the) Atom records ,
and for every one, it calls a function than returns the corresponding XVALUE from Ranges.

Sample data:
Atoms:
------
atom_id xcode
1	5000000000
2	5500000001
3	5600000001
4	5600099999
5	1000000000
6	1000000000


Ranges:
----------
range_id   xcode_from   xcode_to  	xvalue
1	1000000000	2000000000	ABC
2	2000500000	3000000000	ACJKLJKLDJSLF
3	40000000AA	500000CCCC	EEEFD
4	5000000000	6000000000	PAPAKI
5	5500000000	5600000000	NIK
6	5600000000	5600000099	ZZZZZZZ
7	7000000000	9999999999	A22FFRREWRERWQ

so, the select statement, when i ask for the Xvalue of Atom #5, must return "ABC", from range_id #1,
since "1000000000" is between "1000000000"-"2000000000".

The easy, obvious statement is :

SELECT xvalue FROM Ranges WHERE "1000000000" BETWEEN xcode_from AND xcode_to;

ofcourse as we see from ranges 4, 5 and 6, there are ranges within ranges,
such is the case with range 5 and 6, which are included inside range 4. What we look for, the correct
answer is the inner range
so, the correct answer is , for the corresponding Atoms:
atom 1 with xcode 5000000000 is range_id 4 (5000000000-6000000000), xvalue PAPAKI
atom 2 with xcode 5500000001 is range_id 5 (5500000000-5600000000), xvalue NIK
atom 3 with xcode 5600000001 is range_id 6 (5600000000-5600000099), xvalue ZZZZZZZ
atom 4 with xcode 5600099999 is range_id 4 (5000000000-6000000000), xvalue PAPAKI


but this is no problem. the select may return more than 1 record, and with pl/sql I can sort out which is the inner range and
return it from my function.

My problem is the select Logic. With the "WHERE 1000000000 BETWEEN xcode_from AND xcode_to" it is slow.
I look for way to make it faster.

The db is Oracle 8.1.7
the tables have Indexes on all the fields used for the queries,
and have been analyzed.

I have the option / liberty to add more fields to table Ranges, if this would help in speeding the search.
but we cannot alter the Atoms table.

also the Ranges table, would not matter if it increased up to by 100% in number of records (from 50 to 100 thousand recs), if this would help, which is unlikely. I would think it must have less records to speed up searching.

I think the problem is in the logic, or the design of the tables and fields' data, and not in the code.


I s there a way to add a new field (a composite, a concatenation, or the result of some formula)
in table Ranges, that would make possible to search by = equality instead of BETWEEN,
and to have unique values, so every select would return 1 and only 1 record, even in the
case of inner/included ranges ???



thank you, just for the patience to read to the end

[LKBrwn_DBA]

Because the Ranges table is small , a quick solution would be to create the "Ranges" table as ORGANIZATION INDEX with CACHE option.