Re: Cartesian Joins

You can also do some useful matrix algebra. Suppose you have a price table containing three price forecasts and a production table containing four product levels. If you join them together you get twelve revenue forecasts.

create table price (price number);

insert into price values(10);
insert into price values(50);
insert into price values(100);

create table prod (prod number);

insert into prod values(1000);
insert into prod values(2000);
insert into prod values(3000);
insert into prod values(4000);

select price, prod, price*prod;

    ROWNUM PRICE PROD PRICE*PROD ---------- ---------- ---------- ----------

         1         10       1000      10000
         2         50       1000      50000
         3        100       1000     100000
         4         10       2000      20000
         5         50       2000     100000
         6        100       2000     200000
         7         10       3000      30000
         8         50       3000     150000
         9        100       3000     300000
        10         10       4000      40000
        11         50       4000     200000
        12        100       4000     400000

"Martin Doherty" <martin.doherty_at_oracle.com> wrote in message news:3D8666B6.1B7816DD_at_oracle.com...
> Partial cartesian joins can be quite useful, for example to produce the
correct
> number of names and addresses for a mailing label run (e.g. from Oracle
Reports)
>
> Have a table 'CARTESIAN' like this:
> CARDINALITY
> -----------------
> 1
> 2
> 3
> etc
>
> and another table 'CUSTOMER' like this:
> NAME, ADDRESS
>
> Then,
> select cust.name, cust.address
> from customer cust, cartesian cart
> where cart.cardinality <= 2
>
> This will generate 2 labels per customer. If the customer table has a
column
> called label_quantity, then a variable # of labels can be produced per
customer.
>
> I have also found cartesian joins useful when I want to produce large
result
> data sets from small amounts of input data (for volume testing).
>
> Martin Doherty
>
> Bob McAlone wrote:
>
> > Can anyone come up with a scenario where a cartesian join is good? A lot
of
> > the manuals say that is is _probably_ an error / bad design which
implies
> > that it does have a use. Every case me and the guys at work come up with
can
> > be better solved with a half decent normalised design.
> >
> > Cheers
> > Bob
>
Received on Mon Sep 16 2002 - 20:29:05 CDT