## Question and Answer

## You Asked

Tom

I was hoping that you could shed some light on a slight anomoly that I have noticed.

I was looking at the following brain teaser in a newspaper recently, and I thought that I'd write a little program to work it out for me (I know it's cheating)

----------

Each letter in this sum stands for a diferent digit. Given that DAD is odd, what number is represented by ROMAN?

MOMMA

MOMMA

DADS

A

-----

ROMAN

----------

being more familiar with pl/sql than any other programming language, I wrote the following code.

declare

begin

for a in 0..9 loop

for d in 0..9 loop

for s in 0..9 loop

for m in 0..9 loop

for o in 0..9 loop

for r in 0..9 loop

for n in 0..9 loop

if a=d or

a=s or

a=m or

a=o or

a=r or

a=n or

d=s or

d=m or

d=o or

d=r or

d=n or

s=m or

s=o or

s=r or

s=n or

m=o or

m=r or

m=n or

o=r or

o=n or

r=n or

mod(d,2)=0 then null;

elsif

((m * 10000 +

o * 1000 +

m * 100 +

m * 10 +

a) * 2) +

(d * 1000 +

a * 100 +

d * 10 +

s) +

a =

(r * 10000 +

o * 1000 +

m * 100 +

a * 10 +

n) then

dbms_output.put_line('A = ' || a);

dbms_output.put_line('D = ' || d);

dbms_output.put_line('S = ' || s);

dbms_output.put_line('M = ' || m);

dbms_output.put_line('O = ' || o);

dbms_output.put_line('R = ' || r);

dbms_output.put_line('N = ' || n);

dbms_output.put_line('----------------------------');

dbms_output.put_line('ROMAN = ' ||r||o||m||a||n);

end if;

end loop;

end loop;

end loop;

end loop;

end loop;

end loop;

end loop;

end;

/

A = 9

D = 5

S = 0

M = 1

O = 4

R = 3

N = 7

----------------------------

ROMAN = 34197

PL/SQL procedure successfully completed.

Elapsed: 00:00:12.66

Whilst I know the code isn't brilliant and pl/sql is not the best language to write this sort of thing in, it was written quickly and it ran quick enough for me on my 900mhz desktop. It served its purpose.

Then I thought 'I wonder how fast it would run on one of our 8*2.5Ghz processor servers.'

I was surprised to find that the exact same code consistently took over twice as long.

I tried another server, and that was roughly the same.

Tom can you tell me what contributing factors would make this sort of query take so much

longer.

fyi

DESKTOP = XP 1 x 900mhz 10G blocksize 8k

SERVERS = W2000 8 x 2.5ghz 9.2.0.5 blocksize 16k

I was hoping that you could shed some light on a slight anomoly that I have noticed.

I was looking at the following brain teaser in a newspaper recently, and I thought that I'd write a little program to work it out for me (I know it's cheating)

----------

Each letter in this sum stands for a diferent digit. Given that DAD is odd, what number is represented by ROMAN?

MOMMA

MOMMA

DADS

A

-----

ROMAN

----------

being more familiar with pl/sql than any other programming language, I wrote the following code.

declare

begin

for a in 0..9 loop

for d in 0..9 loop

for s in 0..9 loop

for m in 0..9 loop

for o in 0..9 loop

for r in 0..9 loop

for n in 0..9 loop

if a=d or

a=s or

a=m or

a=o or

a=r or

a=n or

d=s or

d=m or

d=o or

d=r or

d=n or

s=m or

s=o or

s=r or

s=n or

m=o or

m=r or

m=n or

o=r or

o=n or

r=n or

mod(d,2)=0 then null;

elsif

((m * 10000 +

o * 1000 +

m * 100 +

m * 10 +

a) * 2) +

(d * 1000 +

a * 100 +

d * 10 +

s) +

a =

(r * 10000 +

o * 1000 +

m * 100 +

a * 10 +

n) then

dbms_output.put_line('A = ' || a);

dbms_output.put_line('D = ' || d);

dbms_output.put_line('S = ' || s);

dbms_output.put_line('M = ' || m);

dbms_output.put_line('O = ' || o);

dbms_output.put_line('R = ' || r);

dbms_output.put_line('N = ' || n);

dbms_output.put_line('----------------------------');

dbms_output.put_line('ROMAN = ' ||r||o||m||a||n);

end if;

end loop;

end loop;

end loop;

end loop;

end loop;

end loop;

end loop;

end;

/

A = 9

D = 5

S = 0

M = 1

O = 4

R = 3

N = 7

----------------------------

ROMAN = 34197

PL/SQL procedure successfully completed.

Elapsed: 00:00:12.66

Whilst I know the code isn't brilliant and pl/sql is not the best language to write this sort of thing in, it was written quickly and it ran quick enough for me on my 900mhz desktop. It served its purpose.

Then I thought 'I wonder how fast it would run on one of our 8*2.5Ghz processor servers.'

I was surprised to find that the exact same code consistently took over twice as long.

I tried another server, and that was roughly the same.

Tom can you tell me what contributing factors would make this sort of query take so much

longer.

fyi

DESKTOP = XP 1 x 900mhz 10G blocksize 8k

SERVERS = W2000 8 x 2.5ghz 9.2.0.5 blocksize 16k

## and Tom said...

You are finding out that 10g is just that much faster at running pl/sql!!!

First, the 8x doesn't count as this is a totally serial thing here. In 10g they redid much of the plsql compiler to make it an optimizing compiler for the first time. The speed at which heavy duty procedural code runs is just that much faster.

Here is 9ir2:

ops$tkyte@ORA9IR2> set timing on

ops$tkyte@ORA9IR2> /

A = 9

D = 5

S = 0

M = 1

O = 4

R = 3

N = 7

----------------------------

ROMAN = 34197

PL/SQL procedure successfully completed.

Elapsed: 00:00:11.13

ops$tkyte@ORA9IR2> /

A = 9

D = 5

S = 0

M = 1

O = 4

R = 3

N = 7

----------------------------

ROMAN = 34197

PL/SQL procedure successfully completed.

Elapsed: 00:00:11.07

ops$tkyte@ORA9IR2>

and in 10g, same MACHINE (didn't change a thing, just switched instances)

ops$tkyte@ORA10G> set timing on

ops$tkyte@ORA10G> /

A = 9

D = 5

S = 0

M = 1

O = 4

R = 3

N = 7

----------------------------

ROMAN = 34197

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.68

ops$tkyte@ORA10G> /

A = 9

D = 5

S = 0

M = 1

O = 4

R = 3

N = 7

----------------------------

ROMAN = 34197

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.68

ops$tkyte@ORA10G>

**sweet isn't it :)**

## Rating

Is this answer out of date? If it is, please let us know via a Comment

# Comments

Thanks Tom

I knew that 10g had been tweaked to give better pl/sql performance, but I didn't realise that the difference would be so much.

Unfortunately our production systems would not see the benefit too much, as most of our performance issues are with I/O (full table scans of 25gb tables combined with poor SAN performance.)

Has 10g got any tweaks in this area (I/O). If so, are there any benchmarks that we could run to give more of a justification to go to 10g, or would it just be a simple case of seeing how long a full table scan on each takes.

I knew that 10g had been tweaked to give better pl/sql performance, but I didn't realise that the difference would be so much.

Unfortunately our production systems would not see the benefit too much, as most of our performance issues are with I/O (full table scans of 25gb tables combined with poor SAN performance.)

Has 10g got any tweaks in this area (I/O). If so, are there any benchmarks that we could run to give more of a justification to go to 10g, or would it just be a simple case of seeing how long a full table scan on each takes.

every release introduces incremental improvments -- you'll find 10g to be overall a better 9i -- you'd have to benchmark your application to see what you see on your hardware (there were lots of DW stuff added in).

We cannot make a bad SAN too much better tho (well, maybe we could -- you could give ASM a spin)

We cannot make a bad SAN too much better tho (well, maybe we could -- you could give ASM a spin)

And do you happen to know what kind of compile-time optimizations have been implemented in 10g ?

Eg pl/sql code rewriting (loop unrolling and similar sophisticated techniques), or it's a matter of small scale optimizations (optimization at the "bytecode" level) ?

BTW What's the correct Oracle name for the pl/sql "bytecode" ;) ?

tnx

Alberto

Eg pl/sql code rewriting (loop unrolling and similar sophisticated techniques), or it's a matter of small scale optimizations (optimization at the "bytecode" level) ?

BTW What's the correct Oracle name for the pl/sql "bytecode" ;) ?

tnx

Alberto

it is a true optimizing compiler (with levels of optimization and everything). It was "large", not small changes. dead code removal, redundant assignment optimization, move code out of loops -- all kinds of stuff.

plsql bytecode is called pcode technically.

plsql bytecode is called pcode technically.

Great! There's a friend of mine whose company builds complex mathematical prediction sw for the Finance (Stock Exchange mainly) market - their most data-intensive number-crunching code should now find its way into the database in 10g (i will tell him).

Thanks

Alberto

Thanks

Alberto

ohhh -- if they can use 4/8 byte IEEE floats -- they will be very impressed. floats and doubles are generally good for scientific applications -- but not good for counting dollars and cents, if their software can live with the reduced precision of a float/double -- they might find it to be really different in 10g. ops$tkyte@ORA9IR2> create or replace function pi return number 2 as<b> 3 subtype my_number is number;</b> 4 last_pi my_number := 0; 5 delta my_number := 0.000001; 6 pi my_number := 1; 7 denom my_number := 3; 8 oper my_number := -1; 9 negone my_number := -1; 10 two my_number := 2; 11 begin 12 loop 13 last_pi := pi; 14 pi := pi + oper * 1/denom; 15 exit when (abs(last_pi-pi) <= delta ); 16 denom := denom + two; 17 oper := oper * negone; 18 end loop; 19 return pi * 4; 20 end; 21 / Function created. ops$tkyte@ORA9IR2> ops$tkyte@ORA9IR2> set timing on ops$tkyte@ORA9IR2> exec dbms_output.put_line( pi ); 3.14159465358579324446263938327350288021 PL/SQL procedure successfully completed. Elapsed: 00:00:01.20 ops$tkyte@ORA9IR2> exec dbms_output.put_line( pi ); 3.14159465358579324446263938327350288021 PL/SQL procedure successfully completed. Elapsed: 00:00:01.15 ops$tkyte@ORA9IR2> set timing off <b>same thing in 10g:</b> ops$tkyte@ORA10G> create or replace function pi return number 2 as 3 subtype my_number is number; 4 last_pi my_number := 0; 5 delta my_number := 0.000001; 6 pi my_number := 1; 7 denom my_number := 3; 8 oper my_number := -1; 9 negone my_number := -1; 10 two my_number := 2; 11 begin 12 loop 13 last_pi := pi; 14 pi := pi + oper * 1/denom; 15 exit when (abs(last_pi-pi) <= delta ); 16 denom := denom + two; 17 oper := oper * negone; 18 end loop; 19 return pi * 4; 20 end; 21 / Function created. ops$tkyte@ORA10G> ops$tkyte@ORA10G> set timing on ops$tkyte@ORA10G> exec dbms_output.put_line( pi ); 3.14159465358579324446263938327350288021 PL/SQL procedure successfully completed. Elapsed: 00:00:00.96 ops$tkyte@ORA10G> exec dbms_output.put_line( pi ); 3.14159465358579324446263938327350288021 PL/SQL procedure successfully completed. Elapsed: 00:00:00.96 ops$tkyte@ORA10G> set timing off <b> so, a bit faster, but....</b> ops$tkyte@ORA10G> create or replace function pi return number 2 as<b> 3 subtype my_number is BINARY_DOUBLE;</b> 4 last_pi my_number := 0; 5 delta my_number := 0.000001; 6 pi my_number := 1; 7 denom my_number := 3; 8 oper my_number := -1; 9 negone my_number := -1; 10 two my_number := 2; 11 begin 12 loop 13 last_pi := pi; 14 pi := pi + oper * 1/denom; 15 exit when (abs(last_pi-pi) <= delta ); 16 denom := denom + two; 17 oper := oper * negone; 18 end loop; 19 return pi * 4; 20 end; 21 / Function created. ops$tkyte@ORA10G> ops$tkyte@ORA10G> set timing on ops$tkyte@ORA10G> exec dbms_output.put_line( pi ); 3.1415946535856922 PL/SQL procedure successfully completed. Elapsed: 00:00:00.24 ops$tkyte@ORA10G> exec dbms_output.put_line( pi ); 3.1415946535856922 PL/SQL procedure successfully completed. Elapsed: 00:00:00.24 ops$tkyte@ORA10G> exec dbms_output.put_line( pi ); 3.1415946535856922 PL/SQL procedure successfully completed. Elapsed: 00:00:00.23 ops$tkyte@ORA10G> set timing off

>floats and doubles are generally good for scientific applications -- but not

>good for counting dollars and cents,if their software can live with the reduced

>precision of a float/double--they might find it to be really different in 10g.

Great-squared ;) - sure they can - now their code is C++, so they are already using IEEE 754 for the most part. That, and (especially) moving calculations in cool analytics sql ... should make their apps fast and scalable beyond any customer's expectation.

