Re: Cartesian Joins

Christian Habermehl wrote:

> Hi,
>
> I try to join 3 tables. Oracle 9.2 does always a cartesian join and
> I don't know why.
>
> here ist my Query:
> SELECT productpcat.productid
> FROM pcat pcatlink, pcat, productpcat
> WHERE pcatlink.treekey LIKE 'AA%'
> AND pcat.treekey LIKE pcatlink.symlinktargetpcattreekey || '%'
> AND productpcat.pcatid=pcat.id
> GROUP BY productpcat.productid;
>
> and the execution plan:
> 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=72 Card=5957 Bytes=148925)
> 1 0 SORT (GROUP BY) (Cost=72 Card=5957 Bytes=148925)
> 2 1 HASH JOIN (Cost=37 Card=5957 Bytes=148925)
> 3 2 TABLE ACCESS (FULL) OF 'PCAT' (Cost=2 Card=343 Bytes=2744)
> 4 2 MERGE JOIN (CARTESIAN) (Cost=33 Card=119134 Bytes=2025278)
> 5 4 TABLE ACCESS (BY INDEX ROWID) OF 'PCAT'
> (Cost=2 Card=1 Bytes=10)
> 6 5 INDEX (RANGE SCAN) OF 'IDX_PCAT_TREEKEY' (NON-UNIQUE)
> (Cost=1 Card=1)
> 7 4 BUFFER (SORT) (Cost=31 Card=339 Bytes=2373)
> 8 7 INDEX (FAST FULL SCAN) OF 'PK_PRODUCTPCAT' (UNIQUE)
> (Cost=31 Card=339 Bytes=2373)
>
> the query takes about 40 seconds. When I do it via

Oracle performs a Cartesian join because that is exactly what you have asked it to do: Not a single inner or outer join between the tables in your statement.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)