select sum(ostatok) from hist where dt_start<=:date and dt_end>:date;

SQL> create table hist(kod integer,dt date);

Таблица создана.

SQL> create index ix_hist on hist(dt);

Индекс создан.

SQL> 
SQL> insert into hist(kod, dt)
   2   select lv, sysdate-lv from
   3   (select level as lv from dual connect by level <= 1000000 ) pivot;

 1000000  строк создано.

SQL> 
SQL> set autotrace on   
SQL> 
SQL> SELECT kod FROM hist t1
   2   WHERE t1.dt IN (SELECT max(t2.dt) FROM hist t2
   3                    WHERE t2.dt < sysdate -  1000 );

       KOD
----------
       1000 


План выполнения
----------------------------------------------------------

-------------------------------------------------------------------------------
| Id  | Operation                      | Name    | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------
|    0  | SELECT STATEMENT               |         |      1  |     22  |      7    ( 0 )|
|    1  |  TABLE ACCESS BY INDEX ROWID   | HIST    |      1  |     22  |      5    ( 0 )|
|*   2  |   INDEX RANGE SCAN             | IX_HIST |   4005  |       |      1    ( 0 )|
|    3  |    SORT AGGREGATE              |         |      1  |      9  |            |
|    4  |     FIRST ROW                  |         |  1001K|  8799K|      2    ( 0 )|
|*   5  |      INDEX RANGE SCAN (MIN/MAX)| IX_HIST |  1001K|  8799K|      2    ( 0 )|
-------------------------------------------------------------------------------

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

    2  - access("T1"."DT"= (SELECT MAX("T2"."DT") FROM "HIST" "T2" WHERE
              "T2"."DT"<SYSDATE@!- 1000 ))
    5  - access("T2"."DT"<SYSDATE@!- 1000 )

Note
-----
   - 'PLAN_TABLE' is old version


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

SQL> 
SQL> SELECT  kod
   2   FROM (SELECT t1.dt as dt_start
   3     , lead(t1.dt) over (order by t1.dt) as dt_end
   4     , t1.kod
   5   FROM hist t1
   6   ) t2
   7   WHERE  1 = 1 
   8     AND t2.dt_start <= sysdate- 1000 
   9     AND t2.dt_end > sysdate- 1000 ;

       KOD
----------
       1000 


План выполнения
----------------------------------------------------------

-------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes |TempSpc| Cost (%CPU)|
-------------------------------------------------------------------------
|    0  | SELECT STATEMENT    |      |  1001K|    29M|       |   7400    ( 4 )|
|*   1  |  VIEW               |      |  1001K|    29M|       |   7400    ( 4 )|
|    2  |   WINDOW SORT       |      |  1001K|    21M|    61M|   7400    ( 4 )|
|    3  |    TABLE ACCESS FULL| HIST |  1001K|    21M|       |    581    ( 7 )|
-------------------------------------------------------------------------

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

    1  - filter("T2"."DT_START"<=SYSDATE@!- 1000  AND
              "T2"."DT_END">SYSDATE@!- 1000 )

Note
-----
   - 'PLAN_TABLE' is old version


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

SQL> 
SQL> set autotrace off

SQL> create table hist(kod integer,dt date);

Таблица создана.

SQL> create index ix_hist on hist(dt);

Индекс создан.

SQL> 
SQL> insert into hist(kod, dt)
   2   select lv, sysdate-lv from
   3   (select level as lv from dual connect by level <= 1000000 ) pivot;

 1000000  строк создано.

SQL> 
SQL> analyze table hist compute statistics;

Таблица проанализирована.

SQL> set autotrace on   
SQL> 
SQL> SELECT kod FROM hist t1
   2   WHERE t1.dt IN (SELECT max(t2.dt) FROM hist t2
   3                    WHERE t2.dt < sysdate -  1000 );

       KOD
----------
       1000 


План выполнения
----------------------------------------------------------

