Re: oracle query

From: Vadim Tropashko <vadimtro_at_yahoo.com>
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

Original text of this message