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!

Simulating LAG/LEAD IGNORE NULLS

From version 11 onwards, the analytic functions LAG and LEAD have the extra option to RESPECT or IGNORE NULLS. This comes in handy when -for example- you don't want a regular next value like this:

rwijk@ORA11GR2> select empno
  2       , comm
  3       , lead(comm) over (order by empno) next_comm
  4    from emp
  5   order by empno
  6  /

     EMPNO       COMM  NEXT_COMM
---------- ---------- ----------
      7369                   300
      7499        300        500
      7521        500
      7566                  1400
      7654       1400
      7698
      7782
      7788
      7839                     0
      7844          0
      7876
      7900
      7902
      7934

14 rows selected.

but a next value which ignore nulls, like this:

rwijk@ORA11GR2> select empno
  2       , comm
  3       , lead(comm ignore nulls) over (order by empno) next_comm
  4    from emp
  5   order by empno
  6  /

     EMPNO       COMM  NEXT_COMM
---------- ---------- ----------
      7369                   300
      7499        300        500
      7521        500       1400
      7566                  1400
      7654       1400          0
      7698                     0
      7782                     0
      7788                     0
      7839                     0
      7844          0
      7876
      7900
      7902
      7934

14 rows selected.

As you can see the LEAD function is now looking for the next non null value, instead of just the next value. For example, the next_comm value for empno 7521 is not NULL (of empno 7566), but 1400 (of empno 7654).

Last week I attended the ODTUG preview sessions at AMIS, and one of the sessions was Alex Nuijten's session about analytic functions. When he mentioned the new IGNORE NULLS option, he also showed a query how you can simulate the same functionality in versions prior to 11g. That query was a rather lengthy one, so I decided I'd try to come up with a shorter one. And this is the result:

rwijk@ORA11GR2> select empno
  2       , comm
  3       , max(comm) over (order by grp range between 1 following and 1 following) next_comm
  4    from ( select empno
  5                , comm
  6                , sum(nvl2(comm,1,0)) over (order by empno) grp
  7             from emp
  8         )
  9   order by empno
 10  /

     EMPNO       COMM  NEXT_COMM
---------- ---------- ----------
      7369                   300
      7499        300        500
      7521        500       1400
      7566                  1400
      7654       1400          0
      7698                     0
      7782                     0
      7788                     0
      7839                     0
      7844          0
      7876
      7900
      7902
      7934

14 rows selected.

And posting it here on my blog will surely mean someone will show up and post an even shorter one :-).

For completeness, doing the same for a LAG ... IGNORE NULLS query:

rwijk@ORA11GR2> select empno
  2       , comm
  3       , lag(comm ignore nulls) over (order by empno) prev_comm
  4    from emp
  5   order by empno
  6  /

     EMPNO       COMM  PREV_COMM
---------- ---------- ----------
      7369
      7499        300
      7521        500        300
      7566                   500
      7654       1400        500
      7698                  1400
      7782                  1400
      7788                  1400
      7839                  1400
      7844          0       1400
      7876                     0
      7900                     0
      7902                     0
      7934                     0

14 rows selected.

This one can be simulated in versions prior to 11g with the following query:

rwijk@ORA11GR2> select empno
  2       , comm
  3       , max(comm) over (order by grp range between 1 following and 1 following) prev_comm
  4    from ( select empno
  5                , comm
  6                , sum(nvl2(comm,1,0)) over (order by empno desc) grp
  7             from emp
  8         )
  9   order by empno
 10  /

     EMPNO       COMM  PREV_COMM
---------- ---------- ----------
      7369
      7499        300
      7521        500        300
      7566                   500
      7654       1400        500
      7698                  1400
      7782                  1400
      7788                  1400
      7839                  1400
      7844          0       1400
      7876                     0
      7900                     0
      7902                     0
      7934                     0

14 rows selected.