Hi,

We have the following requirement that needs to be supported across multiple databases.

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
http://www.itreya.com

Regarding temporary tables: Oracle has a very different approach to temporary tables from SQL Server's, but you can use them for this.
The Oracle approach is:

1) Create the temporary table once only, not via the application, like this:

CREATE GLOBAL TEMPORARY TABLE t ( id NUMBER PRIMARY KEY, name VARCHAR2(30) )
ON COMMIT DELETE ROWS; /* or ON COMMIT PRESERVE ROWS */

2) In the application, INSERT the necessary data into the global temporary table. Do not use CREATE TABLE.

3) The data will be automatically deleted by Oracle at the end of the transaction (or at the end of your session if you used PRESERVE ROWS)

4) Do not DROP the table in the application.

Hi Tony,

Thanks for your reply. I'll look into using GLOBAL TEMPORARY TABLES for both Oracle and SQLServer, and clearing up the data at the end of each transaction (either automated as you've specified for Oracle, or manual for SQLServer AFAIK)

Also, besides using temp tables, 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.

situ

There is another way available in Oracle 9i, I'm not sure if it is ANSI standard and I have no idea whether it guarantees re-use of results, but you could read up on that:

WITH complex_query_1 AS (SELECT ... /* Complex query 1 */ )
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

This is like defining an in-line view called complex_query_1 BEFORE running the main query.

Hi Tony,

Thanks, thats a neat one... but since we need to support Oracle8i too, guess we need to look at global temp tables...

On the whole i guess the DBs as always are moving faster than the standard can cope up... hence all the non-standard syntax. Any pointers from people working on multi-DB apps - do you guys detect the DB and use the most optimal? Isnt that a maintenance nightmare against using standard SQL?

situ