prime3rd

  SQL*Plus: Release 10.2.0.1.0 - Production on Wed Jan 7 15:08:17 2009  Copyright (c) 1982, 2005, Oracle.  All rights reserved.   Connected to: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production  

Table created.

Type created.

Function created.

Procedure created.

1 row created.

1 row created.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

MAX(P)
12763

1 create table prime (p number primary key) -- 仍然是老表
2 organization index -- 只不过升级为 IOT
3 /
4 create or replace type ton is table of number -- 没有变化
5 /
6 create or replace function seqr (l number, h number)
7 return ton pipelined as -- 没有变化
8 begin
9 for i in l..h loop pipe row(i); end loop;
10 return;
11 end;
12 /
13 create or replace procedure loop_prime(k number) as begin
14 insert into prime -- 剩下的才是关键
15 select * from table(
16 select seqr(pp+1,qq-1) from (
17 select p*p pp, q*q qq from ( -- 用分析函数
18 select p, lead(p) over (order by p) q
19 from prime where rownum<=k
20 ) where q is not null order by p desc
21 ) where rownum<=1 -- 取出最大一行
22 ) where not exists ( -- 用来代替老版本的 mod_list
23 select * from prime
24 where rownum<=k and mod(column_value,p)=0
25 );
26 end;
27 /
28 insert into prime values (2);
29 insert into prime values (3);
30 exec loop_prime(2);
31 exec loop_prime(3);
32 exec for i in 4..30 loop loop_prime(i); end loop;
33 -- 压力测试表明速度比老版本快了近一百倍
34 select max(p) from prime;
35 get lpr
36* exit;

 Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production