Do You Fool Around Sudoku ?

Did you know that 11gR2 database could compromise a Sudoku baffle with a singular question and, many of the time, in reduction than a second ?

The subsequent to question shows you how !
Simply pass a flattened Sudoku grid to it a obtain the outcome instantly !

col "Solution" format a9
col "Problem" format a9

The Magic thing at the back this is called Recursive Subquery Factoring.

The Oracle record gives the subsequent to definition:
If a subquery_factoring_clause refers to its own query_name in the subquery that defines it, then the subquery_factoring_clause is mentioned to be recursive.
A recursive subquery_factoring_clause contingency enclose two question blocks:
the initial is the anchor associate and the second is the recursive member.
The anchor associate contingency be present before the recursive member, and it cannot anxiety query_name.
The anchor associate may be calm of a or more question blocks amalgamated by the set operators: UNION ALL, UNION, INTERSECT or MINUS.
The recursive associate contingency follow the anchor associate and contingency anxiety query_name precisely once.
You contingency mix the recursive associate with the anchor associate using the UNION ALL set operator.

This new underline is a deputy of this aged Hierarchical Query underline that exists in Oracle given the days of Aladdin (well, at least, let go 2 of the database in 1977).
Everyone remembers the aged syntax :

choose empno, ename, job, mgr, turn
from emp
beginning with mgr is nothing
link up by previous empno = mgr;

that could/should be rewritten (but not as frequently as it should) as

withT_Emp (empno, name, level) as
( choose empno, ename, job, mgr, turn
from emp
beginning with mgr is nothing
link up by previous empno = mgr
)
select * from T_Emp;

which uses the "with" syntax, whose principal value is to explain the readability of the query.

Although really efficient, this syntax had the waste of being a Non-Ansi Sql Syntax.
Ansi-Sql chronicle of Hierarchical Query is called Recursive Subquery Factoring.
As of 11gR2, Oracle got agreeable with Ansi Sql and introduced Recursive Subquery Factoring. It is essentially an prolongation of the "With" stipulation that enables recursion.

Now, the new syntax is to question would be

with T_Emp (empno, name, job, mgr, hierlevel) as
( choose E.empno, E.ename, E.job, E.mgr, 1 from emp E where E.mgr is nothing
association all
choose E.empno, E.ename, E.job, E.mgr, T.hierlevel + 1from emp E
come together T_Emp T on ( E.mgr = T.empno )
)
select * from T_Emp;

The anchor associate is a deputy is to "start with"
The recursive associate is processed by iterations.
It joins the Source list (EMP) with the outcome from the Recursive Query itself (T_Emp)
Each iteration functions with the results of all its preceding iterations.
Iteration 1 functions on the results of the initial question
Iteration 2 functions on the results of Iteration 1 and initial question
Iteration 3 functions on the results of Iteration 1, Iteration 2 and initial query.

So, knowing that, the Sudoku question it self-explaining;
The anchor associate contains the "Problem" : The Initial Sudoku and the Position of the initial "hole" in the grid.
The recursive associate tries to reinstate the deliberate hole with any of the 9 number that would prove the 3 manners of sudoku
Recursion growth by the grid until it is complete.

Another e.g. : Fibonaccy Numbers : u n = (u n-1 ) + (u n-2 )
with Fib (u1, u2, depth) as
(select 1, 1, 1 from twin
association all
choose u1+u2, u1, depth+1 from Fib where depth<10
)
select u1 from Fib;

Conclusion
Oracle brings here a new underline (which, to be honest, already existed on other simultaneous systems) and extends the power of the database to new boundaries. It's right away up to developers to try and assessment it and find more utilitarian focus than elucidate puzzles...
But still, elucidate a Sudoku in reduction time it takes to say it remains impressive...

Interesting links:

You might be meddlesome by the subsequent to links that casing not similar aspects of this feature.

Oracle Documentation
Lucas Jellema 's Blog
Fibonaci Numbers
we got the Sudoku shade shots from Sudoku Duo for iPad, constructed by my aged buddy Pierre. Visit his site