disj.sql

disj.sql

create table xt(
   id number,
   x number,
   date1 date not null,
   date2 date not null,
   date3 date not null,
   date4 date not null,
   date5 date not null
);
 
insert into xt
select level, 
       dbms_random.value, 
       sysdate - 1000*dbms_random.value, 
       sysdate - 1000*dbms_random.value, 
       sysdate - 1000*dbms_random.value, 
       sysdate - 1000*dbms_random.value, 
       sysdate - 1000*dbms_random.value
from dual
connect by level <= 1e6;

create index i$xt$dates1 on xt(date1, date2, date3, date4, date5);
create index i$xt$dates2 on xt(greatest(date1, date2, date3, date4, date5));

create index i$xt$date1 on xt(date1);
create index i$xt$date2 on xt(date2);
create index i$xt$date3 on xt(date3);
create index i$xt$date4 on xt(date4);
create index i$xt$date5 on xt(date5);
 
call dbms_stats.gather_table_stats(user, 'XT');

alter session set statistics_level=all;
set echo on;

select/*+ full(xt) */ count(*)
from xt
where 
   date1 > trunc(sysdate) - 1 or
   date2 > trunc(sysdate) - 1 or
   date3 > trunc(sysdate) - 1 or
   date4 > trunc(sysdate) - 1 or
   date5 > trunc(sysdate) - 1;
select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

select count(*)
from xt
where 
   greatest(date1, date2, date3, date4, date5) > trunc(sysdate) - 1;

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

with rids(rid) as (
      select d1.rowid from xt d1 where d1.date1 > trunc(sysdate) - 1 
      union all
      select d2.rowid from xt d2 where d2.date2 > trunc(sysdate) - 1 
      union all
      select d3.rowid from xt d3 where d3.date3 > trunc(sysdate) - 1 
      union all                           
      select d4.rowid from xt d4 where d4.date4 > trunc(sysdate) - 1 
      union all                           
      select d5.rowid from xt d5 where d5.date5 > trunc(sysdate) - 1 
  )
select count(distinct rid)
from rids;

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
set echo off;