select distinct who
from access a
where
  room = :room and
  :start <= revoke and grant <= :end;

drop table expla1_tmp;
drop table expla2_tmp;
create table expla1_tmp
(
  oid number,
  frd date,
  tod date
);

create table expla2_tmp
(
  oid number,
  frd date,
  op char( 1 )
);


insert into expla1_tmp
(select level, sysdate - level, sysdate - level +  1 
from dual
connect by level <  1000000 );

insert into expla2_tmp
select oid, frd, 'B'
from expla1_tmp
union all
select oid, tod, 'E'
from expla1_tmp
;

commit;
set timing on
select oid
  from
    expla1_tmp
  where
    sysdate- 5  between frd and tod;

select oid
  from
    expla2_tmp m
  where
    op = 'B'
    and (oid, frd) in
    ( select i.oid, max(i.frd)
        from
          expla2_tmp i
        where
          i.frd <= sysdate- 5 
        group by i.oid
    )
union
select oid
  from
    expla2_tmp m
  where
    op = 'B'
    and frd between sysdate- 5  and sysdate- 4 ;

SQL> set timing on
SQL> select oid
  2    from
  3      expla1_tmp
  4    where
  5      sysdate-5 between frd and tod;

no rows selected

Elapsed: 00:00:00.57
SQL> 

create index expla1_ix_tmp
on expla1_tmp(oid, frd, tod);

create index expla2_ix_tmp
on expla2_tmp(oid, frd);


set timing on
select oid
  from
     expla1_tmp
  where
    sysdate- 5  <= tod and frd <= sysdate- 4 ;

       OID
----------
         4
         5

Elapsed: 00:00:01.04

SQL> 
SQL> select oid
  2    from
  3      expla2_tmp m
  4    where
  5      op = 'B'
  6      and (oid, frd) in
  7      ( select i.oid, max(i.frd)
  8          from
  9            expla2_tmp i
 10          where
 11            i.frd <= sysdate-5
 12          group by i.oid
 13      )
 14  union
 15  select oid
 16    from
 17      expla2_tmp m
 18    where
 19      op = 'B'
 20      and frd between sysdate-5 and sysdate-4;

       OID
----------
         4
         5

Elapsed: 00:00:31.64