Challenge 3

[dbomrrsm]

Write SQL code that will put all Prime numbers between 1 and 1000000 ( 1 million) into a table and then select a count from that table

Rules
1 You have to write everything from scratch, meaning we should be able to execute the script on any computer and get the same result
2 Don't show any code until the 31st January (next Tuesday)
3 We should only show the time and recordcount so that people will have a general idea if their code is fast enough
example PIV 3.0 Ghz 2GB RAM runtime 21 seconds recordcount 567
People should post this every time they improve their code so that we can see what's going on
4 The first five people who sign up will run all the scripts and list the in execution time order or we can designate 5 people it doesn't matter
5 remember the FASTEST time wins, it doesn't have to look pretty

Good luck

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[carp]

Since it appears we only have two people running the code and there were five slots open, I will gladly also run any code that comes along.

And Dave - you can futz with my code anytime!

 

[SantaMufasa]

Aaaahhh! Much better, Dave. Have a Purple Star for your "codilogical eloquence".

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[carp]

Well, geeze, THANKS Dave!

 

[SantaMufasa]

So, DBomrrsm and Djbjr, would yous twos (and any others) like me to run your code head-to-head with Carp's excellent entry?

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[djbjr]

no I am embarresed with my code...it runs in 7 minutes

 

[SantaMufasa]

Djbjr,

Don't feel badly about your entry...I threw together a solution without first seeing Dave's (Carp's) and mine ran longer than yours. I must sit down and pour over Dave's to understand why his is so lightning fast.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[djbjr]

OK well dont bother running it....

DECLARE

v_number1 number;
v_number3 number;
v_number7 number;
v_number9 number;
v_count number;

BEGIN
v_number1 := 1;
v_number3 := 3;
v_number7 := 7;
v_number9 := 9;

insert into prime values (v_number7);

v_number1 := v_number1 + 10;
v_number3 := v_number1 + 10;
v_number7 := v_number1 + 10;
v_number9 := v_number1 + 10;



WHILE v_number1 < 5001 LOOP


select count(*) into v_count from
(select prime_number from prime where prime_number < v_number1/2)
where MOD(v_number1, prime_number) = 0;

If v_count = 0
Then
insert into prime values (v_number1);
end if;

select count(*) into v_count from
(select prime_number from prime where prime_number < v_number3/2)
where MOD(v_number3, prime_number) = 0;

If v_count = 0
Then
insert into prime values (v_number3);
end if;

select count(*) into v_count from
(select prime_number from prime where prime_number < v_number7/2)
where MOD(v_number7, prime_number) = 0;

If v_count = 0
Then
insert into prime values (v_number7);
end if;

select count(*) into v_count from
(select prime_number from prime where prime_number < v_number9/2)
where MOD(v_number9, prime_number) = 0;

If v_count = 0
Then
insert into prime values (v_number9);

end if;

v_number1 := v_number1 + 10;
v_number3 := v_number1 + 10;
v_number7 := v_number1 + 10;
v_number9 := v_number1 + 10;


select count(*) into v_count from
(select prime_number from prime where prime_number < v_number3/2)
where MOD(v_number3, prime_number) = 0;

If v_count = 0
Then
insert into prime values (v_number3);
end if;


select count(*) into v_count from
(select prime_number from prime where prime_number < v_number9/2)
where MOD(v_number9, prime_number) = 0;

If v_count = 0
Then
insert into prime values (v_number9);

end if;

v_number1 := v_number1 + 10;
v_number3 := v_number1 + 10;
v_number7 := v_number1 + 10;
v_number9 := v_number1 + 10;

select count(*) into v_count from
(select prime_number from prime where prime_number < v_number1/2)
where MOD(v_number1, prime_number) = 0;

If v_count = 0
Then
insert into prime values (v_number1);
end if;


select count(*) into v_count from
(select prime_number from prime where prime_number < v_number7/2)
where MOD(v_number7, prime_number) = 0;

If v_count = 0
Then
insert into prime values (v_number7);
end if;

v_number1 := v_number1 + 10;
v_number3 := v_number1 + 10;
v_number7 := v_number1 + 10;
v_number9 := v_number1 + 10;


end loop;


v_number1 := 2;
insert into prime values (v_number1);

v_number1 := 3;
insert into prime values (v_number1);

v_number1 := 5;
insert into prime values (v_number1);
commit;

end;
/



Basically what I was trying to do was do the least amount of divisions possible. I knew that every even number is not prime along with every 3rd odd number. Also I knew that every number that ended in 5 was not prime. Thats why I have 3 sets of comparisions in the loop.

I concede to CARP! WTG CARP!