趣味题

〇〇 · 发表于 2010-6-10 20:42

把我的和8#逐项对照，发现慢的根源竟然是减号,看来一点马虎不得

SQL> with t as(select x from (select level+122 x from dual connect by level<=(987-122)) a
  2  where substr(a.x,1,1)<>substr(a.x,2,1) and  substr(a.x,1,1)<>substr(a.x,3,1) and substr(a.x,2,1)<>substr(a.x,3,1)
  3  and instr(a.x,0)=0)
  4  select count(*) from (
  5  select a.x||'+'||b.x||'-'||c.x||'==0' from t a,t b,t c
  6  where
  7  translate('123456789','$'||a.x||b.x||c.x,'$') is null
  8  and a.x<494 and a.x+b.x-c.x=0 and a.x<b.x and b.x<c.x
  9  )
10  ;

  COUNT(*)
----------
   168

已用时间:  00: 00: 10.06
SQL> with t as(select x from (select level+122 x from dual connect by level<=(987-122)) a
  2  where substr(a.x,1,1)<>substr(a.x,2,1) and  substr(a.x,1,1)<>substr(a.x,3,1) and substr(a.x,2,1)<>substr(a.x,3,1)
  3  and instr(a.x,0)=0)
  4  select count(*) from (
  5  select a.x||'+'||b.x||'-'||c.x||'==0' from t a,t b,t c
  6  where
  7  translate('123456789','$'||a.x||b.x||c.x,'$') is null
  8  and a.x<494 and a.x+b.x=c.x and a.x<b.x and b.x<c.x
  9  )
10  ;

  COUNT(*)
----------
   168

已用时间:  00: 00: 00.04

〇〇 · 发表于 2010-6-10 20:51

SQL> set autot traceonly
SQL> with t as(select x from (select level+122 x from dual connect by level<=(987-122)) a
  2  where substr(a.x,1,1)<>substr(a.x,2,1) and  substr(a.x,1,1)<>substr(a.x,3,1) and substr(a.x,2,1)<>substr(a.x,3,1)
  3  and instr(a.x,0)=0)
  4  select count(*) from (
  5  select a.x||'+'||b.x||'-'||c.x||'==0' from t a,t b,t c
  6  where
  7  translate('123456789','$'||a.x||b.x||c.x,'$') is null
  8  and a.x<494 and a.x+b.x-c.x=0 and a.x<b.x and b.x<c.x
  9  )
10  ;

已用时间:  00: 00: 10.06

Execution Plan
----------------------------------------------------------
0    SELECT STATEMENT Optimizer=CHOOSE
1 2    RECURSIVE EXECUTION OF 'SYS_LE_2_0'
2 0 TEMP TABLE TRANSFORMATION
3 2    SORT (AGGREGATE)
4 3    NESTED LOOPS
5 4       NESTED LOOPS
6 5          VIEW
7 6          TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6615_109DBD8'
8 5          VIEW
9 8          TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6615_109DBD8'
  10 4       VIEW
  11 10          TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6615_109DBD8
      '

Statistics
----------------------------------------------------------
      4  recursive calls
      7  db block gets
   381792  consistent gets
      1  physical reads
      520  redo size
       377  bytes sent via SQL*Net to client
      503  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      5  sorts (memory)
      0  sorts (disk)
      1  rows processed

SQL> with t as(select x from (select level+122 x from dual connect by level<=(987-122)) a
  2  where substr(a.x,1,1)<>substr(a.x,2,1) and  substr(a.x,1,1)<>substr(a.x,3,1) and substr(a.x,2,1)<>substr(a.x,3,1)
  3  and instr(a.x,0)=0)
  4  select count(*) from (
  5  select a.x||'+'||b.x||'-'||c.x||'==0' from t a,t b,t c
  6  where
  7  translate('123456789','$'||a.x||b.x||c.x,'$') is null
  8  and a.x<494 and a.x+b.x=c.x and a.x<b.x and b.x<c.x
  9  )
10  ;

已用时间:  00: 00: 00.05

Execution Plan
----------------------------------------------------------
0    SELECT STATEMENT Optimizer=CHOOSE
1 2    RECURSIVE EXECUTION OF 'SYS_LE_2_0'
2 0 TEMP TABLE TRANSFORMATION
3 2    SORT (AGGREGATE)
4 3    MERGE JOIN
5 4       SORT (JOIN)
6 5          NESTED LOOPS
7 6          VIEW
8 7             TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6616_109DBD8'
9 6          VIEW
  10 9             TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6616_109DBD8'
  11 4       FILTER
  12 11          SORT (JOIN)
  13 12          VIEW
  14 13             TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6616_109DBD8'

