ORACLE bug on Views with Outer Joins

We have recently encountered a serious bug in which a user process is blown off the system as the result of a signal stack dump.

After doing a bit of researching, and with Oracle's help, the problem is that SQL*Plus is using up all of its stack space and then dying when it executes a HALT command (we are on VMS).

This problem occurs when one is using a view with some complex outer joins AND each of the base tables used by the view has had the ANALYZE TABLE xxx COMPUTE STATISTICS command executed on them. It seems that when statistics exist for the base tables, one can get this particular stack dump when using these type of views.

This is a level 2 bug. The workaround is to DELETE all of the statistics on the base tables used by the view. Of course, that sort of shoots the neat idea of a cost-based optimizer, but I guess one cannot have their cake and eat it too. The other workaround is to rewrite your view and select to execute without dumping. Received on Mon May 24 1993 - 22:48:26 CEST