Re: Union View Optimizer Creates Temp Table

Thank for the reply mark. I was able to get the Optimizer to abandon the temp-table approach after reorganizing the logic a bit. The original view had several queries against large tables combined with 'Union All'. Each of the queries were grouping with aggregate expressions and contained 'having' clauses. I moved the aggregation logic to a secondary view and the optimizer no longer has a problem with it. Mark D. Stock wrote in message ... > >Brian Foster wrote: > >> Hi, >> >> I have gathered from previous posts, that the optimizer will not create a >> temp table to process a view if it does not have to eliminate duplicates >> (union all). I have not found that to be the case. Can anyone shed some >> light on this? The example below is a very simple case. > >It's probably far TOO simple. With so few records, and no filter, you are >likely to skip all indexes in favour of sequential scans. > >> My production >> tables have several million rows (A & B) and the view is an unusable pig. I >> am running 9.20 UC3. > >Can we see the query plan for the real query then? > >Cheers, >-- >Mark. > >+----------------------------------------------------------+-----------+ >| Mark D. Stock mailto:mdstock@MydasSolutions.com |//////// /| >| Mydas Solutions Ltd http://MydasSolutions.com |///// / file://| >| +-----------------------------------+//// / ///| >| |We value your comments, which have |/// / ////| >| |been recorded and automatically |// / /////| >| |emailed back to us for our records.|/ ////////| >+----------------------+-----------------------------------+-----------+ > >sending to informix-list