Statistics
----------------------------------------------------------
      4  recursive calls
      10  db block gets
      675  consistent gets
      215  physical reads
      520  redo size
      377  bytes sent via SQL*Net to client
      503  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      6  sorts (memory)
      1  sorts (disk)
      1  rows processed

SQL>

〇〇 · 发表于 2010-6-10 21:04

11g的结果
SQL> with t as(select x from (select level+122 x from dual connect by level<=(987-122)) where substr(x,1,1)<>substr(x,2,1) and  substr(x,1,1)<>substr(x,3,1)

  2  and instr(x,0)=0)
  3  select count(*) from (
  4  select a.x||'+'||b.x||'-'||c.x||'==0' from t a,t b,t c
  5  where a.x+b.x=c.x and a.x<494 and a.x<b.x and b.x<c.x
  6  and translate('123456789','$'||a.x||b.x||c.x,'$') is null
  7  );

  COUNT(*)
----------
   168

已用时间:  00: 00: 10.18
SQL> with t as(select x from (select level+122 x from dual connect by level<=(987-122)) where substr(x,1,1)<>substr(x,2,1) and  substr(x,1,1)<>substr(x,3,1)

  2  and instr(x,0)=0)
  3  select count(*) from (
  4  select  /*+rule */a.x||'+'||b.x||'-'||c.x||'==0' from t a,t b,t c
  5  where a.x+b.x=c.x and a.x<494 and a.x<b.x and b.x<c.x
  6  and translate('123456789','$'||a.x||b.x||c.x,'$') is null
  7  );

  COUNT(*)
----------
   168

已用时间:  00: 00: 00.24

〇〇 · 发表于 2010-6-10 21:06

去掉减号，用rule提示在9i也得到

SQL> with t as(select x from (select level+122 x from dual connect by level<=(987-122)) where substr(x,1,1)<>substr(x,2,1) and  substr(x,1,1
)<>substr(x,3,1)
  2  and instr(x,0)=0)
  3  select count(*) from (
  4  select  /*+rule */a.x||'+'||b.x||'-'||c.x||'==0' from t a,t b,t c
  5  where a.x+b.x=c.x and a.x<494 and a.x<b.x and b.x<c.x
  6  and translate('123456789','$'||a.x||b.x||c.x,'$') is null
  7  );

  COUNT(*)
----------
   168

已用时间:  00: 00: 00.04

dingjun123 · 发表于 2010-6-10 21:19

OO研究精神，实在让人佩服！

〇〇 · 发表于 2010-6-10 21:41

怀疑类似索引
y=x能匹配
y-x=0不能

dingjun123 · 发表于 2010-6-10 21:47

原帖由〇〇于 2010-6-10 21:41 发表
怀疑类似索引
y=x能匹配
y-x=0不能

oracle感觉傻了啊，它不会重写吗！搞不清楚！

newkid · 发表于 2010-6-10 21:59

为什么我得到232行？
11GR2:
WITH n AS (
SELECT ROWNUM n FROM DUAL CONNECT BY ROWNUM<=9
)
,t (n1,n2,n3,n4,n5,n6,n7,n8,n9,lvl) AS (
SELECT n,0,0,0,0,0,0,0,0,1
   FROM n
WHERE n<=4
UNION ALL
SELECT t.n1
      ,DECODE(t.lvl,1,n.n,t.n2)
      ,DECODE(t.lvl,2,n.n,t.n3)
      ,DECODE(t.lvl,3,n.n,t.n4)
      ,DECODE(t.lvl,4,n.n,t.n5)
      ,DECODE(t.lvl,5,n.n,t.n6)
      ,DECODE(t.lvl,6,n.n,t.n7)
      ,DECODE(t.lvl,7,n.n,t.n8)
      ,DECODE(t.lvl,8,n.n,t.n9)
      ,t.lvl+1
   FROM t,n
WHERE n.n NOT IN (n1,n2,n3,n4,n5,n6,n7,n8,n9)
      AND (t.lvl=1 AND n.n>t.n1 AND n.n+t.n1<=9
            OR (t.lvl=2 AND n.n - t.n1- t.n2 IN (0,1))
            OR (t.lvl=5 AND n.n = MOD(t.n4- t.n5,10))
            OR t.lvl<9
            )
)
SELECT n1||n7||n4||' + '||n2||n8||n5||' = '||n3||n9||n6
  FROM t
WHERE lvl=9
   AND n1*100+n7*10+n4 + n2*100+n8*10+n5 = n3*100+n9*10+n6;