thanks again!

Alberto

>good for counting dollars and cents,if their software can live with the reduced

>precision of a float/double--they might find it to be really different in 10g.

Great-squared ;) - sure they can - now their code is C++, so they are already using IEEE 754 for the most part. That, and (especially) moving calculations in cool analytics sql ... should make their apps fast and scalable beyond any customer's expectation.

thanks again!

Alberto

you should see what this can do for some data mining type queries that make use of the math functions heavily.... oh wait, i just happen for some reason, to have an example: ops$tkyte@ORA10G> create table t 2 as 3 select owner, 4 object_id id1, cast( object_id as binary_double ) id2 5 from all_objects 6 / Table created. select (sum(ln(id1))) from t group by owner call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 1 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 3 2.77 2.73 0 165 0 23 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 5 2.77 2.73 0 166 0 23 select (sum(ln(id2))) from t group by owner call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 1 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 3 0.07 0.08 0 165 0 23 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 5 0.07 0.08 0 166 0 23

Great-cubed :) yes, i don't know their details but most prediction algorithms use some kind of frequence-filtering in one stage or another, which means weighted-sums in the time domain or FFT->windowing in the frequence domain->inverse FFT (eg ARMA-like).

And "get the maximizer" operations (i'm thinking about the Maximum Likelihood algo as i recall it from college, the Father of all stochastic predictors).

Oracle, of course, has some nice product/package for this, hasn't it ?

Your answer qualifies for the great-squared-squared followup, which i will post tomorrow since it's 1AM now and i'm literally falling apart ...

tnx!

Alberto

And "get the maximizer" operations (i'm thinking about the Maximum Likelihood algo as i recall it from college, the Father of all stochastic predictors).

Oracle, of course, has some nice product/package for this, hasn't it ?

Your answer qualifies for the great-squared-squared followup, which i will post tomorrow since it's 1AM now and i'm literally falling apart ...

tnx!

Alberto

the one thing I enjoyed when traveling in europe, more than anything -- was the fact that when I answered my email in the morning (europe morning) -- it would actually be "empty" (my inbox) when I was done - since it was about 11pm-midnight in the US (east coast).....

that never happens in real life back home...

timezones -- blessings and curse....

that never happens in real life back home...

timezones -- blessings and curse....

I can envision a new TV commercial for US tourists:

"Come to Europe -

the country where Empty Mailbox is not an oxymoron"

"Come to Europe -

the country where Empty Mailbox is not an oxymoron"

Do these PL/SQL optimizations have any effect on native compilation of procedures, or has this just closed the gap between PL/SQL and native execution times?

just closing the gap more or less -- (natively compiled code was in C, most all C compilers are optimizing compilers already -- much of what was done probably(did NOT test) was already happening in the C compiler.

Anyone managed to solve that brainteaser w/o programmatic brute force? ... took me a whole day and 6+ pages of paper ... there must be something I've missed ... anyone care to post a short/elegant solution? ... just in case you're bored with Oracle :)

if posable could u send me a list on oracle database specs

to my email cpau_is@hotmail.com

to my email cpau_is@hotmail.com

better yet, just goto otn.oracle.com and read the documentation. It is all there just waiting for you to come and read it.

This runs 4 times faster than original on my 9.2.0.4 server:

declare

begin

for d in 1..9 LOOP

IF mod(d,2)=1

THEN

for a in 0..9 LOOP

IF a <> d

THEN

for s in 0..9 LOOP

IF s NOT IN (d,a)

THEN

for m in 0..9 LOOP

IF m NOT IN (s,d,a)

THEN

for o in 0..9 LOOP

IF o NOT IN (m,s,d,a)

THEN

for r in 0..9 LOOP

IF r NOT IN (o,m,s,d,a)

THEN

for n in 0..9 LOOP

IF n NOT IN (r,o,m,s,d,a)

AND

((m * 10000 +

o * 1000 +

m * 100 +

m * 10 +

a) * 2) +

(d * 1000 +

a * 100 +

d * 10 +

s) +

a =

(r * 10000 +

o * 1000 +

m * 100 +

a * 10 +

n) then

dbms_output.put_line('A = ' || a);

dbms_output.put_line('D = ' || d);

dbms_output.put_line('S = ' || s);

dbms_output.put_line('M = ' || m);

dbms_output.put_line('O = ' || o);

dbms_output.put_line('R = ' || r);

dbms_output.put_line('N = ' || n);

dbms_output.put_line('----------------------------');

dbms_output.put_line('ROMAN = ' ||r||o||m||a||n);

end if;

end loop; -- n

END IF;

end loop; -- r

END IF;

end loop; -- o

END IF;

end loop; -- m

END IF;

end loop; -- s

END IF;

end loop; -- a

END IF;

end loop;--d

end;

declare

begin

for d in 1..9 LOOP

IF mod(d,2)=1

THEN

for a in 0..9 LOOP

IF a <> d

THEN

for s in 0..9 LOOP

IF s NOT IN (d,a)

THEN

for m in 0..9 LOOP

IF m NOT IN (s,d,a)

THEN

for o in 0..9 LOOP

IF o NOT IN (m,s,d,a)

THEN

for r in 0..9 LOOP

IF r NOT IN (o,m,s,d,a)

THEN

for n in 0..9 LOOP

IF n NOT IN (r,o,m,s,d,a)

AND

((m * 10000 +

o * 1000 +

m * 100 +

m * 10 +

a) * 2) +

(d * 1000 +

a * 100 +

d * 10 +

s) +

a =

(r * 10000 +

o * 1000 +

m * 100 +

a * 10 +

n) then

dbms_output.put_line('A = ' || a);

dbms_output.put_line('D = ' || d);

dbms_output.put_line('S = ' || s);

dbms_output.put_line('M = ' || m);

dbms_output.put_line('O = ' || o);

dbms_output.put_line('R = ' || r);

dbms_output.put_line('N = ' || n);

dbms_output.put_line('----------------------------');

dbms_output.put_line('ROMAN = ' ||r||o||m||a||n);

end if;

end loop; -- n

END IF;

end loop; -- r

END IF;

end loop; -- o

END IF;

end loop; -- m

END IF;

end loop; -- s

END IF;

end loop; -- a

END IF;

end loop;--d

end;

for me, that ran

9ir2 old code 11

9ir2 new code 2.6

10g old code 4.3

10g new code 0.46

9ir2 old code 11

9ir2 new code 2.6

10g old code 4.3

10g new code 0.46

begin

for d in 1..9 LOOP

-- IF mod(d,2)=1

IF d in (1,3,5,7,9)

THEN

for a in 0..9 LOOP

IF a <> d

THEN

for s in 0..9 LOOP

IF s NOT IN (d,a)

THEN

-- for m in 0..9 LOOP

-- IF m NOT IN (s,d,a)

for n in 0..9 LOOP

IF ( n NOT IN (s,d,a) and ( substr ( ( 3*a + s ), -1, 1 ) = n ) )

THEN

for o in 0..9 LOOP

IF o NOT IN (n,s,d,a)

THEN

for r in 0..9 LOOP

IF r NOT IN (o,n,s,d,a)

THEN

-- for n in 0..9 LOOP

-- IF n NOT IN (r,o,n,s,d,a)

for m in 0..9 LOOP

IF m NOT IN (r,o,n,s,d,a)

AND

((m * 10000 +

o * 1000 +

m * 100 +

m * 10 +

a) * 2) +

(d * 1000 +

a * 100 +

d * 10 +

s) +

a =

(r * 10000 +

o * 1000 +

m * 100 +

a * 10 +

n) then

dbms_output.put_line('A = ' || a);

dbms_output.put_line('D = ' || d);

dbms_output.put_line('S = ' || s);

dbms_output.put_line('M = ' || m);

dbms_output.put_line('O = ' || o);

dbms_output.put_line('R = ' || r);

dbms_output.put_line('N = ' || n);

dbms_output.put_line('----------------------------');

dbms_output.put_line('ROMAN = ' ||r||o||m||a||n);

end if;

end loop; -- m

END IF;

end loop; -- r

END IF;

end loop; -- o

END IF;

end loop; -- n

END IF;

end loop; -- s

END IF;

end loop; -- a

END IF;

end loop;--d

end;

/

for d in 1..9 LOOP

-- IF mod(d,2)=1

IF d in (1,3,5,7,9)

THEN

for a in 0..9 LOOP

IF a <> d

THEN

for s in 0..9 LOOP

IF s NOT IN (d,a)

THEN

-- for m in 0..9 LOOP

-- IF m NOT IN (s,d,a)

for n in 0..9 LOOP

IF ( n NOT IN (s,d,a) and ( substr ( ( 3*a + s ), -1, 1 ) = n ) )

THEN

for o in 0..9 LOOP

IF o NOT IN (n,s,d,a)

THEN

for r in 0..9 LOOP

IF r NOT IN (o,n,s,d,a)

THEN

-- for n in 0..9 LOOP

-- IF n NOT IN (r,o,n,s,d,a)

for m in 0..9 LOOP

IF m NOT IN (r,o,n,s,d,a)

AND

((m * 10000 +

o * 1000 +

m * 100 +

m * 10 +

a) * 2) +

(d * 1000 +

a * 100 +

d * 10 +

s) +

a =

(r * 10000 +

o * 1000 +

m * 100 +

a * 10 +

n) then

dbms_output.put_line('A = ' || a);

dbms_output.put_line('D = ' || d);

dbms_output.put_line('S = ' || s);

dbms_output.put_line('M = ' || m);

dbms_output.put_line('O = ' || o);

dbms_output.put_line('R = ' || r);

dbms_output.put_line('N = ' || n);

dbms_output.put_line('----------------------------');

dbms_output.put_line('ROMAN = ' ||r||o||m||a||n);

end if;

end loop; -- m

END IF;

end loop; -- r

END IF;

end loop; -- o

END IF;

end loop; -- n

END IF;

end loop; -- s

END IF;

end loop; -- a

END IF;

end loop;--d

end;

/

It feels like cheating on the brute force code ... but it is faster nonetheless (9.2.0.5.0) ...

flip@flop> declare

2 a binary_integer;

3 d binary_integer;

4 m binary_integer;

5 n binary_integer;

6 o binary_integer;

7 r binary_integer;

8 s binary_integer;

9 x binary_integer;

10 y binary_integer;

11 begin

12 for m in 0..4 loop

13 r := 2*m+1;

14 for a in 5..9 loop

15 if a+m = 9 or a+m=10 then

16 for o in 2..8 loop

17 if mod(o,2) = 0 and o not in (m, a) then

18 d := 9-o;

19 if d not in (r,m,a) then

20 for x in 1..3 loop

21 for y in 0..1 loop

22 if 2*m+d+x = a+10*y then

23 for n in 0..9 loop

24 if n not in (r,m,a,o,d) then

25 s := n+10*x-3*a;

26 if s >= 0 and s not in (r,m,a,o,d,n) then

27 if ((m * 10000 +

28 o * 1000 +

29 m * 100 +

30 m * 10 +

31 a) * 2) +

32 (d * 1000 +

33 a * 100 +

34 d * 10 +

35 s) +

36 a =

37 (r * 10000 +

38 o * 1000 +

39 m * 100 +

40 a * 10 +

41 n) then

42 dbms_output.put_line('A = ' || a);

43 dbms_output.put_line('D = ' || d);

44 dbms_output.put_line('S = ' || s);

45 dbms_output.put_line('M = ' || m);

46 dbms_output.put_line('O = ' || o);

47 dbms_output.put_line('R = ' || r);

48 dbms_output.put_line('N = ' || n);

49 dbms_output.put_line('----------------------------');

50 dbms_output.put_line('ROMAN = ' ||r||o||m||a||n);

51 end if;

52 end if;

53 end if;

54 end loop;

55 end if;

56 end loop;

57 end loop;

58 end if;

59 end if;

60 end loop;

61 end if;

62 end loop;

63 end loop;

64 end;

65 /

A = 9

D = 5

S = 0

M = 1

O = 4

R = 3

N = 7

----------------------------

ROMAN = 34197

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00

Looks I don't have to upgrade to 10g just yet! :)

flip@flop> declare

2 a binary_integer;

3 d binary_integer;

4 m binary_integer;

5 n binary_integer;

6 o binary_integer;

7 r binary_integer;

8 s binary_integer;

9 x binary_integer;

10 y binary_integer;

11 begin

12 for m in 0..4 loop

13 r := 2*m+1;

14 for a in 5..9 loop

15 if a+m = 9 or a+m=10 then

16 for o in 2..8 loop

17 if mod(o,2) = 0 and o not in (m, a) then

18 d := 9-o;

19 if d not in (r,m,a) then

20 for x in 1..3 loop

21 for y in 0..1 loop

22 if 2*m+d+x = a+10*y then

23 for n in 0..9 loop

24 if n not in (r,m,a,o,d) then

25 s := n+10*x-3*a;

26 if s >= 0 and s not in (r,m,a,o,d,n) then

27 if ((m * 10000 +

28 o * 1000 +

29 m * 100 +

30 m * 10 +

31 a) * 2) +

32 (d * 1000 +

33 a * 100 +

34 d * 10 +

35 s) +

36 a =

37 (r * 10000 +

38 o * 1000 +

39 m * 100 +

40 a * 10 +

41 n) then

42 dbms_output.put_line('A = ' || a);

43 dbms_output.put_line('D = ' || d);

44 dbms_output.put_line('S = ' || s);

45 dbms_output.put_line('M = ' || m);

46 dbms_output.put_line('O = ' || o);

47 dbms_output.put_line('R = ' || r);

48 dbms_output.put_line('N = ' || n);

49 dbms_output.put_line('----------------------------');

50 dbms_output.put_line('ROMAN = ' ||r||o||m||a||n);

51 end if;

52 end if;

53 end if;

54 end loop;

55 end if;

56 end loop;

57 end loop;

58 end if;

59 end if;

60 end loop;

61 end if;

62 end loop;

63 end loop;

64 end;

65 /

A = 9

D = 5

S = 0

M = 1

O = 4

R = 3

N = 7

----------------------------

ROMAN = 34197

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00

Looks I don't have to upgrade to 10g just yet! :)

Tell you what, let's look at the answer first, then we'll write code that we know isn't going to look at half of the possible values

Your solution, though very quick, is basically useless since it works on quite a few assumptions, which are simply derived from looking at the answer. Care to explain how you can be certain, for example, that 'm' can only between 0 and 4, or 'a' can only be between 5 and 9, WITHOUT looking at the answer first? Thanks!

To Hector and Kashif ... From the _cheat_ ...

Let us assume x,y,z,w in {0,1,2,3,4,5,6,7,8,9} such that

wzyx

----

MOMMA

MOMMA

DADS

A

=====

ROMAN

then

C01: 3*A + S = N + 10*x

C02: 2*M + D + x = A + 10*y

C03: 2*M + A + y = M + 10*z

C04: 2*O + D + z = O + 10*w

C05: 2*M + w = R

Now if one works a bit on corollary C05

2*M + w = R ==> 2*M = R w <= 9 w (since R <= 9) ==>

2*M <= 9 (since w >= 0) ==> M <= 9/2 (and since M is a positive integer) ==> M <= 4 hence M in {0,1,2,3,4)

Another conclusion from C05 is:

2*M + w = R since M, w and R are all positive ==> the only way for R to be 0 is for w=0 and 2*M=0 ==> M=0 ==> and this is impossible since M != R ==> R cannot be 0 ==> R in {1,2,3,4,5,6,7,8,9)

As I said, it took 6+ pages to do this on paper all of those conditions I used in my algorithm are logically derived from those 5 corollaries above plus the following derived from the fact that DAD is odd: D in {1,3,5,7,9}

It is just basic (but tedious) math I was hoping someone would post a simple, elegant solution it came from a newspaper after all and not the Journal of Mathematics.

Let us assume x,y,z,w in {0,1,2,3,4,5,6,7,8,9} such that

wzyx

----

MOMMA

MOMMA

DADS

A

=====

ROMAN

then

C01: 3*A + S = N + 10*x

C02: 2*M + D + x = A + 10*y

C03: 2*M + A + y = M + 10*z

C04: 2*O + D + z = O + 10*w

C05: 2*M + w = R

Now if one works a bit on corollary C05

2*M + w = R ==> 2*M = R w <= 9 w (since R <= 9) ==>

2*M <= 9 (since w >= 0) ==> M <= 9/2 (and since M is a positive integer) ==> M <= 4 hence M in {0,1,2,3,4)

Another conclusion from C05 is:

2*M + w = R since M, w and R are all positive ==> the only way for R to be 0 is for w=0 and 2*M=0 ==> M=0 ==> and this is impossible since M != R ==> R cannot be 0 ==> R in {1,2,3,4,5,6,7,8,9)

As I said, it took 6+ pages to do this on paper all of those conditions I used in my algorithm are logically derived from those 5 corollaries above plus the following derived from the fact that DAD is odd: D in {1,3,5,7,9}

It is just basic (but tedious) math I was hoping someone would post a simple, elegant solution it came from a newspaper after all and not the Journal of Mathematics.

After studying your working out for a short period of time, I have come to the following conclusion.

You need to get out more.

You need to get out more.

LOL....

Gabe -- they are trying to tell you something....

Gabe -- they are trying to tell you something....

> it came from a newspaper after all and not the Journal of

> Mathematics.

That probably means that the expected solution was brute-force on paper, which lazy people (such as me) immediately delegate to computers ;)

> Mathematics.

That probably means that the expected solution was brute-force on paper, which lazy people (such as me) immediately delegate to computers ;)

