IFX_FLAT_UCSQ

Has anybody on this list any experience with or info about environment variable IFX_FLAT_UCSQ (flatten uncorrelated subqueries)? All I found is http://www-1.ibm.com/support/docview.wss?rs=630&context=SSGU8G&dc=DB520&uid=swg21209359&loc=en_US&cs=UTF-8&lang=en which is pretty shallow. Michael

Good question. Reading the note it appears that this happens automatically if the optimizer thinks it will be faster but this flag forces it to choose this path. To see if it will help try flattening sample queries from your system yourself and test unflattened against flattened take into account caching. I think flattening a query looks like this: Original Query: select * from planet where planet_id in (select planet_id fromplanet_life where intelligence = 't'); Flattened query: select * from planet where exists(select * from planet_life whereplanet.planet_id = planet_life.planet_id and planet_life.intelligence = 't'); Or could it be: select distinct planet.* from planet, planet_life where planet.life_id= planet_life.life_id and planet_life.intelligence = 't' ; Also, check performance of the unflattened query with and without the flag on. See if the flattened cost looks like the cost of the flattened queries. Let me know how it comes out. I am curious.

Michael: I recently had an opportunity to take great advantage of this feature IFX_FLAT_UCSQ and it significantly improved performance. The characteristics where many queries in which FIRST_ROW optimization was important. It flatten the queries so that a different join and index to be used which helped both the join performance and removed the requirement of doing a sort (the index provided the order). John Miller Michael Mueller wrote: > Has anybody on this list any experience with or info about environment > variable IFX_FLAT_UCSQ (flatten uncorrelated subqueries)? All I found is > > http://www-1.ibm.com/support/docview.wss?rs=630&context=SSGU8G&dc=DB520&uid=swg21209359&loc=en_US&cs=UTF-8&lang=en > > > which is pretty shallow. > > Michael

Thanks John. I would like to know this: Why is this undocumented and off by default? Is it save to use / supported / tested? Maybe someone can answer this? Michael John Miller wrote: > Michael: > > I recently had an opportunity to take great advantage of > this feature IFX_FLAT_UCSQ and it significantly > improved performance. The characteristics where many queries > in which FIRST_ROW optimization was important. > > It flatten the queries so that a different join and > index to be used which helped both the join performance > and removed the requirement of doing a sort (the index provided > the order). > > > John Miller

bozon wrote: > Good question. Reading the note it appears that this happens > automatically if the optimizer thinks it will be faster but this flag > forces it to choose this path. I am sure flattening of uncorrolated subqueries (all, some types? don't know!) is off by default. Setting IFX_FLAT_UCSQ to anything (even empty) turns it on. The note is more confusing than helpful. The writer did not even tell what the "UCSQ" means (uncorrelated subqueries, as opposed to corrolated ones).