173 + 295 = 468
193 + 275 = 468
173 + 286 = 459
183 + 276 = 459
175 + 293 = 468
195 + 273 = 468
176 + 283 = 459
186 + 273 = 459
127 + 368 = 495
167 + 328 = 495
127 + 359 = 486
157 + 329 = 486
128 + 367 = 495
168 + 327 = 495
129 + 357 = 486
159 + 327 = 486
182 + 394 = 576
192 + 384 = 576
162 + 387 = 549
182 + 367 = 549
184 + 392 = 576
194 + 382 = 576
167 + 382 = 549
187 + 362 = 549
128 + 439 = 567
138 + 429 = 567
129 + 438 = 567
139 + 428 = 567
182 + 493 = 675
192 + 483 = 675
152 + 487 = 639
182 + 457 = 639
183 + 492 = 675
193 + 482 = 675
157 + 482 = 639
187 + 452 = 639
142 + 596 = 738
192 + 546 = 738
143 + 586 = 729
183 + 546 = 729
146 + 592 = 738
196 + 542 = 738
146 + 583 = 729
186 + 543 = 729
134 + 658 = 792
154 + 638 = 792
124 + 659 = 783
154 + 629 = 783
138 + 654 = 792
158 + 634 = 792
129 + 654 = 783
159 + 624 = 783
142 + 695 = 837
192 + 645 = 837
145 + 692 = 837
195 + 642 = 837
125 + 739 = 864
135 + 729 = 864
129 + 735 = 864
139 + 725 = 864
162 + 783 = 945
182 + 763 = 945
152 + 784 = 936
182 + 754 = 936
163 + 782 = 945
183 + 762 = 945
154 + 782 = 936
184 + 752 = 936
273 + 195 = 468
293 + 175 = 468
273 + 186 = 459
283 + 176 = 459
275 + 193 = 468
295 + 173 = 468
276 + 183 = 459
286 + 173 = 459
216 + 378 = 594
276 + 318 = 594
218 + 376 = 594
278 + 316 = 594
218 + 349 = 567
248 + 319 = 567
219 + 348 = 567
249 + 318 = 567
281 + 394 = 675
291 + 384 = 675
251 + 397 = 648
291 + 357 = 648
284 + 391 = 675
294 + 381 = 675
257 + 391 = 648
297 + 351 = 648
215 + 478 = 693
275 + 418 = 693
218 + 475 = 693
278 + 415 = 693
218 + 439 = 657
238 + 419 = 657
219 + 438 = 657
239 + 418 = 657
214 + 569 = 783
264 + 519 = 783
219 + 564 = 783
269 + 514 = 783
271 + 593 = 864
291 + 573 = 864
241 + 596 = 837
291 + 546 = 837
273 + 591 = 864
293 + 571 = 864
243 + 576 = 819
273 + 546 = 819
246 + 591 = 837
296 + 541 = 837
246 + 573 = 819
276 + 543 = 819
234 + 657 = 891
254 + 637 = 891
214 + 659 = 873
254 + 619 = 873
237 + 654 = 891
257 + 634 = 891
219 + 654 = 873
259 + 614 = 873
271 + 683 = 954
281 + 673 = 954
273 + 681 = 954
283 + 671 = 954
243 + 675 = 918
273 + 645 = 918
245 + 673 = 918
275 + 643 = 918
235 + 746 = 981
245 + 736 = 981
215 + 748 = 963
245 + 718 = 963
236 + 745 = 981
246 + 735 = 981
216 + 738 = 954
236 + 718 = 954
218 + 745 = 963
248 + 715 = 963
218 + 736 = 954
238 + 716 = 954
327 + 168 = 495
367 + 128 = 495
327 + 159 = 486
357 + 129 = 486
328 + 167 = 495
368 + 127 = 495
329 + 157 = 486
359 + 127 = 486
382 + 194 = 576
392 + 184 = 576
362 + 187 = 549
382 + 167 = 549
384 + 192 = 576
394 + 182 = 576
367 + 182 = 549
387 + 162 = 549
316 + 278 = 594
376 + 218 = 594
318 + 276 = 594
378 + 216 = 594
318 + 249 = 567
348 + 219 = 567
319 + 248 = 567
349 + 218 = 567
381 + 294 = 675
391 + 284 = 675
351 + 297 = 648
391 + 257 = 648
384 + 291 = 675
394 + 281 = 675
357 + 291 = 648
397 + 251 = 648
352 + 467 = 819
362 + 457 = 819
357 + 462 = 819
367 + 452 = 819
324 + 567 = 891
364 + 527 = 891
327 + 564 = 891
367 + 524 = 891
317 + 529 = 846
327 + 519 = 846
319 + 527 = 846
329 + 517 = 846
341 + 586 = 927
381 + 546 = 927
342 + 576 = 918
372 + 546 = 918
346 + 581 = 927
386 + 541 = 927
346 + 572 = 918
376 + 542 = 918
324 + 657 = 981
354 + 627 = 981
314 + 658 = 972
354 + 618 = 972
327 + 654 = 981
357 + 624 = 981
317 + 628 = 945
327 + 618 = 945
318 + 654 = 972
358 + 614 = 972
318 + 627 = 945
328 + 617 = 945
428 + 139 = 567
438 + 129 = 567
429 + 138 = 567
439 + 128 = 567
482 + 193 = 675
492 + 183 = 675
452 + 187 = 639
482 + 157 = 639
483 + 192 = 675
493 + 182 = 675
457 + 182 = 639
487 + 152 = 639
415 + 278 = 693
475 + 218 = 693
418 + 275 = 693
478 + 215 = 693
418 + 239 = 657
438 + 219 = 657
419 + 238 = 657
439 + 218 = 657
452 + 367 = 819
462 + 357 = 819
457 + 362 = 819
467 + 352 = 819