To Alberto ...

<quote>That probably means that the expected solution was brute-force on paper</quote>

Doesnt quite compute ... to me it means exactly the opposite ... that one could do it while riding the subway (no pen or paper necessarily available) ... this is what intrigued me because I couldn't do it w/o munching the numbers on paper. Anyway, it was just for fun (some may get annoyed with all this non-Oracle chat) though I'm still hoping someone would post that brilliant/elegant solution. Oooh and Im as lazy as the next chap it is just that my stubbornness sometimes wins over laziness :)

<quote>That probably means that the expected solution was brute-force on paper</quote>

Doesnt quite compute ... to me it means exactly the opposite ... that one could do it while riding the subway (no pen or paper necessarily available) ... this is what intrigued me because I couldn't do it w/o munching the numbers on paper. Anyway, it was just for fun (some may get annoyed with all this non-Oracle chat) though I'm still hoping someone would post that brilliant/elegant solution. Oooh and Im as lazy as the next chap it is just that my stubbornness sometimes wins over laziness :)

<quote>they are trying to tell you something</quote.

Yes, I'll shut up on this one ... almost knew I'll open a can of worms with that code (kind of no-win situation) ... couldn't resist though ... but yes, no Oracle relevance in my comments.

Yes, I'll shut up on this one ... almost knew I'll open a can of worms with that code (kind of no-win situation) ... couldn't resist though ... but yes, no Oracle relevance in my comments.

