Why do cartesians reselect rows from BOTH tables?

From: Richard Kuhler <noone_at_nowhere.com>
Date: Tue, 04 Mar 2003 20:45:10 GMT
Message-ID: <qf89a.31616$zb.9803581@twister.socal.rr.com>

Why does this query ...

select a.num, b.num
from (

    select p.f as num
    from t
  ) a,
  (
    select p.f as num
    from t
  ) b
/

produce this output ...

         NUM NUM
------------ ------------

           1            2
           3            4
           5            6
           7            8
           9           10
          11           12
          13           14
          15           16
          17           18

Notice that Oracle seems to be re-executing BOTH queries multiple times. Shouldn't the correct answer should look something like this? ...

         NUM NUM
------------ ------------

           1            2
           1            3
           1            4
           5            6
           5            7
           5            8
           9           10
           9           11
           9           12

I'm trying to get a set of values from a query that's extremely expensive to execute (takes several seconds) and use those values for all the rows of another query (millions of them). A simple cartesian of that one result row with each row from the other table is what I want but Oracle is re-executing the query that returns just 1 row for every row in the other table (no matter which order I process them in the nested loops).

Any ideas?

Test script ...

create or replace package p is

    function f return number;

end;
/

show errors

create or replace package body p is

    v integer := 0;

    function f return number is
    begin

        v := v + 1;
        return v;

    end;

end;
/

create table t (id number)
/

insert into t values (1)
/

insert into t values (2)
/

insert into t values (3)
/

select a.num, b.num
from (

    select p.f as num
    from t
  ) a,
  (
    select p.f as num
    from t
  ) b
/

SQL*Plus: Release 9.2.0.2.0 - Production Oracle9i Enterprise Edition Release 9.2.0.2.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.2.0 - Production

Thanks,
Richard Received on Tue Mar 04 2003 - 14:45:10 CST