Excessive BUFFER sort

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. Received on Fri Mar 16 2007 - 10:54:13 CDT