Home -> Usenet -> comp.databases.oracle -> Using temporary tables for query results

Using temporary tables for query results

From: situ <sry_at_hotmail.com>
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)



    
  
  1.   We do a complex query that returns some results. This query is a
UNION of multiple SQLs.
a) We had initially thought of using Views, but because we needed to
pass parameters to this View, and parametrized Views are not supported
in all datbases, we could not use this option.
b) We thought of using Functions, but again, this is a DB-specific
feature, and support varies between databases (SQLServer supports use
of UDF (User Defined Functions) in the FROM clause in SELECTs, Oracle
does not)
c) We have currently decided that the best way to implement this is to
generate the SQL in our program (Java code) and execute it against the
database.






Question: 


Please let me know if you have a better solution for this above
requirement, than to dynamically generate the SQL in Java.



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



SELECT * from COMPLEX_QUERY_1, OTHER_TABLES_2 WHERE CONSTRAINTS_2
UNION



SELECT * from COMPLEX_QUERY_1, OTHER_TABLES_3 WHERE CONSTRAINTS_3



[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:


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?



Thanks in advance!


situ
Received on Fri Dec 13 2002 - 11:55:12 CET












Original text of this message