趣味题

szusunny

font=宋体
1         
2        --------------------------------------------------------------------------------------------------
3        | Id  | Operation                          |  Name                       | Rows  | Bytes | Cost  |
4        --------------------------------------------------------------------------------------------------
5        |   0 | SELECT STATEMENT                   |                             |       |       |       |
6        |   2 |  TEMP TABLE TRANSFORMATION         |                             |       |       |       |
7        |   1 |   RECURSIVE EXECUTION              | SYS_LE_2_0                  |       |       |       |
8        |   0 |    INSERT STATEMENT                |                             |       |       |       |
9        |   1 |     LOAD AS SELECT                 |                             |       |       |       |
10        |*  2 |      VIEW                          |                             |       |       |       |
11        |*  3 |       COUNT STOPKEY                |                             |       |       |       |
12        |   4 |        CONNECT BY WITH FILTERING   |                             |       |       |       |
13        |   5 |         NESTED LOOPS               |                             |       |       |       |
14        |   6 |          TABLE ACCESS FULL         | DUAL                        |       |       |       |
15        |   7 |          TABLE ACCESS BY USER ROWID| DUAL                        |       |       |       |
16        |   8 |         NESTED LOOPS               |                             |       |       |       |
17        |   9 |          BUFFER SORT               |                             |       |       |       |
18        |  10 |           CONNECT BY PUMP          |                             |       |       |       |
19        |  11 |          TABLE ACCESS FULL         | DUAL                        |       |       |       |
20        |   3 |   MERGE JOIN                       |                             |       |       |       |
21        |   4 |    SORT JOIN                       |                             |       |       |       |
22        |   5 |     NESTED LOOPS                   |                             |       |       |       |
23        |   6 |      VIEW                          |                             |       |       |       |
24        |   7 |       TABLE ACCESS FULL            | SYS_TEMP_0FD9D66A9_EF30CCB  |       |       |       |
25        |*  8 |      VIEW                          |                             |       |       |       |
26        |   9 |       TABLE ACCESS FULL            | SYS_TEMP_0FD9D66A9_EF30CCB  |       |       |       |
27        |* 10 |    FILTER                          |                             |       |       |       |
28        |* 11 |     SORT JOIN                      |                             |       |       |       |
29        |  12 |      VIEW                          |                             |       |       |       |
30        |  13 |       TABLE ACCESS FULL            | SYS_TEMP_0FD9D66A9_EF30CCB  |       |       |       |
31        --------------------------------------------------------------------------------------------------
32         
33        Predicate Information (identified by operation id):
34        ---------------------------------------------------
35         
36           2 - filter("A"."NUM">=100 AND INSTR(TO_CHAR("A"."NUM"),'0')=0 AND
37                      SUBSTR(TO_CHAR("A"."NUM"),1,1)<>SUBSTR(TO_CHAR("A"."NUM"),3,1) AND
38                      SUBSTR(TO_CHAR("A"."NUM"),3,1)<>SUBSTR(TO_CHAR("A"."NUM"),2,1) AND
39                      SUBSTR(TO_CHAR("A"."NUM"),1,1)<>SUBSTR(TO_CHAR("A"."NUM"),2,1))
40           3 - filter(ROWNUM<=999)
41           8 - filter("A"."NUM"<"B"."NUM")
42          10 - filter("B"."NUM"<"C"."NUM" AND REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPL
43                      ACE(REPLACE('123456789',SUBSTR(TO_CHAR("A"."NUM"),1,1)),SUBSTR(TO_CHAR("A"."NUM"),2,1)),SUBSTR(TO_
44                      CHAR("A"."NUM"),3,1)),SUBSTR(TO_CHAR("B"."NUM"),1,1)),SUBSTR(TO_CHAR("B"."NUM"),2,1)),SUBSTR(TO_CH
45                      AR("B"."NUM"),3,1)),SUBSTR(TO_CHAR("C"."NUM"),1,1)),SUBSTR(TO_CHAR("C"."NUM"),2,1)),SUBSTR(TO_CHAR
46                      ("C"."NUM"),3,1)) IS NULL)
47          11 - access("C"."NUM"="A"."NUM"+"B"."NUM")
48               filter("C"."NUM"="A"."NUM"+"B"."NUM")
49         
50        Note: rule based optimization
果真是rbo

[ 本帖最后由 〇〇 于 2010-6-10 16:32 编辑 ]

regonly1

