Home > Announcements, DBA, NoCOUG > Hot Off The Press: The NoCOUG Journal and the Second International NoCOUG SQL Challenge‏

Hot Off The Press: The NoCOUG Journal and the Second International NoCOUG SQL Challenge‏

February 13, 2011 Leave a comment Go to comments

Dear NoCOUG members and friends,

Is RAC on NAS safe and performant? Can virtual machines deliver the performance we need? Should we store BLOBS in the database? For what situations can we safely use RAID-5? Storage expert James Morle answers these questions and more in the new issue of the NoCOUG Journal. The Wizard of Odds is also back with the Second International NoCOUG SQL Challenge. Click here to download the NoCOUG Journal.

Who amongst us has not visited the AskTom website looking for the solution to an Oracle database problem? In this issue of the NoCOUG Journal, Tom says: When I first started out in IT, I had no experience, no training, no background whatsoever. I was a math major fresh out of college in the year 1987. I hadn’t taken any computer courses beyond the initial “introduction to” type of classes. It wasn’t until I got a job as a computer programmer—advertised as “no experience required”—that I started even really using computers. So, given that I had no experience, no real formal training—how did I get started, how did I get to where I am today? I think it comes down to two simple words: mentorship and participation.  If you agree with Tom Kyte that participation is one of the keys to career success, perhaps you will consider becoming a member of NoCOUG in its 25th anniversary year. Click here to become a NoCOUG member.

In a previous issue of the NoCOUG Journal, Pythian CTO Alex Gorbachev says: If I had to make a decision on whether to invest my money in certification versus conferences or user group fees, I would choose the latter. It definitely gives a better career boost. If you agree that NoCOUG conferences deliver career-boosting Oracle education, perhaps you will consider attending our winter conference which is now less than two weeks away. Click here to register for the winter conference.

To encourage you to become a member of NoCOUG in its 25th anniversary year, Oracle performance expert Craig Shallahamer is offering a discount to NoCOUG members who attend his upcoming performance management classes in Santa Clara. If you want to be able to tackle your next performance problem head on, without blinking, find the root cause with authority, and provide spot on alternatives, by all means take this class. Use discount code NOCOUG when registering. Click here for more information on Craig’s performance management classes.

Oracle would also like to invite NoCOUG members to the Oracle Enterprise Cloud Summit where cloud experts will share real-world best practices, reference architectures, detailed customer case studies, and more. You’ll learn how to transform IT into a superior service provider with a strategy and roadmap for building, deploying, and managing an enterprise cloud. Click here to register for the cloud summit.

Kindest regards,

The NoCOUG Board of Directors

P.S. We hope to see you again at our winter conference which is now less than two weeks away.

Five “DBA Survivors” are all smiles at the summer conference. Hey Chris, you’re holding the NoCOUG Journal upside down!

Categories: Announcements, DBA, NoCOUG
  1. Iggy Fernandez
    February 13, 2011 at 3:30 pm
    Reply

    SQL commands for creating the required data for the SQL challenge are available at http://bit.ly/g58WVn. The results of the previous challenge are available at http://bit.ly/heUb1T.

  2. Ora Monster
    February 14, 2011 at 2:53 pm
    Reply

    This does not seems to be an SQL challenge (at least for me). Also see Rob’s response here:

    http://rwijk.blogspot.com/2009/03/calculating-probabilities-with-n-throws.html

  3. Iggy Fernandez
    February 14, 2011 at 3:08 pm
    Reply

    Ora Monster :

    This does not seems to be an SQL challenge

    This time we decided to make the challenge a little more fun. The previous challenge was a pure SQL exercise. The new challenge starts out as a puzzle. First the relationship between the words has to be determined, perhaps by analyzing the words using SQL or some other means. Once the relationship between the words has been determined, the secret message has to be reconstructed using SQL.

  4. partiallyfunctional
    February 15, 2011 at 8:56 am
    Reply

    Hi Iggy,

    thanks a lot for the interesting article comparing different sql formulations’ execution plans! I was a bit puzzled by the last one, could you expand on it a little bit? Normally, if I remember correctly the “reference” explanation in Chris Antognini’s book (I don’t have it here), the first operation to be executed should be – in case of a non-combining statement like “select”, it’s (only) child (so here, the table scan or rather, the scan’s “child”, the index access – but here I’d assume the second table is scanned first… in fact I’d think that really there was a nested loop, probably, starting with records in the payroll table and getting the employees by index lookup, one by one…
    On the other hand, if this was what’s really happening, then the plan would say so I guess :-;
    Could you give a hint what’s going on here, please?
    Thanks,
    Sigrid

  5. Iggy Fernandez
    February 15, 2011 at 9:12 am
    Reply

    partiallyfunctional :

    Could you give a hint what’s going on here, please?

    Hi, Sigrid,

    Thanks for the careful reading of the SQL Corner article. The last EXPLAIN plan output is definitely an oddity that breaks all the rules for reading query plans 🙂 It is “the exception that proves the rule.” You’re right that the full scan of the PAYROLL table must be performed first; there’s no other possibility.

    SELECT 
  (
    SELECT lname
    FROM personnel
    WHERE personnel.empid = payroll.empid
  )
FROM payroll
WHERE salary = 199170;

Plan hash value: 1891291052

----------------------------------------------------
| Id  | Operation                   | Name         |
----------------------------------------------------
|   0 | SELECT STATEMENT            |              |
|   1 |  TABLE ACCESS BY INDEX ROWID| PERSONNEL    |
|*  2 |   INDEX UNIQUE SCAN         | PERSONNEL_PK |
|*  3 |  TABLE ACCESS FULL          | PAYROLL      |
----------------------------------------------------

2 - access("PERSONNEL"."EMPID"=:B1)
3 - filter("SALARY"=199170)

    Kindest regards,

    Iggy

  6. Vadim Tropashko
    February 15, 2011 at 10:30 am
    Reply
  7. Iggy Fernandez
    February 15, 2011 at 2:25 pm
    Reply

    The following solution was received from Rob van Wijk in the Netherlands.

    Date: Tue, 15 Feb 2011 22:08:01 +0100
    Subject: Second International NoCOUG SQL Challenge

    Hello Iggy and Jonathan,

    At first I thought there wasn’t enough information to get started, but once I understood that that was part of the fun, I was hooked!
    Thanks and compliments to the one who created the puzzle.

    My Es-Cue-El spell:

    with riddle_tree (word1,word2,word3,sort_string) as
( select r.word1
       , r.word2
       , r.word3
       , cast('1' as varchar2(4000))
    from riddle r
   where not exists
         ( select 'dummy'
             from riddle r2
            where r2.word1 = r.word2
               or r2.word3 = r.word2
         )
   union all
  select r.word1
       , r.word2
       , r.word3
       , rt.sort_string || case r.word2 when rt.word1 then '0' else '2' end
    from riddle r
    join riddle_tree rt on r.word2 in (rt.word1,rt.word3)
)
select word2
  from riddle_tree
 order by rpad(sort_string,4000,'1')

    Regards,
    Rob.

  8. Iggy Fernandez
    February 16, 2011 at 7:21 am
    Reply

    The following solution was received from Ilya Chuhnakov:

    Date: Wed, 16 Feb 2011 14:18:21 +0300
    Subject: NoCOUG SQL Challenge 201102

    Hi!

    If it is not too late yet, following are my solutions for the NoCOUG SQL Challenge 201102.

    1. The first solution is based on Oracle MODEL clause and just ‘automagically’ works.
    There is nothing to comment i guess, as that solution is just too simple, thanks Oracle.
    Tested on Oracle DB 11gR1.

    select replace(max(text) keep (dense_rank first order by length(text) desc),'  ',' ') text from (
select word2,text from riddle
model
  dimension by (word2)
  measures (word1,word3, cast('' as varchar2(1000)) text)
  rules automatic order
  (
    text[word2] = text[word1[cv()]] || ' ' || cv(word2) || ' ' || text[word3[cv()]]
  )
)

    2. Since the MODEL-based solution was way too simple, i tried another approach (requires Oracle 11gR2).
    This time I’ve used CONNECT BY clause to build the tree and 11gR2 LISTAGG to compile words into text.
    The tricky part here was the LISTAGG ordering.

    I’ve defined recursive ordering function as:
    I(N) = I(P(N)) + S(N) / (2^L(N))
    where
    N is the node
    P(N) is the parent of N
    S(N) is -1 if N is left side of P(N) (i.e. N=WORD1(P(N)) and +1 otherwise
    L(N) is node’s depth (i.e. connect by’s LEVEL)

    I think something similar is documented somewhere (around http://en.wikipedia.org/wiki/Tree_traversal#Breadth-first_Traversal).
    But this one is just from my head, so the algorithm and its description may be a bit clunky. Sorry for that.

    Anyways, given that algorithm, I’ve used SYS_CONNECT_BY_PATH (for “recursion”) to build the expression that evaluates to I(N).
    And DBMS_AW to actually evaluate the expression to number. Then just aggregate with LISTAGG and KEEP FIRST the biggest text.

    select max(text) keep (dense_rank first order by length(text) desc) text from (
  select listagg(word2,' ') within group (order by i) text
  from (
    select
      word2,
      connect_by_root(word2) root,
      dbms_aw.eval_number(
        '0'||sys_connect_by_path(decode(prior word1,word2,'-','+') || power(2,-level),' ')
      ) i 
    from
      riddle
    connect by
      word2 in (prior word1,prior word3)
  )     
  group by root
)

    —
    with best regards
    Ilya Chuhnakov

  9. Iggy Fernandez
    February 17, 2011 at 10:42 pm
    Reply

    Another solution by Ilya Chuhnakov:

    Date: Fri, 18 Feb 2011 03:22:28 +0300
    Subject: Re: NoCOUG SQL Challenge 201102

    Hi!

    Just got another idea for your riddle.
    It’s CONNECT BY with LISTAGG again, this time with START WITH (for performance reasons).
    And it just uses SYS_CONNECT_BY_PATH to form the node path which is used for LISTAGG ordering.
    Each node appears as either ‘L’ or ‘R’ within the path (whether it is left or right child) and has ‘M’ on it’s end, so the node will be between it’s children after ordering (I originally used ‘abc’, but ‘LMR’ looks better I think).

    select listagg(word2,' ') within group (order by o) text from (
select
  sys_connect_by_path(
    decode(prior word1,word2,'L','R'),
    '/'
  )||'/M' o,
  word2
from riddle
connect by
  word2 in (prior word1,prior word3)
start with
  word2 in (
    select word2 from riddle r0
    where not exists (
      select null from riddle r1
      where r0.word2 in (r1.word1,r1.word3)
    )
  )
)
  10. Iggy Fernandez
    February 22, 2011 at 6:30 pm
    Reply

    The following solution was received from Anton Scheffer:

    Date: Mon, 21 Feb 2011 17:21:54 +0100
    Subject: Oracle Solution

    Hi,

    My solution for the second SQLchallenge is not very portable, using the Oracle specific model clause, but using the following Es-Cue-El spell 

    select text
from ( select nivo, text
       from riddle  
       model  
         dimension by ( word2 )  
         measures ( word1, word3, cast( '' as varchar2(4000) ) text, 0 nivo )  
         rules automatic order
           ( text[word2] = trim( text[word1[cv()]] || ' ' || cv(word2) || ' ' || text[word3[cv()]] )
           , nivo[word2] = greatest( nvl( nivo[word1[cv()]], 0 ), nvl( nivo[word3[cv()]], 0 ) ) + 1 
           )
       order by nivo desc  
     )
where rownum = 1

    I get the following secret message

    TRYING TO TYPE ONE HUNDRED DISTINCT WORDS IN A SINGLE PARAGRAPH IS REALLY TOUGH IF I CANNOT REPEAT ANY OF THEM THEN PROBABLY THOSE WITH MANY LETTERS SHOULD BE USED MAYBE SOME READERS WILL UTILIZE DICTIONARIES THESAURUSES THESAURI OR POSSIBLY EVEN ENCYCLOPEDIAS BUT MY PREFERENCE HAS ALWAYS BEEN THAT GRAY MATTER BETWEEN YOUR EARS SERIOUSLY MARILYN CHALLENGES SUCH AS THIS REQUIRE SKILLS BEYOND MATH SCIENCE AND PHYSICS SO WHAT DO YOU ASK READING COMPREHENSION WRITING ABILITY GOOD OLD FASHIONED ELBOW GREASE SCIENTISTS DONT CARE ABOUT STRUCTURE THEY WANT RESULTS HEY LOOK ONLY ELEVEN MORE LEFT FIVE FOUR THREE TWO DONE

    Anton Scheffer

  11. Iggy Fernandez
    February 22, 2011 at 6:35 pm
    Reply

    The following solution was received from Marcus Matzberger:

    Date: Mon, 21 Feb 2011 20:40:15 +0100
    Subject: SQL Challenge

    Hello,

    my solution worked on Oracle 11g2, though I must admit that after I finished I found solutions at the web I liked better. Anyway, this has been the first time I used RCTE and it took some gray matter between my own ears to get the result.

    Regards
    Marcus

    WITH words (word1, word2, word3,con,lvl) AS (
    -- the first row is where the center column has no corresponding
    -- entry in word1 and word3
    SELECT  r1.word1
           ,r1.word2
           ,r1.word3
           ,' '||r1.word1||' '||r1.word2||' '||r1.word3||' ' con
           ,1 AS lvl
    FROM    riddle r1
    WHERE   NOT EXISTS (
                SELECT  1
                FROM    riddle r2
                WHERE   r1.word2 = r2.word1
                OR      r1.word2 = r2.word3
                )
    UNION ALL
    -- a child row is when the left or right word matches the center word
    SELECT  r3.word1
           ,r3.word2
           ,r3.word3
           ,' '||r3.word1||' '||r3.word2||' '||r3.word3||' ' con
           ,lvl + 1 AS lvl
    FROM    words
            JOIN riddle r3
              ON (words.word1 = r3.word2 OR words.word3 = r3.word2)
    )
-- now read every line ordered by level and
-- replace every row with two others where
-- the word to be replaced is in the word1 column
,ordered_combinations AS (
    SELECT  word1
           ,word2
           ,con
           ,lvl
           ,ROWNUM r
    FROM    (
            -- left words
            SELECT  word1 word1 -- word to be replaced
                   ,word2 -- Match for word1 of the replacing row
                   ,con
                   ,lvl
            FROM    words
            WHERE   NOT(word1 IS NULL AND word3 IS NULL)
            UNION ALL
            -- right words
            SELECT  word3 word1
                   ,word2
                   ,con
                   ,lvl
            FROM    words
            WHERE   NOT(word1 IS NULL AND word3 IS NULL)
            UNION ALL
            --"end" words (leaves of the tree)
            SELECT  NULL word1
                   ,word2
                   ,con
                   ,lvl
            FROM    words
            WHERE   word1 IS NULL AND word3 IS NULL
            ORDER BY lvl
            )
    WHERE   word1 IS NOT NULL
    )
--replace every left word with the matching combination
,replaced (
     word1
    ,word2
    ,con
    ,r
    ,rn
    ,lvl
    ) AS (
    SELECT  comb.word1
           ,comb.word2
           ,comb.con
           ,comb.r + 1 r
           ,0 rn
           ,1 lvl
    FROM    ordered_combinations comb
    WHERE   comb.r = 1
    UNION ALL
    SELECT  next.word1 word1
           ,next.word2 word2
           ,REPLACE(
                 curr.con
                ,' '||curr.word1||' '
                ,NVL(repl.con,' '||curr.word1||' ')-- don't replace leaves
                ) con
           ,next.r + 1 r
           ,repl.r rn
           ,repl.lvl
    FROM    replaced curr
            LEFT JOIN ordered_combinations repl
              ON (curr.word1 = repl.word2)
            JOIN ordered_combinations next
              ON (curr.r = next.r)
    WHERE   ROWNUM = 1
)
-- now take the last record
SELECT  re.con
FROM    replaced re
WHERE   re.r = (SELECT MAX(o.r) FROM ordered_combinations o)
  12. Iggy Fernandez
    February 28, 2011 at 2:35 pm
    Reply

    The following solution was received from Timo Raitalaakso:

    Date: Mon, 28 Feb 2011 23:21:46 +0200
    Subject: Rafu answering

    http://rafudb.blogspot.com/2011/02/nocoug-second-sql-challenge.html
    Including a graphviz picture of the riddle data.

    with aa as (
select word1, word2, word3, word2 gr
from riddle
where word1 is not null
), bb as (
select gr,pre,word
from aa
unpivot (word for pre in (word1 as 1, word2 as 2, word3 as 3))
), cc as (
select gr,pre,word
, first_value(case when pre = 3 and word != gr then gr end ignore nulls)over(partition by word) bg
, first_value(case when pre = 1 and word != gr then gr end ignore nulls)over(partition by word) ag
, min(pre)over(partition by word) mi
, max(pre)over(partition by word) ma
from bb
), dd (gr,mi,ma,pre,word,ord)as (
select gr,mi,ma,pre,word,cast(2 as varchar2(10))
from cc
where cc.pre=2 and cc.bg is null and cc.ag is null
union all
select cc.gr,cc.mi,cc.ma,cc.pre,cc.word
, dd.ord||case when cc.pre = 1 then cc.mi else cc.ma end
from dd inner join cc on cc.pre in (1,3) and dd.word = cc.gr
)
select listagg(dd.word,' ')within group(order by rpad(dd.ord,10,'2'))
from dd
;

    —
    Timo Raitalaakso
    http://rafudb.blogpost.com

  13. Craig
    September 26, 2011 at 6:57 am
    Reply

    Hey Iggy –

    Was a winner / winning solutions ever posted for the SQL Challenge? I haven’t seen anything about it and would like to see the best of what people came up with.

  14. Iggy Fernandez
    September 26, 2011 at 7:36 am
    Reply

    Thanks, Craig. The winner will be announced in the 100th issue of the NoCOUG Journal which will be published in the first week of November to coincide with NoCOUG Conference 100. The PDF of the Journal will be made available on the NoCOUG website.

  15. Craig
    September 29, 2011 at 6:06 am
    Reply

    Thanks Iggy. As always, I look forward to the journal. Congratulations on reaching 100 conferences / journals.. quite the feat, especially in todays environment.

  16. Iggy Fernandez
    October 25, 2011 at 6:37 am
    Reply

    The results of the Second International NoCOUG SQL Challenge have been published in the 100th issue of the NoCOUG Journal (http://bit.ly/rC2gRA.) The winners are Andre Araujo (Australia), Rob van Wijk (Netherlands), and Ilya Chuhnakov (Russia.) Each winner will receive an Amazon Kindle from contest sponsor Pythian and the August Order of the Wooden Pretzel in keeping with the pronouncement of Steven Feuerstein that “some people can perform seeming miracles with straight Es-Cue-El, but the statements end up looking like pretzels created by somebody who is experimenting with hallucinogens.”

  17. Iggy Fernandez
    December 21, 2011 at 7:33 am
    Reply
  1. February 15, 2011 at 9:30 pm
    My Possible Pasts » Blog Archive » NoCOUG SQL Challenge #2 – It’s On!
  2. February 22, 2011 at 7:07 am
    My Possible Pasts » Blog Archive » NoCOUG SQL Challenge #2 – Submitted
  3. February 22, 2011 at 10:48 am
    My Possible Pasts » Blog Archive » NoCOUG SQL Challenge #2 – My Submission

Leave a reply to Iggy Fernandez Cancel reply