On Mar 16, 10:54 am, "S L Niemann" <slniem..._at_gmail.com> wrote:
> I have an unusual query that shows an awful cost.
> Optimizer Cost Cardinality Bytes
> SELECT STATEMENT ALL_ROWS 103631 39190235 3840643030
> HASH(GROUP BY) 39190235 3840643030
> CONCATENATION
> MERGE JOIN(CARTESIAN) 51831 19603151 1921108798
> MERGE JOIN(CARTESIAN) 366 46017 3819411
> TABLE ACCESS(BY INDEX ROWID)AM ANALYZED 3 1 23
> INDEX(RANGE SCAN)INDX_Y ANALYZED 2 1
> BUFFER(SORT) 363 68464 4107840
> TABLE ACCESS(FULL)OM ANALYZED 363 68464 4107840
> BUFFER(SORT) 51468 426 6390
> TABLE ACCESS(FULL) AMO ANALYZED 1 426 6390
> MERGE JOIN(CARTESIAN) 51788 19587083 1919534134
> MERGE JOIN(CARTESIAN) 366 45979 3816257
> TABLE ACCESS(BY INDEX ROWID)AM ANALYZED 3 1 23
> INDEX(RANGE SCAN)INDX_Y ANALYZED 2 1
> BUFFER(SORT) 363 68464 4107840
> TABLE ACCESS(FULL)OM ANALYZED 363 68464 4107840
> BUFFER(SORT) 51425 426 6390
> TABLE ACCESS(FULL) AMO ANALYZED 1 426 6390
> NESTED LOOPS 12 4 392
> HASH JOIN 8 4 152
> TABLE ACCESS(BY INDEX ROWID)AM ANALYZED 4 2 46
> INDEX(RANGE SCAN)INDX_Y ANALYZED 2 4
> TABLE ACCESS(FULL) AMO ANALYZED 3 426 6390
> TABLE ACCESS(BY INDEX ROWID)OM ANALYZED 1 1 60
> INDEX(UNIQUE SCAN) PK_OI ANALYZED 0 1
>
> The query does join all three tables explicitly by their foreign keys.
> But the buffer sorts are showing at 50K from a full table scan off of
> one of the parent tables. It has a primary key and should be a full
> table scan as there (other than the join) is no limiter on the table.
> The three tables are M:M parents with the joined middle 'child'.
>
> running 10gr2 base with the manual DST patch.
Double checking - this may be moot.. looks like a subquery was using
OR in a bad area, causing cartesian joins
Received on Fri Mar 16 2007 - 11:54:54 CDT