主要时间花在取数字组合上了：
SQL> with tmp as(select level l, replace(sys_connect_by_path(r, ','), ',') s
  2    from (select rownum r from dual connect by level <= 9)
  3   where level = 3
  4  connect by nocycle prior r <> r)
  5  --select * from tmp
  6  select /*+rule*/count(1)--t1.s, t2.s, t3.s
  7    from tmp t1, tmp t2, tmp t3
  8   where t1.s + t2.s = t3.s
  9     and t1.s < t2.s
10     and t2.s < t3.s
11     and instr(t1.s, substr(t2.s, 1, 1)) = 0
12     and instr(t1.s, substr(t2.s, 2, 1)) = 0
13     and instr(t1.s, substr(t2.s, 3, 1)) = 0
14     and instr(t1.s, substr(t3.s, 1, 1)) = 0
15     and instr(t1.s, substr(t3.s, 2, 1)) = 0
16     and instr(t1.s, substr(t3.s, 3, 1)) = 0
17     and instr(t2.s, substr(t3.s, 1, 1)) = 0
18     and instr(t2.s, substr(t3.s, 2, 1)) = 0
19     and instr(t2.s, substr(t3.s, 3, 1)) = 0
20  /

COUNT(1)--T1.S,T2.S,T3.S
------------------------
                     168

Executed in 9.735 seconds

RBO还是有优势的，呵呵，致前CBO花了46s。

[ 本帖最后由 regonly1 于 2010-6-10 17:31 编辑 ]

nyfor

〇〇 的
  5  where replace(replace(replace(
  6        replace(replace(replace(
  7        replace(replace(replace('123456789',substr(a.num,1,1)),substr(a.num,2,1)),substr(a.num,3,1)),
  8                                            substr(b.num,1,1)),substr(b.num,2,1)),substr(b.num,3,1)),
  9                                            substr(c.num,1,1)),substr(c.num,2,1)),substr(c.num,3,1)) is null

修改为:
translate('123456789','0' || a.num || b.num || c.num,'0') is null
会快不少.

〇〇

我看了#8的也想到了translate
但是。。。。
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)
and instr(x,0)=0)
  2  select count(*) from (
  3  select a.x||'+'||b.x||'-'||c.x||'==0' from t a,t b,t c
  4  where a.x+b.x-c.x=0 and a.x<494 and a.x<b.x and b.x<c.x
  5  and translate('123456789','$'||a.x||b.x||c.x,'$') is null
  6  );

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

已用时间:  00: 00: 10.90

〇〇

一个translate竟然比一堆replace还慢

〇〇

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

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

已用时间:  00: 04: 11.05

nyfor

原帖由 〇〇 于 2010-6-10 18:41 发表
一个translate竟然比一堆replace还慢

我用你18#的方法建立了一个 TMP 表，只不过没有收集统计信息。
然后执行你下面的SQL语句发现用translate 明显变快。

你测试变慢， 我怀疑是否是 oracle 在进行数据 filter 的时候先执行这个 translate 后执行其他筛选条件呢？
家里没环境测试。

〇〇

在家里9201上测试

1. 
   
2. SQL> select * from v$version;
   
3. 
   
4. BANNER
   
5. ----------------------------------------------------------------
   
6. Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
   
7. PL/SQL Release 9.2.0.1.0 - Production
   
8. CORE    9.2.0.1.0       Production
   
9. TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
   
10. NLSRTL Version 9.2.0.1.0 - Production
    
11. 
    
12. 已用时间:  00: 00: 00.00
    
13. 
    
14. 
    
15. SQL> with t as(select x from (select level+122 x from dual connect by level<=(987-122))
    
16.   2  where substr(x,1,1)<>substr(x,2,1) and  substr(x,1,1)<>substr(x,3,1)
    
17.   3  and  substr(x,2,1)<>substr(x,3,1) and instr(x,0)=0),
    
18.   4  a as (select rownum rn,x from t where x<494)
    
19.   5  select count(*) from (
    
20.   6  select a.x||'+'||b.x||'-'||c.x||'==0' from a a,t b,t c
    
21.   7  where a.x+b.x-c.x=0 and a.x<494 and a.x<b.x --and b.x<c.x
    
22.   8  and not exists(select 1 from (select rownum l from dual connect by level<=9)where instr(a.x||b.x||c.x ,l,1,2)>0))
    
23.   9  ;
    
24. 
    
25.   COUNT(*)
    
26. ----------
    
27.        168
    
28. 
    
