Re: Why do cartesians reselect rows from BOTH tables?

Actually, what I think the query says explicitly is ...

1. get the value of num for all the rows in table T (call that resultset A).
2. get the value of num for all the rows in table T (call that resultset B).
3. produce the cartesian of those two result sets.

Given that, the result should actually look something like this (but I'd settle for what I posted originally) ...

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

           1            4
           1            5
           1            6
           2            4
           2            5
           2            6
           3            4
           3            5
           3            6

Richard

Rene Nyffenegger wrote:
>
> 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
Received on Tue Mar 04 2003 - 17:22:53 CST