Aggregating over zero rows

In my presentation and paper about grouping last year, I mentioned that an aggregation query without a group by clause is the same as grouping by the empty grouping set. So this query:

SQL> select count(*)
  2    from emp
  3  /

  COUNT(*)
----------
        14

1 rij is geselecteerd.

is the same as:

SQL> select count(*)
  2    from emp
  3   group by ()
  4  /

  COUNT(*)
----------
        14

1 rij is geselecteerd.

However, this is not always true (also known as "false"). It is not true when you aggregate over zero rows. If I repeat the query above, but filter on deptno = 40 (there are no employees in that department), then there is an odd difference:

SQL> select count(*)
  2    from emp
  3   where deptno = 40
  4  /

  COUNT(*)
----------
         0

1 rij is geselecteerd.

SQL> select count(*)
  2    from emp
  3   where deptno = 40
  4   group by ()
  5  /

Er zijn geen rijen geselecteerd.

It's not that the empty grouping set is weird or anything, it's the fact that a group by clause is present. The documentation states:

"In a query containing a GROUP BY clause, the elements of the select list can be aggregate functions, GROUP BY expressions, constants, or expressions involving one of these. Oracle applies the aggregate functions to each group of rows and returns a single result row for each group.

If you omit the GROUP BY clause, then Oracle applies aggregate functions in the select list to all the rows in the queried table or view."

So it seems that the internal implementation looks something like this in pseudocode:

if not "group by clause present"
then
  return exactly one row
else
  return as much rows as there are groups
end if

Even if we are grouping by a regular column instead of the empty grouping set, we get a "No rows selected":

SQL> select deptno
  2       , count(*)
  3    from emp
  4   where deptno = 40
  5   group by deptno
  6  /

Er zijn geen rijen geselecteerd.

We can also see the same behaviour when using the group by extensions, like rollup and cube. They typically show a grand total, like in:

SQL> select deptno
  2       , count(*)
  3    from emp
  4   group by rollup(deptno)
  5  /

    DEPTNO   COUNT(*)
---------- ----------
        10          3
        20          5
        30          6
                   14

4 rijen zijn geselecteerd.

But when there are zero rows, not only the regular group rows disappear, but also the grand total:

SQL> select deptno
  2       , count(*)
  3    from emp
  4   where deptno = 40
  5   group by rollup(deptno)
  6  /

Er zijn geen rijen geselecteerd.

Which is now less of a surprise, because this query is equivalent to:

SQL> select deptno
  2       , count(*)
  3    from emp
  4   where deptno = 40
  5   group by grouping sets (deptno,())
  6  /

Er zijn geen rijen geselecteerd.

Which equals:

SQL> select deptno
  2       , count(*)
  3    from emp
  4   where deptno = 40
  5   group by deptno
  6   union all
  7  select null
  8       , count(*)
  9    from emp
 10   where deptno = 40
 11   group by ()
 12  /

Er zijn geen rijen geselecteerd.

And then we recognize the queries listed earlier.

So the behaviour is consistent, but it surprised me, even though it is clearly documented.

OOW10

Yesterday I came back from my third Oracle OpenWorld. This year I was not on a bloggers pass, so I feel less obliged to cover the event. But I know there are people out there that like to read about my experiences and I still like it very much at San Francisco, so here is a small write-up about my experiences at Oracle OpenWorld 2010.

The event started at Sunday, but later than usual: 12:30 PM I think, instead of 8:30 AM in previous years. For the first time I had to wait in a queue to register and receive the conference pass. I also received an Oracle Develop t-shirt, a conference book and the Appreciation Event wristband, but to my surprise no bag. The guy behind the counter pointed at the screen that said what he was allowed to give, and the screen said nothing about a bag. It felt strange, because except for Frits Hoogland, everybody seemed to wear that bag. Not really a problem, since I have enough of them at home. But I did not want to carry the stuff with me all day, so I decided to go back to the hotel and drop the book and t-shirt.

The sessions I saw on Sunday differed much in quality. I especially liked Cary Millsap's lesson in how to prepare for and deliver a presentation. Very inspiring. Even though I knew all content about messed-up apps already, because they are on his blog as well. I also like Richard Foote's presentation about indexes and Chris Muir's one on load testing APEX applications. In the evening, all ACEs and ACE Directors were transported to a very nice boat for a trip on the bay, with nice food, conversations and magnificent views of San Francisco by night.

