Re: Using temporary tables for query results

Hi everyone,

Thanks for the answers so far. Thought I'd reply to some of the queries raised here.

1. Main issue: I'd like to know if there's a more efficient way for the following problem: a) I would like to make a query COMPLEX_QUERY_1. b) I'd like to use the results of that query in the following query: SELECT * from COMPLEX_QUERY_1, OTHER_TABLES_1 WHERE CONSTRAINTS_1 UNION SELECT * from COMPLEX_QUERY_1, OTHER_TABLES_2 WHERE CONSTRAINTS_2 UNION SELECT * from COMPLEX_QUERY_1, OTHER_TABLES_3 WHERE CONSTRAINTS_3

Is there a cleaner option that works across Oracle and SQLServer, besides using a temp table to store the results of COMPLEX_QUERY_1 ?

Like, Oracle MAY be intelligent enough to figure out that the COMPLEX_QUERY_1 SQL is being repeated thrice, but is there a sure-fire way of telling Oracle to re-use the results? Like maybe some memory caching?

Ideally, I'd have liked to use Views, but as we know, Views dont cache results, so that doesnt help. Also, parametrized views are not part of ANSI-SQL, and infact not even supported in SELECT statements in Oracle.

Again, this solution may not be generic, but I sure would like to know the best way to implement it on Oracle and SQLServer, using ANSI SQL or otherwise.

2. I'd like to go with ANSI SQL as much as possible. We're dealing with a product here, and I'd like to stay standard as much. ANSI SQL 92 was supposed to have given a standard way to create/use temp tables, but for some reason SQLServer does not support it as per specification.

Generating SQL specific for each database is always an option, but one i'd like to resort to last (i.e. 2nd, after trying ANSI SQL :)).

3. Nope Jim, I dont use auto-commit! :) My application is transaction aware.

situ

Tim X <timx_at_spamto.devnul.com> wrote in message news:<87vg1xuqyr.fsf_at_tiger.rapttech.com.au>...
> drmiller100_at_hotmail.com (Doug Miller) writes:
>
> >
> > basically, there are no performance differences between using temp
> > tables and "permanent" tables that get dropped. There might be a teeny
> > bit of overhead involved, but in the order of less then one second
> > difference in response time for a big query.
> >
>
> I don't think this is necessarily true, especially with
> Oracle. If you are using 8i or greater, then look at temporary global
> tables. These are more efficient than a 'normal/permanent' table
> because redo logs are not created for inserts/updates to the table -
> on a large enough table, this can be a big hit on performance. Also,
> the global temp table is put into a different tablespace which avoids
> fragmenting your main tablespace etc.
Received on Mon Dec 16 2002 - 07:39:55 CET