Using temporary tables for query results
Date: 13 Dec 2002 02:55:12 -0800
Message-ID: <31c0a2d1.0212130255.5fc2f1ac_at_posting.google.com>
Hi,
We have the following requirement that needs to be supported across
multiple databases (Oracle and SQLServer to start with)
Question:
2. Secondly, we are using the above complex query's results, as input
in a second complex query. The 2nd query goes like:
SELECT * from COMPLEX_QUERY_1, OTHER_TABLES_1 WHERE CONSTRAINTS_1
UNION
[FYI: we tried using OR and JOINs, but LEFT OUTER JOIN etc did not
solve this requirement, since different tables (OTHER_TABLES_1 etc)
are required in each case; therefore, we had to settle for UNION]
Effectively, we are having to execute COMPLEX_QUERY_1 multiple times.
One option I'm currently thinking of is to use a temporary table which
would cache the results of COMPLEX_QUERY_1, which can then be simply
queried in COMPLEX_QUERY_2
Question:
Thanks in advance!
Please let me know if you have a better solution for this above
requirement, than to dynamically generate the SQL in Java.
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 more efficient approach than using Temporary tables?
Is this feature standardized across all databases? I read that
SQLServer deletes temp tables at the end of the transaction, while
Oracle does not. What are the performance-related implications of
using Temporary tables?
situ
Received on Fri Dec 13 2002 - 11:55:12 CET