Hi,
I had a user complain about a query which was hanging(they had to cancel it after 8hours). The database is 1og R2 on a Solaris box with 32G mem and 8CPUs. The query uses one view which I figured was taking up most of the time

I think Oracle goes for the merge join cartesian as it sees low cardinality. Maybe not the best option in this case. This is the explain plan when I implement the above suggestions. It seems to get worse