Re: "Union all" and "UNION" ???? Return the same result or not ??

Sorry about the delay -- I was a bit busy... Anyway,here I am now:
If you read my initial proposal carefully, you'll notice that the where-clauses are *inside* the subqueries. That way, you'd first limit the intermediate result sets, and then sort them. There comes your performance gain:
SELECT * from
(SELECT COL1 , COL2 ,REC_NO FROM TEST

 WHERE COL1 = :val1 AND COL2 = :val2 AND REC_NO > :val3  UNION ALL
 SELECT COL1 , COL2 ,REC_NO FROM TMP_TEST  WHERE COL1 = :val1 AND COL2 = :val2 AND REC_NO > :val3  ORDER BY COL1 ASC, COL2 ASC, REC_NO ASC )
where ROWNUM < 2

Now you should expect to see two PK index range scans, concatenated, and sorted with a STOPKEY option in the execution plan.

Some notes on the results: If two identical rows exist in the tables
(one in each table), and they both pass the where-clause criteria, and
they have the minimum values in col1, col2, and rec_no, this query will return only one of them. Which one, is undefined. You may not care about this, since the records are identical, but you might want to make this distinction, if in your *production* version you select more than the key columns of these tables. IOW, the question answered by this query is: I want at most one record from either of the tables TEST and TMP_TEST. This record should have the minimum values in col1, col2, and rec_no among all records that pass my predicates. I don't care from which table that record comes. As you notice, this is not exactly "the top record". It's only one of the (potentially two, because of the unique constraints) top records.

If you decide that you actually want both such records, you need another solution. Using rownum<3 will not do the trick. Let me know if this is the case. Or better yet, read Tom Kyte's "Effective Oracle by Design"

Regards,
Flado Received on Fri Jan 30 2004 - 08:12:18 CST