2009-10-29

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.

2009-10-28

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

2009-10-27

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.

2009-10-26

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.

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

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.