IFX_FLAT_UCSQ
Michael Mueller asked about the undocumented IFX_FLAT_UCSQ variable, which flattens uncorrelated subqueries. bozon guessed at flattened forms of queries. John Miller said it significantly improved performance for FIRST_ROW queries by enabling a different join and index and avoiding a sort. Michael found it off by default, enabled by setting it to anything, and asked if it was supported or tested; unanswered.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
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).