29. 已用时间:  00: 00: 30.07
    
30. SQL>  with tmp as(
    
31.   2   select a.num from (select rownum as num from dual connect by rownum <= 999) a
    
32.   3   where substr(a.num,1,1) != substr(a.num,2,1)
    
33.   4   and substr(a.num,3,1) != substr(a.num,2,1)
    
34.   5   and substr(a.num,1,1) != substr(a.num,3,1) and instr(a.num,0)=0
    
35.   6   and a.num >= 123 and a.num<=987)
    
36.   7   --
    
37.   8   select count(*) from(
    
38.   9   select
    
39. 10  a.num || '+' || b.num || '=' || c.num as str
    
40. 11  from tmp a, tmp b, tmp c
    
41. 12  where replace(replace(replace(
    
42. 13        replace(replace(replace(
    
43. 14        replace(replace(replace('123456789',substr(a.num,1,1)),substr(a.num,2,1)),substr(a.num,3,1)),
    
44. 15                                            substr(b.num,1,1)),substr(b.num,2,1)),substr(b.num,3,1)),
    
45. 16                                            substr(c.num,1,1)),substr(c.num,2,1)),substr(c.num,3,1)) is null
    
46. 17        and a.num + b.num = c.num
    
47. 18        and a.num < b.num
    
48. 19        and b.num < c.num
    
49. 20  );
    
50. 
    
51.   COUNT(*)
    
52. ----------
    
53.        168
    
54. 
    
55. 已用时间:  00: 00: 00.05
    
56. SQL>  with tmp as(
    
57.   2   select a.num from (select rownum as num from dual connect by rownum <= 999) a
    
58.   3   where substr(a.num,1,1) != substr(a.num,2,1)
    
59.   4   and substr(a.num,3,1) != substr(a.num,2,1)
    
60.   5   and substr(a.num,1,1) != substr(a.num,3,1) and instr(a.num,0)=0
    
61.   6   and a.num >= 123 and a.num<=987)
    
62.   7   --
    
63.   8   select count(*) from(
    
64.   9   select
    
65. 10  a.num || '+' || b.num || '=' || c.num as str
    
66. 11  from tmp a, tmp b, tmp c
    
67. 12  where translate('123456789','$'||a.num||b.num||c.num,'$') is null
    
68. 13        and a.num + b.num = c.num
    
69. 14        and a.num < b.num
    
70. 15        and b.num < c.num
    
71. 16  );
    
72. 
    
73.   COUNT(*)
    
74. ----------
    
75.        168
    
76. 
    
77. 已用时间:  00: 00: 00.05
    
78. 
    
79. SQL> with t as(select x from (select level+122 x from dual connect by level<=(987-122))
    
80.   2  where substr(x,1,1)<>substr(x,2,1) and  substr(x,1,1)<>substr(x,3,1)
    
81.   3  and instr(x,0)=0)
    
82.   4  select count(*) from (
    
83.   5  select /*+rule */a.x||'+'||b.x||'-'||c.x||'==0' from t a,t b,t c
    
84.   6  where a.x+b.x-c.x=0 and a.x<494 and a.x<b.x and b.x<c.x
    
85.   7  and translate('123456789','$'||a.x||b.x||c.x,'$') is null)
    
86.   8  ;
    
87. ^C终止批处理操作吗(Y/N)? y
    
88. 
    
89. 已连接。
    
90. SQL> with t as(select x from (select level+122 x from dual connect by level<=(987-122))
    
91.   2  where substr(x,1,1)<>substr(x,2,1) and  substr(x,1,1)<>substr(x,3,1)
    
92.   3  and instr(x,0)=0)
    
93.   4  select count(*) from (
    
94.   5  select a.x||'+'||b.x||'-'||c.x||'==0' from t a,t b,t c
    
95.   6  where
    
96.   7  translate('123456789','$'||a.x||b.x||c.x,'$') is null
    
97.   8  and a.x+b.x-c.x=0 and a.x<494 and a.x<b.x and b.x<c.x
    
98.   9  )
    
99. 10  ;
    
100. 
     
101.   COUNT(*)
     
102. ----------
     
103.        168
     
104. 
     
105. 已用时间:  00: 00: 16.06
     
106. SQL>
     
107. 
     
108. 
     
109. SQL> with t as(select x from (select level+122 x from dual connect by level<=(987-122))
     
110.   2  where substr(x,1,1)<>substr(x,2,1) and  substr(x,1,1)<>substr(x,3,1) and substr(x,2,1)<>substr(x,3,1)
     
111.   3  and instr(x,0)=0)
     
