趣味题

〇〇

按newkid的提示修改了，快不少

SQL> drop table t purge;

表已删除。

已用时间:  00: 00: 00.08
SQL> create table t(x char(4),n number(5,0));

表已创建。

已用时间:  00: 00: 00.00
SQL> insert into t select x,to_number(x,'fm0xxx')n from(SELECT REPLACE(SYS_CONNECT_BY_PATH(rn,','),',') x
  2               FROM (SELECT to_char(ROWNUM-1,'fmx') rn FROM DUAL CONNECT BY ROWNUM<=16)
  3              WHERE LEVEL=4
  4              CONNECT BY NOCYCLE rn<> PRIOR rn AND LEVEL<=4
  5            );

已创建43680行。

已用时间:  00: 00: 00.55

SQL> create unique index idxtx on t(x);

索引已创建。

已用时间:  00: 00: 00.06
SQL> create unique index idxtn on t(n);

索引已创建。

已用时间:  00: 00: 00.06
SQL> exec DBMS_STATS.GATHER_TABLE_STATS('LT','T');

PL/SQL 过程已成功完成。

已用时间:  00: 00: 00.23
SQL>
SQL> set autot on
SQL> select count(*) from(
  2  select a.x||'+'||b.x||'='||to_char(a.n+b.n,'fm0xxx')s
  3  from t a,t b --,t c
  4  where a.n<to_number('8000','fm0xxx')
  5        and a.n<b.n
  6        AND a.n+b.n <=to_number('fedc','fm0xxx')
  7        and length(translate('0123456789abcdef','$'||a.x||b.x||to_char(a.n+b.n,'fm0xxx'),'$'))=4
  8        );

  COUNT(*)
----------
   3026800

已用时间:  00: 37: 40.47

执行计划
----------------------------------------------------------
Plan hash value: 1449066498

---------------------------------------------------------------------------------------
| Id  | Operation                     | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |     1 |    18 |   178  (84)| 00:00:03 |
|   1 |  SORT AGGREGATE               |       |     1 |    18 |            |          |
|   2 |   MERGE JOIN                  |       | 23849 |   419K|   178  (84)| 00:00:03 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T     |  2184 | 19656 |     3   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN          | IDXTN |   393 |       |     2   (0)| 00:00:01 |
|*  5 |    FILTER                     |       |       |       |            |          |
|*  6 |     SORT JOIN                 |       | 43680 |   383K|    29   (7)| 00:00:01 |
|   7 |      TABLE ACCESS FULL        | T     | 43680 |   383K|    27   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("A"."N"<TO_NUMBER('8000','fm0xxx'))
   5 - filter("A"."N"+"B"."N"<=TO_NUMBER('fedc','fm0xxx') AND
              LENGTH(TRANSLATE('0123456789abcdef','$'||"A"."X"||"B"."X"||TO_CHAR("A"."N"+"B".
              "N",'fm0xxx'),'$'))=4)
   6 - access("A"."N"<"B"."N")
       filter("A"."N"<"B"."N")


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        190  consistent gets
          0  physical reads
          0  redo size
        525  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

〇〇

其实从3位的结果可以发现
一个右值有8组解
可以通过2个左值的个十百位对调产生，如果规定第一个小，那么最高位不换

387+162=549
187+362=549
367+182=549
167+382=549
162+387=549
362+187=549
182+367=549
382+167=549

如果能找到一个，转出另7（3）个，可以省很多....

〇〇

create or replace procedure pc
as
   time_before BINARY_INTEGER;
   time_after BINARY_INTEGER;
   TYPE t_num IS TABLE OF VARCHAR2(3) INDEX BY  PLS_INTEGER ;
   a t_num;
   b t_num;
   c PLS_INTEGER;
