SQL> set timing on;
SQL>
SQL> set pagesize 1000;
SQL>
SQL>
SQL> with
2 function f_check (p_nlist in varchar2)
3 return int
4 is
5 type r_rec is record( n int,
6 x int,
7 y int);
8
9 type t_rec is table of r_rec index by pls_integer;
10 l_rec t_rec;
11 l_tmp t_rec;
12
13 l_cnt int := 0;
14 l_t_cnt int := -1;
15
16 begin
17
18 select n,
19 ceil(n/6),
20 mod(n-1,6)+1
21 bulk collect into l_rec
22 from (
23 select regexp_substr(p_nlist,'[^,]+',1,level) n
24 from dual
25 connect by level <= regexp_count(p_nlist,',')+1
26 );
27
28 for rec in ( with t as (
29 select substr(sys_connect_by_path(n,','),2) rc
30 from (select level n from dual connect by level<=6)
31 where level = 3
32 connect by prior n < n
33 )
34 select a.rc x_rc,b.rc y_rc
35 from t a,t b ) loop
36
37 l_tmp := l_rec;
38
39 for i in l_tmp.first..l_tmp.last loop
40 if l_tmp.exists(i)=TRUE and instr(rec.x_rc,l_tmp(i).x) > 0 then
41 l_tmp.delete(i);
42 end if;
43 end loop;
44
45 for i in l_tmp.first..l_tmp.last loop
46 if l_tmp.exists(i)=TRUE and instr(rec.y_rc,l_tmp(i).y) > 0 then
47 l_tmp.delete(i);
48 end if;
49 end loop;
50
51 if l_tmp.count > 0 then
52 l_cnt := l_cnt + 1;
53 else
54 return 0;
55 end if;
56
57 end loop;
58
59 select count(*)
60 into l_t_cnt
61 from (
62 with t as (
63 select substr(sys_connect_by_path(n,','),2) rc
64 from (select level n from dual connect by level<=6)
65 where level = 3
66 connect by prior n < n
67 )
68 select a.rc x_rc,b.rc y_rc
69 from t a,t b
70 );
71
72 if l_cnt=l_t_cnt then
73 return 1;
74 else
75 return 0;
76 end if;
77
78 end;
79 t(n,c,x,y) as (select level,chr(64+level),ceil(level/6),mod(level-1,6)+1 from dual connect by level<=6*6),
80 s(lvl,n,nlist,clist) as (select 1, n,cast(n as varchar2(100)),c
81 from t
82 where t.n between 1 and 6
83 union all
84 select lvl + 1,
85 b.n,
86 nlist||','||b.n,
87 clist||','||b.c
88 from s,t b
89 where s.n < b.n
90 and (select greatest(sum(case when a.x = b.x then 1 end),
91 sum(case when a.y = b.y then 1 end)
92 )
93 from t a
94 where instr(clist||','||b.c,a.c)>0
95 ) <= 2
96 and lvl < 10
97 and b.n between (ceil((lvl+1)/2)-1)*6 + 1 and (ceil((lvl+1)/2)-1)*6 + 6*2
98 )
99 select nlist from s where lvl = 10 and f_check(nlist)=1 and rownum <= 100
100 /
NLIST
--------------------------------------------------------------------------------
1,2,7,9,14,16,21,22,29,36
1,2,7,9,14,16,21,22,30,35
1,2,7,9,14,16,23,27,28,36
1,2,7,9,14,16,23,30,33,34
1,2,7,9,14,16,24,27,28,35
1,2,7,9,14,16,24,29,33,34
1,2,7,9,14,17,21,23,28,36
1,2,7,9,14,17,21,23,30,34
1,2,7,9,14,17,22,27,29,36
1,2,7,9,14,17,22,30,33,35
1,2,7,9,14,17,24,27,29,34
1,2,7,9,14,17,24,28,33,35
1,2,7,9,14,18,21,24,28,35
1,2,7,9,14,18,21,24,29,34
1,2,7,9,14,18,22,27,30,35
1,2,7,9,14,18,22,29,33,36
1,2,7,9,14,18,23,27,30,34
1,2,7,9,14,18,23,28,33,36
1,2,7,9,15,16,20,22,29,36
1,2,7,9,15,16,20,22,30,35
1,2,7,9,15,16,23,26,28,36
1,2,7,9,15,16,23,30,32,34
1,2,7,9,15,16,24,26,28,35
1,2,7,9,15,16,24,29,32,34
1,2,7,9,15,17,20,23,28,36
1,2,7,9,15,17,20,23,30,34
1,2,7,9,15,17,22,26,29,36
1,2,7,9,15,17,22,30,32,35
1,2,7,9,15,17,24,26,29,34
1,2,7,9,15,17,24,28,32,35
1,2,7,9,15,18,20,24,28,35
1,2,7,9,15,18,20,24,29,34
1,2,7,9,15,18,22,26,30,35
1,2,7,9,15,18,22,29,32,36
1,2,7,9,15,18,23,26,30,34
1,2,7,9,15,18,23,28,32,36
1,2,7,9,16,20,23,27,29,36
1,2,7,9,16,20,23,30,33,35
1,2,7,9,16,20,24,27,30,35
1,2,7,9,16,20,24,29,33,36
1,2,7,9,16,21,23,26,29,36
1,2,7,9,16,21,23,30,32,35
1,2,7,9,16,21,24,26,30,35
1,2,7,9,16,21,24,29,32,36
1,2,7,9,16,23,26,30,33,36
1,2,7,9,16,23,27,30,32,36
1,2,7,9,16,24,26,29,33,35
1,2,7,9,16,24,27,29,32,35
1,2,7,9,17,20,22,27,28,36
1,2,7,9,17,20,22,30,33,34
1,2,7,9,17,20,24,27,30,34
1,2,7,9,17,20,24,28,33,36
1,2,7,9,17,21,22,26,28,36
1,2,7,9,17,21,22,30,32,34
1,2,7,9,17,21,24,26,30,34
1,2,7,9,17,21,24,28,32,36
1,2,7,9,17,22,26,30,33,36
1,2,7,9,17,22,27,30,32,36
1,2,7,9,17,24,26,28,33,34
1,2,7,9,17,24,27,28,32,34
1,2,7,9,18,20,22,27,28,35
1,2,7,9,18,20,22,29,33,34
1,2,7,9,18,20,23,27,29,34
1,2,7,9,18,20,23,28,33,35
1,2,7,9,18,21,22,26,28,35
1,2,7,9,18,21,22,29,32,34
1,2,7,9,18,21,23,26,29,34
1,2,7,9,18,21,23,28,32,35
1,2,7,9,18,22,26,29,33,35
1,2,7,9,18,22,27,29,32,35
1,2,7,9,18,23,26,28,33,34
1,2,7,9,18,23,27,28,32,34
1,2,7,10,14,15,21,22,29,36
1,2,7,10,14,15,21,22,30,35
1,2,7,10,14,15,23,27,28,36
1,2,7,10,14,15,23,30,33,34
1,2,7,10,14,15,24,27,28,35
1,2,7,10,14,15,24,29,33,34
1,2,7,10,14,17,21,28,29,36
1,2,7,10,14,17,21,30,34,35
1,2,7,10,14,17,22,23,27,36
1,2,7,10,14,17,22,23,30,33
1,2,7,10,14,17,24,27,34,35
1,2,7,10,14,17,24,28,29,33
1,2,7,10,14,18,21,28,30,35
1,2,7,10,14,18,21,29,34,36
1,2,7,10,14,18,22,24,27,35
1,2,7,10,14,18,22,24,29,33
1,2,7,10,14,18,23,27,34,36
1,2,7,10,14,18,23,28,30,33
1,2,7,10,15,16,20,21,29,36
1,2,7,10,15,16,20,21,30,35
1,2,7,10,15,16,23,26,27,36
1,2,7,10,15,16,23,30,32,33
1,2,7,10,15,16,24,26,27,35
1,2,7,10,15,16,24,29,32,33
1,2,7,10,15,20,23,28,29,36
1,2,7,10,15,20,23,30,34,35
1,2,7,10,15,20,24,28,30,35
1,2,7,10,15,20,24,29,34,36
100 rows selected
Executed in 112.685 seconds
|