quote/ derived from the fact that DAD is odd: D in {1,3,5,7,9} /quote

But isn't DADS odd?

But isn't DADS odd?

To Gabe ...

Well i was intrigued as well, in fact i have tryed a couple of math attacks to the problem but failed (so far) - if i succeed i will post a solution just for fun.

I don't think that this kind of problems are a waste of time; at the opposite, they let you explore new ways of thinking, or new Oracle features, that very often can be used later in "real-life" problems (besides being fun of course). So i appreciate discussions like this, and if someone doesn't, the "back" button is there to serve him/her well :)

In my "review" i was just joking about the fact that the author of the puzzle wanted to have you sit down for a couple of hours brute-forcing - and then you said "no-way, i will have my pc slave compute this" - so solving the problem the way it was intended to be solved, just in a better way (but that was just a joke, nothing serious).

"Laziness" was intended in its positive meaning (getting the apple with the least amount of effort) ...

Well i was intrigued as well, in fact i have tryed a couple of math attacks to the problem but failed (so far) - if i succeed i will post a solution just for fun.

I don't think that this kind of problems are a waste of time; at the opposite, they let you explore new ways of thinking, or new Oracle features, that very often can be used later in "real-life" problems (besides being fun of course). So i appreciate discussions like this, and if someone doesn't, the "back" button is there to serve him/her well :)

In my "review" i was just joking about the fact that the author of the puzzle wanted to have you sit down for a couple of hours brute-forcing - and then you said "no-way, i will have my pc slave compute this" - so solving the problem the way it was intended to be solved, just in a better way (but that was just a joke, nothing serious).

"Laziness" was intended in its positive meaning (getting the apple with the least amount of effort) ...

(just for fun)

create table x(x) as

select rownum-1 from all_objects where rownum <= 10;

with d as (

select x d from x

where mod(x,2) = 1

), a as (

select x a, d.* from d, x

where x not in (d)

), s as (

select x s, a.* from a, x

where x not in (d,a)

), m as (

select x m, s.* from s, x

where x not in (d,a,s)

), o as (

select x o, m.* from m, x

where x not in (d,a,s,m)

), r as (

select x r, o.* from o, x

where x not in (d,a,s,m,o)

), n as (

select x n, r.* from r, x

where x not in (d,a,s,m,o,r)

)

select A, D, S, M, O, R, N

from n

where 2 * (10000*m + 1000*o + 100*m + 10*m + a)

+ 1000*d + 100*a + 10*d + s

+ a

= 10000*r + 1000*o + 100*m + 10*a + n

A D S M O R N

---------- ---------- ---------- ---------- ---------- ---------- ----------

9 5 0 1 4 3 7

on my 9.2.0.5 machine run in 05.08 secs,

the OP pl/sql solution run in 11.06 secs.

In 10g, using IEEE 754 datatypes for the "x" table, should be even faster.

create table x(x) as

select rownum-1 from all_objects where rownum <= 10;

with d as (

select x d from x

where mod(x,2) = 1

), a as (

select x a, d.* from d, x

where x not in (d)

), s as (

select x s, a.* from a, x

where x not in (d,a)

), m as (

select x m, s.* from s, x

where x not in (d,a,s)

), o as (

select x o, m.* from m, x

where x not in (d,a,s,m)

), r as (

select x r, o.* from o, x

where x not in (d,a,s,m,o)

), n as (

select x n, r.* from r, x

where x not in (d,a,s,m,o,r)

)

select A, D, S, M, O, R, N

from n

where 2 * (10000*m + 1000*o + 100*m + 10*m + a)

+ 1000*d + 100*a + 10*d + s

+ a

= 10000*r + 1000*o + 100*m + 10*a + n

A D S M O R N

---------- ---------- ---------- ---------- ---------- ---------- ----------

9 5 0 1 4 3 7

on my 9.2.0.5 machine run in 05.08 secs,

the OP pl/sql solution run in 11.06 secs.

In 10g, using IEEE 754 datatypes for the "x" table, should be even faster.

Have been thinking about that "elegant" solution, but without much success.. However, the following code seems to perform fairly well :)

select r.rn R, o.rn O, m.rn M, a.rn A, n.rn N, s.rn S, d.rn D from

(select mod(rownum,10) rn from (select 1 from dual group by cube(1,1,1,1)) where rownum <= 10) m,

(select mod(rownum,10) rn from (select 1 from dual group by cube(1,1,1,1)) where rownum <= 10) o,

(select mod(rownum,10) rn from (select 1 from dual group by cube(1,1,1,1)) where rownum <= 10) d,

(select mod(rownum,10) rn from (select 1 from dual group by cube(1,1,1,1)) where rownum <= 10) a,

(select mod(rownum,10) rn from (select 1 from dual group by cube(1,1,1,1)) where rownum <= 10) s,

(select mod(rownum,10) rn from (select 1 from dual group by cube(1,1,1,1)) where rownum <= 10) r,

(select mod(rownum,10) rn from (select 1 from dual group by cube(1,1,1,1)) where rownum <= 10) n

where d.rn in (1,3,5,7,9)

and m.rn not in ( o.rn,d.rn,a.rn,s.rn,r.rn,n.rn)

and o.rn not in (m.rn, d.rn,a.rn,s.rn,r.rn,n.rn)

and d.rn not in (m.rn,o.rn, a.rn,s.rn,r.rn,n.rn)

and a.rn not in (m.rn,o.rn,d.rn, s.rn,r.rn,n.rn)

and s.rn not in (m.rn,o.rn,d.rn,a.rn, r.rn,n.rn)

and r.rn not in (m.rn,o.rn,d.rn,a.rn,s.rn, n.rn)

and n.rn not in (m.rn,o.rn,d.rn,a.rn,s.rn,r.rn )

and mod(3*a.rn+s.rn-n.rn,10) = 0

and mod(2*m.rn+d.rn-a.rn+trunc((3*a.rn+s.rn-n.rn)/10),10) = 0

and mod(m.rn+a.rn+trunc((2*m.rn+d.rn-a.rn+trunc((3*a.rn+s.rn-n.rn)/10))/10),10) = 0

--and mod(o.rn+d.rn+trunc((m.rn+a.rn+trunc((2*m.rn+d.rn-a.rn+trunc((3*a.rn+s.rn-n.rn)/10))/10))/10),10) = 0

--and 2*m.rn-r.rn+trunc((o.rn+d.rn+trunc((m.rn+a.rn+trunc((2*m.rn+d.rn-a.rn+trunc((3*a.rn+s.rn-n.rn)/10))/10))/10))/10) = 0

and 20120*m.rn+1000*o.rn+1010*d.rn+93*a.rn+s.rn-10000*r.rn-n.rn = 0

select r.rn R, o.rn O, m.rn M, a.rn A, n.rn N, s.rn S, d.rn D from

(select mod(rownum,10) rn from (select 1 from dual group by cube(1,1,1,1)) where rownum <= 10) m,

(select mod(rownum,10) rn from (select 1 from dual group by cube(1,1,1,1)) where rownum <= 10) o,

(select mod(rownum,10) rn from (select 1 from dual group by cube(1,1,1,1)) where rownum <= 10) d,

(select mod(rownum,10) rn from (select 1 from dual group by cube(1,1,1,1)) where rownum <= 10) a,

(select mod(rownum,10) rn from (select 1 from dual group by cube(1,1,1,1)) where rownum <= 10) s,

(select mod(rownum,10) rn from (select 1 from dual group by cube(1,1,1,1)) where rownum <= 10) r,

(select mod(rownum,10) rn from (select 1 from dual group by cube(1,1,1,1)) where rownum <= 10) n

where d.rn in (1,3,5,7,9)

and m.rn not in ( o.rn,d.rn,a.rn,s.rn,r.rn,n.rn)

and o.rn not in (m.rn, d.rn,a.rn,s.rn,r.rn,n.rn)

and d.rn not in (m.rn,o.rn, a.rn,s.rn,r.rn,n.rn)

and a.rn not in (m.rn,o.rn,d.rn, s.rn,r.rn,n.rn)

and s.rn not in (m.rn,o.rn,d.rn,a.rn, r.rn,n.rn)

and r.rn not in (m.rn,o.rn,d.rn,a.rn,s.rn, n.rn)

and n.rn not in (m.rn,o.rn,d.rn,a.rn,s.rn,r.rn )

and mod(3*a.rn+s.rn-n.rn,10) = 0

and mod(2*m.rn+d.rn-a.rn+trunc((3*a.rn+s.rn-n.rn)/10),10) = 0

and mod(m.rn+a.rn+trunc((2*m.rn+d.rn-a.rn+trunc((3*a.rn+s.rn-n.rn)/10))/10),10) = 0

--and mod(o.rn+d.rn+trunc((m.rn+a.rn+trunc((2*m.rn+d.rn-a.rn+trunc((3*a.rn+s.rn-n.rn)/10))/10))/10),10) = 0

--and 2*m.rn-r.rn+trunc((o.rn+d.rn+trunc((m.rn+a.rn+trunc((2*m.rn+d.rn-a.rn+trunc((3*a.rn+s.rn-n.rn)/10))/10))/10))/10) = 0

and 20120*m.rn+1000*o.rn+1010*d.rn+93*a.rn+s.rn-10000*r.rn-n.rn = 0

we

are

getting

carried

away :)

i like it...

are

getting

carried

away :)

i like it...

to Venkat ...

on 9.2.0.5:

mine : 00:00:05.00

yours: 00:00:00.06 (including parsing: 00:00:01.03 - 17x :)

Now i have to figure out why :)

on 9.2.0.5:

mine : 00:00:05.00

yours: 00:00:00.06 (including parsing: 00:00:01.03 - 17x :)

Now i have to figure out why :)

Alberto's solution I can undertand, it is simple math via brute-force SQL.

Venkat's solution gave me a headache. I understand the d.rn in (1,3,5,7,9) (odd), and all the "not in" parts. What the heck do all the truncs and mods do?

Thanks

Venkat's solution gave me a headache. I understand the d.rn in (1,3,5,7,9) (odd), and all the "not in" parts. What the heck do all the truncs and mods do?

Thanks

Venkat's approach follows

Axiom I.

what you can do in SQL, do in SQL, don't use PL/SQL whereever possible

Axiom II.

real men don't use temp-tables -:),

the group by cube(1,1,1,1)-trick is all we need

(btw, I would prefer "rownum-1 rn" instead of "mod(rownum,10) rn")

Axiom I.

what you can do in SQL, do in SQL, don't use PL/SQL whereever possible

Axiom II.

real men don't use temp-tables -:),

the group by cube(1,1,1,1)-trick is all we need

(btw, I would prefer "rownum-1 rn" instead of "mod(rownum,10) rn")

Wanna make things even faster ? Use temp tables ...

Venkat's original:

select r.rn R, o.rn O, m.rn M, a.rn A, n.rn N, s.rn S, d.rn D from

(select mod(rownum,10) rn from (select 1 from dual group by cube(1,1,1,1))

where rownum <= 10) m,

(select mod(rownum,10) rn from (select 1 from dual group by cube(1,1,1,1))

where rownum <= 10) o,

(select mod(rownum,10) rn from (select 1 from dual group by cube(1,1,1,1))

where rownum <= 10) d,

(select mod(rownum,10) rn from (select 1 from dual group by cube(1,1,1,1))

where rownum <= 10) a,

(select mod(rownum,10) rn from (select 1 from dual group by cube(1,1,1,1))

where rownum <= 10) s,

(select mod(rownum,10) rn from (select 1 from dual group by cube(1,1,1,1))

where rownum <= 10) r,

(select mod(rownum,10) rn from (select 1 from dual group by cube(1,1,1,1))

where rownum <= 10) n

where d.rn in (1,3,5,7,9)

and m.rn not in ( o.rn,d.rn,a.rn,s.rn,r.rn,n.rn)

and o.rn not in (m.rn, d.rn,a.rn,s.rn,r.rn,n.rn)

and d.rn not in (m.rn,o.rn, a.rn,s.rn,r.rn,n.rn)

and a.rn not in (m.rn,o.rn,d.rn, s.rn,r.rn,n.rn)

and s.rn not in (m.rn,o.rn,d.rn,a.rn, r.rn,n.rn)

and r.rn not in (m.rn,o.rn,d.rn,a.rn,s.rn, n.rn)

and n.rn not in (m.rn,o.rn,d.rn,a.rn,s.rn,r.rn )

and mod(3*a.rn+s.rn-n.rn,10) = 0

and mod(2*m.rn+d.rn-a.rn+trunc((3*a.rn+s.rn-n.rn)/10),10) = 0

and mod(m.rn+a.rn+trunc((2*m.rn+d.rn-a.rn+trunc((3*a.rn+s.rn-n.rn)/10))/10),10)

= 0

and

mod(o.rn+d.rn+trunc((m.rn+a.rn+trunc((2*m.rn+d.rn-a.rn+trunc((3*a.rn+s.rn-n.rn)

/10))/10))/10),10) = 0

and

2*m.rn-r.rn+trunc((o.rn+d.rn+trunc((m.rn+a.rn+trunc((2*m.rn+d.rn-a.rn+

trunc((3*a.rn+s.rn-n.rn)/10))/10))/10))/10) = 0

and 20120*m.rn+1000*o.rn+1010*d.rn+93*a.rn+s.rn-10000*r.rn-n.rn = 0;

Venkat's + temp table:

create table x(rn) as select rownum-1 from all_objects where rownum <= 10;

select r.rn R, o.rn O, m.rn M, a.rn A, n.rn N, s.rn S, d.rn D

from

x m,

x o,

x d,

x a,

x s,

x r,

x n

where d.rn in (1,3,5,7,9)

and m.rn not in ( o.rn,d.rn,a.rn,s.rn,r.rn,n.rn)

and o.rn not in (m.rn, d.rn,a.rn,s.rn,r.rn,n.rn)

and d.rn not in (m.rn,o.rn, a.rn,s.rn,r.rn,n.rn)

and a.rn not in (m.rn,o.rn,d.rn, s.rn,r.rn,n.rn)

and s.rn not in (m.rn,o.rn,d.rn,a.rn, r.rn,n.rn)

and r.rn not in (m.rn,o.rn,d.rn,a.rn,s.rn, n.rn)

and n.rn not in (m.rn,o.rn,d.rn,a.rn,s.rn,r.rn )

and mod(3*a.rn+s.rn-n.rn,10) = 0

and mod(2*m.rn+d.rn-a.rn+trunc((3*a.rn+s.rn-n.rn)/10),10) = 0

and mod(m.rn+a.rn+trunc((2*m.rn+d.rn-a.rn+trunc((3*a.rn+s.rn-n.rn)/10))/10),10)

= 0

and

mod(o.rn+d.rn+trunc((m.rn+a.rn+trunc((2*m.rn+d.rn-a.rn+trunc((3*a.rn+s.rn-n.rn)

/10))/10))/10),10) = 0

and

2*m.rn-r.rn+trunc((o.rn+d.rn+trunc((m.rn+a.rn+trunc((2*m.rn+d.rn-a.rn+

trunc((3*a.rn+s.rn-n.rn)/10))/10))/10))/10) = 0

and 20120*m.rn+1000*o.rn+1010*d.rn+93*a.rn+s.rn-10000*r.rn-n.rn = 0;

Venkat's original: 00:00:00.05

Venkat's + temp : 00:00:00.02

Axiom III:

Real men don't use temp tables, but the others are faster (to the girls :)

Venkat's original:

select r.rn R, o.rn O, m.rn M, a.rn A, n.rn N, s.rn S, d.rn D from

(select mod(rownum,10) rn from (select 1 from dual group by cube(1,1,1,1))

where rownum <= 10) m,

(select mod(rownum,10) rn from (select 1 from dual group by cube(1,1,1,1))

where rownum <= 10) o,

(select mod(rownum,10) rn from (select 1 from dual group by cube(1,1,1,1))

where rownum <= 10) d,

(select mod(rownum,10) rn from (select 1 from dual group by cube(1,1,1,1))

where rownum <= 10) a,

(select mod(rownum,10) rn from (select 1 from dual group by cube(1,1,1,1))

where rownum <= 10) s,

(select mod(rownum,10) rn from (select 1 from dual group by cube(1,1,1,1))

where rownum <= 10) r,

(select mod(rownum,10) rn from (select 1 from dual group by cube(1,1,1,1))

where rownum <= 10) n

where d.rn in (1,3,5,7,9)

and m.rn not in ( o.rn,d.rn,a.rn,s.rn,r.rn,n.rn)

and o.rn not in (m.rn, d.rn,a.rn,s.rn,r.rn,n.rn)

and d.rn not in (m.rn,o.rn, a.rn,s.rn,r.rn,n.rn)

and a.rn not in (m.rn,o.rn,d.rn, s.rn,r.rn,n.rn)

and s.rn not in (m.rn,o.rn,d.rn,a.rn, r.rn,n.rn)

and r.rn not in (m.rn,o.rn,d.rn,a.rn,s.rn, n.rn)

and n.rn not in (m.rn,o.rn,d.rn,a.rn,s.rn,r.rn )

and mod(3*a.rn+s.rn-n.rn,10) = 0

and mod(2*m.rn+d.rn-a.rn+trunc((3*a.rn+s.rn-n.rn)/10),10) = 0

and mod(m.rn+a.rn+trunc((2*m.rn+d.rn-a.rn+trunc((3*a.rn+s.rn-n.rn)/10))/10),10)

= 0

and

mod(o.rn+d.rn+trunc((m.rn+a.rn+trunc((2*m.rn+d.rn-a.rn+trunc((3*a.rn+s.rn-n.rn)

/10))/10))/10),10) = 0

and

2*m.rn-r.rn+trunc((o.rn+d.rn+trunc((m.rn+a.rn+trunc((2*m.rn+d.rn-a.rn+

trunc((3*a.rn+s.rn-n.rn)/10))/10))/10))/10) = 0

and 20120*m.rn+1000*o.rn+1010*d.rn+93*a.rn+s.rn-10000*r.rn-n.rn = 0;

Venkat's + temp table:

create table x(rn) as select rownum-1 from all_objects where rownum <= 10;

select r.rn R, o.rn O, m.rn M, a.rn A, n.rn N, s.rn S, d.rn D

from

x m,

x o,

x d,

x a,

x s,

x r,

x n

where d.rn in (1,3,5,7,9)

and m.rn not in ( o.rn,d.rn,a.rn,s.rn,r.rn,n.rn)

and o.rn not in (m.rn, d.rn,a.rn,s.rn,r.rn,n.rn)

and d.rn not in (m.rn,o.rn, a.rn,s.rn,r.rn,n.rn)

and a.rn not in (m.rn,o.rn,d.rn, s.rn,r.rn,n.rn)

and s.rn not in (m.rn,o.rn,d.rn,a.rn, r.rn,n.rn)

and r.rn not in (m.rn,o.rn,d.rn,a.rn,s.rn, n.rn)

and n.rn not in (m.rn,o.rn,d.rn,a.rn,s.rn,r.rn )

and mod(3*a.rn+s.rn-n.rn,10) = 0

and mod(2*m.rn+d.rn-a.rn+trunc((3*a.rn+s.rn-n.rn)/10),10) = 0

and mod(m.rn+a.rn+trunc((2*m.rn+d.rn-a.rn+trunc((3*a.rn+s.rn-n.rn)/10))/10),10)

= 0

and

mod(o.rn+d.rn+trunc((m.rn+a.rn+trunc((2*m.rn+d.rn-a.rn+trunc((3*a.rn+s.rn-n.rn)

/10))/10))/10),10) = 0

and

2*m.rn-r.rn+trunc((o.rn+d.rn+trunc((m.rn+a.rn+trunc((2*m.rn+d.rn-a.rn+

trunc((3*a.rn+s.rn-n.rn)/10))/10))/10))/10) = 0

and 20120*m.rn+1000*o.rn+1010*d.rn+93*a.rn+s.rn-10000*r.rn-n.rn = 0;

Venkat's original: 00:00:00.05

Venkat's + temp : 00:00:00.02

Axiom III:

Real men don't use temp tables, but the others are faster (to the girls :)

I am sure not only me sometimes wished a pre-built-view/function

(having available upon Oracle-installation, like the dual-table)

returning as many integers as you like

(and tuned as much as possible)

[ like for example

create type ti is table of integer;

/

create function integers(vfrom in integer, vto in integer) return ti pipelined

as

begin

for i in vfrom .. vto loop

pipe row(i);

end loop;

return;

end;

/

select * from table(integers(0,9));

COLUMN_VALUE

------------

0

1

2

3

4

5

6

7

8

9

]

I am sure there exist quite a number of temp tables

in the world which would be superfluous then

Like Leopold Kronecker said:

"God made the natural numbers; all else is the work of man."

(having available upon Oracle-installation, like the dual-table)

returning as many integers as you like

(and tuned as much as possible)

[ like for example

create type ti is table of integer;

/

create function integers(vfrom in integer, vto in integer) return ti pipelined

as

begin

for i in vfrom .. vto loop

pipe row(i);

end loop;

return;

end;

/

select * from table(integers(0,9));

COLUMN_VALUE

------------

0

1

2

3

4

5

6

7

8

9

]

I am sure there exist quite a number of temp tables

in the world which would be superfluous then

Like Leopold Kronecker said:

"God made the natural numbers; all else is the work of man."

After I posted my SQL, I continued working on the problem and came up with

this. My apologies to Tom for such a long post with no relevance to Oracle!

Borrowing heavily from Gabe's work... ;)

Let us assume x,y,z,w in {0,1,2,3,4,5,6,7,8,9} such that

wzyx

----

MOMMA

MOMMA

DADS

A

=====

ROMAN

Rule 0: No two digits are the same

---> Sum of any two digits can have a max value of 17

(if one is 9 and the other is 8)

C00: D is odd

C01: 3*A + S = N + 10*x

x <= 3 (Max value for 3A+S is 35 if A is 9 and S is 8);

if x = 0 then A will be <= 3 (since 3A+S = N and N <= 9)

C02: 2*M + D + x = A + 10*y

max value for 2M+D+x is 29 (if M=9, D=8 and x=3), implying that y <= 2

C03: 2*M + A + y = M + 10*z

Rewriting this as M + A + y = 10z, max value for M+A+y is 19 and it has

to be a multiple of 10. So M+A+y = 10 and z = 1.

C03a: M + A + y = 10 and z = 1.

C04: 2*O + D + z = O + 10*w

Rewrite C04 as O + D + z = 10w or O + D + 1 = 10w.

Max value for O+D+1 is 18 (O=9, D=8)

and O+D+1 is a multiple of 10. So O+D = 9 and w = 1;

C04a: O + D = 9 and w = 1;

C05: 2*M + w = R

As w=1, we have 2M+1 = R. So R is odd and M <= 4;

C05a: R is an odd number and M <= 4;

C06: Using C03a and C05a: M + A + y = 10 and M <= 4 (and y <= 2)

The minimum value of A is 4 (when M=4 and y=2) but then M=A.

So A >= 5

C07: Using C02 and C05a: 2M + D + x = A + 10*y (x <= 3; y <= 2) and M <= 4

Max value for 2M+D+x is 2*4 + 9 + 3 = 20. This will be true

when y=2 and A=0.

But from C06, A >= 5. Therefore, max value for 2M+D+x < 20,

which leads to y<=1.

y <= 1;

C08: from C01, we see that if x=0 then A <= 3; C06 says A >= 5; So x in {1,2,3}

Since we know that R is odd, we can try all possible values..

R=9: M=4 (from C05)

Solving C03a, M+A+y = 10; A+y = 6 (also y <= 1 from C07);

If y=0, A=6;

Substituting in C02 (2M+D+x = A+10y), we get 8 + D + x = 6, which

is not possible.

If y=1, A=5;

Substituting in C02 (2M+D+x = A+10y), we get 8 + D + x = 15 or D+x=7

Since D is odd and x in {1, 2, 3}, we can say that D=5 and x=2;

D and A both can not be 5 and this is not a valid combination.

R=7: M=3 (from C05)

Solving C03a, M+A+y = 10; A+y = 7 (also y <= 1 from C07);

If y=0, A=7; This makes A and R both same and violates Rule 0;

If y=1, A=6;

Substituting in C02 (2M+D+x = A+10y), 6 + D + x = 16 or D+x=10

Since D is odd and x in {1, 2, 3}, D can be 7 or 9 (x being 3 and 1)

--> D=7 is not allowed, as D and R will be same.

--> D=9; x=1; From C04a, O = 0;

From C01, 3A+S = N+10x ... 18+S=N+10 or 8 + S = N

S can not be 0 as O is 0;

if S = 1, then N = 9 will equal D!

R=5: M=2 (from C05)

Solving C03a, M+A+y = 10; A+y = 8 (also y <= 1 from C07);

If y=0, A=8;

Substituting in C02 (2M+D+x = A+10y), we get 4 + D + x = 8 or D+x=4

Since D is odd and x in {1, 2, 3}, D can be 1 or 3 (x being 3 and 1)

--> D=1 implies O = 8 (O and A are the same)

--> D=3; x=1; implies O = 6 (C04a)

Subst. in C01, 3A+S = N+10x ... 24+S = N+10 or 14+S = N,

which is not possible.

If y=1, A=7;

Subst. in C02 (2M+D+x = A+10y), we get 4 + D + x = 17 or D+x=13

Since D <= 9 and x in {1,2,3}, D+x can never be 13.

Let's do R=1 first ...

R=1: M=0 (from C05)

Solving C03a, M+A+y = 10; A+y = 10 (also y <= 1 from C07);

If y=0, A=10 which is not allowed;

If y=1, A=9;

Substituting in C02 (2M+D+x = A+10y), we get D + x = 19

Since D <= 9 and x in {1,2,3}, D+x can never be 19.

R=3: M=1 (from C05)

Solving C03a, M+A+y = 10; A+y = 9 (also y <= 1 from C07);

If y=1, A=8;

Subst. in C02 (2M+D+x = A+10y), we get 2 + D + x = 18 or D+x=16

Since D <= 9 and x in {1,2,3}, D+x can never be 16.

If y=0, A=9;

Subst. in C02 (2M+D+x = A+10y), we get 2 + D + x = 9 or D+x=7

Since D is odd, x should be even;

As x is one of 1, 2 or 3, and should be even, x=2; Therefore, D=5

D=5 implies O = 4 (C04a)

Substituting in C01, 3A+S = N+10x ... 27+S = N+20 or 7+S = N.

if S=2 then N=9 and A=N. So S is not 2;

if S=1, S will be equal to M; So S is not 1;

if S=0 then N=7;

ROMAN = 34197 (D=5 and S=0)

It took a lot longer to decide on how to approach the problem than

to solve it once I decided on the method...

Thanks to Gabe, Alberto and Matthias for keeping the discussion alive

and to Tom for an extremely useful forum for Oracle users!

this. My apologies to Tom for such a long post with no relevance to Oracle!

Borrowing heavily from Gabe's work... ;)

Let us assume x,y,z,w in {0,1,2,3,4,5,6,7,8,9} such that

wzyx

----

MOMMA

MOMMA

DADS

A

=====

ROMAN

Rule 0: No two digits are the same

---> Sum of any two digits can have a max value of 17

(if one is 9 and the other is 8)

C00: D is odd

C01: 3*A + S = N + 10*x

x <= 3 (Max value for 3A+S is 35 if A is 9 and S is 8);

if x = 0 then A will be <= 3 (since 3A+S = N and N <= 9)

C02: 2*M + D + x = A + 10*y

max value for 2M+D+x is 29 (if M=9, D=8 and x=3), implying that y <= 2

C03: 2*M + A + y = M + 10*z

Rewriting this as M + A + y = 10z, max value for M+A+y is 19 and it has

to be a multiple of 10. So M+A+y = 10 and z = 1.

C03a: M + A + y = 10 and z = 1.

C04: 2*O + D + z = O + 10*w

Rewrite C04 as O + D + z = 10w or O + D + 1 = 10w.

Max value for O+D+1 is 18 (O=9, D=8)

and O+D+1 is a multiple of 10. So O+D = 9 and w = 1;

C04a: O + D = 9 and w = 1;

C05: 2*M + w = R

As w=1, we have 2M+1 = R. So R is odd and M <= 4;

C05a: R is an odd number and M <= 4;

C06: Using C03a and C05a: M + A + y = 10 and M <= 4 (and y <= 2)

The minimum value of A is 4 (when M=4 and y=2) but then M=A.

So A >= 5

C07: Using C02 and C05a: 2M + D + x = A + 10*y (x <= 3; y <= 2) and M <= 4

Max value for 2M+D+x is 2*4 + 9 + 3 = 20. This will be true

when y=2 and A=0.

But from C06, A >= 5. Therefore, max value for 2M+D+x < 20,

which leads to y<=1.

y <= 1;

C08: from C01, we see that if x=0 then A <= 3; C06 says A >= 5; So x in {1,2,3}

Since we know that R is odd, we can try all possible values..

R=9: M=4 (from C05)

Solving C03a, M+A+y = 10; A+y = 6 (also y <= 1 from C07);

If y=0, A=6;

Substituting in C02 (2M+D+x = A+10y), we get 8 + D + x = 6, which

is not possible.

If y=1, A=5;

Substituting in C02 (2M+D+x = A+10y), we get 8 + D + x = 15 or D+x=7

Since D is odd and x in {1, 2, 3}, we can say that D=5 and x=2;

D and A both can not be 5 and this is not a valid combination.

R=7: M=3 (from C05)

Solving C03a, M+A+y = 10; A+y = 7 (also y <= 1 from C07);

If y=0, A=7; This makes A and R both same and violates Rule 0;

If y=1, A=6;

Substituting in C02 (2M+D+x = A+10y), 6 + D + x = 16 or D+x=10

Since D is odd and x in {1, 2, 3}, D can be 7 or 9 (x being 3 and 1)

--> D=7 is not allowed, as D and R will be same.

--> D=9; x=1; From C04a, O = 0;

From C01, 3A+S = N+10x ... 18+S=N+10 or 8 + S = N

S can not be 0 as O is 0;

if S = 1, then N = 9 will equal D!

R=5: M=2 (from C05)

Solving C03a, M+A+y = 10; A+y = 8 (also y <= 1 from C07);

If y=0, A=8;

Substituting in C02 (2M+D+x = A+10y), we get 4 + D + x = 8 or D+x=4

Since D is odd and x in {1, 2, 3}, D can be 1 or 3 (x being 3 and 1)

--> D=1 implies O = 8 (O and A are the same)

--> D=3; x=1; implies O = 6 (C04a)

Subst. in C01, 3A+S = N+10x ... 24+S = N+10 or 14+S = N,

which is not possible.

If y=1, A=7;

Subst. in C02 (2M+D+x = A+10y), we get 4 + D + x = 17 or D+x=13

Since D <= 9 and x in {1,2,3}, D+x can never be 13.

Let's do R=1 first ...

R=1: M=0 (from C05)

Solving C03a, M+A+y = 10; A+y = 10 (also y <= 1 from C07);

If y=0, A=10 which is not allowed;

If y=1, A=9;

Substituting in C02 (2M+D+x = A+10y), we get D + x = 19

Since D <= 9 and x in {1,2,3}, D+x can never be 19.

R=3: M=1 (from C05)

Solving C03a, M+A+y = 10; A+y = 9 (also y <= 1 from C07);

If y=1, A=8;

Subst. in C02 (2M+D+x = A+10y), we get 2 + D + x = 18 or D+x=16

Since D <= 9 and x in {1,2,3}, D+x can never be 16.

If y=0, A=9;

Subst. in C02 (2M+D+x = A+10y), we get 2 + D + x = 9 or D+x=7

Since D is odd, x should be even;

As x is one of 1, 2 or 3, and should be even, x=2; Therefore, D=5

D=5 implies O = 4 (C04a)

Substituting in C01, 3A+S = N+10x ... 27+S = N+20 or 7+S = N.

if S=2 then N=9 and A=N. So S is not 2;

if S=1, S will be equal to M; So S is not 1;

if S=0 then N=7;

ROMAN = 34197 (D=5 and S=0)

It took a lot longer to decide on how to approach the problem than

to solve it once I decided on the method...

Thanks to Gabe, Alberto and Matthias for keeping the discussion alive

and to Tom for an extremely useful forum for Oracle users!

Venkat, just dress up your solution with a couple of references to "modulus arithmetic theorems", and perhaps "divide-and-conquer" and it will be as elegant as an Armani suit :)

Great, congratulations !

Great, congratulations !

Hallo,

since it is Friday afternoon, here a variation on the theme:

puzzle:

find all magical squares

c11 c12 c13

c21 c22 c23

c31 c32 c33

of order 3

with sum of each row = sum of each column = sum of each diagonal

