Re: "Union all" and "UNION" ???? Return the same result or not ??
Date: 30 Jan 2004 06:12:18 -0800
Message-ID: <7b208869.0401300612.7ba6b9e0_at_posting.google.com>
Sorry about the delay -- I was a bit busy...
Anyway,here I am now:
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
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
(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
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 - 15:12:18 CET