Re: How to avoid a temporary table

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. Received on Sat Oct 25 2003 - 03:52:03 CDT