Ok, I may be the last person seeing this, but I thought this is just too funny not to share it. I found a way to get rid of those annoying speeding tickets.
Savepoint Funny
1 month ago
Thursday, April 1, 2010
Tuesday, March 30, 2010
March 30: OGh APEX dag
Today was OGh's APEX day. I saw 5 good presentations, in which I was not bored by Powerpoint once. I probably missed a few other good ones, since in the afternoon we all had to make some hard decisions about which presentation to visit and which ones to skip.
The first presentation was by Kitty Spaas and Mark Rooijakkers called "Centraal Boekhuis goes APEX". An informative and very entertaining story about why Centraal Boekhuis has chosen to migrate their J2EE applications to APEX ones. And about why they are still happy with that choice. This presentation was followed by a great presentation by Patrick Wolf from Oracle Austria, who told us about APEX 4.0. This new version will have lots of new features that will make developing APEX applications even easier than it already was. He demonstrated how dynamic actions and plugins work in a way that was easy to follow. So this was a good start of the day.
After the lunch everyone had to choose one out of three possible presentations. The first presentation I chose was the presentation by Jan Huyzentruyt and Olivier Dupont from iAdvise about Flightcare Belgium. They showed a very strong business case for using APEX: with only 6 people doing IT for a company of 1700 people, they managed to develop 51 small applications, with a total of 750 pages. And the screenshots of the applications looked good. The second choice of the afternoon was made in favour of Dimitri Gielis' presentation about APEX 4.0. He showed the new features that Patrick Wolf skipped. He did this in his usual enthusiastic and interactive style. Unfortunately the session ran quite a bit over time, which was unnecessary, but the content made up for it.
One of the best presentations this day was by my colleague Art Melssen about APEX templates. The APEX templates are now table-based, which doesn't leave much room for interface designers. The new version should contain a few CSS-based templates already. And according to Art, this is much better. Nice examples of how powerful this can be, can be found at csszengarden.com. He recommends to build your own template from scratch and to use CSS for the layout. The presentation inspired me to start doing that myself as well (in the second half of this year). His presentation was full of useful links with webexamples. So that should make a nice self-study for a sunday afternoon or two.
So I returned home with lots of new ideas and with the wish that APEX 4.0 will come out soon... Thanks to Natalie, OGh and the speakers for this great day.
Monday, March 29, 2010
Shredding XML into multiple tables in one shot
To insert the data from an XML document into multiple relational tables, you can use PL/SQL, loop statements and the extract function. But I wanted to know whether it is possible to do it using only SQL. With the XMLTable function you can transform an XML document to a relational format. This post will show how you can use the XMLTable function to store the data into relational tables with a master-detail-detail relationship, when the connecting columns are not part of the XML; they are surrogate key columns.
For this example, I'll use this XML:
SQL> select xml
2 from xml_documents_to_process
3 /
XML
----------------------------------------------------------------------
<?xml version="1.0" encoding="UTF-8"?>
<msg:message xmlns:msg="namespace1">
<msg:metadata>
<msg:CreationTime>2010-03-16T15:23:14.56</msg:CreationTime>
</msg:metadata>
<msg:content xmlns:tcim="namespace2">
<tcim:ReportingDate>2010-03-19</tcim:ReportingDate>
<tcim:Persons>
<tcim:Person>
<tcim:Name>Jeffrey Lebowski</tcim:Name>
<tcim:Items>
<tcim:Item>
<tcim:Name>Rug</tcim:Name>
<tcim:Remarks>Really ties the room together</tcim:Remarks>
</tcim:Item>
<tcim:Item>
<tcim:Name>Creedence Tapes</tcim:Name>
<tcim:Remarks>Stolen</tcim:Remarks>
</tcim:Item>
<tcim:Item>
<tcim:Name>Toe</tcim:Name>
<tcim:Remarks>With nail polish</tcim:Remarks>
</tcim:Item>
</tcim:Items>
</tcim:Person>
<tcim:Person>
<tcim:Name>Jesus Quintana</tcim:Name>
<tcim:Items>
<tcim:Item>
<tcim:Name>Bowling ball</tcim:Name>
<tcim:Remarks>Warmed up</tcim:Remarks>
</tcim:Item>
</tcim:Items>
</tcim:Person>
<tcim:Person>
<tcim:Name>Walter Sobchak</tcim:Name>
</tcim:Person>
</tcim:Persons>
</msg:content>
</msg:message>
1 row selected.
The contents have to be stored in these three tables:
SQL> create table messages
2 ( guid raw(16) primary key
3 , creation_time timestamp(2) not null
4 , reporting_date date not null
5 )
6 /
Table created.
SQL> create table persons
2 ( guid raw(16) primary key
3 , message_guid raw(16) not null references messages(guid)
4 , name varchar2(30) not null
5 )
6 /
Table created.
SQL> create table items
2 ( guid raw(16) primary key
3 , person_guid raw(16) not null references persons(guid)
4 , name varchar2(20) not null
5 , remarks varchar2(40) not null
6 )
7 /
Table created.
Note that I use globally unique identifiers (guid's) here, because I could not make the example below work when using sequences. I challenge you to show me a reasonable way to do the same as I do below, using sequences :-). I really like to know. But for now, I know one more advantage of SYS_GUID() over sequences.
Back to the example. Let me first show how to shred this XML into these three tables in one shot. The explanation comes afterwards.
SQL> create procedure from_xml_to_relational (p_xml in xmltype)
2 is
3 begin
4 insert all
5 when add_messages_indicator = 'Y'
6 then
7 into messages values
8 ( m_guid
9 , creation_time
10 , reporting_date
11 )
12 when add_persons_indicator = 'Y'
13 then
14 into persons values
15 ( p_guid
16 , m_guid
17 , p_name
18 )
19 when add_items_indicator = 'Y'
20 then
21 into items values
22 ( i_guid
23 , p_guid
24 , i_name
25 , remarks
26 )
27 select case when p.id = 1 and nvl(i.id,1) = 1 then 'Y' end add_messages_indicator
28 , case when nvl(i.id,1) = 1 then 'Y' end add_persons_indicator
29 , nvl2(i.id,'Y',null) add_items_indicator
30 , first_value(sys_guid()) over (partition by m.id order by p.id,i.id) m_guid
31 , first_value(sys_guid()) over (partition by m.id,p.id order by i.id) p_guid
32 , sys_guid() i_guid
33 , to_timestamp(m.creation_time,'yyyy-mm-dd"T"hh24:mi:ss.ff') creation_time
34 , to_date(m.reporting_date,'yyyy-mm-dd') reporting_date
35 , p.name p_name
36 , i.name i_name
37 , i.remarks remarks
38 from xmltable
39 ( xmlnamespaces ('namespace1' as "msg", default 'namespace2')
40 , 'msg:message'
41 passing p_xml
42 columns id for ordinality
43 , creation_time varchar2(50) path 'msg:metadata/msg:CreationTime'
44 , reporting_date varchar2(50) path 'msg:content/ReportingDate'
45 , persons xmltype path 'msg:content/Persons'
46 ) m
47 , xmltable
48 ( xmlnamespaces (default 'namespace2')
49 , 'Persons/Person'
50 passing m.persons
51 columns id for ordinality
52 , name varchar2(30) path 'Name'
53 , items xmltype path 'Items'
54 ) p
55 , xmltable
56 ( xmlnamespaces(default 'namespace2')
57 , 'Items/Item'
58 passing p.items
59 columns id for ordinality
60 , name varchar2(20) path 'Name'
61 , remarks varchar2(40) path 'Remarks'
62 ) (+) i
63 ;
64 end;
65 /
Procedure created.
The first challenge is to join the three levels in this XML with each other. This is done by passing a part of the XML to the lower level. For example, in line 45 the Persons-part of the Message XML is caught and passed on to subquery p in line 50. This is repeated from Persons to Items in line 53 and 58.
A second challenge is to do an outerjoin with XMLTable. This wasn't a challenge after all, because - as you can see - all you have to do is to add a (+) sign after the XMLTable function.
The resulting query produces 5 rows. The third challenge is to make the INSERT ALL statement do only 1 insert into the messages table, 3 inserts into persons and 4 inserts into items. To support this challenge, I introduced an "id for ordinality" numeric column. This id column helps to construct expressions add_messages_indicator, add_persons_indicator and add_items_indicator. The id's also help for constructing the right guid's for each row to link the related records to each other. To see how these expressions work, let's show the result of the select:
SQL> select case when p.id = 1 and nvl(i.id,1) = 1 then 'Y' end add_messages_indicator
2 , case when nvl(i.id,1) = 1 then 'Y' end add_persons_indicator
3 , nvl2(i.id,'Y',null) add_items_indicator
4 , first_value(sys_guid()) over (partition by m.id order by p.id,i.id) m_guid
5 , first_value(sys_guid()) over (partition by m.id,p.id order by i.id) p_guid
6 , sys_guid() i_guid
7 , to_timestamp(m.creation_time,'yyyy-mm-dd"T"hh24:mi:ss.ff') creation_time
8 , to_date(m.reporting_date,'yyyy-mm-dd') reporting_date
9 , p.name p_name
10 , i.name i_name
11 , i.remarks remarks
12 from xml_documents_to_process x
13 , xmltable
14 ( xmlnamespaces ('namespace1' as "msg", default 'namespace2')
15 , 'msg:message'
16 passing x.xml
17 columns id for ordinality
18 , creation_time varchar2(50) path 'msg:metadata/msg:CreationTime'
19 , reporting_date varchar2(50) path 'msg:content/ReportingDate'
20 , persons xmltype path 'msg:content/Persons'
21 ) m
22 , xmltable
23 ( xmlnamespaces (default 'namespace2')
24 , 'Persons/Person'
25 passing m.persons
26 columns id for ordinality
27 , name varchar2(30) path 'Name'
28 , items xmltype path 'Items'
29 ) p
30 , xmltable
31 ( xmlnamespaces(default 'namespace2')
32 , 'Items/Item'
33 passing p.items
34 columns id for ordinality
35 , name varchar2(20) path 'Name'
36 , remarks varchar2(40) path 'Remarks'
37 ) (+) i
38 /
ADD ADD ADD M_GUID P_GUID
--- --- --- -------------------------------- --------------------------------
I_GUID CREATION_TIME REPORTING_DATE
-------------------------------- -------------------- -------------------
P_NAME
--------------------------------------------------------------------------------
I_NAME
------------------------------------------------------------
REMARKS
------------------------------
Y Y Y 82F643037E04E60FE0400000000001C0 82F643037E04E60FE0400000000001C0
82F643037E09E60FE0400000000001C0 16-03-10 15:23:14,56 19-03-2010 00:00:00
0000000
Jeffrey Lebowski
Rug
Really ties the room together
Y 82F643037E04E60FE0400000000001C0 82F643037E04E60FE0400000000001C0
82F643037E0AE60FE0400000000001C0 16-03-10 15:23:14,56 19-03-2010 00:00:00
0000000
Jeffrey Lebowski
Creedence Tapes
Stolen
Y 82F643037E04E60FE0400000000001C0 82F643037E04E60FE0400000000001C0
82F643037E0BE60FE0400000000001C0 16-03-10 15:23:14,56 19-03-2010 00:00:00
0000000
Jeffrey Lebowski
Toe
With nail polish
Y Y 82F643037E04E60FE0400000000001C0 82F643037E07E60FE0400000000001C0
82F643037E0CE60FE0400000000001C0 16-03-10 15:23:14,56 19-03-2010 00:00:00
0000000
Jesus Quintana
Bowling ball
Warmed up
Y 82F643037E04E60FE0400000000001C0 82F643037E08E60FE0400000000001C0
82F643037E0DE60FE0400000000001C0 16-03-10 15:23:14,56 19-03-2010 00:00:00
0000000
Walter Sobchak
5 rows selected.
The guid's all look alike, but don't be fooled (as I was initially): they differ where they need to differ. Just look at byte 6 (position 12). If you look closely, you'll see that the column m_guid all contain the same guid's. The first three p_guid's are the same, but row 4 and 5 differ. This is what the partition clause in the first_value analytic function achieves. The i_guid's all differ. With the guid's defined like this, the foreign key columns refer to the right parent records.
Finally, let's show that the INSERT ALL statement has done its job:
SQL> declare
2 l_xml xmltype;
3 begin
4 select xml
5 into l_xml
6 from xml_documents_to_process
7 ;
8 from_xml_to_relational(l_xml);
9 end;
10 /
PL/SQL procedure successfully completed.
SQL> select * from messages
2 /
GUID CREATION_TIME REPORTING_DATE
-------------------------------- -------------------- -------------------
82F643037DDCE60FE0400000000001C0 16-03-10 15:23:14,56 19-03-2010 00:00:00
1 row selected.
SQL> select * from persons
2 /
GUID MESSAGE_GUID NAME
-------------------------------- -------------------------------- --------------------
82F643037DDCE60FE0400000000001C0 82F643037DDCE60FE0400000000001C0 Jeffrey Lebowski
82F643037DDFE60FE0400000000001C0 82F643037DDCE60FE0400000000001C0 Jesus Quintana
82F643037DE0E60FE0400000000001C0 82F643037DDCE60FE0400000000001C0 Walter Sobchak
3 rows selected.
SQL> select * from items
2 /
GUID PERSON_GUID NAME REMARKS
-------------------------------- -------------------------------- -------------------- ------------------------------
82F643037DE1E60FE0400000000001C0 82F643037DDCE60FE0400000000001C0 Rug Really ties the room together
82F643037DE2E60FE0400000000001C0 82F643037DDCE60FE0400000000001C0 Creedence Tapes Stolen
82F643037DE3E60FE0400000000001C0 82F643037DDCE60FE0400000000001C0 Toe With nail polish
82F643037DE4E60FE0400000000001C0 82F643037DDFE60FE0400000000001C0 Bowling ball Warmed up
4 rows selected.
SQL> select m.creation_time
2 , m.reporting_date
3 , p.name
4 , i.name
5 , i.remarks
6 from messages m
7 , persons p
8 , items i
9 where m.guid = p.message_guid
10 and p.guid = i.person_guid (+)
11 /
CREATION_TIME REPORTING_DATE NAME NAME REMARKS
-------------------- ------------------- -------------------- -------------------- ------------------------------
16-03-10 15:23:14,56 19-03-2010 00:00:00 Jeffrey Lebowski Rug Really ties the room together
16-03-10 15:23:14,56 19-03-2010 00:00:00 Jeffrey Lebowski Creedence Tapes Stolen
16-03-10 15:23:14,56 19-03-2010 00:00:00 Jeffrey Lebowski Toe With nail polish
16-03-10 15:23:14,56 19-03-2010 00:00:00 Jesus Quintana Bowling ball Warmed up
16-03-10 15:23:14,56 19-03-2010 00:00:00 Walter Sobchak
5 rows selected.
Monday, March 8, 2010
Weird exception handling
PL/SQL got me fooled today. My assignment was to build a new procedure that gets invoked together with an existing procedure. After I had build and unit tested my new procedure, the tester wanted to conduct a system integration test. He had trouble coming up with a situation where the old and new procedure were called. So I helped him by having a look at some of the surrounding code and my conclusion was: the existing procedure and my new one will not be called. Ever. To support my claim, I had built a small script to simulate the situation. But I missed a tiny detail that seemed irrelevant at first, but which appeared to be crucial.
Here's my first simulation of the situation: two packages, one with a global public exception and a second with a local exception with the same name.
rwijk@ORA11GR1> create package pkg1
2 as
3 e_wrong_contents exception;
4 procedure start1;
5 end pkg1;
6 /
Package created.
rwijk@ORA11GR1> create package pkg2
2 as
3 procedure start2;
4 end pkg2;
5 /
Package created.
rwijk@ORA11GR1> create package body pkg1
2 as
3 procedure start1
4 is
5 begin
6 pkg2.start2;
7 end start1;
8 end pkg1;
9 /
Package body created.
rwijk@ORA11GR1> create package body pkg2
2 as
3 procedure local_procedure
4 is
5 e_wrong_contents exception;
6 begin
7 raise e_wrong_contents;
8 exception
9 when e_wrong_contents then
10 raise e_wrong_contents;
11 when others then
12 -- do something else
13 null;
14 end local_procedure
15 ;
16 procedure start2
17 is
18 begin
19 local_procedure;
20 exception
21 when pkg1.e_wrong_contents then
22 -- existing_procedure;
23 -- my_new_procedure;
24 dbms_output.put_line('pkg1.e_wrong_contents was raised');
25 when others then
26 dbms_output.put_line('others');
27 dbms_output.put_line(sqlerrm || chr(10) || dbms_utility.format_error_backtrace);
28 end start2
29 ;
30 end pkg2;
31 /
Package body created.
My claim was that the existing procedure and my new one were never invoked, because pkg1.e_wrong_contents is never raised. Only the local one, but that one loses scope in procedure start2 and becomes a user defined exception there, as can be seen by running pkg1.start1:
rwijk@ORA11GR1> exec pkg1.start1
others
User-Defined Exception
ORA-06512: at "RWIJK.PKG2", line 10
ORA-06512: at "RWIJK.PKG2", line 16
PL/SQL procedure successfully completed.
The point is that pkg1.e_wrong_contents and e_wrong_contents may look related, but they are not.
After having witnessed that the existing procedure does get invoked in the production database, we had a second look at my script and a colleague pointed out that I missed one detail: a pragma exception_init to a user defined error number (-20660). I had left it out on purpose, since there is no point in assigning a user defined exception to user error number that is not used. I had checked there wasn't any RAISE_APPLICATION_ERROR(-20660,...) in the entire schema. With the pragma exception_init added, the script looks like this:
rwijk@ORA11GR1> create package pkg1
2 as
3 e_wrong_contents exception;
4 pragma exception_init(e_wrong_contents,-20660)
5 ;
6 procedure start1;
7 end pkg1;
8 /
Package created.
rwijk@ORA11GR1> create package pkg2
2 as
3 procedure start2;
4 end pkg2;
5 /
Package created.
rwijk@ORA11GR1> create package body pkg1
2 as
3 procedure start1
4 is
5 begin
6 pkg2.start2;
7 end start1;
8 end pkg1;
9 /
Package body created.
rwijk@ORA11GR1> create package body pkg2
2 as
3 procedure local_procedure
4 is
5 e_wrong_contents exception;
6 pragma exception_init(e_wrong_contents,-20660);
7 begin
8 raise e_wrong_contents;
9 exception
10 when e_wrong_contents then
11 raise e_wrong_contents;
12 when others then
13 -- do something else
14 null;
15 end local_procedure
16 ;
17 procedure start2
18 is
19 begin
20 local_procedure;
21 exception
22 when pkg1.e_wrong_contents then
23 -- existing_procedure;
24 -- my_new_procedure;
25 dbms_output.put_line('pkg1.e_wrong_contents was raised');
26 when others then
27 dbms_output.put_line('others');
28 dbms_output.put_line(sqlerrm || chr(10) || dbms_utility.format_error_backtrace);
29 end start2
30 ;
31 end pkg2;
32 /
Package body created.
And executing pkg1.start1:
rwijk@ORA11GR1> exec pkg1.start1
pkg1.e_wrong_contents was raised
PL/SQL procedure successfully completed.
Now the code is invoked and that's how it works in production. The pragma exception_init assigns both the global and the local one to ORA-20660, which makes them equal.
Why you would ever want to code it like this, I don't know. It's probably best categorized as "historically grown like that".
Defining your own exception, assigning it to a user error number and then raise your exception, is equivalent to just doing a raise_application_error:
rwijk@ORA11GR1> declare
2 e_wrong_contents exception;
3 pragma exception_init(e_wrong_contents,-20660);
4 begin
5 raise e_wrong_contents;
6 end;
7 /
declare
*
ERROR at line 1:
ORA-20660:
ORA-06512: at line 5
rwijk@ORA11GR1> begin
2 raise_application_error(-20660,null);
3 end;
4 /
begin
*
ERROR at line 1:
ORA-20660:
ORA-06512: at line 2
And if I get to refactor the code, I would skip the pragma exception_init's and skip the local exceptions. The code would then look like this:
rwijk@ORA11GR1> create package pkg1
2 as
3 e_wrong_contents exception;
4 procedure start1;
5 end pkg1;
6 /
Package created.
rwijk@ORA11GR1> create package pkg2
2 as
3 procedure start2;
4 end pkg2;
5 /
Package created.
rwijk@ORA11GR1> create package body pkg1
2 as
3 procedure start1
4 is
5 begin
6 pkg2.start2;
7 end start1;
8 end pkg1;
9 /
Package body created.
rwijk@ORA11GR1> create package body pkg2
2 as
3 procedure local_procedure
4 is
5 begin
6 raise pkg1.e_wrong_contents;
7 end local_procedure
8 ;
9 procedure start2
10 is
11 begin
12 local_procedure;
13 exception
14 when pkg1.e_wrong_contents then
15 -- existing_procedure;
16 -- my_new_procedure;
17 dbms_output.put_line('pkg1.e_wrong_contents was raised');
18 when others then
19 dbms_output.put_line('others');
20 dbms_output.put_line(sqlerrm || chr(10) || dbms_utility.format_error_backtrace);
21 end start2
22 ;
23 end pkg2;
24 /
Package body created.
rwijk@ORA11GR1> exec pkg1.start1
pkg1.e_wrong_contents was raised
PL/SQL procedure successfully completed.
Simulating production situations using your own little hand written script is a valuable technique, but you have to be careful to include all the necessary details ...
Monday, February 15, 2010
OGh APEX dag
Tuesday March 30, the Dutch Oracle usergroup OGh organizes one entire day dedicated to APEX. I'm a little biased, as I helped organizing the event, but I think we succeeded in putting together a great program, as you can see here (warning: Dutch). The lineup includes some very well known APEX speakers like Patrick Wolf, Dimitri Gielis and Roel Hartman.
The schedule also includes three sessions where you'll hear from companies who have chosen APEX as their development tool of choice. So if you need to convince your boss to choose the tool that matches the skill set of his team members, send him or her to this day. And be quick, because there are only 7 places left, as of this moment.
Hope to see you there.
Friday, January 15, 2010
enq: JI - contention
To implement entity rules and inter-entity rules, one of the options is to use an on commit-time fast refreshable materialized view with a check constraint on top. You can read a few examples in these posts. It's an elegant way to check for conditions at commit-time, and quite remarkable when you see it for the first time. But there is a serious caveat to this way of implementing business rules, which may or may not be relevant for your situation. In any case, it doesn't hurt to be aware of this caveat. To show what I mean I create a table called percentages_per_year:
rwijk@ORA11GR1> create table percentages_per_year (ym,percentage)
2 as
3 select date '2005-12-01' + numtoyminterval(level,'month')
4 , case mod(level,3) when 0 then 5 else 10 end
5 from dual
6 connect by level <= 48
7 /
Table created.
rwijk@ORA11GR1> select * from percentages_per_year
2 /
YM PERCENTAGE
------------------- ----------
01-01-2006 00:00:00 10
01-02-2006 00:00:00 10
01-03-2006 00:00:00 5
01-04-2006 00:00:00 10
01-05-2006 00:00:00 10
01-06-2006 00:00:00 5
01-07-2006 00:00:00 10
01-08-2006 00:00:00 10
01-09-2006 00:00:00 5
01-10-2006 00:00:00 10
01-11-2006 00:00:00 10
01-12-2006 00:00:00 5
01-01-2007 00:00:00 10
01-02-2007 00:00:00 10
01-03-2007 00:00:00 5
01-04-2007 00:00:00 10
01-05-2007 00:00:00 10
01-06-2007 00:00:00 5
01-07-2007 00:00:00 10
01-08-2007 00:00:00 10
01-09-2007 00:00:00 5
01-10-2007 00:00:00 10
01-11-2007 00:00:00 10
01-12-2007 00:00:00 5
01-01-2008 00:00:00 10
01-02-2008 00:00:00 10
01-03-2008 00:00:00 5
01-04-2008 00:00:00 10
01-05-2008 00:00:00 10
01-06-2008 00:00:00 5
01-07-2008 00:00:00 10
01-08-2008 00:00:00 10
01-09-2008 00:00:00 5
01-10-2008 00:00:00 10
01-11-2008 00:00:00 10
01-12-2008 00:00:00 5
01-01-2009 00:00:00 10
01-02-2009 00:00:00 10
01-03-2009 00:00:00 5
01-04-2009 00:00:00 10
01-05-2009 00:00:00 10
01-06-2009 00:00:00 5
01-07-2009 00:00:00 10
01-08-2009 00:00:00 10
01-09-2009 00:00:00 5
01-10-2009 00:00:00 10
01-11-2009 00:00:00 10
01-12-2009 00:00:00 5
48 rows selected.
The data is such that all percentages in a year add up to exactly 100. To ensure this is always the case in the future, I implement it as a business rule using an on commit-time fast refreshable materialized view:
rwijk@ORA11GR1> create materialized view log on percentages_per_year
2 with sequence, rowid (ym,percentage) including new values
3 /
Materialized view log created.
rwijk@ORA11GR1> create materialized view mv
2 refresh fast on commit
3 as
4 select to_char(ym,'yyyy') year
5 , sum(percentage) year_percentage
6 , count(percentage) count_percentage
7 , count(*) count_all
8 from percentages_per_year
9 group by to_char(ym,'yyyy')
10 /
Materialized view created.
rwijk@ORA11GR1> alter materialized view mv
2 add constraint year_percentage_100_ck
3 check (year_percentage=100)
4 /
Materialized view altered.
rwijk@ORA11GR1> select * from mv
2 /
YEAR YEAR_PERCENTAGE COUNT_PERCENTAGE COUNT_ALL
---- --------------- ---------------- ----------
2009 100 12 12
2008 100 12 12
2007 100 12 12
2006 100 12 12
4 rows selected.
rwijk@ORA11GR1> exec dbms_stats.gather_table_stats(user,'percentages_per_year')
PL/SQL procedure successfully completed.
Now the idea is to run 4 jobs, each updating 12 records of one year. To see what's going on inside each job, I create a logging table with logging procedures that run autonomous transactions:
rwijk@ORA11GR1> create table t_log
2 ( year number(4)
3 , starttime timestamp(6)
4 , endtime timestamp(6)
5 )
6 /
Table created.
rwijk@ORA11GR1> create procedure log_start (p_year in t_log.year%type)
2 is
3 pragma autonomous_transaction;
4 begin
5 insert into t_log (year,starttime)
6 values (p_year,systimestamp)
7 ;
8 commit;
9 end log_start;
10 /
Procedure created.
rwijk@ORA11GR1> create procedure log_end (p_year in t_log.year%type)
2 is
3 pragma autonomous_transaction;
4 begin
5 update t_log
6 set endtime = systimestamp
7 where year = p_year
8 ;
9 commit;
10 end log_end;
11 /
Procedure created.
The jobs will run a procedure called update_percentages. The procedure sets its module and action column, to be able to identify the trace files that are created. It logs itself before the update and after the commit:
rwijk@ORA11GR1> create procedure update_percentages (p_year in number)
2 is
3 begin
4 dbms_application_info.set_module('p',to_char(p_year))
5 ;
6 dbms_session.session_trace_enable
7 ;
8 log_start(p_year)
9 ;
10 update percentages_per_year
11 set percentage = case when extract(month from ym) <= 8 then 9 else 7 end
12 where extract(year from ym) = p_year
13 ;
14 commit
15 ;
16 log_end(p_year)
17 ;
18 end update_percentages;
19 /
Procedure created.
And now let 4 jobs run:
rwijk@ORA11GR1> declare
2 l_job_id binary_integer;
3 begin
4 for i in 2006 .. 2009
5 loop
6 dbms_job.submit(l_job_id,'rwijk.update_percentages('||to_char(i)||');');
7 end loop;
8 end;
9 /
PL/SQL procedure successfully completed.
rwijk@ORA11GR1> commit
2 /
Commit complete.
rwijk@ORA11GR1> pause
After all jobs have run concurrently and have finished, the logging table looks like this:
rwijk@ORA11GR1> select year
2 , starttime
3 , endtime
4 , endtime - starttime duration
5 from t_log
6 order by endtime
7 /
YEAR STARTTIME ENDTIME
----- ------------------------------- -------------------------------
DURATION
-------------------------------
2007 14-JAN-10 09.59.45.625000 PM 14-JAN-10 09.59.45.843000 PM
+000000000 00:00:00.218000
2006 14-JAN-10 09.59.45.609000 PM 14-JAN-10 09.59.45.875000 PM
+000000000 00:00:00.266000
2008 14-JAN-10 09.59.45.656000 PM 14-JAN-10 09.59.45.906000 PM
+000000000 00:00:00.250000
2009 14-JAN-10 09.59.45.625000 PM 14-JAN-10 09.59.45.937000 PM
+000000000 00:00:00.312000
4 rows selected.
You can see the overlap of the processes. The process for year 2007 has finished first, and doesn't experience any noticeable wait. The other three processes do. A look at the trace file shows one line like this:
WAIT #8: nam='enq: JI - contention' ela= 85497 name|mode=1246298118 view object #=106258 0=0 obj#=-1 tim=7377031494
This line is followed after the COMMIT (XCTEND rlbk=0) in the trace file. The wait event is called "enq: JI - contention", where JI stands for materialized view refresh :-). In the documention it says that JI stands for "Enqueue used during AJV snapshot refresh". So it's still unclear where those letters JI really stand for, although the J may be from the J in AJV. And google suggests that AJV stands for "Aggregate join view". Maybe there are so many two-letter enqueue names already that they just picked one?
In the WAIT line from the trace file, we see three parameters after the "ela= 85497". We can see these parameters in the v$event_name view too:
rwijk@ORA11GR1> select event#
2 , name
3 , parameter1
4 , parameter2
5 , parameter3
6 , wait_class
7 from v$event_name
8 where name = 'enq: JI - contention'
9 /
EVENT# NAME
---------- ----------------------------------------------------------------
PARAMETER1
----------------------------------------------------------------
PARAMETER2
----------------------------------------------------------------
PARAMETER3
----------------------------------------------------------------
WAIT_CLASS
----------------------------------------------------------------
818 enq: JI - contention
name|mode
view object #
0
Other
1 row selected.
The first parameter is "name|mode", which equals a rather strange number 1246298118. This pretty-old-but-still-very-useful article of Anjo Kolk helped me interpreting the number:
rwijk@ORA11GR1> select chr(bitand(1246298118,-16777216)/16777215) ||
2 chr(bitand(1246298118,16711680)/65535) “Lock”
3 , bitand(1246298118, 65535) “Mode”
4 from dual
5 /
Lo Mode
-- ----------
JI 6
1 row selected.
Where lock mode 6 means an exclusive lock.
The second parameter is the object_id as you can find it in the all_objects view. The third parameter isn't useful here.
So a materialized view cannot be fast refreshed more than once in a given period. It's serialized during the commit. Normally when I update record 1 and commit and you update record 2 and commit, we don't have to wait for each other. But when having an on commit-time fast refreshable materialized view on top of the table, we do have to wait when two sessions do some totally unrelated transactions concurrently against the same table. Is this a problem? Not when the table is modified infrequently or only by a single session. But it can be a big problem when applied to a table that modifies a lot concurrently.
So be sure to use on commit-time fast refreshable materialized views for implementing business rules only on tables that are not concurrently accessed or infrequently changed. Or be prepared to be called by the users of your system ...
Sunday, January 10, 2010
CAST-COLLECT versus CAST-MULTISET
At work I am building an interface using SQL object types and SQL collection types. I noticed a difference between using the COLLECT aggregate function in combination with a CAST function, versus the CAST-MULTISET method. I started out using CAST-COLLECT, but switched to CAST-MULTISET. Here is a simulation:
rwijk@ORA11GR1> create table customers
2 ( id number(6) not null primary key
3 , name varchar2(30) not null
4 , birthdate date not null
5 )
6 /
Table created.
rwijk@ORA11GR1> create table bankaccounts
2 ( nr number(10) not null primary key
3 , customer_id number(6) not null
4 , current_balance number(14,2) not null
5 )
6 /
Table created.
rwijk@ORA11GR1> insert into customers
2 select 1, 'Jeffrey Lebowski', date '1964-01-01' from dual union all
3 select 2, 'Walter Sobchak', date '1961-01-01' from dual
4 /
2 rows created.
rwijk@ORA11GR1> create sequence customers_seq start with 3
2 /
Sequence created.
rwijk@ORA11GR1> insert into bankaccounts
2 select 123456789, 1, 10 from dual union all
3 select 987654321, 1, 100 from dual union all
4 select 234567890, 2, 2000 from dual
5 /
3 rows created.
rwijk@ORA11GR1> select * from customers
2 /
ID NAME BIRTHDATE
---------- ------------------------------ -------------------
1 Jeffrey Lebowski 01-01-1964 00:00:00
2 Walter Sobchak 01-01-1961 00:00:00
2 rows selected.
rwijk@ORA11GR1> select * from bankaccounts
2 /
NR CUSTOMER_ID CURRENT_BALANCE
---------- ----------- ---------------
123456789 1 10
987654321 1 100
234567890 2 2000
3 rows selected.
Two simple tables with a master-detail relationship. To be able to communicate (not to store, except for simple auditing) a customer, SQL types come in handy. Three types, two object types and a collection type:
rwijk@ORA11GR1> create type to_bankaccount is object
2 ( nr number(10)
3 , current_balance number(14,2)
4 );
5 /
Type created.
rwijk@ORA11GR1> create type ta_bankaccounts is table of to_bankaccount;
2 /
Type created.
rwijk@ORA11GR1> create type to_customer is object
2 ( name varchar2(30)
3 , birthdate date
4 , bankaccounts ta_bankaccounts
5 );
6 /
Type created.
rwijk@ORA11GR1> select type_name
2 , typecode
3 , attributes
4 from user_types
5 /
TYPE_NAME TYPECODE ATTRIBUTES
------------------------------ ------------------------------ ----------
TO_BANKACCOUNT OBJECT 2
TA_BANKACCOUNTS COLLECTION 0
TO_CUSTOMER OBJECT 3
3 rows selected.
The external system can call a function to get the customer information. The function below simulates that function. I used the CAST-COLLECT method:
rwijk@ORA11GR1> create function customer_with_collect
2 ( p_customer_id in customers.id%type
3 ) return to_customer
4 is
5 o_customer to_customer;
6 begin
7 select to_customer
8 ( c.name
9 , c.birthdate
10 , ( select cast
11 ( collect(to_bankaccount(ba.nr,ba.current_balance))
12 as ta_bankaccounts
13 )
14 from bankaccounts ba
15 where ba.customer_id = c.id
16 )
17 )
18 into o_customer
19 from customers c
20 where c.id = p_customer_id
21 ;
22 return o_customer;
23 end customer_with_collect;
24 /
Function created.
rwijk@ORA11GR1> select type_name
2 , typecode
3 , attributes
4 from user_types
5 /
TYPE_NAME TYPECODE ATTRIBUTES
------------------------------ ------------------------------ ----------
TO_BANKACCOUNT OBJECT 2
TA_BANKACCOUNTS COLLECTION 0
TO_CUSTOMER OBJECT 3
3 rows selected.
rwijk@ORA11GR1> select customer_with_collect(1)
2 from dual
3 /
CUSTOMER_WITH_COLLECT(1)(NAME, BIRTHDATE, BANKACCOUNTS(NR, CURRENT_BALANCE))
------------------------------------------------------------------------------
TO_CUSTOMER('Jeffrey Lebowski', '01-01-1964 00:00:00', TA_BANKACCOUNTS(TO_BANK
ACCOUNT(123456789, 10), TO_BANKACCOUNT(987654321, 100)))
1 row selected.
rwijk@ORA11GR1> select type_name
2 , typecode
3 , attributes
4 from user_types
5 /
TYPE_NAME TYPECODE ATTRIBUTES
------------------------------ ------------------------------ ----------
TO_BANKACCOUNT OBJECT 2
TA_BANKACCOUNTS COLLECTION 0
TO_CUSTOMER OBJECT 3
SYSTPuS41SrC7Q9G6/OfQT4VHPA== COLLECTION 0
4 rows selected.
But as you can see, when you use the function, an internal type has been persistently created. This is the internal type that is used to store the result of the COLLECT-function in:
rwijk@ORA11GR1> select collect(to_bankaccount(nr,current_balance))
2 from bankaccounts
3 /
COLLECT(TO_BANKACCOUNT(NR,CURRENT_BALANCE))(NR, CURRENT_BALANCE)
------------------------------------------------------------------------------
SYSTPuS41SrC7Q9G6/OfQT4VHPA==(TO_BANKACCOUNT(123456789, 10), TO_BANKACCOUNT(98
7654321, 100), TO_BANKACCOUNT(234567890, 2000))
1 row selected.
Note that the name of the type is the same as listed earlier.
If you don't query the user_types view as I did, then you might be surprised that the following sequence doesn't work:
rwijk@ORA11GR1> drop type to_customer
2 /
Type dropped.
rwijk@ORA11GR1> drop type ta_bankaccounts
2 /
Type dropped.
rwijk@ORA11GR1> drop type to_bankaccount
2 /
drop type to_bankaccount
*
ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or table dependents
So I have to drop the internal type first, before I can drop type TO_BANKACCOUNT:
rwijk@ORA11GR1> column type_name new_value systype
rwijk@ORA11GR1> select type_name
2 from user_types
3 where type_name like 'SYS%'
4 /
TYPE_NAME
------------------------------
SYSTPuS41SrC7Q9G6/OfQT4VHPA==
1 row selected.
rwijk@ORA11GR1> drop type "&systype"
2 /
old 1: drop type "&systype"
new 1: drop type "SYSTPuS41SrC7Q9G6/OfQT4VHPA=="
Type dropped.
rwijk@ORA11GR1> drop type to_bankaccount
2 /
Type dropped.
This behaviour annoyed me slightly, so I checked out another way using CAST-MULTISET:
rwijk@ORA11GR1> create type to_bankaccount is object
2 ( nr number(10)
3 , current_balance number(14,2)
4 );
5 /
Type created.
rwijk@ORA11GR1> create type ta_bankaccounts is table of to_bankaccount;
2 /
Type created.
rwijk@ORA11GR1> create type to_customer is object
2 ( name varchar2(30)
3 , birthdate date
4 , bankaccounts ta_bankaccounts
5 );
6 /
Type created.
rwijk@ORA11GR1> create function customer_with_multiset
2 ( p_customer_id in customers.id%type
3 ) return to_customer
4 is
5 o_customer to_customer;
6 begin
7 select to_customer
8 ( c.name
9 , c.birthdate
10 , cast
11 ( multiset
12 ( select to_bankaccount(ba.nr,ba.current_balance)
13 from bankaccounts ba
14 where ba.customer_id = c.id
15 )
16 as ta_bankaccounts
17 )
18 )
19 into o_customer
20 from customers c
21 where c.id = p_customer_id
22 ;
23 return o_customer;
24 end customer_with_multiset;
25 /
Function created.
And with CAST-MULTISET, there are no additional internal types created:
rwijk@ORA11GR1> select customer_with_multiset(1)
2 from dual
3 /
CUSTOMER_WITH_MULTISET(1)(NAME, BIRTHDATE, BANKACCOUNTS(NR, CURRENT_BALANCE))
------------------------------------------------------------------------------
TO_CUSTOMER('Jeffrey Lebowski', '01-01-1964 00:00:00', TA_BANKACCOUNTS(TO_BANK
ACCOUNT(123456789, 10), TO_BANKACCOUNT(987654321, 100)))
1 row selected.
rwijk@ORA11GR1> select type_name
2 , typecode
3 , attributes
4 from user_types
5 /
TYPE_NAME TYPECODE ATTRIBUTES
------------------------------ ------------------------------ ----------
TA_BANKACCOUNTS COLLECTION 0
TO_BANKACCOUNT OBJECT 2
TO_CUSTOMER OBJECT 3
3 rows selected.
So that's 0-1 in favour of CAST-MULTISET. Let's check performance:
rwijk@ORA11GR1> declare
2 o_customer to_customer;
3 begin
4 runstats_pkg.rs_start;
5 for i in 1..10000
6 loop
7 o_customer := customer_with_collect(1);
8 end loop;
9 runstats_pkg.rs_middle;
10 for i in 1..10000
11 loop
12 o_customer := customer_with_multiset(1);
13 end loop;
14 runstats_pkg.rs_stop(1000);
15 end;
16 /
Run1 draaide in 171 hsecs
Run2 draaide in 130 hsecs
Run1 draaide in 131.54% van de tijd
Naam Run1 Run2 Verschil
STAT.undo change vector size 10,264 2,844 -7,420
LATCH.shared pool 10,236 8 -10,228
STAT.buffer is pinned count 0 20,000 20,000
STAT.redo size 30,784 3,780 -27,004
Run1 latches totaal versus run2 -- verschil en percentage
Run1 Run2 Verschil Pct
183,334 170,551 -12,783 107.50%
PL/SQL procedure successfully completed.
That's 0-2.
Subscribe to:
Posts (Atom)
