Not overlapping daily

Attending. First day gone. Looking forward for tommorow. Tanel should you call your seminar Basic Oracle Troubleshooting Seminar as you talk about how a certain c program is executing function by function? Recommended attendance to all who troubleshoot Oracle.

Back to basics. Should it not be basics of a rdbms system to launch a trigger on an event that it is instructed to execute. With Oracle the issue is not so obvious. A certain kind of a compound trigger will not fire when called through jdbc. Metalink -soon to retire- bug no 6785707.

Another reason for my previous post. But was i thinking too complex. There is actually no need to track bitwise the used years. The problem is not O 2^n problem, but O n. As n is the number of distinct possible values in the validity interval. How about changing possible values from yearly to daily.

drop table z cascade constraints purge;

create table z(z number(16) not null
              , validfrom date not null
              , validtill date not null
              , constraint tilld check (trunc(validfrom)=validfrom)
              , constraint fromd check (trunc(validtill)=validtill)
              , constraint fro2000 
                     check (to_date('20000101','yyyymmdd') < validfrom)
              , constraint til2050 
                     check (validtill <= to_date('20500101','yyyymmdd'))
              , constraint frotil check (validfrom <= validtill)
              );

begin 
 for i in (
  select 'create unique index z'||d||' 
              on z (case when validfrom <= to_date('''||d||''',''yyyymmdd'') 
                          and to_date('''||d||''',''yyyymmdd'') < validtill 
                         then z 
                         else null 
                     end)' createindex
   from
  (select level l
        , to_char(to_date('20000101','yyyymmdd')+level-1,'yyyymmdd') d 
     from dual 
  connect by level<=to_date('20500101','yyyymmdd')-to_date('20000101','yyyymmdd')
  )
  order by l
  )
 loop
  execute immediate i.createindex;
 end loop;
end;
/

begin
*
ERROR at line 1:
ORA-01792: maximum number of columns in a table or view is 1000
ORA-06512: at line 18

Not a production ready aproach. Howcome index creation states that no more columns for a table?

select count(*) 
  from user_indexes 
 where table_name = 'Z';

  COUNT(*)
----------
       997

select count(*) 
  from user_tab_columns 
 where table_name = 'Z';

  COUNT(*)
----------
         3

select count(*) 
  from user_tab_cols 
 where table_name = 'Z';

  COUNT(*)
----------
      1000

A function based index is creating a invisible virtual column to table. A table may have at least 1000 columns.

Well we have 997 first days covered. How a small inserting test is performing with those 997 indexes?

SQL> insert into z
  2  values(1,to_date('20010101','yyyymmdd'),to_date('20010102','yyyymmdd'));

1 row created.

Elapsed: 00:00:00.50
SQL>
SQL> insert into z
  2  values(1,to_date('20020101','yyyymmdd'),to_date('20020102','yyyymmdd'));

1 row created.

Elapsed: 00:00:00.40
SQL>
SQL> insert into z
  2  values(1,to_date('20020102','yyyymmdd'),to_date('20030101','yyyymmdd'));

1 row created.

Elapsed: 00:00:00.42
SQL>
SQL> insert into z
  2  values(1,to_date('20020201','yyyymmdd'),to_date('20020202','yyyymmdd'));
insert into z
*
ERROR at line 1:
ORA-00001: unique constraint (RAFU.Z20020201) violated


Elapsed: 00:00:01.50
SQL>
SQL> select * from z;

         Z VALIDFRO VALIDTIL
---------- -------- --------
         1 20010101 20010102
         1 20020101 20020102
         1 20020102 20030101

Elapsed: 00:00:01.01
SQL>
SQL> update z
  2     set validfrom=to_date('20010101','yyyymmdd')
  3   where z=1 and validfrom=to_date('20020101','yyyymmdd');
update z
*
ERROR at line 1:
ORA-00001: unique constraint (RAFU.Z20010101) violated


Elapsed: 00:00:01.15