Re: How to avoid a temporary table

pete_at_mynix.org wrote:

>Daniel Morgan <damorgan_at_x.washington.edu> writes:
>
>
>
>>My sense, after more than 30 years in this business, is that your
>>response to Oracle
>>
>>temporary tables is more driven by a lack of understanding Oracle than
>>anything else.
>>
>>
>
>Indeed, that is what I always read, and is exactly what is puzzling
>me.
>
>
>
>>Temporary tables are almost always unnecessary and when the are
>>required the Oracle
>>
>>temporary tables are a far more efficient solution than performing DDL
>>on-the-fly.
>>
>>
>>Why don't you post the issue and let us take a look at it.
>>
>>
>
>OK, I'll start with one issue. My real examples are too complex (I'd
>need 5 tables or more with many indexes) to post, so I have to come up
>with some artificial examples.
>
>One of them, that could be done using "full outer join" if those would
>not crash oracle 50% of the time and/or have very bad performance, is:
>
>insert into tmp_tab(c1,c2)
>select v1,v2 from some_tab;
>
>insert into tmp_tab(c1,c3)
>select v1,v2 from some_other_tab;
>
>select c1,sum(c2),sum(c3) from tmp_tab group by c1;
>
>Yes, you could do
>
>select a.v1,sum(a.v2),sum(b.v2)
>from (select v1,v2 from some_tab) a full outer join
>(select v1,v2 from some_other_tab) b using(v1)
>group by v1
>
>however for more real world examples, full outer join does not work
>(yet), using 9.2.0.3 or 9.2.0.4.
>
>
>Also, w.r.t. modularization, you can envisage that I write come
>complex statement in a SP as a cursor with some parameters. Now I can
>call the SP and the cursor is used to fill a temporary table.
>
>Then I call another SP that adds to the same temporary table. You
>could never write it in "one giant" sql statement without duplicating
>SQL; it also violates modularization and makes your SQL code harder to read/write.
>
>Other examples are things like: I can make a join between three
>tables, and then use updates to involve information from other
>tables. If I would have to to it without intermediate result in a
>temporary table, I would need to write a query involving 10 tables,
>which beats the CBO, and it is very complex in such cases to give the
>correct hints.
>
>Just as a basic point: for me storing intermediate results in
>temporary tables is essential to break down large and complex queries,
>in order to apply the "divide and conquer" principle as is done
>anywhere (except in pure SQL).
>
>I am not looking for a sulution for a particular problem.
>
>I'm just wondering what other GERNEAL techniques exist, if any, to
>break down complex queries into manageable and modular pieces without
>'resorting' to those "unnecessary" temporary tables.
>
>Thanks.
>
>

Well Howard likes temp tables and justifies it with the fact that Oracle does it. I use them
occassionally but do it because in those specific situations it is the only/best solution for the
problem. Not once, however, does it involve DDL which forces serialization and kills both
performance and scalability.

I have been performing full outer joins in Oracle since version 7. Probably in version 6 but
my memory isn't that good.

It can be done using UNION ALL and it can be done using in-line views. Either technique
will be substantially faster and more scalable than temp tables. If you are unfamiliar with
using in-line views I'd be happy to post an example or two.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)