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!

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

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