• Questions
• # Performance Query

Thanks for the question, Iain.

Asked: October 23, 2004 - 12:20 pm UTC

Last updated: April 21, 2005 - 6:20 am UTC

Version: 9.2.0.5

Viewed 1000+ times

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
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

(40 ratings)

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

### Performance Query

Iain, October 23, 2004 - 3:35 pm UTC

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.

October 23, 2004 - 4:52 pm UTC

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)

Alberto Dell'Era, October 23, 2004 - 5:24 pm UTC

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

October 23, 2004 - 6:23 pm UTC

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.

Alberto Dell'Era, October 23, 2004 - 6:43 pm UTC

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

October 23, 2004 - 6:50 pm UTC

```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

```

Alberto Dell'Era, October 23, 2004 - 7:14 pm UTC

>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

October 23, 2004 - 7:21 pm UTC

```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

```

Alberto Dell'Era, October 23, 2004 - 7:46 pm UTC

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

October 23, 2004 - 8:00 pm UTC

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....

Alberto Dell'Era, October 24, 2004 - 8:01 am UTC

I can envision a new TV commercial for US tourists:

"Come to Europe -
the country where Empty Mailbox is not an oxymoron"

David Aldridge, October 24, 2004 - 1:18 pm UTC

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?

October 24, 2004 - 1:58 pm UTC

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.

### just for fun ...

Gabe, October 25, 2004 - 12:16 am UTC

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 :)

### perfomance query

conan, October 25, 2004 - 6:28 am UTC

if posable could u send me a list on oracle database specs
to my email cpau_is@hotmail.com

October 25, 2004 - 7:50 am UTC

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

### not elegant but faster

Darko Egersdorfer, October 25, 2004 - 7:47 am UTC

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;

October 25, 2004 - 7:54 am UTC

for me, that ran

9ir2 old code 11
9ir2 new code 2.6

10g old code 4.3
10g new code 0.46

### Slightly more optimized...

Kashif, October 25, 2004 - 9:47 am UTC

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;
/

### now ... who's cheating?

Gabe, October 25, 2004 - 10:15 am UTC

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! :)

### cheat

Hector, October 27, 2004 - 7:20 am UTC

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

### To Gabe...

Kashif, October 27, 2004 - 4:00 pm UTC

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!

### hmmm ...

Gabe, November 01, 2004 - 4:04 pm UTC

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
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.

### corollary giving me a coronary

Hector, November 10, 2004 - 12:15 pm UTC

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

You need to get out more.

November 10, 2004 - 12:27 pm UTC

LOL....

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

Alberto Dell'Era, November 10, 2004 - 2:19 pm UTC

> 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 ;)

Gabe, November 10, 2004 - 3:03 pm UTC

To Alberto ...

<quote>That probably means that the expected solution was brute-force on paper</quote>
Doesnt 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 Im as lazy as the next chap  it is just that my stubbornness sometimes wins over laziness :)

### I hear, I hear ...

Gabe, November 10, 2004 - 3:20 pm UTC

<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.

A reader, November 10, 2004 - 5:55 pm UTC

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

Alberto Dell'Era, November 11, 2004 - 8:01 am UTC

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) ...

### pure sql brute-force approach

Alberto Dell'Era, November 13, 2004 - 7:51 am UTC

(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.

Venkat, November 15, 2004 - 3:10 pm UTC

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

November 15, 2004 - 9:01 pm UTC

we
are
getting
carried
away :)

i like it...

### Venkat wins

Alberto Dell'Era, November 15, 2004 - 4:56 pm UTC

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 :)

### Alberto vs. Venkat

A reader, November 15, 2004 - 10:14 pm UTC

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

### I vote for Venkat

Matthias Rogel, November 16, 2004 - 3:53 am UTC

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")

### a pragmatical view to temp tables

Alberto Dell'Era, November 16, 2004 - 5:30 pm UTC

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 :)

### ;-)

Matthias Rogel, November 17, 2004 - 4:08 am UTC

### when will Oracle support Kronecker's main idea ?

Matthias Rogel, November 17, 2004 - 5:31 am UTC

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."

### Not really elegant, but ...

Venkat, November 17, 2004 - 2:42 pm UTC

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
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,
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!

Alberto Dell'Era, November 18, 2004 - 6:11 pm UTC

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 !

### A variation on the theme

Matthias Rogel, November 19, 2004 - 9:02 am UTC

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 ?
Too all: how to exclude solutions that only differ by a
symmetry (rotation, reflection)

Venkat, November 19, 2004 - 2:31 pm UTC

-- 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)

### of course an improvement

Matthias Rogel, November 22, 2004 - 7:45 am UTC

thx, venkat

Tom:
is there any chance to solve these kind of queries using
Model-Clause (which I didn't have understood so far) ?

November 22, 2004 - 7:52 am UTC

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

### good to hear ...

Matthias Rogel, November 22, 2004 - 8:47 am UTC

....
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 !

### ok...

Menon, November 23, 2004 - 7:55 pm UTC

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?"

### ah...

Menon, November 24, 2004 - 10:02 am UTC

sorry got what DAD stands for:)

### Late but better than never...

Darek, January 11, 2005 - 4:22 pm UTC

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
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.

### View optimized code

Steve G, April 20, 2005 - 10:41 am UTC

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

April 20, 2005 - 9:00 pm UTC

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

what are you looking to see?

### View Optimized Code

Steve G, April 21, 2005 - 2:21 am UTC

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?

April 21, 2005 - 6:20 am UTC

not in a documented sense, no.

# More to Explore

##### PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

##### PL/SQL docs

PL/SQL reference manual from the Oracle documentation library