create table tst_timetab (hour date, day date, month date, year date, primary key (hour) )
Table created.
insert into tst_timetab
select day+offset/24 hour, trunc(day+offset/24, 'dd') day, trunc(day+offset/24, 'mm') month, trunc(day+offset/24, 'yy') year
from (select to_date('1-jan-2020', 'dd-mon-yyyy') day, (level-1) offset from dual connect by level<=5*365*24)
43800 row(s) inserted.
commit
Statement processed.
exec DBMS_STATS.GATHER_TABLE_STATS(sys_context('USERENV', 'CURRENT_USER'), 'tst_timetab', cascade=>true, method_opt => 'FOR ALL COLUMNS SIZE 255 FOR COLUMNS SIZE 255 (day,hour)', no_invalidate=>false )
Statement processed.
explain plan for
select *
from tst_timetab
where
day = to_date('20-jan-2022', 'dd-mon-yyyy')
and hour >= to_date('20-jan-2022', 'dd-mon-yyyy')
and hour < to_date('21-jan-2022', 'dd-mon-yyyy')
Statement processed.
select * from table(dbms_xplan.display)
PLAN_TABLE_OUTPUT | Plan hash value: 1542201271 | -------------------------------------------------------------------------------------------------------- | | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | | -------------------------------------------------------------------------------------------------------- | | 0 | SELECT STATEMENT | | 1 | 32 | 3 (0)| 00:00:01 | | |* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TST_TIMETAB | 1 | 32 | 3 (0)| 00:00:01 | | |* 2 | INDEX RANGE SCAN | SYS_C00176670627 | 28 | | 2 (0)| 00:00:01 | | -------------------------------------------------------------------------------------------------------- | Predicate Information (identified by operation id): | --------------------------------------------------- | 1 - filter("DAY"=TO_DATE(' 2022-01-20 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) | 2 - access("HOUR">=TO_DATE(' 2022-01-20 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND | "HOUR"<TO_DATE(' 2022-01-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss')) |
---|
select *
from tst_timetab
where
day = to_date('20-jan-2022', 'dd-mon-yyyy')
and hour >= to_date('20-jan-2022', 'dd-mon-yyyy')
and hour < to_date('21-jan-2022', 'dd-mon-yyyy')
HOUR | DAY | MONTH | YEAR | 20-JAN-22 | 20-JAN-22 | 01-JAN-22 | 01-JAN-22 | 20-JAN-22 | 20-JAN-22 | 01-JAN-22 | 01-JAN-22 | 20-JAN-22 | 20-JAN-22 | 01-JAN-22 | 01-JAN-22 | 20-JAN-22 | 20-JAN-22 | 01-JAN-22 | 01-JAN-22 | 20-JAN-22 | 20-JAN-22 | 01-JAN-22 | 01-JAN-22 | 20-JAN-22 | 20-JAN-22 | 01-JAN-22 | 01-JAN-22 | 20-JAN-22 | 20-JAN-22 | 01-JAN-22 | 01-JAN-22 | 20-JAN-22 | 20-JAN-22 | 01-JAN-22 | 01-JAN-22 | 20-JAN-22 | 20-JAN-22 | 01-JAN-22 | 01-JAN-22 | 20-JAN-22 | 20-JAN-22 | 01-JAN-22 | 01-JAN-22 | 20-JAN-22 | 20-JAN-22 | 01-JAN-22 | 01-JAN-22 | 20-JAN-22 | 20-JAN-22 | 01-JAN-22 | 01-JAN-22 | 20-JAN-22 | 20-JAN-22 | 01-JAN-22 | 01-JAN-22 | 20-JAN-22 | 20-JAN-22 | 01-JAN-22 | 01-JAN-22 | 20-JAN-22 | 20-JAN-22 | 01-JAN-22 | 01-JAN-22 | 20-JAN-22 | 20-JAN-22 | 01-JAN-22 | 01-JAN-22 | 20-JAN-22 | 20-JAN-22 | 01-JAN-22 | 01-JAN-22 | 20-JAN-22 | 20-JAN-22 | 01-JAN-22 | 01-JAN-22 | 20-JAN-22 | 20-JAN-22 | 01-JAN-22 | 01-JAN-22 | 20-JAN-22 | 20-JAN-22 | 01-JAN-22 | 01-JAN-22 | 20-JAN-22 | 20-JAN-22 | 01-JAN-22 | 01-JAN-22 | 20-JAN-22 | 20-JAN-22 | 01-JAN-22 | 01-JAN-22 | 20-JAN-22 | 20-JAN-22 | 01-JAN-22 | 01-JAN-22 | 20-JAN-22 | 20-JAN-22 | 01-JAN-22 | 01-JAN-22 |
---|