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
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.
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 ;)
contradiction ?!?!
Gabe, November 10, 2004 - 3:03 pm UTC
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 :)
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
But isn't DADS odd?
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
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!
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 ?
Venkat: what's your solution ?
Too all: how to exclude solutions that only differ by a
symmetry (rotation, reflection)
How about ...
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?"
What is "DAD"?
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
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.
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.