drop table edge purge;
create table edge (nullable number, min_val number not null, max_val number not null);
insert into edge select rownum, rownum-10, rownum+10 from dual connect by level<100000;
create index edge_min on edge(min_val);
create index edge_max on edge(max_val);
create index edge_min_max on edge(min_val,max_val);
create index edge_max_min on edge(max_val,min_val);
begin
  dbms_stats.gather_table_stats(ownname => user, tabname => 'EDGE', estimate_percent => 100, degree => 8, cascade => true);
end;

commit;

set autotrace traceonly
select count(nullable) from edge t where 50000 between min_val and max_val;

Затрач.время: 00:00:00.04

План выполнения
----------------------------------------------------------
Plan hash value: 3004890546

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    15 |    71   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |    15 |            |          |
|*  2 |   TABLE ACCESS FULL| EDGE | 25011 |   366K|    71   (2)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("MIN_VAL"<=50000 AND "MAX_VAL">=50000)

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold


Статистика
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        174  consistent gets
          0  physical reads
          0  redo size
        357  bytes sent via SQL*Net to client
        360  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed



select /*+ index(t edge_min_max) */ count(nullable) from edge t where 50000 between min_val and max_val;

Затрач.время: 00:00:00.04

План выполнения
----------------------------------------------------------
Plan hash value: 720509129

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |     1 |    15 |   108   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE              |              |     1 |    15 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| EDGE         | 25011 |   366K|   108   (1)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | EDGE_MIN_MAX | 25011 |       |    73   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("MAX_VAL">=50000 AND "MIN_VAL"<=50000)
       filter("MAX_VAL">=50000)

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold


Статистика
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         73  consistent gets
          0  physical reads
          0  redo size
        357  bytes sent via SQL*Net to client
        360  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed