Oracle RDBMS 11gR2 – Solving a Sudoku using Recursive Subquery Factoring

Oracle Database 11g Release 2 introduces a new feature called Recursive Subquery Factoring. My collegue Lucas sees it as a substitute for Connect By based hierarchical querying, Oracle RDBMS 11gR2 – new style hierarchical querying using Recursive Subquery Factoring. When I first was thinking about a pratical use for this feature I couldn’t come up with anything, but on second thought:: solving a Sudoku!

Say you have a sudoku like:

Oracle RDBMS 11gR2 - Solving a Sudoku using Recursive Subquery Factoring 250px sudoku by l2g 20050714 svg

 To solve this sudoku you first have to transforms this to a single string by appending all rows together:

"53  7    6  195    98    6 8   6   34  8 3  17   2   6 6    28    419  5    8  79"

Past this string into a Recursive Subquery, run it and you get a new string with your solved sudoku:

with x( s, ind ) as
( select sud, instr( sud, ' ' )
  from ( select '53  7    6  195    98    6 8   6   34  8 3  17   2   6 6    28    419  5    8  79' sud from dual )
  union all
  select substr( s, 1, ind - 1 ) || z || substr( s, ind + 1 )
       , instr( s, ' ', ind + 1 )
  from x
     , ( select to_char( rownum ) z
         from dual
         connect by rownum <= 9
       ) z
  where ind > 0
  and not exists ( select null
                   from ( select rownum lp
                          from dual
                          connect by rownum <= 9
                        )
                   where z = substr( s, trunc( ( ind - 1 ) / 9 ) * 9 + lp, 1 )
                   or    z = substr( s, mod( ind - 1, 9 ) - 8 + lp * 9, 1 )
                   or    z = substr( s, mod( trunc( ( ind - 1 ) / 3 ), 3 ) * 3
                                      + trunc( ( ind - 1 ) / 27 ) * 27 + lp
                                      + trunc( ( lp - 1 ) / 3 ) * 6
                                   , 1 )
                 )
)
select s
from x
where ind = 0
/

sqlplus

This string can be transformed back to a nice display of the solution

Oracle RDBMS 11gR2 - Solving a Sudoku using Recursive Subquery Factoring 250px sudoku by l2g 20050714 solution svg

 

So with Recursive Subquery Factoring you can solve your sudokus in 1 statement wich does fit on your screen, not something like in Solving a Sudoku with 1 SQL-statement: the Model-clause

 

Anton

Tags:,

45 Comments

  1. Mirek Burnejko January 28, 2012
  2. Dora December 16, 2011
  3. jareeq October 7, 2011
  4. Tonny August 11, 2011
  5. Ramesh G July 6, 2010
  6. emil March 2, 2010
  7. Anton Scheffer January 4, 2010
  8. C December 31, 2009
  9. Anton Scheffer December 22, 2009
  10. Bijesh K December 22, 2009
  11. W. G. December 13, 2009
  12. Anton Scheffer December 11, 2009
  13. addintel December 11, 2009
  14. Arnaud Ladrière December 2, 2009
  15. Cafer November 12, 2009
  16. Frank November 9, 2009
  17. Raja November 7, 2009
  18. Anton Scheffer November 6, 2009
  19. Mike November 6, 2009
  20. Frank November 5, 2009
  21. Anton Scheffer November 3, 2009
  22. Dan November 3, 2009
  23. Anton Scheffer November 2, 2009
  24. SRINI November 2, 2009
  25. daily sudoku November 2, 2009
  26. club penguin toys November 1, 2009
  27. monty November 1, 2009
  28. sal November 1, 2009
  29. covalic November 1, 2009
  30. Helpers November 1, 2009
  31. aleph November 1, 2009
  32. fooledbyprimes October 31, 2009
  33. chithanh October 31, 2009
  34. chithanh October 31, 2009
  35. David October 31, 2009
  36. Prakash Murthy October 31, 2009
  37. Jack Hewitt October 31, 2009
  38. Ittichai Chammavanijakul October 22, 2009
  39. Jean-Marc Desvaux October 22, 2009
  40. Anton Scheffer October 14, 2009
  41. Laurent Schneider October 14, 2009
  42. Laurent Schneider October 14, 2009
  43. Marco Gralike October 13, 2009
  44. Rob van Wijk October 13, 2009
  45. Lucas Jellema October 13, 2009