Re: oracle query

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:
> > In article <95fl8f$21c$1_at_nnrp1.deja.com>,
> > Aloha Kakuikanu <alohakaku_at_yahoo.com> wrote:
> > > In article <95fcin$qgb$1_at_nnrp1.deja.com>,
 

> > > BTW, try to query Greatest Common Divisor -- I've read that
> > > SQL is incomplete, so you cannot calculate anything your way.
> >
> > 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
> >
> I ran your query just for fun. 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. By any chance, do you know Euclid
> algorithm?

I can use one table only. A view

select t-(xplusy+1)*xplusy/2 y, xplusy-t+(xplusy+1)*xplusy/2 x from (

    select FLOOR((SQRT(8*(rownum-1)+1)-1)/2) xplusy, rownum-1 t     from BIGTABLE
)

is similar to Cantor's method for putting the rationals and naturals into one-to-one correspondence. Here is the output

Y X
---------- ----------

0          0
0          1
1          0
0          2
1          1
2          0
0          3
1          2
...        ...

Cantor's method obviously generalizes to three dimensions, so the final query becomes:

select max(z) from (

   select x,

          ttt-(xplusy+1)*xplusy/2 y,
          xplusy-ttt+(xplusy+1)*xplusy/2 z from (
             select FLOOR((SQRT(8*tt+1)-1)/2) xplusy,
                    tt ttt,
                    x from (
                        select t-(xplusy+1)*xplusy/2 tt,
                        xplusy-t+(xplusy+1)*xplusy/2 x from (
                            select FLOOR((SQRT(8*(rownum-1)+1)-1)/2)
                                                               xplusy,
                                   rownum-1 t from LOCATIONS
                   )
         )
      )

and the output

max(Z)

Enjoy plan analysis. I still owe you Euclid algorithm.

Sent via Deja.com
http://www.deja.com/ Received on Wed Feb 07 2001 - 04:51:18 CET