Re: Why do cartesians reselect rows from BOTH tables?

You said explicitely that Oracle has to evaluate a function for the value of num. Now, each time oracle needs that value, it does exactly that. The resultset needs the value for num 18 times, so you get it 18 times

hth

Rene Nyffenegger

> 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
>

-- 
  Projektleitung und Entwicklung in Oracle/C++/C# Projekten
  http://www.adp-gmbh.ch/cv.html