2009-10-08

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

1 comment:

  1. The second Tanel day was Advanced, pitty there was not third...

    ReplyDelete

About Me

My Photo
I am Timo Raitalaakso. I have been working since 2001 at Solita Oy as a Senior Database Specialist. I have received Oracle ACE nomination. My main focus is on projects involving Oracle database. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.