Re: Cartesian Joins

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.

A practical example.

You do a CREATE TABLE with a UNRECOVERABLE SELECT. The result table needs to have a column that contains a fixed value (which in turn is used for partitioning when adding the results table to the partitioned table).

If you add this fixed value (literal or number) to the SELECT, Oracle determines the column type. So you may get a NUMBER instead or a NUMBER(8). Or a VARCHAR2 instead of a CHAR(5). Try a couple of CREATE TABLE AS SELECTs using literals and numbers and attempt to control the resulting column's data type.. you can not.

So how do you force the column's type to be what you want? Cartesian join.

You create an interim table. One column. With the data type you want. You insert into this a single row with the fixed value you want.

Then you perform a cartesian join on this table as part of the CREATE TABLE AS SELECT statement - now you are guaranteed to have that fixed value in the resulting table with the correct data type.

Oracle is not just about normalised designs (which btw is a Very Good Thing). It is also about processing data. Often large volumes of data in Oracle needs to be processed (especially in the Data Warehouse and Decision Support environment). You can try and do that (and mostly fail) by writing external applications (Pro*C, Delphi, Visual Basic, whatever). Or you can do that by using Oracle itself - it has a rich set of tools and you can do the most amazing things via "plain" SQL (which is never as plain as people at times think).

--
Billy