简单分析《趣味题》中的SQL
来源：互联网 发布：安卓一键转发后台源码 编辑：程序博客网 时间：2024/05/16 12:41
这是原文地址《趣味题》，题目是这样的：
1～9有9个数字，三个三个一组，可能正好能组成一个加法等式比如：124＋659＝783首先是szusunny在8楼给出了一个解决方案：
01with tmp as( 02select a.num from (select rownum as num from dual connect by rownum <= 999) a 03where substr(a.num,1,1) != substr(a.num,2,1) 04 and substr(a.num,3,1) != substr(a.num,2,1) 05 and substr(a.num,1,1) != substr(a.num,3,1) 06 and a.num >= 100 07) 08-- 09select10a.num || '+' || b.num || '=' || c.num as str 11from tmp a, tmp b, tmp c 12where replace(replace(replace( 13 replace(replace(replace( 14 replace(replace(replace('123456789', 15 substr(a.num,1,1)),substr(a.num,2,1)),substr(a.num,3,1)), 16 substr(b.num,1,1)),substr(b.num,2,1)),substr(b.num,3,1)), 17 substr(c.num,1,1)),substr(c.num,2,1)),substr(c.num,3,1)) 18 is null19 and a.num + b.num = c.num 20 and a.num < b.num 21 and b.num < c.num我们逐步分析：
1select rownum as num from dual connect by rownum <= 999这个语句是产生 1~999 的连续序列。
1select a.num from (select rownum as num from dual connect by rownum <= 999) a 2where substr(a.num,1,1) != substr(a.num,2,1) 3 and substr(a.num,3,1) != substr(a.num,2,1) 4 and substr(a.num,1,1) != substr(a.num,3,1) 5 and a.num >= 100经过外层条件的过滤后就产生了一组三位数，这个三位数满足以下条件：
1。三个位两两不想等
2。范围是 123 ~ 987
将这组数字放在临时表tmp中，以备下面的查询使用。
01select02a.num || '+' || b.num || '=' || c.num as str 03from tmp a, tmp b, tmp c 04where replace(replace(replace( 05 replace(replace(replace( 06 replace(replace(replace('123456789', 07 substr(a.num,1,1)),substr(a.num,2,1)),substr(a.num,3,1)), 08 substr(b.num,1,1)),substr(b.num,2,1)),substr(b.num,3,1)), 09 substr(c.num,1,1)),substr(c.num,2,1)),substr(c.num,3,1)) 10 is null11 and a.num + b.num = c.num 12 and a.num < b.num 13 and b.num < c.num这个语句有点长，其中select的表达式就是构建输出xxx+yyy=zzz，重点是where后面的条件，先看冗长、一层层嵌套的replace函数：
1replace(replace(replace( 2replace(replace(replace( 3replace(replace(replace('123456789', 4 substr(a.num,1,1)),substr(a.num,2,1)),substr(a.num,3,1)), 5 substr(b.num,1,1)),substr(b.num,2,1)),substr(b.num,3,1)), 6 substr(c.num,1,1)),substr(c.num,2,1)),substr(c.num,3,1)) 7is null简单来说就是3个3位数字（共9位）是否都在123456789这9个数字中，并且没有重复。
基本思路是穷举法，通过将临时表按条件自联接两次，从而获得所有可能的结果。
其中，9个replace函数嵌套的代码可以用translate函数代替：
1translate('123456789','0' || a.num || b.num || c.num,'0') is null最后OO将该方案修改为更加简练的语句：
01with t as( 02 select x from (select level+122 x from dual connect by level<=(987-122)) 03 where substr(x,1,1)<>substr(x,2,1) 04 and substr(x,1,1)<>substr(x,3,1) 05 and instr(x,0)=0 06) 07--select count(*) from ( 08 select a.x||'+'||b.x||'='||c.x 09 from t a,t b,t c 10 where a.x+b.x=c.x 11 and a.x<494 12 and a.x<b.x 13 and b.x<c.x 14 and translate('123456789','$'||a.x||b.x||c.x,'$') is null15--);当然OO也做了些许优化，但是基本思路还是用穷举法遍历所有可能的组合。
执行计划和统计信息如下，我的服务器上执行用时约13秒：
01------------------------------------------------------------------------------------------------------------ 02| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 03------------------------------------------------------------------------------------------------------------ 04| 0 | SELECT STATEMENT | | 1 | 39 | 8 (0)| 00:00:01 | 05| 1 | TEMP TABLE TRANSFORMATION | | | | | | 06| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6921_55670 | | | | | 07|* 3 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 | 08|* 4 | CONNECT BY WITHOUT FILTERING| | | | | | 09| 5 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | 10| 6 | NESTED LOOPS | | 1 | 39 | 6 (0)| 00:00:01 | 11| 7 | MERGE JOIN CARTESIAN | | 1 | 26 | 4 (0)| 00:00:01 | 12|* 8 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 | 13| 9 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6921_55670 | 1 | 13 | 2 (0)| 00:00:01 | 14| 10 | BUFFER SORT | | 1 | 13 | 4 (0)| 00:00:01 | 15| 11 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 | 16| 12 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6921_55670 | 1 | 13 | 2 (0)| 00:00:01 | 17|* 13 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 | 18| 14 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6921_55670 | 1 | 13 | 2 (0)| 00:00:01 | 19------------------------------------------------------------------------------------------------------------ 20 21Predicate Information (identified by operation id): 22--------------------------------------------------- 23 24 3 - filter(SUBSTR(TO_CHAR("X"),1,1)<>SUBSTR(TO_CHAR("X"),2,1) AND 25 SUBSTR(TO_CHAR("X"),1,1)<>SUBSTR(TO_CHAR("X"),3,1) AND INSTR(TO_CHAR("X"),'0')=0) 26 4 - filter(LEVEL<=865) 27 8 - filter("A"."X"<494) 28 13 - filter("C"."X"="A"."X"+"B"."X" AND "A"."X"<"B"."X" AND "B"."X"<"C"."X" AND 29 TRANSLATE('123456789','$'||TO_CHAR("A"."X")||TO_CHAR("B"."X")||TO_CHAR("C"."X"),'$') IS NULL) 30 31 32Statistics 33---------------------------------------------------------- 34 2 recursive calls 35 8 db block gets 36 430532 consistent gets 37 1 physical reads 38 600 redo size 39 4873 bytes sent via SQL*Net to client 40 644 bytes received via SQL*Net from client 41 13 SQL*Net roundtrips to/from client 42 2 sorts (memory) 43 0 sorts (disk) 44 168 rows processed可以看到这种方法的成本还是很高的。
newkid在41楼给出了另外一种解决方案，他用到了Oracle 11g的递归CTE语法，递归CTE在SQL Server 2005开始支持，也是SQL Server层次查询的常规做法：
01WITH n AS ( 02 SELECT ROWNUM n FROM DUAL CONNECT BY ROWNUM<=9 03 ) 04,t (n1,n2,n3,n4,n5,n6,n7,n8,n9,lvl) AS ( 05 SELECT n,0,0,0,0,0,0,0,0,1 06 FROM n 07 WHERE n<=4 08 UNION ALL09 SELECT t.n1 10 ,DECODE(t.lvl,1,n.n,t.n2) 11 ,DECODE(t.lvl,2,n.n,t.n3) 12 ,DECODE(t.lvl,3,n.n,t.n4) 13 ,DECODE(t.lvl,4,n.n,t.n5) 14 ,DECODE(t.lvl,5,n.n,t.n6) 15 ,DECODE(t.lvl,6,n.n,t.n7) 16 ,DECODE(t.lvl,7,n.n,t.n8) 17 ,DECODE(t.lvl,8,n.n,t.n9) 18 ,t.lvl+1 19 FROM t,n 20 WHERE n.n NOT IN (n1,n2,n3,n4,n5,n6,n7,n8,n9) 21 AND (t.lvl=1 AND n.n>t.n1 AND n.n+t.n1<=9 22 OR (t.lvl=2 AND n.n - t.n1- t.n2 IN (0,1)) 23 OR (t.lvl=5 AND n.n = MOD(t.n4+ t.n5,10)) 24 OR t.lvl IN (3,4,6,7,8) 25 ) 26 ) 27SELECT n1||n7||n4||' + '||n2||n8||n5||' = '||n3||n9||n6 28 FROM t 29WHERE lvl=9 30 AND n1*100+n7*10+n4 + n2*100+n8*10+n5 = n3*100+n9*10+n6 31 ;N1~N9的意义比较特殊，并不是直观 N1N2N3 + N4N5N6 = N7N8N9，从最外层查询的判断条件 n1*100+n7*10+n4 + n2*100+n8*10+n5 = n3*100+n9*10+n6 可以知道N1N7N4 + N2N8N5 = N3N9N6，用竖式来表示比较容易看出来规律来：
1 N1 N7 N4 2+ N2 N8 N5 3---------- 4 N3 N9 N6只要稍作分析可以得到以下几个性质，其实都是小学数学的问题：
1。N1和N2中较小的那个最大值不超过4，如果规定第一组三位数一定要比第二组三位数小，那么N1的取值范围只能是1~4，这也解释n<=4这个条件。
2。N1+N2不能超过9，这个估计比较容易明白的。
3。(N4+N5)%10 = N6
4。N3-N2-N1的值不是1 就是 0 。
这个SQL还是比较难分析，由于是递归CTE的，最好一步一步地跟踪临时表t的变化。
起始步骤，称为第0步，首先是union all 之前的语句，比较好懂：
1WITH n AS ( 2 SELECT ROWNUM n FROM DUAL CONNECT BY ROWNUM<=9 3 ) 4,t (n1,n2,n3,n4,n5,n6,n7,n8,n9,lvl) AS ( 5 SELECT n,0,0,0,0,0,0,0,0,1 6 FROM n 7 WHERE n<=4 8) 9select * from t
1N1 N2 N3 N4 N5 N6 N7 N8 N9 LVL 2--- --- --- --- --- --- --- --- --- --- 3 1 0 0 0 0 0 0 0 0 1 4 2 0 0 0 0 0 0 0 0 1 5 3 0 0 0 0 0 0 0 0 1 6 4 0 0 0 0 0 0 0 0 1进入迭代过程，临时表不断增长：
第1~2步：
01WITH n AS ( 02 SELECT ROWNUM n FROM DUAL CONNECT BY ROWNUM<=9 03 ) 04,t (n1,n2,n3,n4,n5,n6,n7,n8,n9,lvl) AS ( 05 SELECT n,0,0,0,0,0,0,0,0,1 06 FROM n 07 WHERE n<=4 08 UNION ALL09 SELECT t.n1 10 ,DECODE(t.lvl,1,n.n,t.n2) 11 ,DECODE(t.lvl,2,n.n,t.n3) 12 ,DECODE(t.lvl,3,n.n,t.n4) 13 ,DECODE(t.lvl,4,n.n,t.n5) 14 ,DECODE(t.lvl,5,n.n,t.n6) 15 ,DECODE(t.lvl,6,n.n,t.n7) 16 ,DECODE(t.lvl,7,n.n,t.n8) 17 ,DECODE(t.lvl,8,n.n,t.n9) 18 ,t.lvl+1 19 FROM t,n 20 WHERE n.n NOT IN (n1,n2,n3,n4,n5,n6,n7,n8,n9) 21 AND (t.lvl=1 AND n.n > t.n1 AND n.n+t.n1<=9 22 OR (t.lvl=2 AND n.n - t.n1 - t.n2 IN (0,1) ) 23 OR (t.lvl=5 AND n.n = MOD(t.n4+ t.n5,10) ) 24 OR t.lvl IN (3,4,6,7,8) 25 ) 26 ) 27SELECT n1,n2,n3,n4,n5,n6,n7,n8,n9,lvl,lvl-1 step 28 FROM t 29WHERE lvl<=301N1 N2 N3 N4 N5 N6 N7 N8 N9 LVL STEP <-- 第0步的结果 02--- --- --- --- --- --- --- --- --- --- ---------- 03 1 0 0 0 0 0 0 0 0 1 0 04 2 0 0 0 0 0 0 0 0 1 0 05 3 0 0 0 0 0 0 0 0 1 0 06 4 0 0 0 0 0 0 0 0 1 0 07 N1 N2 N3 N4 N5 N6 N7 N8 N9 LVL STEP <-- 第1步的结果 08--- --- --- --- --- --- --- --- --- --- ---------- 09 1 2 0 0 0 0 0 0 0 2 1 10 1 3 0 0 0 0 0 0 0 2 1 11 1 4 0 0 0 0 0 0 0 2 1 12 1 5 0 0 0 0 0 0 0 2 1 13 1 6 0 0 0 0 0 0 0 2 1 14 1 7 0 0 0 0 0 0 0 2 1 15 1 8 0 0 0 0 0 0 0 2 1 16 2 3 0 0 0 0 0 0 0 2 1 17 2 4 0 0 0 0 0 0 0 2 1 18 2 5 0 0 0 0 0 0 0 2 1 19 2 6 0 0 0 0 0 0 0 2 1 20 2 7 0 0 0 0 0 0 0 2 1 21 3 4 0 0 0 0 0 0 0 2 1 22 3 5 0 0 0 0 0 0 0 2 1 23 3 6 0 0 0 0 0 0 0 2 1 24 4 5 0 0 0 0 0 0 0 2 1 25 N1 N2 N3 N4 N5 N6 N7 N8 N9 LVL STEP <-- 第2步的结果 26--- --- --- --- --- --- --- --- --- --- ---------- 27 1 2 3 0 0 0 0 0 0 3 2 28 1 2 4 0 0 0 0 0 0 3 2 29 1 3 4 0 0 0 0 0 0 3 2 30 1 3 5 0 0 0 0 0 0 3 2 31 1 4 5 0 0 0 0 0 0 3 2 32 1 4 6 0 0 0 0 0 0 3 2 33 1 5 6 0 0 0 0 0 0 3 2 34 1 5 7 0 0 0 0 0 0 3 2 35 1 6 7 0 0 0 0 0 0 3 2 36 1 6 8 0 0 0 0 0 0 3 2 37 1 7 8 0 0 0 0 0 0 3 2 38 1 7 9 0 0 0 0 0 0 3 2 39 1 8 9 0 0 0 0 0 0 3 2 40 2 3 5 0 0 0 0 0 0 3 2 41 2 3 6 0 0 0 0 0 0 3 2 42 2 4 6 0 0 0 0 0 0 3 2 43 2 4 7 0 0 0 0 0 0 3 2 44 2 5 7 0 0 0 0 0 0 3 2 45 2 5 8 0 0 0 0 0 0 3 2 46 2 6 8 0 0 0 0 0 0 3 2 47 2 6 9 0 0 0 0 0 0 3 2 48 2 7 9 0 0 0 0 0 0 3 2 49 3 4 7 0 0 0 0 0 0 3 2 50 3 4 8 0 0 0 0 0 0 3 2 51 3 5 8 0 0 0 0 0 0 3 2 52 3 5 9 0 0 0 0 0 0 3 2 53 3 6 9 0 0 0 0 0 0 3 2 54 4 5 9 0 0 0 0 0 0 3 2从第1第2步结果可以看出
1。条件 t.lvl=1 AND n.n > t.n1 AND n.n+t.n1<=9 限制了第1步的结果范围，不会出现以下的一条记录：
1N1 N2 N3 N4 N5 N6 N7 N8 N9 LVL STEP 2--- --- --- --- --- --- --- --- --- --- ---------- 3 1 9 0 0 0 0 0 0 0 2 12。条件 t.lvl=2 AND n.n - t.n1 - t.n2 IN (0,1) 限制了第2步的结果范围，不会出现以下的一条记录：
1N1 N2 N3 N4 N5 N6 N7 N8 N9 LVL STEP 2--- --- --- --- --- --- --- --- --- --- ---------- 3 1 2 8 0 0 0 0 0 0 3 2如此类推，第5步就是判断 (N4+N5)%10 = N6，SQL中的条件字句是t.lvl=5 AND n.n = MOD(t.n4+ t.n5,10)，摘录部分结果集：
01N1 N2 N3 N4 N5 N6 N7 N8 N9 LVL 02--- --- --- --- --- --- --- --- --- --- 03 4 5 9 1 7 8 0 0 0 6 04 4 5 9 2 1 3 0 0 0 6 05 4 5 9 2 6 8 0 0 0 6 06 4 5 9 3 8 1 0 0 0 6 07 4 5 9 6 1 7 0 0 0 6 08 4 5 9 6 2 8 0 0 0 6 09 4 5 9 6 7 3 0 0 0 6 10 4 5 9 7 1 8 0 0 0 6 11 4 5 9 7 6 3 0 0 0 6 12 4 5 9 8 3 1 0 0 0 6可以清楚看出(N4+N5)%10=N6这样的规律。
现在回过头来看，那一堆decode语句：
01SELECT t.n1 02 ,DECODE(t.lvl,1,n.n,t.n2) 03 ,DECODE(t.lvl,2,n.n,t.n3) 04 ,DECODE(t.lvl,3,n.n,t.n4) 05 ,DECODE(t.lvl,4,n.n,t.n5) 06 ,DECODE(t.lvl,5,n.n,t.n6) 07 ,DECODE(t.lvl,6,n.n,t.n7) 08 ,DECODE(t.lvl,7,n.n,t.n8) 09 ,DECODE(t.lvl,8,n.n,t.n9) 10 ,t.lvl+1每一次迭代都是“修改”修改一个列，与此反复经过9步迭代之后N1~N9都被“修改”了，最后，最外层查询语句作最终的验证筛选
1SELECT n1||n7||n4||' + '||n2||n8||n5||' = '||n3||n9||n6 2 FROM t 3WHERE lvl=9 4 AND n1*100+n7*10+n4 + n2*100+n8*10+n5 = n3*100+n9*10+n6 5 ;执行计划和统计信息如下，用时约0.2秒：
01----------------------------------------------------------------------------------------------------------------------- 02| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 03----------------------------------------------------------------------------------------------------------------------- 04| 0 | SELECT STATEMENT | | 2 | 260 | 8 (0)| 00:00:01 | 05| 1 | TEMP TABLE TRANSFORMATION | | | | | | 06| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D695D_55670 | | | | | 07| 3 | COUNT | | | | | | 08|* 4 | CONNECT BY WITHOUT FILTERING | | | | | | 09| 5 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | 10|* 6 | VIEW | | 2 | 260 | 6 (0)| 00:00:01 | 11| 7 | UNION ALL (RECURSIVE WITH) BREADTH FIRST| | | | | | 12|* 8 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 | 13| 9 | TABLE ACCESS FULL | SYS_TEMP_0FD9D695D_55670 | 1 | 13 | 2 (0)| 00:00:01 | 14| 10 | NESTED LOOPS | | 1 | 143 | 4 (0)| 00:00:01 | 15| 11 | RECURSIVE WITH PUMP | | | | | | 16|* 12 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 | 17| 13 | TABLE ACCESS FULL | SYS_TEMP_0FD9D695D_55670 | 1 | 13 | 2 (0)| 00:00:01 | 18----------------------------------------------------------------------------------------------------------------------- 19 20Predicate Information (identified by operation id): 21--------------------------------------------------- 22 23 4 - filter(ROWNUM<=9) 24 6 - filter("LVL"=9 AND "N1"*100+"N7"*10+"N4"+"N2"*100+"N8"*10+"N5"="N3"*100+"N9"*10+"N6") 25 8 - filter("N"<=4) 26 12 - filter("N"."N"<>"N1" AND "N"."N"<>"N2" AND "N"."N"<>"N3" AND "N"."N"<>"N4" AND "N"."N"<>"N5" AND 27 "N"."N"<>"N6" AND "N"."N"<>"N7" AND "N"."N"<>"N8" AND "N"."N"<>"N9" AND ("T"."LVL"=1 AND "N"."N">"T"."N1" AND 28 "N"."N"+"T"."N1"<=9 OR "T"."LVL"=2 AND ("N"."N"-"T"."N1"-"T"."N2"=0 OR "N"."N"-"T"."N1"-"T"."N2"=1) OR 29 "T"."LVL"=5 AND "N"."N"=MOD("T"."N4"+"T"."N5",10) OR ("T"."LVL"=3 OR "T"."LVL"=4 OR "T"."LVL"=6 OR "T"."LVL"=7 30 OR "T"."LVL"=8))) 31 32 33Statistics 34---------------------------------------------------------- 35 3 recursive calls 36 4023 db block gets 37 22960 consistent gets 38 1 physical reads 39 600 redo size 40 5566 bytes sent via SQL*Net to client 41 644 bytes received via SQL*Net from client 42 13 SQL*Net roundtrips to/from client 43 11 sorts (memory) 44 0 sorts (disk) 45 168 rows processednewkid 的思想是，每次迭代都用条件限制相应的结果集，我的优化思想是尽量限制每一步的结果集，每一步的结果集越少，下一步产生的结果集就越少，于是我修改newkid的SQL语句中的条件语句。
我在第7和第8步加入了判断条件，更精确地控制这两步产生的结果集：
01WITH n AS ( 02 SELECT ROWNUM n FROM DUAL CONNECT BY ROWNUM<=9 03 ) 04,t (n1,n2,n3,n4,n5,n6,n7,n8,n9,lvl) AS ( 05 SELECT n,0,0,0,0,0,0,0,0,1 06 FROM n 07 WHERE n<=4 08 UNION ALL09 SELECT t.n1 10 ,DECODE(t.lvl,1,n.n,t.n2) 11 ,DECODE(t.lvl,2,n.n,t.n3) 12 ,DECODE(t.lvl,3,n.n,t.n4) 13 ,DECODE(t.lvl,4,n.n,t.n5) 14 ,DECODE(t.lvl,5,n.n,t.n6) 15 ,DECODE(t.lvl,6,n.n,t.n7) 16 ,DECODE(t.lvl,7,n.n,t.n8) 17 ,DECODE(t.lvl,8,n.n,t.n9) 18 ,t.lvl+1 19 FROM t,n 20 WHERE n.n NOT IN (n1,n2,n3,n4,n5,n6,n7,n8,n9) 21 AND (t.lvl=1 AND n.n>t.n1 AND n.n+t.n1<=9 22 OR (t.lvl=2 AND n.n - t.n1- t.n2 IN (0,1) AND n.n>t.n1) 23 OR (t.lvl=5 AND n.n = MOD(t.n4+ t.n5,10)) 24 OR (t.lvl=7 AND trunc( ((t.n7 + n.n)*10+t.n4+t.n5)/100 )=t.n3-t.n2-t.n1) 25 OR (t.lvl=8 AND (t.n3 - t.n1 - t.n2 )*10 + n.n - t.n7 - t.n8 = trunc((t.n4+t.n5)/10) ) 26 OR t.lvl IN (3,4,6) 27 ) 28 ) 29SELECT n1||n7||n4||' + '||n2||n8||n5||' = '||n3||n9||n6 30 FROM t 31WHERE lvl=9 32 AND n1*100+n7*10+n4 + n2*100+n8*10+n5 = n3*100+n9*10+n6执行计划如下，用时约0.11秒：
01------------------------------------------------------------------------------------------------------------------------ 02| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 03------------------------------------------------------------------------------------------------------------------------ 04| 0 | SELECT STATEMENT | | 2 | 260 | 9 (12)| 00:00:01 | 05| 1 | TEMP TABLE TRANSFORMATION | | | | | | 06| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6975_55670 | | | | | 07| 3 | COUNT | | | | | | 08|* 4 | CONNECT BY WITHOUT FILTERING | | | | | | 09| 5 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | 10| 6 | SORT ORDER BY | | 2 | 260 | 7 (15)| 00:00:01 | 11|* 7 | VIEW | | 2 | 260 | 6 (0)| 00:00:01 | 12| 8 | UNION ALL (RECURSIVE WITH) BREADTH FIRST| | | | | | 13|* 9 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 | 14| 10 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6975_55670 | 1 | 13 | 2 (0)| 00:00:01 | 15| 11 | NESTED LOOPS | | 1 | 143 | 4 (0)| 00:00:01 | 16| 12 | RECURSIVE WITH PUMP | | | | | | 17|* 13 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 | 18| 14 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6975_55670 | 1 | 13 | 2 (0)| 00:00:01 | 19------------------------------------------------------------------------------------------------------------------------ 20 21Predicate Information (identified by operation id): 22--------------------------------------------------- 23 24 4 - filter(ROWNUM<=9) 25 7 - filter("LVL"=9 AND "N1"*100+"N7"*10+"N4"+"N2"*100+"N8"*10+"N5"="N3"*100+"N9"*10+"N6") 26 9 - filter("N"<=4) 27 13 - filter("N"."N"<>"N1" AND "N"."N"<>"N2" AND "N"."N"<>"N3" AND "N"."N"<>"N4" AND "N"."N"<>"N5" AND 28 "N"."N"<>"N6" AND "N"."N"<>"N7" AND "N"."N"<>"N8" AND "N"."N"<>"N9" AND ("T"."LVL"=1 AND "N"."N">"T"."N1" AND 29 "N"."N"+"T"."N1"<=9 OR "T"."LVL"=2 AND ("N"."N"-"T"."N1"-"T"."N2"=0 OR "N"."N"-"T"."N1"-"T"."N2"=1) OR 30 "T"."LVL"=5 AND "N"."N"=MOD("T"."N4"+"T"."N5",10) OR "T"."LVL"=7 AND 31 TRUNC((("T"."N7"+"N"."N")*10+"T"."N4"+"T"."N5")/100)="T"."N3"-"T"."N2"-"T"."N1" OR "T"."LVL"=8 AND 32 ("T"."N3"-"T"."N1"-"T"."N2")*10+"N"."N"-"T"."N7"-"T"."N8"=TRUNC(("T"."N4"+"T"."N5")/10) OR ("T"."LVL"=3 OR 33 "T"."LVL"=4 OR "T"."LVL"=6))) 34 35 36Statistics 37---------------------------------------------------------- 38 3 recursive calls 39 330 db block gets 40 12221 consistent gets 41 1 physical reads 42 556 redo size 43 5566 bytes sent via SQL*Net to client 44 644 bytes received via SQL*Net from client 45 13 SQL*Net roundtrips to/from client 46 12 sorts (memory) 47 0 sorts (disk) 48 168 rows processed
一开始看到这题之后，我第一个想法是按照我最初做数独题目的时候，用几个表来联接，在一个“大包围”的方案中进行筛选，于是我尝试写了一下：
01WITH n AS ( 02 SELECT ROWNUM n FROM DUAL CONNECT BY ROWNUM<=9 03 ), 04tmp as ( 05 select n1.n n1 , n2.n n2, n3.n n3 , 06 n4.n n4 , n5.n n5, n6.n n6 , 07 n7.n n7 , n8.n n8, n9.n n9 08 from n n1,n n2,n n3,n n4,n n5,n n6,n n7,n n8,n n9 09 where n1.n between 1 and 4 10 and n1.n + n4.n <= 9 /* 11 and n7.n - n1.n - n4.n = trunc(( ( n2.n + n5.n ) + (n3.n + n6.n - n9.n)/10 )/10)*/ 12 and n8.n - n2.n - n5.n = (n3.n + n6.n - n9.n)/10 13 and mod( n3.n + n6.n ,10 ) = n9.n 14 and n1.n < n4.n 15 and n1.n*100+n2.n*10+n3.n + n4.n*100+n5.n*10+n6.n = n7.n*100+n8.n*10+n9.n 16 and n1.n not in ( n2.n, n3.n, n4.n, n5.n, n6.n, n7.n, n8.n, n9.n) 17 and n2.n not in ( n1.n, n3.n, n4.n, n5.n, n6.n, n7.n, n8.n, n9.n) 18 and n3.n not in ( n1.n, n2.n, n4.n, n5.n, n6.n, n7.n, n8.n, n9.n) 19 and n4.n not in ( n1.n, n2.n, n3.n, n5.n, n6.n, n7.n, n8.n, n9.n) 20 and n5.n not in ( n1.n, n2.n, n3.n, n4.n, n6.n, n7.n, n8.n, n9.n) 21 and n6.n not in ( n1.n, n2.n, n3.n, n4.n, n5.n, n7.n, n8.n, n9.n) 22 and n7.n not in ( n1.n, n2.n, n3.n, n4.n, n5.n, n6.n, n8.n, n9.n) 23 and n8.n not in ( n1.n, n2.n, n3.n, n4.n, n5.n, n6.n, n7.n, n9.n) 24 and n9.n not in ( n1.n, n2.n, n3.n, n4.n, n5.n, n6.n, n7.n, n8.n ) 25) 26select * from tmp;这里没什么算法可言，首先是9个1~9的序列表进行连接，联接的条件包括，其中直接就把题意的写到联接条件中
1n1.n*100+n2.n*10+n3.n + n4.n*100+n5.n*10+n6.n = n7.n*100+n8.n*10+n9.n当然还有9个数字不重复出现的条件
1and n1.n not in ( n2.n, n3.n, n4.n, n5.n, n6.n, n7.n, n8.n, n9.n) 2and n2.n not in ( n1.n, n3.n, n4.n, n5.n, n6.n, n7.n, n8.n, n9.n) 3and n3.n not in ( n1.n, n2.n, n4.n, n5.n, n6.n, n7.n, n8.n, n9.n) 4and n4.n not in ( n1.n, n2.n, n3.n, n5.n, n6.n, n7.n, n8.n, n9.n) 5and n5.n not in ( n1.n, n2.n, n3.n, n4.n, n6.n, n7.n, n8.n, n9.n) 6and n6.n not in ( n1.n, n2.n, n3.n, n4.n, n5.n, n7.n, n8.n, n9.n) 7and n7.n not in ( n1.n, n2.n, n3.n, n4.n, n5.n, n6.n, n8.n, n9.n) 8and n8.n not in ( n1.n, n2.n, n3.n, n4.n, n5.n, n6.n, n7.n, n9.n) 9and n9.n not in ( n1.n, n2.n, n3.n, n4.n, n5.n, n6.n, n7.n, n8.n )然后之前的条件是经过我们推导得出的“优化”条件。
执行计划和统计信息如下，用时约0.23秒：
01------------------------------------------------------------------------------------------------------------ 02| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | 03------------------------------------------------------------------------------------------------------------ 04| 0 | SELECT STATEMENT | | 1 | 117 | 20 (0)| 00:00:01 | 05| 1 | TEMP TABLE TRANSFORMATION | | | | | | 06| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6999_55670 | | | | | 07| 3 | COUNT | | | | | | 08|* 4 | CONNECT BY WITHOUT FILTERING| | | | | | 09| 5 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | 10| 6 | NESTED LOOPS | | 1 | 117 | 18 (0)| 00:00:01 | 11| 7 | NESTED LOOPS | | 1 | 104 | 16 (0)| 00:00:01 | 12| 8 | NESTED LOOPS | | 1 | 91 | 14 (0)| 00:00:01 | 13| 9 | NESTED LOOPS | | 1 | 78 | 12 (0)| 00:00:01 | 14| 10 | NESTED LOOPS | | 1 | 65 | 10 (0)| 00:00:01 | 15| 11 | NESTED LOOPS | | 1 | 52 | 8 (0)| 00:00:01 | 16| 12 | NESTED LOOPS | | 1 | 39 | 6 (0)| 00:00:01 | 17| 13 | NESTED LOOPS | | 1 | 26 | 4 (0)| 00:00:01 | 18| 14 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 | 19| 15 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6999_55670 | 1 | 13 | 2 (0)| 00:00:01 | 20|* 16 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 | 21| 17 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6999_55670 | 1 | 13 | 2 (0)| 00:00:01 | 22|* 18 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 | 23| 19 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6999_55670 | 1 | 13 | 2 (0)| 00:00:01 | 24|* 20 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 | 25| 21 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6999_55670 | 1 | 13 | 2 (0)| 00:00:01 | 26|* 22 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 | 27| 23 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6999_55670 | 1 | 13 | 2 (0)| 00:00:01 | 28|* 24 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 | 29| 25 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6999_55670 | 1 | 13 | 2 (0)| 00:00:01 | 30|* 26 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 | 31| 27 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6999_55670 | 1 | 13 | 2 (0)| 00:00:01 | 32|* 28 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 | 33| 29 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6999_55670 | 1 | 13 | 2 (0)| 00:00:01 | 34|* 30 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 | 35| 31 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6999_55670 | 1 | 13 | 2 (0)| 00:00:01 | 36------------------------------------------------------------------------------------------------------------ 37 38Predicate Information (identified by operation id): 39--------------------------------------------------- 40 41 4 - filter(ROWNUM<=9) 42 16 - filter("N8"."N"<>"N9"."N") 43 18 - filter("N7"."N"<>"N8"."N" AND "N7"."N"<>"N9"."N") 44 20 - filter("N6"."N"<>"N7"."N" AND "N6"."N"<>"N8"."N" AND "N6"."N"<>"N9"."N") 45 22 - filter("N9"."N"=MOD("N3"."N"+"N6"."N",10) AND "N3"."N"<>"N6"."N" AND "N3"."N"<>"N7"."N" AND 46 "N3"."N"<>"N8"."N" AND "N3"."N"<>"N9"."N") 47 24 - filter("N3"."N"<>"N5"."N" AND "N5"."N"<>"N6"."N" AND "N5"."N"<>"N7"."N" AND 48 "N5"."N"<>"N8"."N" AND "N5"."N"<>"N9"."N") 49 26 - filter("N2"."N"<>"N3"."N" AND "N2"."N"<>"N5"."N" AND "N2"."N"<>"N6"."N" AND 50 "N2"."N"<>"N7"."N" AND "N2"."N"<>"N8"."N" AND "N2"."N"<>"N9"."N" AND 51 "N8"."N"-"N2"."N"-"N5"."N"=("N3"."N"+"N6"."N"-"N9"."N")/10) 52 28 - filter("N2"."N"<>"N4"."N" AND "N3"."N"<>"N4"."N" AND "N4"."N"<>"N5"."N" AND 53 "N4"."N"<>"N6"."N" AND "N4"."N"<>"N7"."N" AND "N4"."N"<>"N8"."N" AND "N4"."N"<>"N9"."N" AND 54 "N4"."N">1) 55 30 - filter("N1"."N">=1 AND "N1"."N"<=4 AND "N1"."N"+"N4"."N"<=9 AND "N1"."N"<"N4"."N" AND 56 "N1"."N"*100+"N2"."N"*10+"N3"."N"+"N4"."N"*100+"N5"."N"*10+"N6"."N"="N7"."N"*100+"N8"."N"*10+"N9"."N 57 " AND "N1"."N"<>"N2"."N" AND "N1"."N"<>"N3"."N" AND "N1"."N"<>"N4"."N" AND "N1"."N"<>"N5"."N" AND 58 "N1"."N"<>"N6"."N" AND "N1"."N"<>"N7"."N" AND "N1"."N"<>"N8"."N" AND "N1"."N"<>"N9"."N") 59 60 61Statistics 62---------------------------------------------------------- 63 2 recursive calls 64 8 db block gets 65 49879 consistent gets 66 1 physical reads 67 600 redo size 68 3501 bytes sent via SQL*Net to client 69 578 bytes received via SQL*Net from client 70 7 SQL*Net roundtrips to/from client 71 1 sorts (memory) 72 0 sorts (disk) 73 84 rows processed虽然效率不怎么样，但是比预想中好，consistent gets仅比newkid的多3倍，比szusunn（以及OO修改后的版本）的方法少8倍。
这充分说明SQL是一种声明式语言，具体实施交给数据库引擎去做的事实。
- 简单分析《趣味题》中的SQL
- 简单趣味题
- 趣味题的分析
- 数组中的趣味题（一）
- 数组中的趣味题（二）
- 数组中的趣味题（一）
- 【趣味SQL】打印菱形
- SQL趣味练习题
- 趣味题
- 趣味题
- 趣味题
- 趣味题
- 趣味题
- 趣味题
- 趣味题
- 数据分析中的sql
- sql中的分析函数
- 趣味题：父select中选中的子select不能有
- UNIX/LINUX 平台可执行文件格式分析
- 内存溢出修改tomcat的JVM参数
- 备份恢复mysql5数据库
- 语义搜索
- java中的线程
- 简单分析《趣味题》中的SQL
- S60视图(View)架构简介
- 终于找到一个有助理解left/right/full outer join的例子
- ejb部署：欺骗你眼睛的ide
- C#中读写INI文件
- Binary Search Tree
- SQL Server 2008高可用性系列：日志传送
- 朱自清--匆匆
- SQL Server 2008高可用性系列：数据库快照
