OUGF Autumn Seminar

Today is the last day to register to OUGF Autumn Seminar 2009 at Vanha ylioppilastalo (The old student house), Helsinki 5.11.2009. Also time for me to put together a presentation about hierarchical storing structures and - queries. To be held there in finnish.

Coding Dojo

So, the day came to use PL/SQL MapReduce. Sooner than I thought two days ago. The purpose to use it was not parallel execution but the example itself. Today I participated Solita Oy free time activity coding dojo. And the simplest task there was to put together SpellNumbers and count used letters. I used the brute force method to spell all required numbers. I guess others used more sophisticated approaches. Mine is for sure the only SQL implementation today. Did not get to see other implementations and missed Sauna, because I left early to play with my kids.

My implementation euler17.sql

Direct path insert and Data Guard

Christian Antognini describes nicely the direct path insert hints from version 10.2 to 11.2.

Direct path insert works with Data Guard. No undo is generated for direct path insert and redo is generated. No redo is generated if nologgigin is set on the destination table. With Data Guard force logging should be set. Good explanation about this can be found in several discussions in asktom.

MapReduce

There might come a day to use parallel SQL processing. A good description about Map-Reduce model using Parallel Pipelined Table Functions and parallel operations.

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