Re: oracle query
Date: Thu, 08 Feb 2001 00:49:28 GMT
Message-ID: <95sqek$hbj$1_at_nnrp1.deja.com>
In article <95pcl6$egp$1_at_nnrp1.deja.com>,
Aloha Kakuikanu <alohakaku_at_yahoo.com> wrote:
> In article <95movl$5hq$1_at_nnrp1.deja.com>,
> Vadim Tropashko <vadimtro_at_yahoo.com> wrote:
> > select max(c.gcd) from
> > (select rownum as x from PHONES where rownum < 15) a,
> > (select rownum as y from PHONES where rownum < 15) b,
> > (select rownum as gcd from PHONES where rownum < 15) c
> > where gcd*x=6
> > and gcd*y=9
> Optimizer plan for your masterpiece scans
> all your tables in memory and then performes lots of nested loops. The
> cost is almost a billion, literally.
Next improvement would be using functional indexes.
TABLE ncube (
x NUMBER,
y NUMBER,
z NUMBER
);
insert into ncube
select FLOOR(i/(64*64)) z, MOD(FLOOR(i/64),64) y, MOD(i,64) x from BIGTABLE;
(BIGTABLE here contains 64*64*64 rows so that resulting table NCUBE will contain all combinations of integers 0<=x<64, 0<=y<64, 0<=z<64)
CREATE INDEX zx_fi ON ncube(z*x);
CREATE INDEX zy_fi ON ncube(z*y);
Those indexes speed up both FLOOR_SQRT and GCD queries dramatically:
select x as FLOOR_SQRT from NCUBE
where x*z = 25
and x=z
SELECT max(z) as GCD from NCUBE
where z*x = 20
and z*y = 12
Note that with a slight query modification
select x as FLOOR_SQRT from NCUBE
where x*z+1 = 25+1
and x=z
optimizer is no longer able to find a plan with index range scan -- it is not a computer algebra system, after all.
Sent via Deja.com
http://www.deja.com/
Received on Thu Feb 08 2001 - 01:49:28 CET