begin
c:=0;
time_before := DBMS_UTILITY.GET_TIME;
for i in 123..987 loop
if substr(i,1,1)<>substr(i,2,1)and
substr(i,2,1)<>substr(i,3,1)and
substr(i,1,1)<>substr(i,3,1)and
instr(i,0)=0 then
c:=c+1;
a(c):=i;
b(c):=i;
end if;
end loop;
for k in 1 ..3 loop
FOR I IN 1 .. c LOOP
FOR J IN 1 .. c LOOP
if a(i)<500 and a(i)+b(j)<1000 and a(i)<b(j) and translate('123456789','$'||a(i)||b(j)||(a(i)+b(j)),'$')is null then
--dbms_output.put_line(a(i)||b(j)||(a(i)+b(j)));
NULL;
end if;
end loop;
end loop;
end loop;
time_after := DBMS_UTILITY.GET_TIME;
DBMS_OUTPUT.PUT_LINE ('output:'||(time_after - time_before));
end;
/

SQL> exec pc;
output:115

PL/SQL 过程已成功完成。

已用时间:  00: 00: 01.17

〇〇

提前退出循环,没什么提高
create or replace procedure pc1
as
   time_before BINARY_INTEGER;
   time_after BINARY_INTEGER;
   TYPE t_num IS TABLE OF VARCHAR2(3) INDEX BY  PLS_INTEGER ;
   a t_num;
   b t_num;
   c PLS_INTEGER;
begin
c:=0;
time_before := DBMS_UTILITY.GET_TIME;
for i in 123..987 loop
if substr(i,1,1)<>substr(i,2,1)and
substr(i,2,1)<>substr(i,3,1)and
substr(i,1,1)<>substr(i,3,1)and
instr(i,0)=0 then
c:=c+1;
a(c):=i;
b(c):=i;
end if;
end loop;
for k in 1 ..3 loop
FOR I IN 1 .. c LOOP
FOR J IN 1 .. c LOOP
if a(i)<500 and a(i)+b(j)<1000 and a(i)<b(j) and translate('123456789','$'||a(i)||b(j)||(a(i)+b(j)),'$')is null then
--dbms_output.put_line(a(i)||b(j)||(a(i)+b(j)));
NULL;
elsif a(i)>=500 then --0.42s
        goto l_end;
elsif a(i)+b(j)>=1000 then --0.41s
        goto next_j;
end if;
end loop;
<<next_j>>
null;
end loop;
<<l_end>>
null;
end loop;

time_after := DBMS_UTILITY.GET_TIME;
DBMS_OUTPUT.PUT_LINE ('output:'||(time_after - time_before));
end;
/

〇〇

