Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Jan.

Asked: February 03, 2020 - 10:30 am UTC

Answered by: Chris Saxon - Last updated: February 07, 2020 - 9:58 am UTC

Category: Database Development - Version: 12.2

Viewed 100+ times

You Asked

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

and we said...

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.

and you rated our response

  (3 ratings)

Reviews

Speed example

February 04, 2020 - 3:39 pm UTC

Reviewer: Jan Niemi from Sweden

Thanks for the answer. Even if the answer where "no" :-)

In my reality we make a select of 100.000 rows every hour.
Making a join to this "from_minute / to_minute"-table. (twice)
It is an outer join.
This will take 22 seconds.

IF i change the join to just "my_minute = from_minute" (not between)
it will only take 2 seconds.

So … I was just hoping there was some magic solution in Oracle for this.

Thanks anyway!
Chris Saxon

Followup  

February 04, 2020 - 6:12 pm UTC

I don't understand where the 22 seconds comes from?

It's likely you can get the query much faster than that. Particularly if the equality condition runs in 2 seconds. But we'll need to see the plan!

Share the execution plan for your query with us and we'll see how we can help further.

For details on how to do this, read https://blogs.oracle.com/sql/how-to-create-an-execution-plan


This is related to START_DATE, END_DATE -problem

February 06, 2020 - 1:33 pm UTC

Reviewer: lh from finland

Hi


This same kind of behaviour is also on tables where are columns START_DATE, END_DATE and You want to find out row that is between START_DATE, END_DATE.

In these situations it is quite common, that index by column order (END_DATE,START_DATE) is faster that (START_DATE,END_DATE).

This is due to the fact that typically it is queried recent dates.
If index is by (START_DATE, END_DATE) most of index entries do satisfy the criteria START_DATE <= :parameter_value. All of these index entries must then be scanned to check END_DATE.



If (END_DATE, START_DATE) is used, then typically much less index entries satisfy criteria :parameter_value <= END_DATE and so there is less work to be done with criteria START_DATE <= :parameter_value.

This is quite common design error (?), which can have effects when number of these rows grows. Luckily with current machines number of rows has to be quite big and usage of these statements great in order it to have noticable effects.


Having constraint START_DATE <= END_DATE does not help.



If there somehow could be defined that these periods START_DATE, END_DATE cannot overlap, then optimizer could understand to stop scanning index entries when it finds first row satisfying these criteria or finding rows out of scope.
But maybe sometimes.



lh
Chris Saxon

Followup  

February 07, 2020 - 9:58 am UTC

Great point.

If there somehow could be defined that these periods START_DATE, END_DATE cannot overlap, then optimizer could understand to stop scanning index entries when it finds first row satisfying these criteria or finding rows out of scope.

Temporal constraints to do things like this are part of the SQL standard. Maybe we'll get them one day!

If no overlaps...

February 08, 2020 - 9:18 am UTC

Reviewer: Stew Ashton from France

If there are no overlaps and there is an index on (FROM*, TO*) or (START*, END*), we can efficiently find the maximum FROM or START that interests us, then double-check the TO or END part.
SQL> create table t(start_date, end_date, filler) as
  2  select date '1970-01-01' + level start_date, date '1970-01-01' + level + 1 end_date,
  3  rpad('x',100,'x') filler
  4  from dual
  5  connect by level <= 100000;

Table T created.

SQL> create index ti on t(start_date, end_date);

Index TI created.

SQL> select /*+ gather_plan_statistics */ * from t
  2  where start_date = (
  3    select max(start_date) from t
  4    where start_date <= sysdate
  5  )
  6  and sysdate < end_date;

START_DATE          END_DATE            FILLER                                                                                              
------------------- ------------------- ------...
2020-02-08 00:00:00 2020-02-09 00:00:00 xxxxxx...

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST +PREDICATE'));

PLAN_TABLE_OUTPUT
-----------------
... 
-----------------------------------------------------------------------------------------
| Id  | Operation                           | Name | Starts | E-Rows | A-Rows | Buffers |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |      |      1 |        |      1 |       5 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T    |      1 |      1 |      1 |       5 |
|*  2 |   INDEX RANGE SCAN                  | TI   |      1 |      1 |      1 |       4 |
|   3 |    SORT AGGREGATE                   |      |      1 |      1 |      1 |       2 |
|   4 |     FIRST ROW                       |      |      1 |      1 |      1 |       2 |
|*  5 |      INDEX RANGE SCAN (MIN/MAX)     | TI   |      1 |      1 |      1 |       2 |
-----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("START_DATE"= AND "END_DATE">SYSDATE@! AND "END_DATE" IS NOT NULL)
   5 - access("START_DATE"<=SYSDATE@!)

Best regards,
Stew

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database