Re: IFX_FLAT_UCSQ

>> I am sure flattening of uncorrolated subqueries (all, some types? don't know!) is off by default. This is not correct. I am sure that any query can be flattened if the cost says to do it that way. Also read the excellent link that Bill Dare included: http://www.iiug.org/waiug/archive/iugnew83/FeaturesIDS73.htm >From your link: ****specifically**** The optimizer looks at information such as the distribution and the calculated cost of the query to try to determine if subquery flattening will increase the performance of the query. <b> Occasionally, better performance can be gained by using subquery flattening even if the optimizer decides against it. <b> emphasis mine. ****specifically**** It seems clear to me that the optimizer can choose to subquery flatten any time it wants. This flag just says ignore the cost estimate and flatten the query if it can be flattened. It is kind of like the use_hash optimizer hint. The optimizer always has the option to use hash joins but it won't unless it thinks that it is cheaper to do so than other kinds of joins. That is why I said test your specific queries. Based on what I read, it looks like this will only help if your queries fool the optimizer.