On Monday I visited four sessions, which were all pretty good. First a live PL/SQL Challenge by Steven Feuerstein, with some interesting questions. No prices here, but Steven had created a secondary challenge for the people present at Oracle OpenWorld, where I won a second place and a 100 dollar Amazon gift card. Thanks Steven! Another session I enjoyed was Lucas Jellema's and Alex Nuijten's Xenogenetics session, about some use cases for using Java in PL/SQL, among others. In the evening I visited OTN Night, mainly for some food :-).

Tuesday I went to Oracle Closed World together with Frits Hoogland, where I saw a very good presentation of Kyle Hayley about Visual SQL Tuning with his Embarcadero product. The steps I intuitively take to solve performance problems with my explain plans and tkprofs were nicely illustrated with graphs. Pick as a starting point the table with the best filter predicates, then join down (to details) and then join up (to master tables). When visualizing all that, it becomes obvious. At Closed World I received a t-shirt which I wore on Thursday. This generated quite some comments ("Is that from Mogens? He is a funny guy.") and even from strangers who liked the "Meat.Beer.Explode." phrase. I returned to my hotel pretty early to do some preparation for my own session and in the evening I went to Ruby Skye for the Oracle Benelux Party.

Wednesday was my own presentation called "Oracle Database 11g's Result Cache". At Hotel Nikko in the Bay View room. This room was on the 25th floor and had a magnificent view of the bay. The session went well, and I was exactly on time. Unfortunately, I heard later that not everybody could see the SQL*Plus screen well, even though I had specifically asked the guy at the back of the room if he could read it... I received some interesting questions at the end, which I will try to answer in some future blogpost. Next up was the Bloggers Meetup, which was fun. And afterwards, Roel Hartman, Jacco Landlust and his girlfriend Margot and me went to the appreciation event, where I saw Don Henley, the Black Eyed Peas (slow start, but great ending) and two songs of the Steve Miller Band.

On Thursday I only saw two sessions, of which I really liked one. It was Jože Senegačnik's session called Query Transformations about all kinds of transformations the cost based optimizer performs when hard parsing queries. These transformations are visible in the 10053 trace file, which I rarely use. The session was great because it fitted great with what I already knew, but there were lots of interesting little details I did not know. After gathering at the OTN Lounge with some fellow ACEs and skipping the wrap-up party, I headed back to my hotel.

Upcoming conferences

Tomorrow I'm off to Oracle OpenWorld for the third time in a row. I'm looking forward to a week attending great sessions, meeting friends, visiting the appreciation event, the Oracle ACE dinner and the Blogger's Meetup and presenting my session about the result cache. If you want to attend that session, the session details are:

ID#: S319106
Title: Oracle Database 11g’s Result Cache
Date: 22-SEP-10
Time: 13:00-14:00
Venue: Hotel Nikko
Room: Bay View

I will also be giving the result cache presentation at two other conferences closer to home.

At October 5, Planboard - known for their successful Oracle DBA Symposiums - organizes their first Oracle Developer Symposium. This is a conference you should not miss if you are a Dutch speaking Oracle developer. One day, with three tracks: Database, APEX and Fusion Middleware. More details can be found here.

At October 27 and 28, ODTUG OPP 2010 will be held in Brussels. They have combined two conferences: one about PL/SQL and one about APEX, with 5 tracks in total.

Last wednesday I rehearsed the presentation at a knowledge session at CIBER. I will be making some adjustments, but the Wednesday version of my presentation can be downloaded here:

The powerpoint and the scripts.

European Summer Time

I needed a function to determine how many hours fit in a given day. In the Netherlands, a function that does "return 24;" is not good enough. In the northern hemisphere, Daylight Saving Time is quite common. And in Europe, except Iceland, we have European Summer Time which states that the clock moves one hour forward on the last Sunday in March and it moves one hour backwards on the last Sunday in October. Which means there is a day in March that consists of 23 hours and in October there is one with 25 hours.

So, for my function I needed two other functions: one that returns the date where European Summer Time starts and one that returns the date where European Summer Time ends. I found existing code which - if it had been modularized - looked like this:

create function european_summer_time_start1 (p_year in number)
  return date
