Temp table question

How expensive is it to use an explicit temp table (select ... into temp ...) followed by drop table ..., vs an implicitly created temp table created within a select for use by an order by clause? How does this compare to creating a view (which will require the use of an implicit temp table)? I will only use the object once and will then drop it. In my sql I essentially need to count both the number of unique items (multiple rows in the result set per item) and the average number of rows/item in the result set - something like select count(f0.id)/count(distinct f0.id), count(distinct f0.id) from foo f0 where f0.val = 'A' or f0.val = 'B'; I can't put two count distinct expressions in the same query with IDS 7.3 which is what I'm using. I need to sort the result set on this funky key. If I the temp table costs to much, I suppose I can just as well sort it in the application. In some other vendors products I can use an implicit view, something like select a/b,b from (select count(f0.id) a,count(distinct f0.id) b from foo f0 where f0.val = 'A' or f0.val = 'B'); Informix doesn't appear to support this syntax. Cheers

Jay Walters wrote: > > How expensive is it to use an explicit temp table (select ... into temp > ...) followed by drop table ..., vs an implicitly created temp table > created within a select for use by an order by clause? > > How does this compare to creating a view (which will require the use of > an implicit temp table)? > > I will only use the object once and will then drop it. > > In my sql I essentially need to count both the number of unique items > (multiple rows in the result set per item) and the average number of > rows/item in the result set - something like > > select count(f0.id)/count(distinct f0.id), > count(distinct f0.id) > from foo f0 > where f0.val = 'A' or f0.val = 'B';> > I can't put two count distinct expressions in the same query with IDS > 7.3 which is what I'm using. > > I need to sort the result set on this funky key. If I the temp table > costs to much, I suppose I can just as well sort it in the application. > > In some other vendors products I can use an implicit view, something > like > > select a/b,b > from (select count(f0.id) a,count(distinct f0.id) b > from foo f0 where f0.val = 'A' or f0.val = 'B'); > > Informix doesn't appear to support this syntax. Use the temp table. Informix's parallel sort capability will probably result in faster execution anyway. Your query does access the temp table in a complex fashion that will benefit from not having to deal with rows in the original table that you do not care about simply because they reside on the same page as rows you do care about. Bottom line though is to do what the rest of us do: try it out and see which is the best way. Remember Kagel's First Law of SQL: There are AT LEAST three ways to express ANY SQL query. If you have not found three, keep looking, there may be a better way. First correllary: If you are using a programming language there is at least one additional method substituting program logic for SQL. The point you can only know the best way to perform a query by trying them all. Art S. Kagel