(create_ts_min,create_ts_max,run_id from,run_id to)

drop table ane_logkeys purge;
drop table ane_logdata purge;
drop sequence ane_log$sq;
create table ane_logdata(run_id integer, create_ts timestamp(3), somedata varchar2(100))
partition by range(run_id) interval (10)
(partition p_base values less than (1) segment creation deferred);
create table ane_logkeys(run_id_min integer, run_id_max integer
, create_ts_from timestamp(3)
, create_ts_to timestamp(3)
);
create sequence ane_log$sq;

var v_run_id number
var v_create_ts varchar2(100)
alter session set nls_timestamp_tz_format='yyyy-mm-dd hh24:mi:ss.ff3';
alter session set nls_timestamp_format='yyyy-mm-dd hh24:mi:ss.ff3';
exec select ane_log$sq.nextval, systimestamp into :v_run_id, :v_create_ts from dual;
merge into ane_logkeys t
using (select :v_run_id v_run_id, to_timestamp(:v_create_ts) v_create_ts from dual) s
on (v_create_ts between t.create_ts_from and create_ts_to)
when matched then
update set run_id_min=least(run_id_min, v_run_id)
, run_id_max=greatest(run_id_max, v_run_id)
when not matched then
insert (run_id_min, run_id_max, create_ts_from, create_ts_to)
values (v_run_id, v_run_id, trunc(v_create_ts,'DD'), trunc(v_create_ts,'DD')+1-1/86400)
;
insert into ane_logdata(run_id, create_ts, somedata)
select :v_run_id, to_timestamp(:v_create_ts), 'value #'||rownum||' for run_id='||:v_run_id||' and create_ts='||:v_create_ts
from dual connect by level < 1000
;

explain plan for
with t(run_id_min,run_id_max) as (select min(run_id_min), max(run_id_max) from ane_logkeys where CREATE_TS_FROM between :1 and :2)
select count(*) from t, ane_logdata d where d.run_id between t.run_id_min and t.run_id_max
and d.create_ts between :1 and :2;


set linesize 1000
col PLAN_TABLE_OUTPUT format a300

select * from table(dbms_xplan.display(format => 'ALL'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2951821927
----------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |             |     1 |    52 |    17   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE            |             |     1 |    52 |            |          |       |       |
|   2 |   NESTED LOOPS             |             |     1 |    52 |    17   (0)| 00:00:01 |       |       |
|   3 |    VIEW                    |             |     1 |    26 |     3   (0)| 00:00:01 |       |       |
|*  4 |     FILTER                 |             |       |       |            |          |       |       |
|   5 |      SORT AGGREGATE        |             |     1 |    39 |            |          |       |       |
|*  6 |       FILTER               |             |       |       |            |          |       |       |
|*  7 |        TABLE ACCESS FULL   | ANE_LOGKEYS |     1 |    39 |     3   (0)| 00:00:01 |       |       |
|   8 |    PARTITION RANGE ITERATOR|             |     1 |    26 |    14   (0)| 00:00:01 |   KEY |   KEY |
|*  9 |     TABLE ACCESS FULL      | ANE_LOGDATA |     1 |    26 |    14   (0)| 00:00:01 |   KEY |   KEY |
----------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   1 - SEL$2
   3 - SEL$1 / T@SEL$2
   4 - SEL$1
   7 - SEL$1 / ANE_LOGKEYS@SEL$1
   9 - SEL$2 / D@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter(TO_TIMESTAMP(:1)<=TO_TIMESTAMP(:2))
   6 - filter(TO_TIMESTAMP(:1)<=TO_TIMESTAMP(:2))
   7 - filter("CREATE_TS_FROM">=TO_TIMESTAMP(:1) AND "CREATE_TS_FROM"<=TO_TIMESTAMP(:2))
   9 - filter("D"."CREATE_TS">=TO_TIMESTAMP(:1) AND "D"."CREATE_TS"<=TO_TIMESTAMP(:2) AND
              "D"."RUN_ID">="T"."RUN_ID_MIN" AND "D"."RUN_ID"<="T"."RUN_ID_MAX")
Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - (#keys=0) COUNT(*)[22]
   2 - (#keys=0)
   3 - "T"."RUN_ID_MIN"[NUMBER,22], "T"."RUN_ID_MAX"[NUMBER,22]
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   4 - MAX("RUN_ID_MAX")[22], MIN("RUN_ID_MIN")[22]
   5 - (#keys=0) MAX("RUN_ID_MAX")[22], MIN("RUN_ID_MIN")[22]
   6 - "RUN_ID_MIN"[NUMBER,22], "RUN_ID_MAX"[NUMBER,22]
   7 - "RUN_ID_MIN"[NUMBER,22], "RUN_ID_MAX"[NUMBER,22]
Note
-----
   - dynamic sampling used for this statement (level=2)

SQL>