-------------------------------------------------------------------------------
| Id  | Operation                      | Name    | Rows  | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------
|    0  | SELECT STATEMENT               |         |      1  |     11  |      7    ( 0 )|
|    1  |  TABLE ACCESS BY INDEX ROWID   | HIST    |      1  |     11  |      4    ( 0 )|
|*   2  |   INDEX RANGE SCAN             | IX_HIST |      1  |       |      3    ( 0 )|
|    3  |    SORT AGGREGATE              |         |      1  |      7  |            |
|    4  |     FIRST ROW                  |         |   999K|  6829K|      3    ( 0 )|
|*   5  |      INDEX RANGE SCAN (MIN/MAX)| IX_HIST |   999K|  6829K|      3    ( 0 )|
-------------------------------------------------------------------------------

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

    2  - access("T1"."DT"= (SELECT MAX("T2"."DT") FROM "HIST" "T2" WHERE
              "T2"."DT"<SYSDATE@!- 1000 ))
    5  - access("T2"."DT"<SYSDATE@!- 1000 )

Note
-----
   - 'PLAN_TABLE' is old version


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

SQL> 
SQL> SELECT  kod
   2   FROM (SELECT t1.dt as dt_start
   3     , lead(t1.dt) over (order by t1.dt) as dt_end
   4     , t1.kod
   5   FROM hist t1
   6   ) t2
   7   WHERE  1 = 1 
   8     AND t2.dt_start <= sysdate- 1000 
   9     AND t2.dt_end > sysdate- 1000 ;

       KOD
----------
       1000 


План выполнения
----------------------------------------------------------

-------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes |TempSpc| Cost (%CPU)|
-------------------------------------------------------------------------
|    0  | SELECT STATEMENT    |      |  1000K|    29M|       |   5117    ( 5 )|
|*   1  |  VIEW               |      |  1000K|    29M|       |   5117    ( 5 )|
|    2  |   WINDOW SORT       |      |  1000K|    10M|    38M|   5117    ( 5 )|
|    3  |    TABLE ACCESS FULL| HIST |  1000K|    10M|       |    581    ( 7 )|
-------------------------------------------------------------------------

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

    1  - filter("T2"."DT_START"<=SYSDATE@!- 1000  AND
              "T2"."DT_END">SYSDATE@!- 1000 )

Note
-----
   - 'PLAN_TABLE' is old version


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

SQL> 
SQL> set autotrace off
SQL> 
SQL> drop table hist;

Таблица удалена.

SQL> create table hist(kod integer,start_dt date,end_dt date);

Таблица создана.

SQL> create index ix_hist on hist(start_dt,end_dt);

Индекс создан.

SQL> 
SQL> insert into hist(kod, start_dt,end_dt)
   2   select lv, sysdate-lv, sysdate-lv+ 1  from
   3   (select level as lv from dual connect by level <= 1000000 ) pivot;

 1000000  строк создано.

SQL> 
SQL> analyze table hist compute statistics;

Таблица проанализирована.

SQL> 
SQL> set autotrace on   
SQL> 
SQL> SELECT *
   2   FROM hist t1
   3   WHERE t1.start_dt < sysdate- 1000  and t1.end_dt >=sysdate- 1000 ;

       KOD START_DT END_DT
---------- -------- --------
       1000   18 . 02 . 06   19 . 02 . 06 


План выполнения
----------------------------------------------------------

---------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)|
---------------------------------------------------------------
|    0  | SELECT STATEMENT  |      |   1000  |  18000  |    928   ( 16 )|
|*   1  |  TABLE ACCESS FULL| HIST |   1000  |  18000  |    928   ( 16 )|
---------------------------------------------------------------

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

    1  - filter("T1"."END_DT">=SYSDATE@!- 1000  AND
              "T1"."START_DT"<SYSDATE@!- 1000 )

Note
-----
   - 'PLAN_TABLE' is old version


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

SQL> set autotrace off
SQL> 
SQL> drop table hist;