(I didn't think of solving this in SQL before I saw this thread)

My solution:

with nums as (

select 1 c from dual union all select 2 from dual union all select 3 from dual

),

possible_rows as (

select rownum as row_num, "1st".c as col1, "2nd".c as col2, "3rd".c as col3 from nums "1st", nums "2nd", nums "3rd"

where "1st".c != "2nd".c

and "1st".c != "3rd".c

and "2nd".c != "3rd".c

)

, possible_row_orderings as (

select po1.*, po2.*, po3.* from

(select row_num as row1 from possible_rows) po1,

(select row_num as row2 from possible_rows) po2,

(select row_num as row3 from possible_rows) po3

)

select p1.col1 as c11, p1.col2 as c12, p1.col3 as c13,

p2.col1 as c21, p2.col2 as c22, p2.col3 as c23,

p3.col1 as c31, p3.col2 as c32, p3.col3 as c33

from possible_row_orderings pro,

possible_rows p1, possible_rows p2, possible_rows p3

where row1 != row2

and row1 != row3

and row2 != row3

and p1.row_num = pro.row1

and p2.row_num = pro.row2

and p3.row_num = pro.row3

and /* sum over 1st row = sum over 2nd row */

p1.col1 + p1.col2 + p1.col3 = p2.col1 + p2.col2 + p2.col3

and /* sum over 1st row = sum over 3rd row */

p1.col1 + p1.col2 + p1.col3 = p3.col1 + p3.col2 + p3.col3

/* and sum over 2st row = sum over 3rd row follows from the last two lines */

/* and p2.col1 + p2.col2 + p2.col3 = p3.col1 + p3.col2 + p3.col3 */

and /* sum over 1st columns = sum over 1st row */

p1.col1 + p2.col1 + p3.col1 = p1.col1 + p1.col2 + p1.col3

and /* sum over 2nd columns = sum over 1st row */

p1.col2 + p2.col2 + p3.col2 = p1.col1 + p1.col2 + p1.col3

/* and sum over 3rd columns = sum over 1st row follows from previous lines */

/* p1.col2 + p2.col2 + p3.col2 = p1.col1 + p1.col2 + p1.col3 */

and /* sum over 1st diagonal = sum over 1st row */

p1.col1 + p2.col2 + p3.col3 = p1.col1 + p1.col2 + p1.col3

and /* sum over 2nd diagonal = sum over 1st row */

p1.col3 + p2.col2 + p3.col1 = p1.col1 + p1.col2 + p1.col3

(I think you can see how to generalize that to order 4, 5 ...)

Alberto: can you tune that query ?

Venkat: what's your solution ?

Too all: how to exclude solutions that only differ by a

symmetry (rotation, reflection)

since it is Friday afternoon, here a variation on the theme:

puzzle:

find all magical squares

c11 c12 c13

c21 c22 c23

c31 c32 c33

of order 3

with sum of each row = sum of each column = sum of each diagonal

(I didn't think of solving this in SQL before I saw this thread)

My solution:

with nums as (

select 1 c from dual union all select 2 from dual union all select 3 from dual

),

possible_rows as (

select rownum as row_num, "1st".c as col1, "2nd".c as col2, "3rd".c as col3 from nums "1st", nums "2nd", nums "3rd"

where "1st".c != "2nd".c

and "1st".c != "3rd".c

and "2nd".c != "3rd".c

)

, possible_row_orderings as (

select po1.*, po2.*, po3.* from

(select row_num as row1 from possible_rows) po1,

(select row_num as row2 from possible_rows) po2,

(select row_num as row3 from possible_rows) po3

)

select p1.col1 as c11, p1.col2 as c12, p1.col3 as c13,

p2.col1 as c21, p2.col2 as c22, p2.col3 as c23,

p3.col1 as c31, p3.col2 as c32, p3.col3 as c33

from possible_row_orderings pro,

possible_rows p1, possible_rows p2, possible_rows p3

where row1 != row2

and row1 != row3

and row2 != row3

and p1.row_num = pro.row1

and p2.row_num = pro.row2

and p3.row_num = pro.row3

and /* sum over 1st row = sum over 2nd row */

p1.col1 + p1.col2 + p1.col3 = p2.col1 + p2.col2 + p2.col3

and /* sum over 1st row = sum over 3rd row */

p1.col1 + p1.col2 + p1.col3 = p3.col1 + p3.col2 + p3.col3

/* and sum over 2st row = sum over 3rd row follows from the last two lines */

/* and p2.col1 + p2.col2 + p2.col3 = p3.col1 + p3.col2 + p3.col3 */

and /* sum over 1st columns = sum over 1st row */

p1.col1 + p2.col1 + p3.col1 = p1.col1 + p1.col2 + p1.col3

and /* sum over 2nd columns = sum over 1st row */

p1.col2 + p2.col2 + p3.col2 = p1.col1 + p1.col2 + p1.col3

/* and sum over 3rd columns = sum over 1st row follows from previous lines */

/* p1.col2 + p2.col2 + p3.col2 = p1.col1 + p1.col2 + p1.col3 */

and /* sum over 1st diagonal = sum over 1st row */

p1.col1 + p2.col2 + p3.col3 = p1.col1 + p1.col2 + p1.col3

and /* sum over 2nd diagonal = sum over 1st row */

p1.col3 + p2.col2 + p3.col1 = p1.col1 + p1.col2 + p1.col3

(I think you can see how to generalize that to order 4, 5 ...)

Alberto: can you tune that query ?

Venkat: what's your solution ?

Too all: how to exclude solutions that only differ by a

symmetry (rotation, reflection)

-- use #s 1 through 9

-- a,b,c are row1, d,e,f are row2 and g,h,i are row3

select a,b,c,d,e,f,g,h,i, a+b+c total

from (

select a,b,c,d,e,f,g,h,i

,row_number() over (partition by d||e||f order by rownum) row_sym

,row_number() over (partition by b||e||h order by rownum) col_sym

from (select rownum a from (select 1 from dual group by cube(1,1,1,1))

where rownum < 10) t1,

(select rownum b from (select 1 from dual group by cube(1,1,1,1))

where rownum < 10) t2,

(select rownum c from (select 1 from dual group by cube(1,1,1,1))

where rownum < 10) t3,

(select rownum d from (select 1 from dual group by cube(1,1,1,1))

where rownum < 10) t4,

(select rownum e from (select 1 from dual group by cube(1,1,1,1))

where rownum < 10) t5,

(select rownum f from (select 1 from dual group by cube(1,1,1,1))

where rownum < 10) t6,

(select rownum g from (select 1 from dual group by cube(1,1,1,1))

where rownum < 10) t7,

(select rownum h from (select 1 from dual group by cube(1,1,1,1))

where rownum < 10) t8,

(select rownum i from (select 1 from dual group by cube(1,1,1,1))

where rownum < 10) t9

where a+b = f+i and a+c = e+h and b+c = d+g

and d+e = c+i and d+f = b+h and e+f = g+a

and g+h = c+f and g+i = b+e and h+i = a+d

and a+i = c+g

--and a not in (b,c,d,e,f,g,h,i)

--and b not in (a,c,d,e,f,g,h,i)

--and c not in (a,b,d,e,f,g,h,i)

--and d not in (a,b,c,e,f,g,h,i)

--and e not in (a,b,c,d,f,g,h,i)

--and f not in (a,b,c,d,e,g,h,i)

--and g not in (a,b,c,d,e,f,h,i)

--and h not in (a,b,c,d,e,f,g,i)

--and i not in (a,b,c,d,e,f,g,h)

and a not in (b,c,d,g) and b not in (a,c,e,h) and c not in (a,b,f,i)

and d not in (a,g,e,f) and e not in (d,f,b,h) and f not in (c,i,d,e)

and g not in (a,d,h,i) and h not in (b,e,g,i) and i not in (g,h,c,f)

)

where row_sym = 1 and col_sym = 1

order by (a+b+c)

-- a,b,c are row1, d,e,f are row2 and g,h,i are row3

select a,b,c,d,e,f,g,h,i, a+b+c total

from (

select a,b,c,d,e,f,g,h,i

,row_number() over (partition by d||e||f order by rownum) row_sym

,row_number() over (partition by b||e||h order by rownum) col_sym

from (select rownum a from (select 1 from dual group by cube(1,1,1,1))

where rownum < 10) t1,

(select rownum b from (select 1 from dual group by cube(1,1,1,1))

where rownum < 10) t2,

(select rownum c from (select 1 from dual group by cube(1,1,1,1))

where rownum < 10) t3,

(select rownum d from (select 1 from dual group by cube(1,1,1,1))

where rownum < 10) t4,

(select rownum e from (select 1 from dual group by cube(1,1,1,1))

where rownum < 10) t5,

(select rownum f from (select 1 from dual group by cube(1,1,1,1))

where rownum < 10) t6,

(select rownum g from (select 1 from dual group by cube(1,1,1,1))

where rownum < 10) t7,

(select rownum h from (select 1 from dual group by cube(1,1,1,1))

where rownum < 10) t8,

(select rownum i from (select 1 from dual group by cube(1,1,1,1))

where rownum < 10) t9

where a+b = f+i and a+c = e+h and b+c = d+g

and d+e = c+i and d+f = b+h and e+f = g+a

and g+h = c+f and g+i = b+e and h+i = a+d

and a+i = c+g

--and a not in (b,c,d,e,f,g,h,i)

--and b not in (a,c,d,e,f,g,h,i)

--and c not in (a,b,d,e,f,g,h,i)

--and d not in (a,b,c,e,f,g,h,i)

--and e not in (a,b,c,d,f,g,h,i)

--and f not in (a,b,c,d,e,g,h,i)

--and g not in (a,b,c,d,e,f,h,i)

--and h not in (a,b,c,d,e,f,g,i)

--and i not in (a,b,c,d,e,f,g,h)

and a not in (b,c,d,g) and b not in (a,c,e,h) and c not in (a,b,f,i)

and d not in (a,g,e,f) and e not in (d,f,b,h) and f not in (c,i,d,e)

and g not in (a,d,h,i) and h not in (b,e,g,i) and i not in (g,h,c,f)

)

where row_sym = 1 and col_sym = 1

order by (a+b+c)

thx, venkat

Tom:

is there any chance to solve these kind of queries using

Model-Clause (which I didn't have understood so far) ?

Tom:

is there any chance to solve these kind of queries using

Model-Clause (which I didn't have understood so far) ?

probably, but I'm not even thinking about going there :)

....

that there still exist mysteries inside Oracle

which we are supposed to investigate ourselves

and without a 24x7-Tom !

I wish you can get an 8-hour-sleep as often as possible !

that there still exist mysteries inside Oracle

which we are supposed to investigate ourselves

and without a 24x7-Tom !

I wish you can get an 8-hour-sleep as often as possible !

I am a-scratching my head on what the following means?

"Each letter in this sum stands for a diferent digit. Given that DAD is odd,what number is represented by ROMAN?"

What is "DAD"?

"Each letter in this sum stands for a diferent digit. Given that DAD is odd,what number is represented by ROMAN?"

What is "DAD"?

sorry got what DAD stands for:)

Thanks, for intersting discusion, I have done this task in ca. 30 min. I think author of this question was very smart :) and someone have ask how to do it simple, so it is my approach

MOMMA

MOMMA

DADS

A

-----

ROMAN

R could be only 1-9, so M could be 1-4

I took M=1, then A=9.

D is odd so it could by 5 or 7 (MMD=A 117=9).

If S=0 then D=5.

And rest ist easy...

I took always first number, and it was right.

MOMMA

MOMMA

DADS

A

-----

ROMAN

R could be only 1-9, so M could be 1-4

I took M=1, then A=9.

D is odd so it could by 5 or 7 (MMD=A 117=9).

If S=0 then D=5.

And rest ist easy...

I took always first number, and it was right.

Is there a way to actually view the optimized code that the plsql engine executes?

there is a way to dump the equivalent of plsql assembler code -- not sure it is really "relevant".

what are you looking to see?

what are you looking to see?

I am trying to see how much optimization is performed.

For example if i had the following:

DECLARE

v_num number;

BEGIN

FOR i IN 1 .. 10000 LOOP

v_num := 100;

END LOOP;

END;

Would the compiler optimize this to:

DECLARE

v_num number;

BEGIN

v_num := 100;

END;

And if so how could i tell.

Or take for example:

DECLARE

v_num number;

BEGIN

FOR i IN 1 .. 10000 LOOP

v_num := i;

END LOOP;

Return v_num; -- I know its an anonymous block but you

-- get the point.

END;

Maybe the compiler ended up with:

DECLARE

v_num number;

BEGIN

Return 1000; -- I know its an anonymous block but you

-- get the point.

END;

I am just wondering if there is any method to compare what is actually written with what the compiler decides to do? Is there any method to tell that the compiler has actually performed some of the above steps?

For example if i had the following:

DECLARE

v_num number;

BEGIN

FOR i IN 1 .. 10000 LOOP

v_num := 100;

END LOOP;

END;

Would the compiler optimize this to:

DECLARE

v_num number;

BEGIN

v_num := 100;

END;

And if so how could i tell.

Or take for example:

DECLARE

v_num number;

BEGIN

FOR i IN 1 .. 10000 LOOP

v_num := i;

END LOOP;

Return v_num; -- I know its an anonymous block but you

-- get the point.

END;

Maybe the compiler ended up with:

DECLARE

v_num number;

BEGIN

Return 1000; -- I know its an anonymous block but you

-- get the point.

END;

I am just wondering if there is any method to compare what is actually written with what the compiler decides to do? Is there any method to tell that the compiler has actually performed some of the above steps?

not in a documented sense, no.