is
begin
  execute immediate
    'alter session set nls_date_language = "English"'
  ;
  return
    next_day(to_date('01-04-'||p_year,'dd-mm-rrrr')-1, 'sunday') - 7
  ;
end european_summer_time_start1;

and

create function european_summer_time_end1 (p_year in number)
  return date
is
begin
  execute immediate
    'alter session set nls_date_language="English"'
  ;
  return
    next_day(to_date('01-11-'||p_year,'dd-mm-rrrr')-1, 'sunday') - 7
  ;
end european_summer_time_end1;

It uses the NEXT_DAY function to determine the next sunday after March 31 and October 31, and then subtracts a week to get the last Sunday of March and October. The second parameter ('sunday') is in the date language of the session. And to be sure the function runs correctly, the nls_date_language parameter is set. I don't like this at all, because it sets a session parameter without setting the value back after the function has finished. Code might run differently depending on whether the function has been run previously in the session or not. I want the code to be NLS independent and I came up with these two functions:

create function european_summer_time_start2 (p_year in number)
  return date
is
begin
  return
    trunc
    ( to_date(to_char(p_year) || '0401','yyyymmdd')
    , 'iw'
    ) - interval '1' day
  ;
end european_summer_time_start2;

and

create function european_summer_time_end2 (p_year in number)
  return date
is
begin
  return
    trunc
    ( to_date(to_char(p_year) || '1101','yyyymmdd')
    , 'iw'
    ) - interval '1' day
  ;
end european_summer_time_end2;

Here I use the TRUNC-function to set the date value back to the beginning of the ISO-week, which is always on Monday, regardless of NLS-settings. Then subtract one day and we have the last Sunday of March/October.

Then I saw the formula on Wikipedia in the same link I mentioned earlier:

Formula used to calculate the beginning of European Summer Time:
Sunday (31 − (5 * y ÷ 4 + 4) mod 7) March at 01:00 GMT

Formula used to calculate the end of European Summer Time:
Sunday (31 − (5 * y ÷ 4 + 1) mod 7) October at 01:00 GMT

Implementing these formulas lead to a third variant of the two functions:

create function european_summer_time_start3 (p_year in number)
  return date
is
begin
  return
    to_date
    ( to_char(p_year) ||
      '03' ||
      to_char(31 - trunc(mod(5 * p_year / 4 + 4, 7)))
    , 'yyyymmdd'
    )
  ;
end european_summer_time_start3;

and

create function european_summer_time_end3 (p_year in number)
  return date
is
begin
  return
    to_date
    ( to_char(p_year) ||
      '10' ||
      to_char(31 - trunc(mod(5 * p_year / 4 + 1, 7)))
    , 'yyyymmdd'
    )
  ;
end european_summer_time_end3;

Next, I compared the three variants. They return the right dates:

rwijk@TEST10> select european_summer_time_start1(year) ws1
  2       , european_summer_time_start2(year) ws2
  3       , european_summer_time_start3(year) ws3
  4       , european_summer_time_end1(year)   sw1
  5       , european_summer_time_end2(year)   sw2
  6       , european_summer_time_end3(year)   sw3
  7    from ( select 1999 + level year
  8             from dual
  9          connect by level <= 11
 10         )
 11  /

WS1        WS2        WS3        SW1        SW2        SW3
---------- ---------- ---------- ---------- ---------- ----------
26-03-2000 26-03-2000 26-03-2000 29-10-2000 29-10-2000 29-10-2000
25-03-2001 25-03-2001 25-03-2001 28-10-2001 28-10-2001 28-10-2001
31-03-2002 31-03-2002 31-03-2002 27-10-2002 27-10-2002 27-10-2002
30-03-2003 30-03-2003 30-03-2003 26-10-2003 26-10-2003 26-10-2003
28-03-2004 28-03-2004 28-03-2004 31-10-2004 31-10-2004 31-10-2004
27-03-2005 27-03-2005 27-03-2005 30-10-2005 30-10-2005 30-10-2005
26-03-2006 26-03-2006 26-03-2006 29-10-2006 29-10-2006 29-10-2006
25-03-2007 25-03-2007 25-03-2007 28-10-2007 28-10-2007 28-10-2007
30-03-2008 30-03-2008 30-03-2008 26-10-2008 26-10-2008 26-10-2008
29-03-2009 29-03-2009 29-03-2009 25-10-2009 25-10-2009 25-10-2009
28-03-2010 28-03-2010 28-03-2010 31-10-2010 31-10-2010 31-10-2010

