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

flado_at_imail.de (Vladimir Andreev) wrote in message news:<7b208869.0401300612.7ba6b9e0_at_posting.google.com>...
> 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

My problem is I need to know exactly "the top" record then perform sequential update later. That is why the original design is like this: (SELECT COL1 , COL2 ,REC_NO FROM TEST
 UNION
 SELECT COL1 , COL2 ,REC_NO FROM TMP_TEST )  WHERE (COL1 = :val1 AND COL2 = :val2 AND REC_NO > :val3 ) AND ROWNUM < 2
ORDER BY COL1 ASC, COL2 ASC, REC_NO ASC And it causes performance problem when the records is getting huge. (In my case, millions of records in each table.)

I think if I can get "the top" record from one table and the other separately,
then get the exactly "top one" record by comparing the 2 records. However,
just like "UNION", sorting seems can't be avoided if I need to get the "top one" from each table. Again, that generates performance problem when the records is huge.

May be like what you said , read the book and see if can get some good ideas...... Received on Sat Jan 31 2004 - 11:43:01 CET