232 rows selected.

Elapsed: 00:00:07.70

newkid · 发表于 2010-6-10 22:23

重复的原因找出来了，死活改不过来，我怀疑11GR2有BUG.

newkid · 发表于 2010-6-10 22:27

这样改就行：
WITH n AS (
SELECT ROWNUM n FROM DUAL CONNECT BY ROWNUM<=9
)
,t (n1,n2,n3,n4,n5,n6,n7,n8,n9,lvl) AS (
SELECT n,0,0,0,0,0,0,0,0,1
   FROM n
WHERE n<=4
UNION ALL
SELECT t.n1
      ,DECODE(t.lvl,1,n.n,t.n2)
      ,DECODE(t.lvl,2,n.n,t.n3)
      ,DECODE(t.lvl,3,n.n,t.n4)
      ,DECODE(t.lvl,4,n.n,t.n5)
      ,DECODE(t.lvl,5,n.n,t.n6)
      ,DECODE(t.lvl,6,n.n,t.n7)
      ,DECODE(t.lvl,7,n.n,t.n8)
      ,DECODE(t.lvl,8,n.n,t.n9)
      ,t.lvl+1
   FROM t,n
WHERE n.n NOT IN (n1,n2,n3,n4,n5,n6,n7,n8,n9)
      AND (t.lvl=1 AND n.n>t.n1 AND n.n+t.n1<=9
            OR (t.lvl=2 AND n.n - t.n1- t.n2 IN (0,1))
            OR (t.lvl=5 AND n.n = MOD(t.n4- t.n5,10))
            OR t.lvl <9 -- AND t.lvl NOT IN (1,2,5)
            )
)
SELECT n1||n7||n4||' + '||n2||n8||n5||' = '||n3||n9||n6
  FROM t
WHERE lvl=9
   AND n1*100+n7*10+n4 + n2*100+n8*10+n5 = n3*100+n9*10+n6
   AND n1<n2    ---- 最后进行过滤
;

这样就不行：
WITH n AS (
SELECT ROWNUM n FROM DUAL CONNECT BY ROWNUM<=9
)
,t (n1,n2,n3,n4,n5,n6,n7,n8,n9,lvl) AS (
SELECT n,0,0,0,0,0,0,0,0,1
   FROM n
WHERE n<=4
UNION ALL
SELECT t.n1
      ,DECODE(t.lvl,1,n.n,t.n2)
      ,DECODE(t.lvl,2,n.n,t.n3)
      ,DECODE(t.lvl,3,n.n,t.n4)
      ,DECODE(t.lvl,4,n.n,t.n5)
      ,DECODE(t.lvl,5,n.n,t.n6)
      ,DECODE(t.lvl,6,n.n,t.n7)
      ,DECODE(t.lvl,7,n.n,t.n8)
      ,DECODE(t.lvl,8,n.n,t.n9)
      ,t.lvl+1
   FROM t,n
WHERE n.n NOT IN (n1,n2,n3,n4,n5,n6,n7,n8,n9)
      AND (t.lvl=1 AND n.n>t.n1 AND n.n+t.n1<=9
            OR (t.lvl=2 AND n.n - t.n1- t.n2 IN (0,1))
            OR (t.lvl=5 AND n.n = MOD(t.n4- t.n5,10))
            OR t.lvl <9 AND t.lvl NOT IN (1,2,5) --------- 遍历过程中过滤
            )
)
SELECT n1||n7||n4||' + '||n2||n8||n5||' = '||n3||n9||n6
  FROM t
WHERE lvl=9
   AND n1*100+n7*10+n4 + n2*100+n8*10+n5 = n3*100+n9*10+n6
   ;

[转载] 趣味题

回复 #35 dingjun123 的帖子

浏览过的版块