11 rows selected.

But as mentioned in the Wikipedia article, the third variant produces wrong results in 2100, which is not a leap year:

rwijk@TEST10> select european_summer_time_start1(year) ws1
  2       , european_summer_time_start2(year) ws2
  3       , european_summer_time_start3(year) ws3
  4       , european_summer_time_end1(year)   sw1
  5       , european_summer_time_end2(year)   sw2
  6       , european_summer_time_end3(year)   sw3
  7    from ( select 2097 + level year
  8             from dual
  9          connect by level <= 5
 10         )
 11  /

WS1        WS2        WS3        SW1        SW2        SW3
---------- ---------- ---------- ---------- ---------- ----------
30-03-2098 30-03-2098 30-03-2098 26-10-2098 26-10-2098 26-10-2098
29-03-2099 29-03-2099 29-03-2099 25-10-2099 25-10-2099 25-10-2099
28-03-2100 28-03-2100 27-03-2100 31-10-2100 31-10-2100 30-10-2100
27-03-2101 27-03-2101 26-03-2101 30-10-2101 30-10-2101 29-10-2101
26-03-2102 26-03-2102 25-03-2102 29-10-2102 29-10-2102 28-10-2102

5 rows selected.

It probably is the faster function though, as it doesn't need date arithmetic and ISO week calculation, but I was curious in how much it differs. Here is the test that I used:

rwijk@TEST10> var N number
rwijk@TEST10> exec :N := 100000

PL/SQL procedure successfully completed.

rwijk@TEST10> set timing on
rwijk@TEST10> declare
  2    l_date date;
  3  begin
  4    for i in 1..:N
  5    loop
  6      l_date := european_summer_time_start1(2010);
  7    end loop;
  8  end;
  9  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.20
rwijk@TEST10> declare
  2    l_date date;
  3  begin
  4    for i in 1..:N
  5    loop
  6      l_date := european_summer_time_start2(2010);
  7    end loop;
  8  end;
  9  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.30
rwijk@TEST10> declare
  2    l_date date;
  3  begin
  4    for i in 1..:N
  5    loop
  6      l_date := european_summer_time_start3(2010);
  7    end loop;
  8  end;
  9  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.79

The test was executed several times with consistent timings. So now I have to decide whether I use the function that works fastest but breaks in 2100, or one that runs a little slower but keeps on working. Performance doesn't really matter here, so I'll stick with the second variant.

The PL/SQL Challenge effect

In Google Analytics I noticed a strange peak in my page visits. On a normal weekday when I haven't posted something new, approximately 200 people visit one or more blogposts here. But on Thursday July 8, there were 299. When zooming in on that day, I noticed that my blogpost about SAVE EXCEPTIONS was visited 101 times, where 5 or 10 per day is normal for that blogpost. When something like this happens, it is usually caused by someone posting a link to my blog, but that was not the case now. Then I looked at the searched keywords and I saw these lines (I filtered out lots of other rows):

save exceptions 8
oracle save exceptions 6
forall log errors save exceptions 2
forall save exceptions log errors 2
forall save exceptions oracle 2
log errors save exceptions 2
oracle forall save exceptions 2
save exception 2
"log errors" "save exceptions" 1
"log errors" "save exceptions" "dbms_errlog.create_error_log" 1
"log errors" "save exceptions" forall 1
"save exceptions" "log errors" 1
_http://rwijk.blogspot.com/2007/11/save-exceptions.html 1
both log errors and save exception 1
create_error_log "save exceptions" 1
forall 'log error' 'save exceptions' 1
forall log errors save exception both togheter 1
forall save exception 1
forall save exceptions 1
forall save exceptions and log errors together 1
forall save exceptions log errors sql%bulk_exceptions 1
forall statement "log errors" "save exceptions" "bulk_exceptions" 1
log eroors save exceptions forall 1
log error oracle save exceptions 1
log error save exceptions 1
log errors save exceptions forall oracle 1
log errors,save exception with for all oracle 1
oracle bulk "save exceptions" 1
oracle forall log errors save exceptions 1
oracle forall save exception 1
oracle forall save exceptions dbms_errlog bulk_exceptions 1
oracle pl sql log errors save exceptions 1
oracle save exceptions forall 1
oracle save exceptions log errors 1
oracle save exceptions log errors for all 1
oracle save exceptions vs log errors 1
pl/sql log errors save exception forall 1
pl/sql save exceptions 1
plsql using log errors and save exception 1
save exceprions 1
save exceptions example 1
save exceptions in oracle sql 1
save exceptions in pl sql 1
save exceptions log error oracle 1
save exeptions 1
use log errors and save exception in forall orcle 1
using save exceptions and log errors in the same forall statement 1
using save exceptions and log errors inside forall pl sql 1
when save exceptions are used in oracle 1