112.   4  select count(*) from (
     
113.   5  select a.x||'+'||b.x||'-'||c.x||'==0' from t a,t b,t c
     
114.   6  where
     
115.   7  translate('123456789','$'||a.x||b.x||c.x,'$') is null
     
116.   8  and a.x+b.x-c.x=0 and a.x<494 and a.x<b.x and b.x<c.x
     
117.   9  )
     
118. 10  ;
     
119. 
     
120.   COUNT(*)
     
121. ----------
     
122.        168
     
123. 
     
124. 已用时间:  00: 00: 10.05
     
125. 
     
126. 
     
127. SQL> explain plan for with t as(select x from (select level+122 x from dual connect by level<=(987-122))
     
128.   2  where substr(x,1,1)<>substr(x,2,1) and  substr(x,1,1)<>substr(x,3,1) and substr(x,2,1)<>substr(x,3,1)
     
129.   3  and instr(x,0)=0)
     
130.   4  select count(*) from (
     
131.   5  select a.x||'+'||b.x||'-'||c.x||'==0' from t a,t b,t c
     
132.   6  where
     
133.   7  translate('123456789','$'||a.x||b.x||c.x,'$') is null
     
134.   8  and a.x+b.x-c.x=0 and a.x<494 and a.x<b.x and b.x<c.x
     
135.   9  )
     
136. 10  ;
     
137. 
     
138. 已解释。
     
139. 
     
140. 已用时间:  00: 00: 00.01
     
141. SQL> select * from table(dbms_xplan.display);
     
142. 
     
143. PLAN_TABLE_OUTPUT
     
144. ------------------------------------------------------------------------------------------------------------------------------------
     
145. 
     
146. --------------------------------------------------------------------------------------------------
     
147. | Id  | Operation                          |  Name                       | Rows  | Bytes | Cost  |
     
148. --------------------------------------------------------------------------------------------------
     
149. |   0 | SELECT STATEMENT                   |                             |       |       |       |
     
150. |   2 |  TEMP TABLE TRANSFORMATION         |                             |       |       |       |
     
151. |   1 |   RECURSIVE EXECUTION              | SYS_LE_2_0                  |       |       |       |
     
152. |   0 |    INSERT STATEMENT                |                             |       |       |       |
     
153. |   1 |     LOAD AS SELECT                 |                             |       |       |       |
     
154. |   2 |      VIEW                          |                             |       |       |       |
     
155. |*  3 |       FILTER                       |                             |       |       |       |
     
156. |   4 |        CONNECT BY WITH FILTERING   |                             |       |       |       |
     
157. |   5 |         NESTED LOOPS               |                             |       |       |       |
     
158. |   6 |          TABLE ACCESS FULL         | DUAL                        |       |       |       |
     
159. |   7 |          TABLE ACCESS BY USER ROWID| DUAL                        |       |       |       |
     
160. |   8 |         NESTED LOOPS               |                             |       |       |       |
     
161. |   9 |          BUFFER SORT               |                             |       |       |       |
     
162. |  10 |           CONNECT BY PUMP          |                             |       |       |       |
     
163. |* 11 |          FILTER                    |                             |       |       |       |
     
164. |  12 |           TABLE ACCESS FULL        | DUAL                        |       |       |       |
     
165. |   3 |   SORT AGGREGATE                   |                             |       |       |       |
     
166. |   4 |    NESTED LOOPS                    |                             |       |       |       |
     
167. |   5 |     NESTED LOOPS                   |                             |       |       |       |
     
168. |   6 |      VIEW                          |                             |       |       |       |
     
169. |   7 |       TABLE ACCESS FULL            | SYS_TEMP_0FD9D6606_109DBD8  |       |       |       |
     
170. |*  8 |      VIEW                          |                             |       |       |       |
     
171. |   9 |       TABLE ACCESS FULL            | SYS_TEMP_0FD9D6606_109DBD8  |       |       |       |
     
172. |* 10 |     VIEW                           |                             |       |       |       |
     
173. |  11 |      TABLE ACCESS FULL             | SYS_TEMP_0FD9D6606_109DBD8  |       |       |       |
     
174. --------------------------------------------------------------------------------------------------
     
175. 
     
176. Predicate Information (identified by operation id):
     
177. ---------------------------------------------------
     
178. 
     