数学软件的写法
Mathematica代码：
data=Select[Map[FromDigits /@ # &, Union[Sort@Partition[#, 3] & /@ Permutations[Range[9]]]], Total[#] == 2 Max[#] &]


haskell程序
import List
  
  d9 = [1..9]

  v a b c = 100 * a + 10 * b + c
  
  digit n i = mod (div n (10^i)) 10

  isRight n l =  ((n <= 987) && (null $ intersect l [digit n 0, digit n 1, digit n 2]) && ((digit n 0) /= (digit n 1)) && ((digit n 1) /= (digit n 2)) && ((digit n 2) /= (digit n 0)))
  
  result = [(x, y, x + y)|a<-[1..4], b<-d9\\[a], c<-d9\\[a, b], let x = v a b c,
                           d<-[a+1..9-a]\\[b, c], e<-d9\\[a, b, c, d], f<-d9\\[a, b, c, d, e], let y = v d e f,
                           isRight (x + y) [a, b, c, d, e, f, 0]]

  main = do
            print $ zip [1..] result

nyfor

成精了....

〇〇

几个有效的改进，还能把b(j)的上界再缩短，避免判断a(i)+b(j)>=1000
create or replace procedure pc3
as
   time_before BINARY_INTEGER;
   time_after BINARY_INTEGER;
   TYPE t_num IS TABLE OF VARCHAR2(3) INDEX BY  PLS_INTEGER ;
   x t_num;
   a t_num;
   b t_num;
   c PLS_INTEGER;
   c1 PLS_INTEGER;
begin
c:=0;
time_before := DBMS_UTILITY.GET_TIME;
for i in 123..987 loop
if substr(i,1,1)<>substr(i,2,1)and
substr(i,2,1)<>substr(i,3,1)and
substr(i,1,1)<>substr(i,3,1)and
instr(i,0)=0 then
c:=c+1;
if i<500 then
a(c):=i;
c1:=c;
end if;
b(c):=i;
x(i):=c;
end if;
end loop;
for k in 1 ..3 loop
FOR I IN 1 .. c1 LOOP
FOR J IN x(a(i))+1 .. c LOOP
if /*a(i)<500 and a(i)<b(j) and*/ a(i)+b(j)<1000 and  translate('123456789','$'||a(i)||b(j)||(a(i)+b(j)),'$')is null then
--dbms_output.put_line(a(i)||b(j)||(a(i)+b(j)));
NULL;
--elsif a(i)>=500 then --0.42s
--        goto l_end;
elsif a(i)+b(j)>=1000 then --0.41s
        goto next_j;
end if;
end loop;
<<next_j>>
null;
end loop;
<<l_end>>
null;
end loop;

time_after := DBMS_UTILITY.GET_TIME;
DBMS_OUTPUT.PUT_LINE ('output:'||(time_after - time_before));
end;
/





SQL> create or replace procedure pc
  2  as
  3     time_before BINARY_INTEGER;
  4     time_after BINARY_INTEGER;
  5     TYPE t_num IS TABLE OF VARCHAR2(3) INDEX BY  PLS_INTEGER ;
  6     a t_num;
  7     b t_num;
  8     c PLS_INTEGER;
  9  begin
10  c:=0;
11  time_before := DBMS_UTILITY.GET_TIME;
12  for i in 123..987 loop
13  if substr(i,1,1)<>substr(i,2,1)and
14  substr(i,2,1)<>substr(i,3,1)and
15  substr(i,1,1)<>substr(i,3,1)and
16  instr(i,0)=0 then
17  c:=c+1;
18  a(c):=i;
19  b(c):=i;
20  end if;
21  end loop;
22  for k in 1 ..3 loop
23  FOR I IN 1 .. c LOOP
24  FOR J IN 1 .. c LOOP
25  if a(i)<500 and a(i)+b(j)<1000 and a(i)<b(j) and translate('123456789','$'||a(i)||b(j)||(a(i)+b(j)),'$')is null then
26  --dbms_output.put_line(a(i)||b(j)||(a(i)+b(j)));
27  NULL;
28  end if;
29  end loop;
30  end loop;
31  end loop;
32  time_after := DBMS_UTILITY.GET_TIME;
33  DBMS_OUTPUT.PUT_LINE ('output:'||(time_after - time_before));
34  end;
35  /

过程已创建。

已用时间:  00: 00: 00.07
SQL> set serverout on
SQL> exec pc;
output:180

PL/SQL 过程已成功完成。

已用时间:  00: 00: 01.08
SQL> /

过程已创建。

已用时间:  00: 00: 00.01
SQL> exec pc;
output:175

PL/SQL 过程已成功完成。

已用时间:  00: 00: 01.07
SQL> create or replace procedure pc1
  2  as
  3     time_before BINARY_INTEGER;
  4     time_after BINARY_INTEGER;
  5     TYPE t_num IS TABLE OF VARCHAR2(3) INDEX BY  PLS_INTEGER ;
  6     a t_num;
  7     b t_num;
  8     c PLS_INTEGER;
  9  begin
10  c:=0;
11  time_before := DBMS_UTILITY.GET_TIME;
12  for i in 123..987 loop
13  if substr(i,1,1)<>substr(i,2,1)and
14  substr(i,2,1)<>substr(i,3,1)and
15  substr(i,1,1)<>substr(i,3,1)and
16  instr(i,0)=0 then
17  c:=c+1;
18  a(c):=i;
19  b(c):=i;
20  end if;
21  end loop;
22  for k in 1 ..3 loop
23  FOR I IN 1 .. c LOOP
24  FOR J IN 1 .. c LOOP
25  if a(i)<500 and a(i)+b(j)<1000 and a(i)<b(j) and translate('123456789','$'||a(i)||b(j)||(a(i)+b(j)),'$')is null then
26  --dbms_output.put_line(a(i)||b(j)||(a(i)+b(j)));
27  NULL;
28  elsif a(i)>=500 then --0.42s
29          goto l_end;
30  elsif a(i)+b(j)>=1000 then --0.41s
31          goto next_j;
32  end if;
33  end loop;
34  <<next_j>>
35  null;
36  end loop;
37  <<l_end>>
38  null;
39  end loop;
40
41  time_after := DBMS_UTILITY.GET_TIME;
42  DBMS_OUTPUT.PUT_LINE ('output:'||(time_after - time_before));
43  end;
44  /

过程已创建。

已用时间:  00: 00: 00.00
SQL> exec pc1;
output:171

PL/SQL 过程已成功完成。

已用时间:  00: 00: 01.07
SQL> create or replace procedure pc2
  2  as
  3     time_before BINARY_INTEGER;
  4     time_after BINARY_INTEGER;
  5     TYPE t_num IS TABLE OF VARCHAR2(3) INDEX BY  PLS_INTEGER ;
  6     a t_num;
  7     b t_num;
  8     c PLS_INTEGER;
  9     c1 PLS_INTEGER;
10  begin
11  c:=0;
12  time_before := DBMS_UTILITY.GET_TIME;
13  for i in 123..987 loop
14  if substr(i,1,1)<>substr(i,2,1)and
15  substr(i,2,1)<>substr(i,3,1)and
16  substr(i,1,1)<>substr(i,3,1)and
17  instr(i,0)=0 then
18  c:=c+1;
19  if i<500 then
20  a(c):=i;
21  c1:=c;
22  end if;
23  b(c):=i;
24  end if;
25  end loop;
26  for k in 1 ..3 loop
27  FOR I IN 1 .. c1 LOOP
28  FOR J IN 1 .. c LOOP
29  if /*a(i)<500 and*/ a(i)+b(j)<1000 and a(i)<b(j) and translate('123456789','$'||a(i)||b(j)||(a(i)+b(j)),'$')is null then
30  --dbms_output.put_line(a(i)||b(j)||(a(i)+b(j)));
31  NULL;
32  --elsif a(i)>=500 then --0.42s
33  --        goto l_end;
34  elsif a(i)+b(j)>=1000 then --0.41s
35          goto next_j;
36  end if;
37  end loop;
38  <<next_j>>
39  null;
40  end loop;
41  <<l_end>>
42  null;
43  end loop;
44
45  time_after := DBMS_UTILITY.GET_TIME;
46  DBMS_OUTPUT.PUT_LINE ('output:'||(time_after - time_before));
47  end;
48  /

过程已创建。

已用时间:  00: 00: 00.00
SQL> exec pc2;
output:158

PL/SQL 过程已成功完成。

已用时间:  00: 00: 01.05
SQL> exec pc2;
output:158

PL/SQL 过程已成功完成。

已用时间:  00: 00: 01.05


SQL> create or replace procedure pc3
  2  as
  3     time_before BINARY_INTEGER;
  4     time_after BINARY_INTEGER;
  5     TYPE t_num IS TABLE OF VARCHAR2(3) INDEX BY  PLS_INTEGER ;
  6     x t_num;
  7     a t_num;
  8     b t_num;
  9     c PLS_INTEGER;
10     c1 PLS_INTEGER;
11  begin
12  c:=0;
13  time_before := DBMS_UTILITY.GET_TIME;
14  for i in 123..987 loop
15  if substr(i,1,1)<>substr(i,2,1)and
16  substr(i,2,1)<>substr(i,3,1)and
17  substr(i,1,1)<>substr(i,3,1)and
18  instr(i,0)=0 then
19  c:=c+1;
20  if i<500 then
21  a(c):=i;
22  c1:=c;
23  end if;
24  b(c):=i;
25  x(i):=c;
26  end if;
27  end loop;
28  for k in 1 ..3 loop
29  FOR I IN 1 .. c1 LOOP
30  FOR J IN x(a(i))+1 .. c LOOP
31  if /*a(i)<500 and a(i)<b(j) and*/ a(i)+b(j)<1000 and  translate('123456789','$'||a(i)||b(j)||(a(i)+b(j)),'$')is null then
32  --dbms_output.put_line(a(i)||b(j)||(a(i)+b(j)));
33  NULL;
34  --elsif a(i)>=500 then --0.42s
35  --        goto l_end;
36  elsif a(i)+b(j)>=1000 then --0.41s
37          goto next_j;
38  end if;
39  end loop;
40  <<next_j>>
41  null;
42  end loop;
43  <<l_end>>
44  null;
45  end loop;
46
47  time_after := DBMS_UTILITY.GET_TIME;
48  DBMS_OUTPUT.PUT_LINE ('output:'||(time_after - time_before));
49  end;
50  /

过程已创建。

已用时间:  00: 00: 00.00
SQL> exec pc3;
output:136

PL/SQL 过程已成功完成。

已用时间:  00: 00: 01.03
SQL> exec pc3;
output:138

PL/SQL 过程已成功完成。

已用时间:  00: 00: 01.03
SQL>

newkid

你只需要建一个索引：
create unique index idxtn on t(n,x);

其实IOT就可以了，表完全无用。

〇〇

复合索引就不用回表？不是很懂
终于找到一个iot的应用了

〇〇

改善的pl/sql

create or replace procedure pc3
as
   time_before BINARY_INTEGER;
   time_after BINARY_INTEGER;
   TYPE t_num IS TABLE OF VARCHAR2(3) INDEX BY  PLS_INTEGER ;
   x t_num;
   a t_num;
   b t_num;
   c PLS_INTEGER;
   c1 PLS_INTEGER;
   r PLS_INTEGER;
begin
c:=0;
time_before := DBMS_UTILITY.GET_TIME;
for i in 123..987 loop
if substr(i,1,1)<>substr(i,2,1)and
substr(i,2,1)<>substr(i,3,1)and
substr(i,1,1)<>substr(i,3,1)and
instr(i,0)=0 then
c:=c+1;
if i<500 then
a(c):=i;
c1:=c;
end if;
b(c):=i;
x(i):=c;
else
x(i):=c;
end if;
end loop;
for k in 1 ..3 loop
r:=0;
FOR I IN 1 .. c1 LOOP
FOR J IN x(a(i))+1 .. x(987-a(i)) LOOP
if /*a(i)<500 and a(i)<b(j) and a(i)+b(j)<1000 and*/  translate('123456789','$'||a(i)||b(j)||(a(i)+b(j)),'$')is null then
--dbms_output.put_line(a(i)||b(j)||(a(i)+b(j)));
--NULL;
r:=r+1;
--elsif a(i)+b(j)>=1000 then --0.41s
--        goto next_j;
end if;
end loop;
<<next_j>>
null;
end loop;
end loop;

time_after := DBMS_UTILITY.GET_TIME;
DBMS_OUTPUT.PUT_LINE ('output:'||(time_after - time_before)||'rows:'||r);
end;
/

已创建。

时间:  00: 00: 00.00
exec pc3;
t:106rows:168

L 过程已成功完成。

时间:  00: 00: 01.00
exec pc3;
t:108rows:168

L 过程已成功完成。

时间:  00: 00: 01.00