And then I remembered last week's PL/SQL Challenge. On July 8, it had a nice question about what happens when you combine the FORALL SAVE EXCEPTIONS with a LOG ERRORS clause...

And while I'm talking about the PL/SQL Challenge: if you haven't played it yet and you want to learn the language better, then you should give it a try. If you played the game and you haven't learned anything, then at least you'll have a high score :-). The questions are very diverse regarding the topics and regarding difficulty. So there will always be topics with which you are highly familiar and those with which you are not. For example, I use PL/SQL almost 15 years now, but somehow I almost never used the UTL_FILE package. It just seems counterintuitive to me to work with files when you have a database at your disposal. But by playing the quiz I learned the package inside out.

And for those of you who wished me luck and wanted to know how the playoff went: well, it went smooth. So compliments to the developers of the site. The 10 questions itself contained a lot of text and code to read and grasp in just 15 minutes. And I thought the questions were on average tougher than normal. I managed to answer all questions in time, but I had to rush. I'm not so sure about the correctness of all my answers, though. We'll see.

One Oracle forum to rule them all

Previous year I already expressed some of my thoughts on the OTN Forums and Stack Overflow here. In short my story was: I absolutely love the Q&A engine of Stack Overflow, but the best answers can be found on OTN. However, with more knowledgeable people appearing on Stack Overflow, the gap is closing. In the comments section and from conversations I came to know that there is still some resistance with Stack Overflow to some people on two other points:

- Stack Overflow is primarily aimed at the developers community, so a DBA won't feel much at home as there are only a few real DBA questions. For Oracle this is a real pity, because there is a huge amount of knowledge about Oracle databases among the DBA's. If we could only welcome them as well...

- To categorize questions, Stack Overflow uses tags. People who answer questions use tags to see all potentially interesting questions. However, you'll see lots of not relevant questions as well this way. You can't ever filter out all questions that you don't want to see, but the signal-to-noise ratio should at least be above some personal threshold. And this can be hard to achieve on Stack Overflow depending on your interests.

I ended my blog post last year with the phrase "I wish that the best of both worlds can be combined somewhere in the future.". And this wish can come true, with Stack Exchange. It's now possible to use the brilliant Q&A engine of Stack Overflow and create a forum site for only Oracle related questions. This should be very interesting for DBA's as well, and because of the Oracle-focus, it becomes much easier to express your interests. The community can tag and re-tag all questions and direct them to the world's best experts and wannabe's in Oracle land on a certain field. I have a dream, of no more hardly readable questions with lousy titles, no more inaccessible forum sites, no more trolling and no more questions without reasonable answers. I dream of one Oracle forum that unites all other Oracle forums, so we can learn from ALL experts, not just the few that happen to be present in a particular forum.

So, I support Gary Myers' idea and hope that you do as well. And if you do, please click the FOLLOW button here. The technology is ready, the proposal is there, now we only need to express our wish as a community and commit ourselves to it.

Spread the word.

Oracle 11g's Result Cache at OOW10?

Oracle Mix has a Suggest-a-Session facility for Oracle OpenWorld 2010. Some 120+ sessions are already suggested and you can vote on the sessions you'd like to see on Oracle OpenWorld. The sessions with the most votes will be selected in the program.

My proposal is a session about Oracle 11g's Result Cache. Already 24 people have voted for my session (thank you!), but it looks like I need a couple more. So, if you'd like to see a technical session about the result cache at Oracle OpenWorld or if you just want to support me :-), please vote. Note that you have to vote for at least 3 sessions for your votes to count. The voting closes this Saturday, June 20.

UPDATE 10th July:
I've just received a speaker invitation to present my session at Oracle OpenWorld. Thank you all for the extra votes that made this possible!