Optimizer for Dataware house applications

I had problems using both the optimizer modes for a dataware house application.
The problem using cost based optimizer is it requires lot of temp tablespace to perform the Hash join/Sort-merger join( for 200 gb database it needs 200GB TEMP) . When using rule based optimizer, the query is not optmized preoperly like choosing the wrong driving table/or wrong indexes. All the queries are adhoc in nature and generated by a middleware based on the user request, so I can't give the hints/or tune the query.

I am interested to know how this problem is handled and what is the best option.

Thanks in advance

Reddy M. Received on Sat Jan 11 1997 - 00:00:00 CST