MERGE JOIN CARTESIAN on one row causes lots of 'direct path write temp'

Hello,

We're running a query on 11.2.0.2 on IBM/AIX...

It looks like:

select ...

FROM schema1.ABC abc, schema2.DEF def

WHERE def.type = 'N'

Table DEF contains 2 rows, with type='N' it returns one row; *no* columns from DEF appear

in the SELECT clause; and as you noticed - no join between the two tables.

ABC contains almost 9,900,000 rows. None of them is temporary: just regular tables. The

statement can't be modified (it's made by a tool, dozens of tables are processed the same

way, sometimes this cartesian join makes sense - but here in this case it doesn't indeed,