179.    3 - filter(INSTR(TO_CHAR(LEVEL+122),'0')=0 AND SUBSTR(TO_CHAR(LEVEL+122),2,1)<>SUBSTR(TO_CHA
     
180.               R(LEVEL+122),3,1) AND SUBSTR(TO_CHAR(LEVEL+122),1,1)<>SUBSTR(TO_CHAR(LEVEL+122),3
     
181.               ,1) AND SUBSTR(TO_CHAR(LEVEL+122),1,1)<>SUBSTR(TO_CHAR(LEVEL+122),2,1))
     
182.   11 - filter(LEVEL<=865)
     
183.    8 - filter("B"."X"<"C"."X")
     
184.   10 - filter("A"."X"<"B"."X" AND "A"."X"<494 AND "A"."X"+"B"."X"-"C"."X"=0 AND TRANSLATE('1234
     
185.               56789','$'||TO_CHAR("A"."X")||TO_CHAR("B"."X")||TO_CHAR("C"."X"),'$') IS NULL)
     
186. 
     
187. Note: rule based optimization
     
188. 
     
189. 已选择43行。
     
190. 
     
191. 已用时间:  00: 00: 00.07
     
192. SQL> explain plan for with t as(select x from (select level+122 x from dual connect by level<=(987-122))
     
193.   2  where substr(x,1,1)<>substr(x,2,1) and  substr(x,1,1)<>substr(x,3,1)
     
194.   3  and instr(x,0)=0)
     
195.   4  select count(*) from (
     
196.   5  select /*+rule */a.x||'+'||b.x||'-'||c.x||'==0' from t a,t b,t c
     
197.   6  where a.x+b.x-c.x=0 and a.x<494 and a.x<b.x and b.x<c.x
     
198.   7  and translate('123456789','$'||a.x||b.x||c.x,'$') is null)
     
199.   8  ;
     
200. 
     
201. 已解释。
     
202. 
     
203. 已用时间:  00: 00: 00.00
     
204. SQL> select * from table(dbms_xplan.display);
     
205. 
     
206. PLAN_TABLE_OUTPUT
     
207. ------------------------------------------------------------------------------------------------------------------------------------
     
208. 
     
209. --------------------------------------------------------------------------------------------------
     
210. | Id  | Operation                          |  Name                       | Rows  | Bytes | Cost  |
     
211. --------------------------------------------------------------------------------------------------
     
212. |   0 | SELECT STATEMENT                   |                             |       |       |       |
     
213. |   2 |  TEMP TABLE TRANSFORMATION         |                             |       |       |       |
     
214. |   1 |   RECURSIVE EXECUTION              | SYS_LE_2_0                  |       |       |       |
     
215. |   0 |    INSERT STATEMENT                |                             |       |       |       |
     
216. |   1 |     LOAD AS SELECT                 |                             |       |       |       |
     
217. |   2 |      VIEW                          |                             |       |       |       |
     
218. |*  3 |       FILTER                       |                             |       |       |       |
     
219. |   4 |        CONNECT BY WITH FILTERING   |                             |       |       |       |
     
220. |   5 |         NESTED LOOPS               |                             |       |       |       |
     
221. |   6 |          TABLE ACCESS FULL         | DUAL                        |       |       |       |
     
222. |   7 |          TABLE ACCESS BY USER ROWID| DUAL                        |       |       |       |
     
223. |   8 |         NESTED LOOPS               |                             |       |       |       |
     
224. |   9 |          BUFFER SORT               |                             |       |       |       |
     
225. |  10 |           CONNECT BY PUMP          |                             |       |       |       |
     
226. |* 11 |          FILTER                    |                             |       |       |       |
     
227. |  12 |           TABLE ACCESS FULL        | DUAL                        |       |       |       |
     
228. |   3 |   SORT AGGREGATE                   |                             |       |       |       |
     
229. |   4 |    NESTED LOOPS                    |                             |       |       |       |
     
230. |   5 |     NESTED LOOPS                   |                             |       |       |       |
     
231. |   6 |      VIEW                          |                             |       |       |       |
     
232. |   7 |       TABLE ACCESS FULL            | SYS_TEMP_0FD9D6607_109DBD8  |       |       |       |
     
233. |*  8 |      VIEW                          |                             |       |       |       |
     
234. |   9 |       TABLE ACCESS FULL            | SYS_TEMP_0FD9D6607_109DBD8  |       |       |       |
     
235. |* 10 |     VIEW                           |                             |       |       |       |
     
236. |  11 |      TABLE ACCESS FULL             | SYS_TEMP_0FD9D6607_109DBD8  |       |       |       |
     
237. --------------------------------------------------------------------------------------------------
     
238. 
     
239. Predicate Information (identified by operation id):
     
240. ---------------------------------------------------
     
241. 
     
242.    3 - filter(INSTR(TO_CHAR(LEVEL+122),'0')=0 AND SUBSTR(TO_CHAR(LEVEL+122),1,1)<>SUBSTR(TO_CHA
     
243.               R(LEVEL+122),3,1) AND SUBSTR(TO_CHAR(LEVEL+122),1,1)<>SUBSTR(TO_CHAR(LEVEL+122),2
     
244.               ,1))
     
245.   11 - filter(LEVEL<=865)
     
246.    8 - filter("B"."X"<"C"."X")
     
247.   10 - filter(TRANSLATE('123456789','$'||TO_CHAR("A"."X")||TO_CHAR("B"."X")||TO_CHAR("C"."X"),'
     
248.               $') IS NULL AND "A"."X"<"B"."X" AND "A"."X"<494 AND "A"."X"+"B"."X"-"C"."X"=0)
     
249. 
     
250. Note: rule based optimization
     
251. 
     
252. 已选择43行。
     
253. 
     
254. 已用时间:  00: 00: 00.01
     
255. SQL> explain plan for  with tmp as(
     
256.   2   select a.num from (select rownum as num from dual connect by rownum <= 999) a
     
257.   3   where substr(a.num,1,1) != substr(a.num,2,1)
     
258.   4   and substr(a.num,3,1) != substr(a.num,2,1)
     
259.   5   and substr(a.num,1,1) != substr(a.num,3,1) and instr(a.num,0)=0
     
260.   6   and a.num >= 123 and a.num<=987)
     
261.   7   --
     
262.   8   select count(*) from(
     
263.   9   select
     
264. 10  a.num || '+' || b.num || '=' || c.num as str
     
265. 11  from tmp a, tmp b, tmp c
     
266. 12  where translate('123456789','$'||a.num||b.num||c.num,'$') is null
     
267. 13        and a.num + b.num = c.num
     
268. 14        and a.num < b.num
     
269. 15        and b.num < c.num
     
270. 16  );
     
271. 
     
272. 已解释。
     
273. 
     
274. 已用时间:  00: 00: 00.00
     
275. SQL> select * from table(dbms_xplan.display);
     
276. 
     
277. PLAN_TABLE_OUTPUT
     
278. ------------------------------------------------------------------------------------------------------------------------------------
     
279. 
     
280. --------------------------------------------------------------------------------------------------
     
281. | Id  | Operation                          |  Name                       | Rows  | Bytes | Cost  |
     
282. --------------------------------------------------------------------------------------------------
     
283. |   0 | SELECT STATEMENT                   |                             |       |       |       |
     
284. |   2 |  TEMP TABLE TRANSFORMATION         |                             |       |       |       |
     
285. |   1 |   RECURSIVE EXECUTION              | SYS_LE_2_0                  |       |       |       |
     
286. |   0 |    INSERT STATEMENT                |                             |       |       |       |
     
287. |   1 |     LOAD AS SELECT                 |                             |       |       |       |
     
288. |*  2 |      VIEW                          |                             |       |       |       |
     
289. |*  3 |       COUNT STOPKEY                |                             |       |       |       |
     
290. |   4 |        CONNECT BY WITH FILTERING   |                             |       |       |       |
     
291. |   5 |         NESTED LOOPS               |                             |       |       |       |
     
292. |   6 |          TABLE ACCESS FULL         | DUAL                        |       |       |       |
     
293. |   7 |          TABLE ACCESS BY USER ROWID| DUAL                        |       |       |       |
     
294. |   8 |         NESTED LOOPS               |                             |       |       |       |
     
295. |   9 |          BUFFER SORT               |                             |       |       |       |
     
296. |  10 |           CONNECT BY PUMP          |                             |       |       |       |
     
297. |  11 |          TABLE ACCESS FULL         | DUAL                        |       |       |       |
     
298. |   3 |   SORT AGGREGATE                   |                             |       |       |       |
     
299. |   4 |    MERGE JOIN                      |                             |       |       |       |
     
300. |   5 |     SORT JOIN                      |                             |       |       |       |
     
301. |   6 |      NESTED LOOPS                  |                             |       |       |       |
     
302. |   7 |       VIEW                         |                             |       |       |       |
     
303. |   8 |        TABLE ACCESS FULL           | SYS_TEMP_0FD9D6608_109DBD8  |       |       |       |
     
304. |*  9 |       VIEW                         |                             |       |       |       |
     
305. |  10 |        TABLE ACCESS FULL           | SYS_TEMP_0FD9D6608_109DBD8  |       |       |       |
     
306. |* 11 |     FILTER                         |                             |       |       |       |
     
307. |* 12 |      SORT JOIN                     |                             |       |       |       |
     
308. |  13 |       VIEW                         |                             |       |       |       |
     
309. |  14 |        TABLE ACCESS FULL           | SYS_TEMP_0FD9D6608_109DBD8  |       |       |       |
     
310. --------------------------------------------------------------------------------------------------
     
311. 
     
312. Predicate Information (identified by operation id):
     
313. ---------------------------------------------------
     
314. 
     
315.    2 - filter("A"."NUM"<=987 AND "A"."NUM">=123 AND INSTR(TO_CHAR("A"."NUM"),'0')=0 AND SUBSTR(
     
316.               TO_CHAR("A"."NUM"),1,1)<>SUBSTR(TO_CHAR("A"."NUM"),3,1) AND SUBSTR(TO_CHAR("A"."N
     
317.               UM"),3,1)<>SUBSTR(TO_CHAR("A"."NUM"),2,1) AND SUBSTR(TO_CHAR("A"."NUM"),1,1)<>SUB
     
318.               STR(TO_CHAR("A"."NUM"),2,1))
     
319.    3 - filter(ROWNUM<=999)
     
320.    9 - filter("A"."NUM"<"B"."NUM")
     
321.   11 - filter("B"."NUM"<"C"."NUM" AND TRANSLATE('123456789','$'||TO_CHAR("A"."NUM")||TO_CHAR("B
     
322.               "."NUM")||TO_CHAR("C"."NUM"),'$') IS NULL)
     
323.   12 - access("C"."NUM"="A"."NUM"+"B"."NUM")
     
324.        filter("C"."NUM"="A"."NUM"+"B"."NUM")
     
325. 
     
326. Note: rule based optimization
     
327. 
     
328. 已选择48行。
     
329. 
     
330. 已用时间:  00: 00: 00.01
     
331. SQL>
     
332. 
     
333. SQL> explain plan for with t as(select x from (select level+122 x from dual connect by level<=(987-122))
     
334.   2  where substr(x,1,1)<>substr(x,2,1) and  substr(x,1,1)<>substr(x,3,1) and substr(x,2,1)<>substr(x,3,1)
     
335.   3  and instr(x,0)=0)
     
336.   4  select count(*) from (
     
337.   5  select /*+rule */a.x||'+'||b.x||'-'||c.x||'==0' from t a,t b,t c
     
338.   6  where a.x+b.x-c.x=0 and a.x<494 and a.x<b.x and b.x<c.x
     
339.   7  and translate('123456789','$'||a.x||b.x||c.x,'$') is null)
     
340.   8  ;
     
341. 
     
342. 已解释。
     
343. 
     
344. 已用时间:  00: 00: 00.00
     
345. SQL> select * from table(dbms_xplan.display);
     
346. 
     
347. PLAN_TABLE_OUTPUT
     
348. --------------------------------------------------------------------------------------------------------------------
     
349. 
     
350. --------------------------------------------------------------------------------------------------
     
351. | Id  | Operation                          |  Name                       | Rows  | Bytes | Cost  |
     
352. --------------------------------------------------------------------------------------------------
     
353. |   0 | SELECT STATEMENT                   |                             |       |       |       |
     
354. |   2 |  TEMP TABLE TRANSFORMATION         |                             |       |       |       |
     
355. |   1 |   RECURSIVE EXECUTION              | SYS_LE_2_0                  |       |       |       |
     
356. |   0 |    INSERT STATEMENT                |                             |       |       |       |
     
357. |   1 |     LOAD AS SELECT                 |                             |       |       |       |
     
358. |   2 |      VIEW                          |                             |       |       |       |
     
359. |*  3 |       FILTER                       |                             |       |       |       |
     
360. |   4 |        CONNECT BY WITH FILTERING   |                             |       |       |       |
     
361. |   5 |         NESTED LOOPS               |                             |       |       |       |
     
362. |   6 |          TABLE ACCESS FULL         | DUAL                        |       |       |       |
     
363. |   7 |          TABLE ACCESS BY USER ROWID| DUAL                        |       |       |       |
     
364. |   8 |         NESTED LOOPS               |                             |       |       |       |
     
365. |   9 |          BUFFER SORT               |                             |       |       |       |
     
366. |  10 |           CONNECT BY PUMP          |                             |       |       |       |
     
367. |* 11 |          FILTER                    |                             |       |       |       |
     
368. |  12 |           TABLE ACCESS FULL        | DUAL                        |       |       |       |
     
369. |   3 |   SORT AGGREGATE                   |                             |       |       |       |
     
370. |   4 |    NESTED LOOPS                    |                             |       |       |       |
     
371. |   5 |     NESTED LOOPS                   |                             |       |       |       |
     
372. |   6 |      VIEW                          |                             |       |       |       |
     
373. |   7 |       TABLE ACCESS FULL            | SYS_TEMP_0FD9D6609_109DBD8  |       |       |       |
     
374. |*  8 |      VIEW                          |                             |       |       |       |
     
375. |   9 |       TABLE ACCESS FULL            | SYS_TEMP_0FD9D6609_109DBD8  |       |       |       |
     
376. |* 10 |     VIEW                           |                             |       |       |       |
     
377. |  11 |      TABLE ACCESS FULL             | SYS_TEMP_0FD9D6609_109DBD8  |       |       |       |
     
378. --------------------------------------------------------------------------------------------------
     
379. 
     
380. Predicate Information (identified by operation id):
     
381. ---------------------------------------------------
     
382. 
     
383.    3 - filter(INSTR(TO_CHAR(LEVEL+122),'0')=0 AND SUBSTR(TO_CHAR(LEVEL+122),2,1)<>SUBSTR(TO_CHA
     
384.               R(LEVEL+122),3,1) AND SUBSTR(TO_CHAR(LEVEL+122),1,1)<>SUBSTR(TO_CHAR(LEVEL+122),3
     
385.               ,1) AND SUBSTR(TO_CHAR(LEVEL+122),1,1)<>SUBSTR(TO_CHAR(LEVEL+122),2,1))
     
386.   11 - filter(LEVEL<=865)
     
387.    8 - filter("B"."X"<"C"."X")
     
388.   10 - filter(TRANSLATE('123456789','$'||TO_CHAR("A"."X")||TO_CHAR("B"."X")||TO_CHAR("C"."X"),'
     
389.               $') IS NULL AND "A"."X"<"B"."X" AND "A"."X"<494 AND "A"."X"+"B"."X"-"C"."X"=0)
     
390. 
     
391. Note: rule based optimization
     
392. 
     
393. 已选择43行。
     
394. 
     
395. 已用时间:  00: 00: 00.01
     
396. 
     
397. SQL> with t as(select x from (select level+122 x from dual connect by level<=(987-122))
     
398.   2  where substr(x,1,1)<>substr(x,2,1) and  substr(x,1,1)<>substr(x,3,1) and substr(x,2,1)<>substr(x,3,1)
     
399.   3  and instr(x,0)=0)
     
400.   4  select count(*) from (
     
401.   5  select /*+rule */a.x||'+'||b.x||'-'||c.x||'==0' from t a,t b,t c
     
402.   6  where a.x+b.x-c.x=0 and a.x<494 and a.x<b.x and b.x<c.x
     
403.   7  and translate('123456789','$'||a.x||b.x||c.x,'$') is null)
     
404.   8  ;
     
405. ^C终止批处理操作吗(Y/N)? y
     

复制代码

〇〇

用了rule提示反而不出结果

〇〇

迄今为止最快的,sort join+merge join

已连接。
SQL>  with tmp as(
  2   select a.num from (select rownum as num from dual connect by rownum <= 999) a
  3   where substr(a.num,1,1) != substr(a.num,2,1)
  4   and substr(a.num,3,1) != substr(a.num,2,1)
  5   and substr(a.num,1,1) != substr(a.num,3,1) and instr(a.num,0)=0
  6   and a.num >= 123 and a.num<=987)
  7   --
  8   --select count(*) from(
  9   select
10  a.num || '+' || b.num || '=' || c.num as str
11  from tmp a, tmp b, tmp c
12  where translate('123456789','$'||a.num||b.num||c.num,'$') is null
13        and a.num + b.num = c.num
14        and a.num < b.num
15        and b.num < c.num
16  --);

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

已选择168行。

已用时间:  00: 00: 00.08