Hi!
Is it possible to create an index with some kind of "between" option?
I have a dimension-table with 30 columns where the unique key is a combination of 2 columns (from_minute, to_minute).
Primary key is a unique sequence.
Total number of rows is 5000.
A simplifying example:
SEKVNR FROM_MINUTE TO_MINUTE
1 -999999 -20000
2 -19999 -10
3 -9 0
4 1 1
5 2 2
6 3 3
7 4 10
8 11 99999
9 1000000 999999
Using a "normal" index is just to slow when you have this where clause: "where my_minute between FROM_MINUTE and TO_MINUTE".
How can I build an index so Oracle can find the correct record fast?
Like if my_minute=8 it would find sekvnr=7 in just a nano second :)
Best regards
Exactly how fast is the current query? And what exactly are you doing that means this is not fast enough?
Anyway, a basic index on ( from_minute, to_minute ) should help:
create table t as
with rws ( sekvnr, from_minute, to_minute ) as (
select 1 sekvnr, -99999 from_minute, -20000 to_minute from dual union all
select 2 sekvnr, -19999 from_minute, -10 to_minute from dual union all
select 3 sekvnr, -9 from_minute, 0 to_minute from dual union all
select 4 sekvnr, 1 from_minute, 1 to_minute from dual union all
select 5 sekvnr, 2 from_minute, 2 to_minute from dual union all
select 6 sekvnr, 3 from_minute, 3 to_minute from dual union all
select 7 sekvnr, 4 from_minute, 10 to_minute from dual union all
select 8 sekvnr, 11 from_minute, 99999 to_minute from dual union all
select 9 sekvnr, 1000000 from_minute, 999999 to_minute from dual
)
select * from rws;
create index i
on t ( from_minute, to_minute );
select * from t
where 8 between from_minute and to_minute;
select *
from table(dbms_xplan.display_cursor(null, null, 'IOSTATS LAST'));
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 2 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 1 | 2 | 1 |00:00:00.01 | 2 |
|* 2 | INDEX RANGE SCAN | I | 1 | 2 | 1 |00:00:00.01 | 1 |
------------------------------------------------------------------------------------------------------
Note that the A(ctual)-time column only has a resolution of hundredths of a second. In reality the query is much faster. A little under 100 microseconds in this example for me.